SQL统计DWH事实表按客户月度拆分的各类型交易数问题求助

实现方案 #

错误原因分析 #

原有语句的子查询未绑定外层分组的customer_id和month维度，导致子查询每次返回的都是全表对应交易类型的总计数，而非当前分组下的统计值。

最优实现：条件聚合（兼容绝大多数SQL引擎） #

直接在分组后通过CASE WHEN判断交易类型，再统计对应计数即可，性能远高于关联子查询或多表JOIN的写法：

SELECT
  customer_id AS Customer,
  month AS Month,
  COUNT(CASE WHEN transaction_type = 1 THEN transaction_id END) AS transaction_type_1,
  COUNT(CASE WHEN transaction_type = 2 THEN transaction_id END) AS transaction_type_2,
  COUNT(transaction_id) AS Total_transactions
FROM DWH
GROUP BY customer_id, month
ORDER BY customer_id, month;

逻辑说明

• CASE WHEN transaction_type = 1 THEN transaction_id END：只有交易类型为1时才返回交易ID，否则返回NULL
• COUNT统计时会自动忽略NULL值，最终得到的就是当前客户、当月对应类型的交易数量
• 总交易数直接统计所有交易ID即可，不需要额外筛选

简化写法（适用支持IF函数的引擎，如MySQL/Spark SQL） #

如果你的数仓引擎支持IF函数，可以进一步简化语法：

SELECT
  customer_id AS Customer,
  month AS Month,
  COUNT(IF(transaction_type = 1, transaction_id, NULL)) AS transaction_type_1,
  COUNT(IF(transaction_type = 2, transaction_id, NULL)) AS transaction_type_2,
  COUNT(transaction_id) AS Total_transactions
FROM DWH
GROUP BY customer_id, month
ORDER BY customer_id, month;

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