具有 30 天範圍的滾動 SUM

我正在使用 SQL Server 2012，並且有一個大約 200 萬行的數據庫。我需要弄清楚如何計算 30 天範圍內的滾動總和並返回設定數量 ( amt_pur) 內的任何結果。這些是交易訂單，因此 ID 號 ( ID_NO) 是相同的，但交易日期 ( TD) 有時會以分鐘到多年的時間變化。因此，如果客戶在任何 30 天內訂購了 10 件商品，我需要這些結果。我嘗試了這樣的事情和許多Google搜尋的變體：

select id_no, td, 
sum(amt_pur) as amtpur from db1 where td between td and dateadd(day,30,td)
group by id_no, td, amt_pur having sum(amt_pur)>10 
order by amtpur desc

這不會讓我得到滾動類型的計算結果。我有一個可以驗證的特定 ID，所以我知道我沒有得到正確的 ID。請幫忙！

我沒有對此進行廣泛測試，但我認為它可以滿足您的需求。這裡我假設 ID_NO 是客戶標識符，TD 是下單日期，AMT_PUR 代表下單金額。當客戶在從那天開始的 30 天日期範圍內訂購了 10 件或更多商品時，您需要所有行。

下面是一些測試數據：

CREATE TABLE DB1 (
ID_NO VARCHAR(10) NULL,
TD DATETIME NULL,
AMT_PUR INTEGER NULL
);

BEGIN TRANSACTION;
INSERT INTO DB1 VALUES ('A', '01/01/2016', 3);
INSERT INTO DB1 VALUES ('A', '01/05/2016', 1);
INSERT INTO DB1 VALUES ('A', '01/09/2016', 2);
INSERT INTO DB1 VALUES ('A', '02/01/2016', 7);
INSERT INTO DB1 VALUES ('A', '02/02/2016', 2);
INSERT INTO DB1 VALUES ('A', '03/01/2016', 2);
INSERT INTO DB1 VALUES ('A', '03/04/2016', 1);
INSERT INTO DB1 VALUES ('B', '02/01/2016', 3);
INSERT INTO DB1 VALUES ('B', '02/28/2016', 6);
INSERT INTO DB1 VALUES ('B', '03/05/2016', 3);
INSERT INTO DB1 VALUES ('B', '03/28/2016', 6);
COMMIT TRANSACTION;

假設您剛剛以反向 TD 順序遍歷每個 ID_NO 並跟踪 AMT_PUR 的執行總和。您不能只保留執行總和大於或等於 10 的行，因為之前添加到總數中的某些行可能不在目前行的 30 天內。但是，如果有一種方法可以刪除離目前行太遠的行，那麼您可以只使用執行總和，並且您可以通過一次數據傳遞來進行計算。

這種方法背後的想法是將所有數據的兩個副本聯合在一起。第二個副本將反轉 AMT_PUR 值，並從原始值中減去 31 天。使用該格式的數據，您只需通過一次即可執行計算。我們可以使用帶有 OVER() 子句的 SUM() 以這種方式循環數據。

SELECT
ID_NO
, TD
, REVERSE_RUNNING_SUM
FROM
(
   SELECT 
     ID_NO
   , TD
   , SUM(AMT_PUR) OVER (PARTITION BY ID_NO ORDER BY TD DESC, VALID_FLAG ASC) REVERSE_RUNNING_SUM
   , VALID_FLAG
   FROM 
   (
       SELECT ID_NO
       , TD
       , AMT_PUR
       , 1 VALID_FLAG
       FROM db1

       UNION ALL

       SELECT ID_NO
       , DATEADD(DAY, -31, TD) TD
       , -1 * AMT_PUR
       , -1 VALID_FLAG
       FROM db1
   ) t
) tt
WHERE tt.VALID_FLAG = 1
AND tt.REVERSE_RUNNING_SUM >= 10;

次要更新：將 db1 交叉連接到兩行表而不是使用 UNION ALL 可能會更有效，但我將在我的程式碼中保留 UNION ALL 方法，因為我認為這更容易理解。

引用自：https://dba.stackexchange.com/questions/149135