我有下表存盤股票的歷史價格
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
我想通過使用最后 2 條記錄和視窗函式的值來獲得前一天的 1 天變化，LAG()所以我想出了以下查詢
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 不只是最新記錄
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
我想要的是這個
stock_id | last_price | timestamp | one_day_change
---------- ------------ ------------ ----------------
2 | 330 | 2022-10-21 | 338
實作這一目標的最佳方法是什么？也許LAG()不適合這個用例？
uj5u.com熱心網友回復：
視窗函式不會像聚合函式那樣減少結果行的數量。它只是將另一列添加到結果集中。
您應該按升序排序timestamp，這lag意味著前一天。然后添加一個僅選擇具有最新時間戳的行的外部查詢：
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;
uj5u.com熱心網友回復：
僅限于第一個（即按時間順序排列的最新）結果。
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;
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/522619.html