CODEWITHSUNDEEP
sqlpostgresqltime-series
Sigma
Sigma

Reputation: 800

How to get only one result with postgres lag() function

I have the following table which stores historical prices for stocks

 stock_id |  open  | close  |  high  |  low   |      timestamp
----------+--------+--------+--------+--------+---------------------
        2 |    338 |    330 |    338 |    330 | 2022-10-21 05:30:00
        2 |    341 |    338 |    341 |    338 | 2022-10-20 05:30:00
        2 | 340.05 |    340 |    341 |    340 | 2022-10-19 05:30:00
        2 |    357 |    340 |    357 |    340 | 2022-10-18 05:30:00
        2 |    358 |    358 |    358 |    358 | 2022-10-12 05:30:00

I want to get the 1 day change from the previous day by using values of last 2 record and window function LAG() so I came up with the following query

SELECT stock_id,
       close as last_price,
       timestamp::DATE,
       LAG(close) OVER (PARTITION BY stock_id
                        ORDER BY timestamp desc) AS one_day_change
FROM historical_prices WHERE stock_id = 2;

But this me all the change not just the latest record

stock_id | last_price | timestamp  | one_day_change
----------+------------+------------+----------------
        2 |        330 | 2022-10-21 |
        2 |        338 | 2022-10-20 |            330
        2 |        340 | 2022-10-19 |            338

What I want instead is this

stock_id | last_price | timestamp  | one_day_change
----------+------------+------------+----------------
       2 |        330 | 2022-10-21 |            338

What would be the best way to accomplish this? Maybe LAG() is not suitable for this usecase?

Upvotes: 0

Views: 117

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 247370

A window function doesn't reduce the number of result rows like an aggregate function does. It just adds another column to the result set.

You should sort ascending by timestamp, so that lag means the previous day. Then add an outer query that only selects the row with the latest timestamp:

SELECT stock_id, last_price, timestamp, one_day_change
FROM (SELECT stock_id,
             close as last_price,
             timestamp::DATE AS timestamp,
             LAG(close) OVER (ORDER BY timestamp) AS one_day_change
      FROM historical_prices
      WHERE stock_id = 2) AS subq
ORDER BY timestamp DESC
FETCH FIRST 1 ROWS ONLY;

Upvotes: 1

Marc
Marc

Reputation: 11633

Limit to just the first (i.e., chronologically latest) result.

SELECT stock_id,
       close as last_price,
       timestamp::DATE,
       LAG(close) OVER (PARTITION BY stock_id
                        ORDER BY timestamp desc) AS one_day_change
FROM historical_prices
WHERE stock_id = 2
ORDER BY timestamp desc
LIMIT 1;

Upvotes: 0

Related Questions