Linked Questions
11 questions linked to/from Referencing current row in FILTER clause of window function
147
votes
6
answers
199k
views
PostgreSQL unnest() with element number
When I have a column with separated values, I can use the unnest() function:
myTable
id | elements
---+------------
1 |ab,cd,efg,hi
2 |jk,lm,no,pq
3 |rstuv,wxyz
select id, unnest(string_to_array(...
- 3,997
47
votes
1
answer
36k
views
PostgreSQL: running count of rows for a query 'by minute'
I need to query for each minute the total count of rows up to that minute.
The best I could achieve so far doesn't do the trick. It returns count per minute, not the total count up to each minute:
...
- 18.5k
9
votes
3
answers
23k
views
Conditional lead/lag function PostgreSQL?
I have a table like this:
Name activity time
user1 A1 12:00
user1 E3 12:01
user1 A2 12:02
user2 A1 10:05
user2 A2 10:06
user2 A3 10:07
user2 M6 ...
- 101
5
votes
1
answer
2k
views
Window functions filter through current row
This is a follow-up to this question, where my query was improved to use window functions instead of aggregates inside a LATERAL join. While the query is now much faster, I've found that the results ...
- 406
2
votes
2
answers
1k
views
PySpark Multiple Columns Using Windows
I have a Dataframe as follows:
|id |date_1 |date_2 |
+---+----------+-----------+
|0 |2017-01-21|2017-04-01 |
|1 |2017-01-22|2017-04-24 |
|2 |2017-02-23|2017-04-30 |
|3 |2017-02-27|2017-04-...
- 23
1
vote
1
answer
2k
views
Moving window/average based on timestamps in PostgreSQL
My question is the same as the one asked here, except the chosen answer goes "assuming you want to restart the rolling average after each 15 minute interval." What if I don't? I.e. what if I want a ...
- 427
2
votes
3
answers
257
views
Select finishes where athlete didn't finish first for the past 3 events
Suppose I have a database of athletic meeting results with a schema as follows
DATE,NAME,FINISH_POS
I wish to do a query to select all rows where an athlete has competed in at least three events ...
- 44.3k
2
votes
1
answer
1k
views
Optimize slow aggregates in LATERAL join
In my PostgreSQL 9.6.2 database, I've got a query that builds a table of calculated fields from some stock data. It calculates a moving average window of 1 through 10 years for each row in the table, ...
- 406
1
vote
0
answers
1k
views
Windows Function Filter Clause in Redshift
I have following table in a Redshift. I am trying to implement a filter clause in windows function that is described over here - Referencing current row in FILTER clause of window function but filter ...
- 33
5
votes
0
answers
456
views
PostgreSQL Window Function: How to reference a column from the current row vs. a column from the frame row?
[db<>fiddle link]
I have the following data, by group, each row having an increasing index, and containing a limit and a value:
CREATE TABLE items ("group" VARCHAR, index INTEGER, "limit" INTEGER,...
- 185
0
votes
2
answers
106
views
SQL recursive CTEs to perm array
I'm having some trouble formulating the correct recursive behaviour to get what I want in SQL.
I'm limited to the BigQuery environment and I want to avoid using any JavaScript if I can, so I wanted to ...
- 1,693