为何在Snowflake中使用SQL无法正确按user_id合并分组交易数据?
核心问题定位
导致PD/CN交易拆分为两行、字段出现空值的原因有两点:
- 原
GROUP BY子句错误纳入了trans_type和trans_id字段,不同交易类型的记录会被划分到独立分组,无法按用户+交易时间维度合并为单行 - 聚合逻辑写法错误,
sum()函数嵌套在CASE判断内部,没有基于公共分组维度做条件聚合,导致空值无法被正确填充到对应字段位置
可直接落地的改造后SQL
使用Snowflake完全兼容的条件聚合写法,无需修改原业务过滤规则,替换原有语句即可实现合并效果:
select atr.user_id , date_trunc('day', atr.trans_date) trans_day , atr.year_month , max(case when atr.trans_type = 'PD' then atr.trans_id end) as PD_transaction_id , sum(case when atr.trans_type = 'PD' then atr.amount else 0 end) as PD_amt , max(case when atr.trans_type = 'CN' then atr.trans_id end) as CN_transaction_id , sum(case when atr.trans_type = 'CN' then atr.amount else 0 end) as CN_amt from wisen_data.sm_account_trans atr where ((trans_type = 'CN' and trans_sub_type = 'HSNMLP') or trans_type in ('PP','PD')) and atr.status not in ('VOIDED','FAILED') and atr.trans_date >= date_trunc('month', current_date-3*365) group by atr.user_id, trans_day, atr.year_month order by trans_day
写法说明
- 移除了原语句中
GROUP BY里的trans_type、trans_id字段,仅保留用户ID、交易日期、年月三个公共分组维度,从根源上避免不同交易类型被拆分为独立分组 - 将CASE判断逻辑嵌套在聚合函数内部:同分组下所有PD交易的ID、金额会被聚合到对应的PD字段中,所有符合规则的CN交易的ID、金额会被聚合到对应的CN字段中,两类数据共存于同一用户的单行记录内,不会再出现跨行列空值的情况
- 交易ID字段使用
max()聚合是为了兼容同分组下存在多条同类型交易的场景,不会因为聚合逻辑报错;如果需要无对应交易时金额字段返回空值,可将SUM逻辑里的else 0去掉
落地校验提示
执行后可优先抽查目标用户的输出结果:确认同一用户同一交易时间维度下,PD和CN字段是否共存于单行、无跨行列空值,确认金额合计值和源表对应筛选条件下的汇总值一致即可。
内容的提问来源于stack exchange,提问作者joeyp
相关产品推荐
相关产品推荐

