DB-小提琴
CREATE TABLE logistics (
id SERIAL PRIMARY KEY,
time_stamp DATE,
product VARCHAR(255),
quantity INT
);
INSERT INTO logistics
(time_stamp, product, quantity)
VALUES
('2020-01-14', 'Product_A', '100'),
('2020-01-14', 'Product_B', '300'),
('2020-01-15', 'Product_B', '400'),
('2020-01-15', 'Product_C', '350'),
('2020-01-16', 'Product_B', '530'),
('2020-01-16', 'Product_C', '350'),
('2020-01-16', 'Product_D', '670'),
('2020-01-17', 'Product_C', '500'),
('2020-01-17', 'Product_D', '980'),
('2020-01-17', 'Product_E', '700'),
('2020-01-17', 'Product_F', '450');
預期結果：
time_stamp | difference | info
------------|----------------|--------------------------------------
2020-01-14 | 400 | =(100 300)
2020-01-15 | 350 | =(400 350)-(300 100)
2020-01-16 | 800 | =(530 350 670)-(400 350)
2020-01-17 | 1080 | =(500 980 700 450)-(530 350 670)
我想計算兩者之間的總差。
因此，我嘗試使用來自這個問題的查詢：quantitytimestamps
SELECT
t1.time_stamp AS time_stamp,
t1.quantity AS quantity,
COALESCE(t1.quantity-LAG(t1.quantity) OVER (PARTITION BY t1.time_stamp ORDER BY t1.time_stamp), t1.quantity) AS difference
FROM
(SELECT
l.time_stamp AS time_stamp,
SUM(l.quantity) AS quantity
FROM logistics l
GROUP BY 1
ORDER BY 1) t1
GROUP BY 1,2
ORDER BY 1,2;
但是，我無法獲得預期的結果。
我需要更改什么才能使其正常作業？
uj5u.com熱心網友回復：
查詢呼叫中的partition by子句是多余的。lag內部查詢已經按時間戳分組，因此外部查詢不需要（也不應該！）進一步劃分結果。
使用partition by子句時，每次lag呼叫僅應用于一個時間戳（因為它們在外部查詢中已經是唯一的），并且您會得到錯誤的結果。
洗掉它，你應該沒問題：
SELECT
t1.time_stamp AS time_stamp,
t1.quantity AS quantity,
COALESCE(t1.quantity-LAG(t1.quantity) OVER (ORDER BY t1.time_stamp), t1.quantity) AS difference
FROM
(SELECT
l.time_stamp AS time_stamp,
SUM(l.quantity) AS quantity
FROM logistics l
GROUP BY 1
ORDER BY 1) t1
GROUP BY 1,2
ORDER BY 1,2;
DBFiddle 演示
uj5u.com熱心網友回復：
您可以洗掉函式PARTITION BY中的LAG。
如果“產品”列是結果的一部分。那么PARTITION BY product就有意義了。
但既然不是這樣，那就ORDER BY t1.time_stamp足夠了。
SELECT t1.time_stamp AS time_stamp, t1.quantity AS quantity, COALESCE(t1.quantity-LAG(t1.quantity) OVER (ORDER BY t1.time_stamp), t1.quantity) AS difference FROM (SELECT l.time_stamp AS time_stamp, SUM(l.quantity) AS quantity FROM logistics l GROUP BY 1 ORDER BY 1) t1 GROUP BY 1,2 ORDER BY 1,2;
時間戳 | 數量 | 不同之處 :--------- | --------: | ---------: 2020-01-14 | 400 | 400 2020-01-15 | 750 | 350 2020-01-16 | 1550 | 800 2020-01-17 | 2630 | 1080
db<>在這里擺弄
uj5u.com熱心網友回復：
你可以使用這個：
select time_stamp
,s-COALESCE(LAG(t1.s) OVER (ORDER BY t1.time_stamp),0) as difference
from
(select time_stamp,sum(quantity) as s
from logistics
group by time_stamp)t1
輸出：
time_stamp difference
2020-01-14 400
2020-01-15 350
2020-01-16 800
2020-01-17 1080
uj5u.com熱心網友回復：
您也可以使用 with 子句，首先計算總和，然后更容易計算滯后：
with _sum as (
SELECT distinct
logistics.time_stamp AS time_stamp,
sum(quantity) over (PARTITION by time_stamp) as summe
from logistics
order by time_stamp
)
select
time_stamp,
summe - coalesce(lag(summe) over (order by time_stamp),0)
from _sum
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/415502.html
標籤：