这两天被扔过来一个脏活儿:做一个试点运行系统的运营指标统计。
活儿之所以称为“脏”,是因为要统计8家单位共12个项目的指标。而每个项目有3个用户类指标,以及分17个功能模块,每个功能模块又分5个维度的指标。也就是单个项目是17 x 5 + 3 = 78个指标。总共78 x 12 = 936个。指标如下图所示:
交接人告知,实际上运营指标也才设计出来几天,相关开发工程师只给了几段SQL用来在数据库查询,至于说准确性啥的也不清楚。而看完统计演示,发现真的是最原始的“最粗最笨”的办法:每个项目通过6段SQL查询,然后完全人工在查询结果中挨个核对数据并填入excel报表!
疯了,一群草台班子!笔者当时的内心OS是这样的。
其中有关“当日活跃用户数(完成一笔有效业务交易,如单据提交或审批)”这个指标的,上图中标红字段,交接人表示开发人员反馈只能通过单位统计,无法通过项目来统计。而这一段SQL语句,需要手动替换单位编码,然后反复运行查询。代码如下:
#当日活跃用户数(完成一笔有效业务交易,如单据提交或审批)当日参与提交或审批人数SELECTCOUNT(DISTINCT h.USER_ID_)
FROMcopro_bpm.copro_bpm_run_message r LEFT JOIN copro_bpm.act_hi_comment h ON h.PROC_INST_ID_ = r.proc_inst_id
WHEREDATE(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
FROMcopro_bpm.copro_bpm_run_message
WHEREDATE(update_time) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)AND org_id IN (387, 174, 165, 97, 157, 106, 133, 147)
GROUP BYorg_id
ORDER BYorg_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
WHEREDATE(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;
查询结果如下:
笔者将上面截图的结果,全部复制粘贴进excel中,即可一次性做处理。能减少一些耗时,以及避免人工去比对数据所带来的可能的失误。
解决的办法,主要灵感得益于来自于知乎的一篇帖子,在此感谢。并将帖子链接粘贴如下,供参考:
《SQL分组统计把不存在的组计数为0》
以上,希望能帮到遇到同样问题的朋友;)