計算欄位值沒有變化的天數

請考慮下表：

我們有約翰的三條記錄和傑克的兩條記錄。在每個日期中，使用者要麼向金額欄位添加一個值（正值），要麼從中減去一個值（負值）。

金額總和未發生變化的天數很重要。

例如，從1/1/2022到1/2/2022，dear John 的值是 40。但是在1/2/2022，金額減少了 30。因此，30 天（從1/1到1/2 ), 數量等於 40, 直到1/6 , 總量 ( 40 - 30 ) 保持不變約 120 天。最後，直到今天（今天是8/23），金額的總和將等於40 - 30 + 50 = 60。

---

問題：

• 如何計算這些間隔以獲得天數？
• 如何為所有使用者獲得這些間隔？

我閱讀了有關 Window 或 CTE 的資訊，但無濟於事。

我為什麼問這個問題？

數據庫記錄的數量已經增加到大約一百萬條，對於 21,000 個使用者，我們必須獲取每個區間的長度並乘以之前記錄的總量。

---

預期輸出：

例如，對於 John，我希望得到以下輸出：

40 × 30(days) + (40 - 30) × 60 (days) + (40 - 30 + 50) × 53 (days)

為了回答您的問題，我執行了以下操作（下面的所有程式碼都可以在此處的小提琴中找到）：

CREATE TABLE test
(
 t_user TEXT    NOT NULL,
 t_date DATE    NOT NULL,
 amount INTEGER NOT NULL
);

填充：

INSERT INTO test VALUES
('John', '2022-01-01',  40),
('John', '2022-02-01', -30),
('John', '2022-06-01',  50);

第一關：

我留下了幾個額外的欄位，以便您可以遵循邏輯 - 我已將它們刪除以進行最後的傳遞。

SELECT
 t_user, 
 t_date, 

 CASE
   WHEN LEAD(t_date) OVER w IS NULL
     THEN current_date
   ELSE LEAD(t_date) OVER w
 END AS ld,
 
 CASE
   WHEN LEAD(t_date) OVER w is null
     THEN current_date - t_date
   ELSE LEAD(t_date) OVER w - t_date
 END AS d_cnt,
 amount,
 
 SUM(amount) OVER (PARTITION BY t_user ORDER BY t_date) AS rr_tot

FROM
 test
WINDOW w AS (PARTITION BY t_user ORDER BY t_date)
ORDER BY t_user DESC, t_date;

結果：

t_user      t_date          ld  d_cnt  amount  s_tot
John    2022-01-01  2022-02-01     31      40     40
John    2022-02-01  2022-06-01    120     -30     10
John    2022-06-01  2022-08-23     83      50     60

• r_tot（執行總計）跟踪每個日期範圍內的總計，r_tot * d_cnt這是最後一次通過中的乘法步驟 ( )所必需的
• 該WINDOW w子句只是（真的）有助於易讀性的一點語法糖

最後一關：

SELECT
 t_user, 
 SUM(r_tot * d_cnt)
FROM
(
 SELECT
   t_user, 
   CASE
     WHEN LEAD(t_date) OVER w is null
       THEN current_date - t_date
     ELSE LEAD(t_date) OVER w - t_date
   END AS d_cnt,
   amount,
   SUM(amount) OVER (PARTITION BY t_user ORDER BY t_date) AS r_tot
 FROM
   test
 WINDOW w AS (PARTITION BY t_user ORDER BY t_date)
 
) AS sub1
GROUP BY t_user
ORDER BY t_user;

結果：

t_user   sum
John    7420

有幾點需要注意：

• 請使用ISO 8601 標準日期 - 該格式是YYYY-MM-DD- 它還使您的程式碼更易於移植、普遍理解和明確。標準的存在是有原因的，除非您有充分的理由不使用，否則應該使用標準！
• 將來，您能否提供一下您的表格和數據？這為問題提供了一個單一的事實點 ( SPOT )，並代表那些試圖幫助您的人節省了重複工作
• 不要使用關鍵字（和/或保留字） ，例如DateandUser作為列名或表名 - 這會使 SQL 的可移植性降低、可讀性降低並且更容易出錯

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