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

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

活儿之所以称为“脏”,是因为要统计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》

以上,希望能帮到遇到同样问题的朋友;)

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/426119.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

服务器数据恢复—Linux操作系统环境下网站数据的恢复案例

服务器数据恢复环境&#xff1a; 一台linux操作系统服务器上跑了几十个网站&#xff0c;服务器上只有一块SATA硬盘。 服务器故障&#xff1a; 服务器突然宕机&#xff0c;尝试再次启动失败。将硬盘拆下检测&#xff0c;发现存在坏扇区。找当地一家数据恢复公司处理后&#xff…

合宙Air201模组LuatOS:点点鼠标就搞定的FOTA远程升级,你知道吗?

你是不是也经常遇到小伙伴吐槽&#xff1a;开发是个苦差事&#xff01;做项目倒还好&#xff0c;就怕遇到项目升级&#xff0c;那简直让人头大。。。 如果你也有这种困惑&#xff0c;就多了解一下合宙的开发工具&#xff0c;简单实用又高效&#xff0c;甚至只需点点鼠标&#…

水下目标检测数据集 urpc2021

项目背景&#xff1a; 水下目标检测在海洋科学研究、水下考古、海洋资源勘探等多个领域具有重要的应用价值。由于水下环境的复杂性和多变性&#xff0c;传统的人工检测方法存在诸多限制&#xff0c;自动化检测技术的需求日益增加。URPC2021数据集旨在为水下目标检测提供高质量…

Python OpenCV精讲系列 - 高级图像处理技术(七)

&#x1f496;&#x1f496;⚡️⚡️专栏&#xff1a;Python OpenCV精讲⚡️⚡️&#x1f496;&#x1f496; 本专栏聚焦于Python结合OpenCV库进行计算机视觉开发的专业教程。通过系统化的课程设计&#xff0c;从基础概念入手&#xff0c;逐步深入到图像处理、特征检测、物体识…

【技术分享】走进Docker的世界:从基础到实战全面解析(Docker全流程)

文章目录 【技术分享】走进Docker的世界&#xff1a;从基础到实战全面解析(Docker全流程)1.简介与概述1.1为什么需要使用docker1.2 使用docker的好处1.3 应用场景1.4 容器与虚拟机区别1.4 Docker和虚拟机的区别1.5 Docker官网 2. Docker快速入门2.1 Docker安装2.2 Docker核心名…

ARCGIS PRO DSK MapTool

MapTool用于自定义地图操作工具&#xff0c;使用户能够在ArcGIS Pro中执行特定的地图交互操作。添加 打开MapTool1.vb文件&#xff0c;可以看到系统已经放出MapTool1类&#xff1a; Public Sub New()将 IsSketchTool 设置为 true 以使此属性生效IsSketchTool TrueSketchTyp…

Errorresponsefromdaemon:toomanyrequests:Youhavereachedyourpullratelimit.

Errorresponsefromdaemon:toomanyrequests:Youhavereachedyourpullratelimit.Youmayincreasethelimitbyauthenticatingandupgrading:https://www.docker.com/increase−rate−limit.See ′ dockerrun−−help 在拉取docker进行的时候遇到这个问题,如何解决呢?本文提供的解决方…

基于springboot学生健康管理系统的设计与实现

文未可获取一份本项目的java源码和数据库参考。 进入21世纪以来&#xff0c;随着经济水平的高速发展&#xff0c;人们的生活质量有了很大提升&#xff0c;物质和精神生活得到了极大满足。但人们的健康水平却情况堪忧。据不完全统计&#xff0c;全国近七层人口处于亚健康状态&a…

《探索云原生与相关技术》

在当今的科技领域中&#xff0c;云原生&#xff08;Cloud Native&#xff09;已经成为了一个热门的话题。它代表着一种构建和运行应用程序的全新方式。 云原生的概念 云原生是一套技术体系和方法论&#xff0c;旨在充分利用云计算的优势来构建更具弹性、可扩展性和高效性的应…

Java或者前端 实现中文排序(调API的Demo)

目录 前言1. 前端2. Java 前言 前端 Vue 中的中文排序通常使用 JavaScript 提供的 localeCompare 方法来比较中文字符串 Java 后端可以使用 Collator 类来实现中文排序 1. 前端 在 Vue 中&#xff0c;使用 localeCompare 来实现中文字符串的排序&#xff1a; <template&…

MySQL_数据库基本操作

课 程 推 荐我 的 个 人 主 页&#xff1a;&#x1f449;&#x1f449; 失心疯的个人主页 &#x1f448;&#x1f448;入 门 教 程 推 荐 &#xff1a;&#x1f449;&#x1f449; Python零基础入门教程合集 &#x1f448;&#x1f448;虚 拟 环 境 搭 建 &#xff1a;&#x1…

在服务器上开Juypter Lab教程(远程访问)

在服务器上开Juypter Lab教程&#xff08;远程访问&#xff09; 文章目录 在服务器上开Juypter Lab教程&#xff08;远程访问&#xff09;一、安装anaconda1、安装anaconda2、提权限3、运行4、同意协议5、安装6、是否要自动初始化 conda7、结束8、检查 二、Anaconda安装Pytorch…

.net core 通过Sqlsugar生成实体

通过替换字符串的方式生成代码&#xff0c;其他代码也可以通这种方式生成 直接上代码 设置模板 将这几个模板文件设置为&#xff1a;嵌入资源 模板内容&#xff1a; using SqlSugar;namespace {Namespace}.Domain.Admin.{ModelName}; /// <summary> /// {TableDisplay…

[数据集][目标检测]葡萄成熟度检测数据集VOC+YOLO格式1123张3类别

数据集格式&#xff1a;Pascal VOC格式YOLO格式(不包含分割路径的txt文件&#xff0c;仅仅包含jpg图片以及对应的VOC格式xml文件和yolo格式txt文件) 图片数量(jpg文件个数)&#xff1a;1123 标注数量(xml文件个数)&#xff1a;1123 标注数量(txt文件个数)&#xff1a;1123 标注…

上海儿童自闭症寄宿制学校,让孩子找到归属感

在探讨自闭症儿童教育的广阔图景中&#xff0c;上海作为一座充满人文关怀的城市&#xff0c;始终致力于为这些特殊的孩子提供更加全面、专业的支持体系。而当我们把这份关注与努力投射到具体实践上&#xff0c;广州的星贝育园自闭症儿童寄宿制学校便成为了这样一个温馨而有力的…

室内灯具检测系统源码分享

室内灯具检测检测系统源码分享 [一条龙教学YOLOV8标注好的数据集一键训练_70全套改进创新点发刊_Web前端展示] 1.研究背景与意义 项目参考AAAI Association for the Advancement of Artificial Intelligence 项目来源AACV Association for the Advancement of Computer Vis…

RocksDB系列一:基本概念

0 引言 RocksDB 是 Facebook 基于 Google 的 LevelDB 代码库于 2012 年创建的高性能持久化键值存储引擎。它针对 SSD 的特定特性进行了优化&#xff0c;目标是大规模&#xff08;分布式&#xff09;应用&#xff0c;并被设计为嵌入在更高层次应用中的库组件。RocksDB应用范围很…

unity3d入门教程七

unity3d入门教程七 17.1物理系统17.2静态刚体17.3刚体的碰撞17.4刚体的反弹18.1运动学刚体18.2碰撞检测18.3碰撞事件回调18.4目标的识别18.5碰撞的规避 17.1物理系统 在物理系统中的物体具有质量和速度的是刚体 不用写代码就会自由落体运动了 17.2静态刚体 给 ‘地面’ 添…

kubernetes技术详解,带你深入了解k8s

目录 一、Kubernetes简介 1.1 容器编排应用 1.2 Kubernetes简介 1.3 k8s的设计架构 1.3.1 k8s各个组件的用途 1.3.2 k8s各组件之间的调用关系 1.3.3 k8s的常用名词概念 1.3.4 k8s的分层结构 二、k8s集群环境搭建 2.1 k8s中容器的管理方式 2.2 k8s环境部署 2.2.1 禁用…

stm32开发之串口空闲中断和环形数组的最简单的组合使用

前言 本次使用的是lwrb开源的源码&#xff1b;测试环境使用的是stm32f407zgt6这里不介绍lwrb的内容&#xff0c;如有需要请自行去查阅.这里会使用到rt_container_of的宏定义(相关介绍请参考rt_thread或linux源码相关的宏定义,其表达的内容是一致的)这里使用的是threadx做为os本…