Snowflake带条件求和：多账户用户存在活跃账户时的总支付统计

实现逻辑 #

• 第一步：按CustomerEmail分组，判断每个邮箱是否存在至少1个活跃账户
• 第二步：过滤出符合条件的邮箱，对所有账户的Total_Paid求和得到累计支付总金额

完整SQL代码 #

WITH user_active_flag AS (
    SELECT 
        CustomerEmail,
        Total_Paid,
        -- 标记该邮箱是否有至少一个活跃账户
        MAX(Active) OVER (PARTITION BY CustomerEmail) AS has_active_account
    FROM your_table_name -- 此处替换为实际业务表名
)
SELECT 
    CustomerEmail,
    SUM(Total_Paid) AS total_paid_all_accounts
FROM user_active_flag
WHERE has_active_account = TRUE
GROUP BY CustomerEmail;

适配说明 #

如果你的表中Active字段为字符串类型（例如存储值为'Y'/'N'），可以将CTE中的打标逻辑调整为如下写法即可：

MAX(CASE WHEN Active = 'Y' THEN 1 ELSE 0 END) OVER (PARTITION BY CustomerEmail) AS has_active_account

外层过滤条件同步调整为WHERE has_active_account = 1

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