我有一個包含這些列的表：id、sku、價格、日期
CREATE TABLE price_log (
id int NOT NULL PRIMARY KEY,
sku text NOT NULL,
date date NOT NULL,
price real NOT NULL,
CONSTRAINT date_sku UNIQUE (sku,date)
)
我想得到一份加班費變化的報告。
我想要的輸出是：
sku old_price new_price change_date
A 10 11 2022-01-03
B 1 5.99 2022-01-02
B 5.99 3.5 2022-01-03
B 3.5 12 2022-01-04
現在我能做的最好的就是使用LAG()函式：
WITH cte AS (
SELECT sku, price, MAX(date) AS date
FROM price_log
GROUP BY sku, price
)
SELECT sku, price, date,
LAG(price,1) OVER (
PARTITION BY sku
ORDER BY date
) AS old_price
FROM cte
小提琴手
uj5u.com熱心網友回復：
select sku
,old_price
,new_price
,change_date
from (
select sku
,lag(price) over(partition by sku order by date) as old_price
,price as new_price
,date as change_date
from price_log
) price_log
where old_price <> new_price
| 庫存單位 | 舊價格 | 新的價格 | 改變日期 |
|---|---|---|---|
| 一個 | 10 | 11 | 2022-01-03 |
| 乙 | 1 | 5.99 | 2022-01-02 |
| 乙 | 5.99 | 3.5 | 2022-01-03 |
| 乙 | 3.5 | 12 | 2022-01-04 |
小提琴
uj5u.com熱心網友回復：
嘗試以下操作：
WITH CTE AS
(
SELECT sku, price, date,
LAG(price,1,price) OVER (PARTITION BY sku ORDER BY date) AS old_price
FROM price_log
)
SELECT sku, old_price, price AS new_price, date AS change_date
FROM CTE
WHERE price <> old_price
查看演示。
uj5u.com熱心網友回復：
有 N 種方法可以做到這一點。例如，一個是使用橫向：
select p1.sku, t.old_price, p1.price as new_price, p1.date as change_Date
from price_log p1,
lateral (select price from price_log p2
where p1.sku = p2.sku and
p1.date > p2.date
order by date desc
limit 1) t(old_price);
DbFiddle 演示
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/504848.html
標籤：sql PostgreSQL
下一篇：在tsvector中拆分字串