Is there LAG window function or equivalent in QuestDB?

Community
,
1

I’m trying to query the diff between row values, in SQL it would be something like

SELECT timestamp, price - LAG(price) OVER (ORDER BY timestamp) as price_diff
from trades

But QuestDB returns Window function expected error at the position of LAG.

Is there a similar window function in QuestDB that can work instead of LAG?

2

You can use first_value window function like to simulate LAG function:

first_value(value) OVER (ORDER BY timestamp ROWS 1 PRECEDING) as prev_value

... same as

LAG(value) OVER (ORDER BY timestamp)
3

Is there a way to calc time difference between rows? first_value only accepts double.

4

It is not ideal, but you will likely need to cast fron timestamp (a long) to a double, and then back later. We have an open issue to expand type support for first_value.

1 Like