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

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?

CodePudding user response：

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;

CodePudding user response：

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;