SQL 計算累積欄位：以 store、store1、store2 為例的查詢寫法

需求

資料表 t_test(id, store)，需產生：

• store1 = 該行及之前所有 store 之和（前綴和）。
• store2 = 該行及之前所有 store1 之和（二階前綴和）。

單一查詢實現

SELECT
    id,
    store,
    (SELECT SUM(store) FROM t_test b WHERE b.id <= a.id) AS store1,
    (SELECT SUM(store1) FROM (
        SELECT id, (SELECT SUM(store) FROM t_test c WHERE c.id <= b.id) AS store1
        FROM t_test b
    ) d WHERE d.id <= a.id) AS store2
FROM t_test a
ORDER BY id;

效能建議

資料量大時，建議使用視窗函數（Oracle/PostgreSQL/SQL Server 2012+）：

SELECT
    id,
    store,
    SUM(store) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) AS store1,
    SUM(SUM(store) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING))
        OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) AS store2
FROM t_test;