SQL Server 2012窗口函数计算运行总计

Question

我需要一些关于窗口函数的帮助.

我最近一直在玩sql 2012窗口函数.我知道您可以计算窗口内的总和以及窗口内的运行总和.但我在想;是否可以计算先前的运行总数,即不包括当前行的运行总数？我假设您需要使用ROW或RANGE参数,我知道有一个CURRENT ROW选项,但我需要一个CURRENT ROW – 我的语法无效.我对ROW和RANGE参数的了解是有限的,所以任何帮助都会被感激不尽.

我知道这个问题有很多解决方案,但我希望了解ROW,RANGE参数,我认为这些问题可以解决.我已经提供了一种可能的方法来计算以前的运行总量,但我想知道是否有更好的方法.

USE AdventureWorks2012

SELECT s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty,SUM(s.OrderQty) OVER (PARTITION BY  SalesOrderID) AS RunningTotal,SUM(s.OrderQty) OVER (PARTITION BY  SalesOrderID 
                         ORDER BY SalesOrderDetailID) - s.OrderQty AS PrevIoUsRunningTotal
    -- Sudo code - I know this does not work
    --,SUM(s.OrderQty) OVER (PARTITION BY  SalesOrderID 
    --                   ORDER BY SalesOrderDetailID
    --                   ROWS BETWEEN UNBOUNDED PRECEDING 
    --                                   AND CURRENT ROW - 1) 
    -- AS  SudoCodePrevIoUsRunningTotal
FROM Sales.SalesOrderDetail s
WHERE SalesOrderID IN (43670,43669,43667,43663)
ORDER BY s.SalesOrderID,s.OrderQty

提前致谢USE AdventureWorks2012

SELECT s.SalesOrderID,s.OrderQty

Answer 1

您可以减去当前行的值：

SUM(s.OrderQty) OVER (PARTITION BY SalesOrderID
                      ORDER BY SalesOrderDetailID) - s.OrderQty

或者根据syntax at MSDN和ypercube's answer：

<window frame preceding> ::= 
{
    UNBOUNDED PRECEDING
  | <unsigned_value_specification> PRECEDING
  | CURRENT ROW
}

- >

SUM(s.OrderQty) OVER (PARTITION BY SalesOrderID
                      ORDER BY SalesOrderDetailID
                      ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)

SQL Server 2012窗口函数计算运行总计

解决方法

猜你在找的MsSQL相关文章