Snowflake中如何计算截至各年月的累计收入除以累计独立用户数

你的原始SQL仅计算了当月的总金额和独立账户数的比值，要实现从数据起始到对应年月的累计值，需要拆分两步计算：累计总金额、累计独立账户数，再将两者做除法。

方法一：高效分步计算（推荐，适合大数据量） #

该方法通过先统计账户首次下单月份，再分别计算累计总金额和累计独立账户数，最后关联结果得到目标值：

WITH 
-- 1. 按月统计当月总金额，并计算累计总金额
monthly_gross AS (
    SELECT
        TO_CHAR(DATE_TRUNC('month', orderdate::date), 'yyyy-mm') AS year_month,
        SUM(grossamount) AS monthly_gross
    FROM orders
    GROUP BY year_month
    ORDER BY year_month
),
cumulative_gross AS (
    SELECT
        year_month,
        SUM(monthly_gross) OVER (ORDER BY year_month) AS total_cumulative_gross
    FROM monthly_gross
),
-- 2. 找出每个账户的首次下单年月
first_order AS (
    SELECT
        accountid,
        TO_CHAR(DATE_TRUNC('month', MIN(orderdate::date)), 'yyyy-mm') AS first_order_month
    FROM orders
    GROUP BY accountid
),
-- 3. 统计到每个年月为止的累计独立账户数
cumulative_accounts AS (
    SELECT
        mg.year_month,
        COUNT(fo.accountid) AS total_cumulative_accounts
    FROM monthly_gross mg
    LEFT JOIN first_order fo ON fo.first_order_month <= mg.year_month
    GROUP BY mg.year_month
    ORDER BY mg.year_month
)
-- 4. 关联累计总金额和累计账户数，计算最终平均值
SELECT
    cg.year_month,
    cg.total_cumulative_gross,
    ca.total_cumulative_accounts,
    cg.total_cumulative_gross::FLOAT / ca.total_cumulative_accounts AS avg_cumulative_gross_per_account
FROM cumulative_gross cg
JOIN cumulative_accounts ca ON cg.year_month = ca.year_month;

方法二：窗口函数快速实现（适合小数据量） #

如果你的数据库支持窗口函数中的COUNT(DISTINCT)（如PostgreSQL），可以用更简洁的方式实现：

WITH monthly_data AS (
    SELECT
        TO_CHAR(DATE_TRUNC('month', orderdate::date), 'yyyy-mm') AS year_month,
        SUM(grossamount) AS monthly_total,
        ARRAY_AGG(DISTINCT accountid) AS monthly_accounts
    FROM orders
    GROUP BY year_month
    ORDER BY year_month
)
SELECT
    year_month,
    -- 累计总金额
    SUM(monthly_total) OVER (ORDER BY year_month) AS cumulative_gross,
    -- 累计独立账户数：合并所有历史月份的账户数组后去重计数
    (SELECT COUNT(DISTINCT acct) FROM unnest(ARRAY_AGG(monthly_accounts) OVER (ORDER BY year_month)) AS acct) AS cumulative_unique_accounts,
    -- 计算累计平均值
    SUM(monthly_total) OVER (ORDER BY year_month)::FLOAT / 
    (SELECT COUNT(DISTINCT acct) FROM unnest(ARRAY_AGG(monthly_accounts) OVER (ORDER BY year_month)) AS acct) AS result
FROM monthly_data;

关键说明 #

• 累计总金额：通过窗口函数SUM() OVER (ORDER BY year_month)实现按月累加
• 累计独立账户数：核心是统计所有在当前年月及之前下单过的唯一账户，方法一中通过"首次下单月份"过滤，避免重复计数；方法二中通过合并历史账户数组后去重实现
• 除法时转换为FLOAT是为了避免整数除法导致结果被截断

内容的提问来源于stack exchange，提问作者parmeni4