星期三, 6月 21, 2023

[SQL] 累計加總應用 - 金額拆帳

在論壇上看見的問題 - SQL計算金額問題,題意如下圖

[SQL] 累計加總應用 - 金額拆帳

個人解法如下,論壇上有大神提供解法,相較下大神是比較優雅點,但核心精神都是透過 SUM() OVER 來進行專案金額累計後,再來進行數學運算
SELECT
  T3.* ,
  專案金額 - 已收金額 AS 未收款
FROM
  (
    SELECT
      T2.* ,
      CASE	
        WHEN 專案累積金額 <= 已收總金額 THEN 專案金額
        WHEN 專案累積金額 > 已收總金額 AND 已收總金額 - 前一筆累計金額 > 0 THEN 已收總金額 - 前一筆累計金額
        ELSE 0
      END AS 已收金額
    FROM
      (
        SELECT
          T.* ,
          LAG(專案累積金額 , 1 , 0) OVER (PARTITION BY 結帳單 ORDER BY 專案) AS 前一筆累計金額
        FROM	
          (
            SELECT 
              * ,
              SUM(專案金額) OVER (PARTITION BY 結帳單 ORDER BY 專案 ROWS UNBOUNDED PRECEDING) AS 專案累積金額
            FROM TestTable
          ) AS T
      ) AS T2
  ) AS T3
ORDER BY 結帳單, 專案

2 則留言:

  1. 基本上使用 OVER 速度就是最快了,很難再改的更好

    但是為什麼不使用 CTE?
    這樣看的好痛苦

    回覆刪除
  2. 使用 CTE 確實會增加可讀性,不會這麼多層 ^_^

    回覆刪除