在SQL Server 2014上使用over子句运行总计

母亲

我使用以下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] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章