当前位置：首页 > article >正文

SQL使用IN进行分组统计时如何将不存在的字段显示为0

article 2024/11/17 1:48:35

这两天被扔过来一个脏活儿：做一个试点运行系统的运营指标统计。

活儿之所以称为“脏”，是因为要统计8家单位共12个项目的指标。而每个项目有3个用户类指标，以及分17个功能模块，每个功能模块又分5个维度的指标。也就是单个项目是17 x 5 + 3 = 78个指标。总共78 x 12 = 936个。指标如下图所示：

在这里插入图片描述

交接人告知，实际上运营指标也才设计出来几天，相关开发工程师只给了几段SQL用来在数据库查询，至于说准确性啥的也不清楚。而看完统计演示，发现真的是最原始的“最粗最笨”的办法：每个项目通过6段SQL查询，然后完全人工在查询结果中挨个核对数据并填入excel报表！

疯了，一群草台班子！笔者当时的内心OS是这样的。

其中有关“当日活跃用户数（完成一笔有效业务交易，如单据提交或审批）”这个指标的，上图中标红字段，交接人表示开发人员反馈只能通过单位统计，无法通过项目来统计。而这一段SQL语句，需要手动替换单位编码，然后反复运行查询。代码如下：

#当日活跃用户数（完成一笔有效业务交易，如单据提交或审批）当日参与提交或审批人数

SELECT
         COUNT(DISTINCT h.USER_ID_)
FROM
        copro_bpm.copro_bpm_run_message r 
        LEFT JOIN copro_bpm.act_hi_comment h ON h.PROC_INST_ID_ = r.proc_inst_id
WHERE
		DATE(r.update_time) >=  DATE_SUB(CURDATE(), INTERVAL 1 DAY)
		AND DATE(r.update_time) <  CURDATE() 
        AND r.org_id in (387);

很容易想到的优化点是，将所有单位编码放到最后一句的IN关键字后面，如下：

#当日活跃用户数（完成一笔有效业务交易，如单据提交或审批）当日参与提交或审批人数

SELECT
org_id, CASE WHEN update_user IS NULL THEN 0 ELSE COUNT(DISTINCT update_user) END AS update_user_count
FROM
	copro_bpm.copro_bpm_run_message
WHERE
	DATE(update_time) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)
  AND org_id IN (387, 174, 165, 97, 157, 106, 133, 147)
GROUP BY
	org_id
ORDER BY
	org_id ASC;

然而运行测试发现一个问题：由于是按天来进行的统计，因此有些单位有时候这一天并没有数据，所以查询结果只显示了有数据的单位，类似下图：

在这里插入图片描述而这样一来，在excel中还是得人工去核对数据，不方便所有单位一起来复制粘贴。

因此，本文标题的解决的问题点来了：怎么将通过IN进行分组统计的结果中不存在的字段显示为0。结合这个情况，就是想办法将上面截图的结果中，未显示的其他单位编码和结果（显示为0）显示出来。

由于笔者平时使用SQL频率并不高，因此面对这个问题还是花了点儿时间解决。解决完后，始发现这应该是做指标统计的同学必然会遇到的一个常见问题。而解决办法也是一个必备经验。

下面的内容，希望给同样面对此种情况的朋友一些启发：

这个问题的根源，是因为通过where条件查询出的结果，只会显示存在的内容，不会显示不存在的内容。

笔者查看网上的帖子后，结合实践，目前找到两个好的办法：

一是通过LEFT JOIN，对于左表，不管右表有没有数据（对于上面的例子，假如IN后面的字段，有些不存在右表中；但都存在于左表中），那么不存在的单位相关字段（例子中是：update_user）将显示NULL，这样只要使用IFNULL将结果转为0即可达到目标。

但这个方法需要两个表，笔者这个例子中是使用一个表。应该可以构造一个临时表，作为右表。但笔者认为对于使用SQL不多的人，理解起来以及操作起来可能都相对要困难一些。

二是通过UNION ALL，将每个单位的结果组合起来。

最终，笔者使用了第二种方法，并稍做了改良。而这个改良的思路，笔者认为值得借鉴：

就是构造每个单位的所要查询的内容都是0的临时表，然后通过UNION ALL与原正常查询的结果组合，这样得到的新表，将是IN后面所有单位都为0，再加上本来就有查询结果的单位，本例中就是截图中的org_id为133，结果为0这条数据。也就是说，因为UNION ALL不会去掉重复值（即org_id为133的两条数据，其中一条update_user为0，一条为3），而其他单位update_user是我们自己新构建的值0。这样，对于新表，再去按普通查询去查，将对org_id重复的进行合并，即org_id为133的合并显示为有值的3，其他不重复的显示为0。

通过这样的巧妙，也达成了目标。

写起来比较绕，但其实核心是理解解决方式的思路。思路了解后自己根据实际情况做调整，相信即可解决遇到的问题。

最后笔者改良后的代码如下：

#当日活跃用户数（完成一笔有效业务交易，如单据提交或审批）当日参与提交或审批人数

SELECT
org_id, update_user AS update_user_count
FROM
(SELECT 
org_id, COUNT(DISTINCT update_user) AS update_user
FROM
copro_bpm.copro_bpm_run_message
WHERE
	DATE(update_time) =  DATE_SUB(CURDATE(), INTERVAL 1 DAY)
  AND org_id IN (387, 174, 165, 97, 157, 106, 133, 147)
GROUP BY org_id
UNION ALL
SELECT 387 AS org_id, 0 AS update_user FROM DUAL
UNION ALL
SELECT 174 AS org_id, 0 AS update_user FROM DUAL
UNION ALL
SELECT 165 AS org_id, 0 AS update_user FROM DUAL
UNION ALL
SELECT 97 AS org_id, 0 AS update_user FROM DUAL
UNION ALL
SELECT 157 AS org_id, 0 AS update_user FROM DUAL
UNION ALL
SELECT 106 AS org_id, 0 AS update_user FROM DUAL
UNION ALL
SELECT 133 AS org_id, 0 AS update_user FROM DUAL
UNION ALL
SELECT 147 AS org_id, 0 AS update_user FROM DUAL) temp
GROUP BY org_id;