CODEWITHSUNDEEP
sqlpostgresqlselectwindow-functions
Geert-Jan
Geert-Jan

Reputation: 18895

How to express window frame in SQL window function relating to property value of current row?

Consider a table of events of form: id,price,timestamp. For each event I'd like to get the min(price) of all events that happen between [curEvent.timestamp, curEvent.timestamp + 10sec]

It seems SQL window functions would be a logical candidate.

For example, if I was sure how many events after the current event I want to inspect to calculate the min(price) this would be done with something like this: 

SELECT *,min(price) OVER (ORDER BY timestamp ROWS BETWEEN 1 FOLLOWING AND 100 FOLLOWING) AS min_price from events

However, I don't know the number of events/rows up front. Instead, I want it to be variable based on [curEvent.timestamp, curEvent.timestamp + 10sec]

It this at all possible?

Upvotes: 1

Views: 415

Answers (2)

S-Man
S-Man

Reputation: 23676

PostgreSQL 11 solution:

demo: db<>fiddle

SELECT
    price, 
    ts,
    min(price) OVER (ORDER BY ts RANGE BETWEEN CURRENT ROW AND INTERVAL '10 seconds' FOLLOWING)
FROM times

Postgres 11 adds the functionality of RANGE BETWEEN INTERVALs for window functions which does exactly what you expect (documentation):

Sample data (first 2 columns) and result (3rd column):

price   ts                      min
1       2018-09-09 10:00:00     1
2       2018-09-09 10:00:04     2
100     2018-09-09 10:00:09.8   10
200     2018-09-09 10:00:10     3.5
20      2018-09-09 10:00:11     3.5
10      2018-09-09 10:00:19     3.5
3.5     2018-09-09 10:00:20     3.5
35      2018-09-09 10:00:21     35

Further reading, JOOQ Blog

Upvotes: 2

Mureinik
Mureinik

Reputation: 311308

I can't think of a way to do this with a window function, but you could use a corrolated subquery instead:

SELECT *,
       (SELECT MIN(price)
        FROM   events
        WHERE  ts BETWEEN e.ts AND e.ts + INTERVAL '10 SECOND')
FROM   events e

Upvotes: 1

Related Questions