SQL行转列统计客户月度活动时出现重复行问题排查

问题根因 #

你写的SQL核心问题是没有对按月份拆分的活动标识做聚合运算：

• 你在CASE语句中用到了PART_ID、ACTIVITY字段，这两个字段既没有加入GROUP BY的分组维度，也没有被聚合函数包裹，兼容该写法的数据库会按原始表的每行记录单独计算CASE结果，自然就会出现同一个客户对应多条记录的情况。
• 你的需求是同一个客户只要当月存在任意一条活动值大于0的记录就标记为1，需要对每个CASE的运算结果取最大值才能实现。

修正后的SQL #

SELECT *
FROM
(
    SELECT CUST_ID, RULLED_PROFIT_CENTER,
      MAX(CASE WHEN PART_ID = 202012 AND ACTIVITY > 0 THEN 1 ELSE 0 END) AS ARA_20,
      MAX(CASE WHEN PART_ID = 202101 AND ACTIVITY > 0 THEN 1 ELSE 0 END) AS OCA_21,
      MAX(CASE WHEN PART_ID = 202102 AND ACTIVITY > 0 THEN 1 ELSE 0 END) AS SUB_21,
      MAX(CASE WHEN PART_ID = 202103 AND ACTIVITY > 0 THEN 1 ELSE 0 END) AS MAR_21,
      MAX(CASE WHEN PART_ID = 202104 AND ACTIVITY > 0 THEN 1 ELSE 0 END) AS NIS_21,
      MAX(CASE WHEN PART_ID = 202105 AND ACTIVITY > 0 THEN 1 ELSE 0 END) AS MAY_21,
      MAX(CASE WHEN PART_ID = 202106 AND ACTIVITY > 0 THEN 1 ELSE 0 END) AS HAZ_21,
      MAX(CASE WHEN PART_ID = 202107 AND ACTIVITY > 0 THEN 1 ELSE 0 END) AS TEM_21,
      MAX(CASE WHEN PART_ID = 202108 AND ACTIVITY > 0 THEN 1 ELSE 0 END) AS AGU_21,
      MAX(CASE WHEN PART_ID = 202109 AND ACTIVITY > 0 THEN 1 ELSE 0 END) AS EYL_21
    FROM ACTIVITY
    WHERE RULLED_PROFIT_CENTER IN (108, 103, 170)
    GROUP BY CUST_ID, RULLED_PROFIT_CENTER
)
WHERE ARA_20 + OCA_21 + SUB_21 + MAR_21 + NIS_21 + MAY_21 +
      HAZ_21 + TEM_21 + AGU_21 + EYL_21 > 0

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