我使用以下T-SQL子查询来计算的运行总量RefId
。
由于我的表有超过2M的行,因此我想使用OVER
子句重写查询。
但是我无法得到相同的结果。whenCreationDate
不是唯一的RefId
,结果不正确,因为前一个具有相同日期的行被添加到总和中。
有解决方案以获得正确的结果吗?
样本数据集:
CREATE TABLE Command
(
CreationDate SMALLDATETIME,
RefId INT,
Qte INT
);
INSERT INTO Command VALUES('2021-01-10', 100, 10);
INSERT INTO Command VALUES('2021-01-11', 100, 20);
INSERT INTO Command VALUES('2021-01-11', 100, 60);
INSERT INTO Command VALUES('2021-01-11', 100, 10);
INSERT INTO Command VALUES('2021-01-12', 100, 20);
INSERT INTO Command VALUES('2021-01-10', 200, 20);
INSERT INTO Command VALUES('2021-01-11', 200, 10);
INSERT INTO Command VALUES('2021-01-12', 200, 20);
INSERT INTO Command VALUES('2021-01-12', 200, 10);
子查询结果正确
SELECT
c1.*,
(SELECT SUM(c2.Qte)
FROM Command c2
WHERE c1.RefId = c2.RefId
AND c2.CreationDate < c1.CreationDate) AS RunninTotal_Qte
FROM
Command c1
ORDER BY
c1.RefId, c1.CreationDate
查询用OVER
子句重写;结果不正确
SELECT
c1.*,
SUM(c1.Qte) OVER (PARTITION BY c1.RefId ORDER BY c1.CreationDate
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS RunningTotal_Qte
FROM
Command c1
ORDER BY
c1.RefId, c1.CreationDate
如果需要的话,您已经执行的方法会更加高效,这是仅使用窗口函数的方法:
SELECT c1.*,
SUM(c1.Qte) OVER (PARTITION BY c1.RefId ORDER BY c1.CreationDate)
- SUM(c1.Qte) OVER (PARTITION BY c1.RefId, c1.CreationDate ORDER BY c1.CreationDate) AS RunningTotal_Qte
FROM Command c1
ORDER BY c1.RefId, c1.CreationDate
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句