SQLで累積値や累計を日次で算出する方法

サンプルクエリ

WITH lp_cust AS (
    SELECT DISTINCT 
        customer_id,
        MIN(day) as first_hit
    FROM
        [Page Hitsのテーブル]
    WHERE
    	[抽出条件]
    GROUP BY
        customer_id
)

SELECT
    first_hit as date,
    SUM(uni_viewers) OVER (
        ORDER BY first_hit ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as cum_viewers
FROM
    (
        SELECT
            first_hit,
            COUNT(DISTINCT customer_id) as uni_viewers
        FROM
            lp_cust
        GROUP BY
            first_hit
    )

説明

lp_custはCustomer IDとそのCustomerが最初に該当のPageを訪れた日の組み合わせを算出したテーブル。
lp_custのデータから、日付毎にCIDをカウントし、その後 SUM(uni_viewers) OVER ( ORDER BY first_hit ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) で、first_hitを昇順に並べ、頭行(unbounded preceding)から現在行(current row)までuni_viewersをsumしcum_viewersを求める処理になります。