黄冈网站建设公司制作网站,网站筹建中,重庆一般做一个网站需要多少钱,网络科技网站设计这两天被扔过来一个脏活儿#xff1a;做一个试点运行系统的运营指标统计。
活儿之所以称为“脏”#xff0c;是因为要统计8家单位共12个项目的指标。而每个项目有3个用户类指标#xff0c;以及分17个功能模块#xff0c;每个功能模块又分5个维度的指标。也就是单个项目是1…这两天被扔过来一个脏活儿做一个试点运行系统的运营指标统计。
活儿之所以称为“脏”是因为要统计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》
以上希望能帮到遇到同样问题的朋友
