673,749 questions
Score of 0
0 answers
65 views
How can a pivot table correctly compute rollups for non-additive measures like AVG and COUNT(DISTINCT)?
I am implementing a pivot table visualization for a BI application and am looking for the correct architectural approach to support rollups for non-additive measures.
The backend returns one ...
- reputation score 15
Score of 0
1 answer
104 views
SUM(amount) not working when also calling base column amount in SELECT clause
I want to create a query that shows the amount of an item kept at a specific location, but also the total combined amount of every location that has that item.
For example; if location 1 has 400 of a ...
- reputation score 91
Score of 0
2 answers
157 views
How to calculate relative volume (RVOL) efficiently in Postgres
Given the following schema:
Column
Type
Collation
Nullable
Default
id
bigint
not null
generated by default as identity
timestamp
integer
not null
open
double precision
not null
high
double ...
- reputation score 18
Score of 0
0 answers
158 views
ERROR 4016 (Internal error) on LEFT JOIN with NOT IN in ON clause
Update: Per comments, the original CASE NULL WHEN t0.c1 ... always evaluates to ELSE 6. Repro below uses 6 NOT IN (SELECT t0.c1 FROM t0) instead; PK vs no-PK behavior unchanged.
I'm seeing this on ...
- reputation score 11
Score of 1
0 answers
108 views
Ignite 3.1 SQL Join query performance issue
Environment:
Apache Ignite 3.1.0 (Native)
Two colocated tables with the same distribution zone.
Table details - PRODUCT_MAPPING:
CREATE TABLE PRODUCT_MAPPING
(
COMPANY_ID VARCHAR(10) ...
- reputation score 11
Score of -2
1 answer
150 views
Calculate dates of two different records in case expression [closed]
When the ContactType is TRINT5, I need to get that ContactDate up to 3 days from the ContactDate of ContactType TRINT2 to show 'Met' in my CASE expression. If more than 3 days, then 'Not Met'.
SELECT ...
- reputation score 37
Advice
1
vote
13
replies
144
views
Extract XML data from forms into tables - presence of namespaces in XML
I have a situation, where I am importing data stored in XML from a SQL Server table, into a temp table (#), also in SQL Server.
The sample form data is as below:
<ABCDE xmlns:i="http://www.w3....
- reputation score 465
Tooling
0
votes
4
replies
196
views
Replace values in SQL
How can I replace numerical values with NULL in SQL
You’re a junior data analyst working for a local news station and your team uses BigQuery to analyze weather trends. The station’s meteorologist ...
- reputation score 1
Score of 5
1 answer
193 views
Why are some device groups filtered out after aggregating a 10-minute subquery?
I have an alarm summary running on Apache IoTDB 2.0.8 table model. The SQL first builds 10-minute temperature buckets in a subquery, then the outer query counts how many buckets crossed the threshold. ...
- reputation score 98
Best practices
0
votes
8
replies
158
views
How to replace zero values with NULL in Google BigQuery without syntax errors?
My assignment is:
I am a junior data analyst working with a 2023 weather dataset in Google BigQuery. I need to analyze the relationship between wind speed and visibility. However, the raw data has ...
- reputation score 1
Score of 2
1 answer
153 views
JSON path query for full-text searching a value with language in another key
I have difficulty crafting a JSON path query that can be tuned using a Full-Text Search index effectively for what seems like a not too difficult use case. This would be for Postgres 14, but info on ...
- reputation score 2595
Score of 1
1 answer
155 views
Return the next OFF event after each ON event
I'm querying motion sensor data stored in GridDB. Each row represents a sensor state change.
Sample data:
dt
message
2021-06-30 10:00:00
ON
2021-06-30 10:00:13
OFF
2021-06-30 10:04:57
ON
2021-06-30 10:...
- reputation score 13
Best practices
0
votes
4
replies
66
views
Split overlapping date ranges and prioritize one record type over another
I have a table containing effective-dated records. For a given PID, I need to determine the correct CODE for any date based on a priority rule between PCD values.
Sample data:
PID
PCD
STDT
CODE
ETDT
...
- reputation score 163
Advice
0
votes
3
replies
89
views
Replicate SAS Merge happening in loop into Pyspark
I have an use case where I need to replicate a sas behaviour into pyspark. In Sas, the Merge between two datasets are happening and its not 1*1 key merge. Its a m*n key merge involving multiple keys. ...
- reputation score 331
Score of 1
2 answers
232 views
Query that shows if a member meets defined metrics from the metrics table
I am trying to figure out how to write a query that can show me if a member meets defined metrics from our metrics table. There is a table that holds member survey answers and we have business ...
- reputation score 125
