SQL 如何调整子查询匹配主查询分组,实现分渠道双周期毛利统计
问题根因
- 原查询的标量子查询未与主查询建立渠道维度关联:子查询没有筛选和当前主查询行匹配的
ga_mapping值,独立计算了2020年Q4全渠道的总毛利,因此所有行都会返回同一个全局聚合值,无法按渠道拆分统计。 - 子查询内的
group by 2属于无效写法:子查询的SELECT列表仅包含sum(os.margin)这一个聚合值,不存在可用于分组的第2列,这行代码不会触发任何渠道维度的聚合,等价于没有分组过滤逻辑。 - 附带的执行坑点:日期范围
2019-09-31为无效日期(9月仅30天),直接执行会触发日期格式/范围报错,需修正为2019-09-30。
调整方案
方法1:修正相关子查询匹配逻辑
直接在原SQL基础上补全子查询的关联条件、移除无效分组即可,改动最小:
SELECT os_main.ga_mapping as channel , SUM(os_main.margin) as margin_2019 , ( SELECT sum(os_sub.margin) FROM orders_summary os_sub WHERE os_sub.date_order between '2020-10-01' and '2020-12-31' -- 关键关联:匹配当前行对应的渠道,实现按行维度拆分聚合 AND os_sub.ga_mapping = os_main.ga_mapping ) as margin_2020 -- 加NULLIF避免除零错误 , ROUND(((margin_2020 - margin_2019) / NULLIF(margin_2019, 0)) * 100, 2) as rate_of_change FROM orders_summary os_main WHERE os_main.date_order BETWEEN '2019-07-01' AND '2019-09-30' GROUP BY os_main.ga_mapping;
注:这种写法的局限性是主查询WHERE仅筛选了2019周期的数据,会遗漏2019周期无交易、仅在2020周期有交易的渠道,统计维度不全。
方法2:生产环境推荐的条件聚合写法
通过条件判断在单次表扫描中完成两个周期的毛利统计,执行效率更高,统计维度更全:
SELECT ga_mapping as channel , SUM(CASE WHEN date_order BETWEEN '2019-07-01' AND '2019-09-30' THEN margin ELSE 0 END) as margin_2019 , SUM(CASE WHEN date_order BETWEEN '2020-10-01' AND '2020-12-31' THEN margin ELSE 0 END) as margin_2020 , ROUND( ( (SUM(CASE WHEN date_order BETWEEN '2020-10-01' AND '2020-12-31' THEN margin ELSE 0 END) - SUM(CASE WHEN date_order BETWEEN '2019-07-01' AND '2019-09-30' THEN margin ELSE 0 END)) / NULLIF(SUM(CASE WHEN date_order BETWEEN '2019-07-01' AND '2019-09-30' THEN margin ELSE 0 END), 0) ) * 100 , 2) as rate_of_change FROM orders_summary WHERE (date_order BETWEEN '2019-07-01' AND '2019-09-30') OR (date_order BETWEEN '2020-10-01' AND '2020-12-31') GROUP BY ga_mapping;
这种写法的优势:
- 仅需单次扫描订单表即可完成全量统计,避免相关子查询逐行匹配带来的性能损耗
- 覆盖所有在两个周期内有交易的渠道,不会因主查询的日期筛选遗漏维度
- 通过
NULLIF处理分母为0的场景,避免单渠道2019毛利为0时触发除零报错
内容的提问来源于stack exchange,提问作者Mitchell.Laferla
相关产品推荐
相关产品推荐

