程序员经验分享-hwhale

【实用】Mysql 按照时间（年月周日）维度统计，不存在时间数据 自动补充 0 数值

2023-11-16

前言


ps： 网上看了一大堆文章， 介绍的东西真的是很够呛， 就没一个能真正用起来的， 各个都是自动补，然后很多都是不好用的。

我自己整理一篇，这是真能用。

本篇内容 ：

① 按照 日 、周、月 、年  的维度 去对数据 做分组统计 

② 不存在的数据自动补充 0  （实用）

正文

不多说，开搞。


结合实例 ：

先看我们的表 student 

 建表sql：

CREATE TABLE `student` (
    `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
    `name` VARCHAR(50) NULL DEFAULT NULL COMMENT '名字' COLLATE 'utf8_general_ci',
    `admission_time` DATETIME NULL DEFAULT NULL COMMENT '入学时间',
    PRIMARY KEY (`id`) USING BTREE
)


现在我们就举个简单的业务场景：

根据admission_time 入学时间，按照年月日周这些维度 去统计 学生数量。


我们来做一些模拟数据，8条数据：

第一部分 ， 按照时间维度统计的SQL 写法


我们先简单看看 按照 日 、周  、月 、年 的统计 sql怎么写 ：


日 维度 

sql：

SELECT date_format(admission_time, '%Y-%m-%d') date_time, COUNT(*) num
FROM student
GROUP BY date_time ;

 看看效果：


 
ps ： 注意了 这些 ‘-’ 是我们定义的格式， 我们不要 ‘-’ 换成其他的也是可以的，但是 不能不要 ymd 这些关键字。

举个小栗子：
 
 
比如 ，date_format(admission_time, '收藏%Y点%m赞%d')

SELECT date_format(admission_time, '收藏%Y点%m赞%d') date_time, COUNT(*) num
FROM student
GROUP BY date_time ;

看看效果 ：

周维度 

 sql：

SELECT DATE_FORMAT(admission_time,'%Y-%u 周')  AS  date_time, COUNT(*) num FROM student GROUP BY date_time;

ps ： 为了让你们知道 这个周的概念， 我故意加了个中文。 而且特意把一条数据 时间改成 22年的最后一周的一天。

 
看看效果 ：


月维度 

sql：

SELECT DATE_FORMAT(admission_time, '%Y-%m') date_time, COUNT(*) num
FROM student
GROUP BY date_time ;

看看效果 ：

年维度

sql：

SELECT DATE_FORMAT(admission_time, '%Y') date_time, COUNT(*) num
FROM student
GROUP BY date_time ;

看看效果:

好了 知道这些基础的 时间分组sql 用法之后， 那么我们接下来就 来 玩下怎么 解决自动补 0 的这个问题。

第二部分 ， 自动填充不存在的数据 数值 0


首先，如果说 sql查出来的数据没有， 我们拿月维度的来 做个示例 ，就像这样 ：

 

这里突然想到个点，很多人说那这里面混了 2022年， 2023年的数据， 我想要指定查询某段时间的怎么搞？

其实一样的，就根据时间查询就行：

比如像这样传入我们的筛选时间范围，按照月就这样 ：

sql：


SELECT * FROM (

select date_format(admission_time, '%Y-%m') date_time, count(*) num
from student
group by date_format(admission_time, '%Y-%m')

)t WHERE t.date_time BETWEEN '2023-01' AND   '2023-03'

效果：


 
回到刚才，可以看到统计出来的数据， 比如说23年的，有1月的，2月的， 3月的， 那么 4，5，6，7后面这些月份，没数据，那怎么办？

如何默认去填充 0呢 ？
 

写代码填充，后端拿到查数据库返回的数据，for循环遍历，检测时间段内的日期， 比如说 12个月，看看哪个月没有，就填充。

确实 这是可以的， 但是今天这一篇介绍的是通过sql返回 ， 不考虑代码上面的填补。

接下来看看SQL怎么玩 。

思路&想法 ： 
 

我们能查出来 student 现有的日期数据， 那么缺少的数据 我也得给整出来 。

那我们肯定不能去改 student表的数据呀， 现在就是单纯少了一些 空白月份的数据 。


所以我们选择 临时数据表的思维。


ps ： 网上一大堆文章，都让咱们去跑个存储过程  强行生成一个表..

你看看（一万个拒绝） 

 还有这种，按照目前时间拼接出来的：

（5000个拒绝，这种now 直接切割到现在，还得写一大堆这种01，02，03，04；
   如果我是要填充 日维度的数据，那我这sql代码量不就爆炸了？
） 

 这种也是：

OK， 我们来看看我们的SQL ：

 

select DATE_FORMAT(date_add('2023-01-01', interval row MONTH),'%Y-%m') date_time from
 ( 
    SELECT @row := @row + 1 as row FROM 
    (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
    (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2, 
    (SELECT @row:=-1) r
 ) se
 where DATE_FORMAT(date_add('2023-01-01', interval row MONTH),'%Y-%m') <= DATE_FORMAT('2023-12-01','%Y-%m')

效果 ：
 

ps： 这里用了月举例， 要弄年或周或者日的 在文末有补充。

sql 作用简析 ：

简析点一 



 

 简析点二

 可以看到 ，这里面 我写了2行这个玩意  。

(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t
 

简单说下：
 

写一行这个玩意， 代表能搞出 10 条 符合 范围时间内日期格式时间数据 ；

当写多一行呢（2行的时候），代表 10 * 10 =100 条 

当再写多一行呢（3行的时候）？  10*10*10 =1000条

所以我们上面的sql，我写了2行，

代表 在我传入的时间范围 2023-01-01 到 2323-12-01  内

我写的时间格式是取月， 一共其实就12 个月数据，也就是12条， 但是只写一次10条不够用，我也就用了2次（100条）。

言下之意， 其实你跟我这样写2次，100条， 什么概念， 1年12个月 相当于12条，这样 100条相当于可以查跨度 8年的时间了 （8*12-96）

如果你不是要查日， 业务需求一般不会让咱们写跨度这么大的。

当然了，如果就是有， 那么我们大不了直接写 4条， 相当于 10*10*10*10 =1 万  条。

OK，不啰嗦，回到我们的示例 ：

我们现在 如果说是查跨度 2年的数据， 比如现在按照我们part的sql 查出来是这样子的 。

可以看到结果集， 22年数据 缺了很多 需要补0的， 23年也缺了很多。

所以我们这时候需要做一个 left join 即可解决自动补 0 的事情。

sql操作图析:
 

sql:


SELECT  A.date_time, COALESCE(B.num, 0) as num  FROM 


(

SELECT DATE_FORMAT(date_add('2023-01-01', interval row MONTH),'%Y-%m') date_time FROM
 ( 
    SELECT @row := @row + 1 as row FROM 
    (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
    (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
	 (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
	 (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4,
	  
    (SELECT @row:=-1) r
 ) se
WHERE DATE_FORMAT(date_add('2023-01-01', interval row MONTH),'%Y-%m') <= DATE_FORMAT('2023-12-01','%Y-%m')

) A


LEFT JOIN 

(

SELECT DATE_FORMAT(admission_time, '%Y-%m') date_time, COUNT(*) num
FROM student
GROUP BY DATE_FORMAT(admission_time, '%Y-%m')

) B


ON    A.date_time= B.date_time

看看效果 ：

这盛世如我所愿， 好了， 该篇就到这。 

文末补充 日、年、周 维度的 列出完整数据条sql ：

按日 列出范围内日期的sql ：
 

select date_add('2023-01-01', interval row DAY) date from
 ( 
    SELECT @row := @row + 1 as row FROM 
    (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
    (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
	  (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
	   (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4,   
    (SELECT @row:=-1) r
 ) se
 where date_add('2023-01-01', interval row DAY) <= '2023-01-20'

 效果：

按周 列出范围内日期的sql ：

按照周要注意一点，当传入每年的01-01这一天的时候 会出现0周 ，可以做一下处理。

 select DATE_FORMAT(date_add('2023-01-01', interval row WEEK),'%Y-%u') date_time from
 ( 
    SELECT @row := @row + 1 as row FROM 
    (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
    (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
	 (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
	 (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4,
	  
    (SELECT @row:=-1) r
 ) se
 where DATE_FORMAT(date_add('2023-01-01', interval row WEEK),'%Y-%u') <= DATE_FORMAT('2023-01-06','%Y-%u')

按年 列出范围内日期的sql ：


select DATE_FORMAT(date_add('2020-01-01', interval row YEAR),'%Y') date_time from
 ( 
    SELECT @row := @row + 1 as row FROM 
    (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
    (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
	 (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
	 (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4,
	  
    (SELECT @row:=-1) r
 ) se
 where DATE_FORMAT(date_add('2020-01-01', interval row YEAR),'%Y') <= DATE_FORMAT('2023-12-01','%Y')

效果：

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

mysql

数据库

补0

日期分组

【实用】Mysql 按照时间（年月周日）维度统计，不存在时间数据 自动补充 0 数值 的相关文章

  • 如何使用 MySQL 选择有特定值的 2 个连续行？

    我正在构建一个系统 该系统应该显示学生何时连续缺席两天 例如 此表包含缺勤情况 day id missed 2016 10 6 1 true 2016 10 6 2 true 2016 10 6 3 false 2016 10 7 1 tr

  • 如何从 MySQL 数据查询创建 XML 文件？

    我想知道一种仅使用 MySQL 查询创建 XML 文件的方法 根本不使用任何脚本语言 有关于这个主题的书籍 教程吗 UPDATE 我想澄清一下 我想使用 sql 查询将 XML 数据转发到 php 脚本 Here s 关于从 MySQL S

  • 来自数据库的 jfreechart 散点图

    如何使用java中的jfreechart绘制mysql数据库表中数据的散点图 我使用过 Swing 库 任何链接都会有帮助 我搜索了谷歌但找不到理解的解决方案 如果您有代码 请提供给我 实际上我确实做了条形图并使用 jfreechart 绘

  • phpActiveRecord 日期时间格式不正确

    当尝试使用 phpActiveRecord 在表中创建记录时 出现以下错误 Invalid datetime format 1292 Incorrect datetime value 2013 06 20 11 59 08 PDT for

  • 使用 PHP 将 latin1_swedish_ci 转换为 utf8

    我有一个数据库 里面充满了类似的值 Dhaka 应该是 Dhaka 因为我在创建数据库时没有指定排序规则 现在我想修复它 我无法从最初获取数据的地方再次获取数据 所以我在想是否可以在 php 脚本中获取数据并将其转换为正确的字符 我已将数据

  • 在 MySQL 中进行全文搜索的最有效方法

    我有 3 个表 我想查询搜索词文本框 我的查询目前看起来像这样 SELECT Artist FROM Artist Band Instrument WHERE MATCH Artist name AGAINST mysearchterm O

  • 无法从我的 java 应用程序访问 mysql 数据库（在 AWS 实例上运行）

    我有一个在 AWS EC2 Ubuntu 实例上运行的 xampp 服务器 并且那里有一个名为 androiddb 的数据库 我可以通过在导航器中写入来访问数据库http public ip phpmyadmin http public i

  • 使用php插入sql数据库时出错

    我有一个带有 MySQL 插入查询的程序 sql INSERT INTO people person id name username password email salt VALUES person id name username p

  • PHP 和 MySQL 的重音字符错误

    我的问题是 直接通过 PHP 编写的内容是正确重音的 但是当重音单词来自 MySQL 时 字母会像这样 我尝试使用html charset as ISO 8859 1它修复了 MySQL 字母 但破坏了其他字母 解决这一切的一种方法是设置我

  • 在 BIRT 中输入参数后更新数据集查询

    在 BIRT 报告设计中传递参数后 如何更改或更新数据集的查询 详细说明 我有一个如下所示的查询 WHERE 该参数标记可以保存不同的值 在用户输入参数后 它看起来像这样 例如 WHERE column name 1 or WHERE co

  • 连接 3 三张表

    我有这个图表应该可以解释我的情况 我需要一些关于连接 3 个表的帮助 我不知道如何做这种事情 因此 我可以通过执行以下操作来经历一段检索记录的 while 循环 img src alt Album AlbumID 使用内部联接 http w

  • PHP：如何检查总数。 URL 中的参数？

    我正在使用 REQUEST 检索参数 有没有办法找到总数 URL 中的参数 而不是检索每个参数然后进行计数 这将为您提供总数 分隔的 URL 查询参数 count explode SERVER QUERY STRING 如果您只想要唯一的参

  • 您可以使用 MySQL 查询来完整创建数据库的副本吗

    我有一个包含 5 个表的 MySQL 数据库的实时版本和一个测试版本 我不断使用 phpMyAdmin 将实时版本中的每个表复制到测试版本 有谁有mysql查询语句来制作数据库的完整副本吗 查询字符串需要考虑结构 数据 自动增量值以及与需要

  • 如何在 bash 上运行 MySQL 命令？

    以下代码在命令行上运行 mysql user myusername password mypassword database mydatabase execute DROP DATABASE myusername CREATE DATABA

  • 用教义 2 DBAL 连接子查询

    我正在重构 Zend 框架2应用程序使用学说 2 5 DBAL 而不是 Zend DB ZF1 我有以下 Zend Db 查询 subSelect db gt select gt from user survey status entrie

  • 连接两个表而不返回不需要的行

    我的表结构如下所示 tbl users tbl issues userid real name issueid assignedid creatorid 1 test 1 1 1 1 2 test 2 2 1

  • 我不断收到此 mysql 错误代码 #1089

    CREATE TABLE movies movie movie id INT 3 NULL AUTO INCREMENT movie name VARCHAR 25 NULL movie embedded id VARCHAR 50 NUL

  • 用于全文搜索和 2 亿多条记录的数据库

    我即将创建一个包含至少 2 亿个条目的庞大数据库 数据库需要可使用全文进行搜索 并且速度应该很快 我的数据库从许多不同的数据源获取数据 我需要定期导入新的或更新的数据 将我的所有数据存储在像 mysql 这样的关系数据库中 然后创建一个 n

  • PDO获取最后插入的ID

    我有一个查询 我想获取插入的最后一个 ID 字段ID是主键并且自动递增 我知道我必须使用这个声明 LAST INSERT ID 该语句适用于如下查询 query INSERT INTO cell place ID VALUES LAST I

  • 如何在php/mysql中使用事务

    我正在使用 php mysql 我知道 mysql 中的事务 但不能在我的脚本中使用 下面是我的脚本 如何在我的代码中使用 php 事务 即 BEGIN ROLLBACK COMMIT foreach json a shop as json

随机推荐

  • 整数溢出的漏洞危害和预防

    智能合约作为区块链2 0的代表技术 适应于区块链去中心化 分布式的特点 具有独立运行 不可篡改的优良特性 可用于实现包含金融工具在内的各类分布式应用 开发者可以自行定义交易逻辑并开发代码发布到链上 合约代码在矿工节点的虚拟机环境 如EVM

  • vue踩坑填坑（一）：引入模块组件

    在webpack vue开发中 如果在一个vue文件中引入另外一个封装的模块组件的vue文件 则有以下两种方式 首先想要在以下代码中引入一个封装好的输入框组件input text vue

  • cf服务器维护会不会掉分,《cf》枪王排位长时间不打会不会掉分？ 枪王排位扣分机制介绍...

    川北在线核心提示 原标题 cf 枪王排位长时间不打会不会掉分 枪王排位扣分机制介绍 CF枪王排位大师以上不打会掉分么 很多小伙伴都在问枪王排位长时间不打会不会掉分 为此牛游戏小编为大家带来cf枪王排位扣分机制介绍 一起来看看枪王排位长时间不

  • Basic Level 1014 福尔摩斯的约会 (20分)

    题目 大侦探福尔摩斯接到一张奇怪的字条 我们约会吧 3485djDkxh4hhGE 2984akDfkkkkggEdsb s hgsfdk d Hyscvnm 大侦探很快就明白了 字条上奇怪的乱码实际上就是约会的时间星期四 14 04 因为

  • JDK8 HashMap put() 方法源码分析

    文章目录 一 前置知识 红黑树定义 二 构造方法 HashMap HashMap int initialCapacity float loadFactor tableSizeFor int cap 计算hashmap初始容量 三 put 方

  • 入门级题解7. 整数反转

    给你一个 32 位的有符号整数 x 返回将 x 中的数字部分反转后的结果 如果反转后整数超过 32 位的有符号整数的范围 231 231 1 就返回 0 假设环境不允许存储 64 位整数 有符号或无符号 思路 反转 想到链表反转 又看到是整

  • android studio对数据库进行,Android Studio 学习（四） 数据库

    文件存储 写数据 String data Data ti save FileOutputStream out null BufferedWriter writer null try out openFileOutput data Conte

  • C++毕业设计基于QT实现的超市收银管理系统源代码+数据库

    C 毕业设计基于QT实现的超市收银管理系统源代码 数据库 编译使用 编译完成后 需要拷贝 file目录下的数据库 POP db文件到可执行程序目录下 登录界面 主界面 会员管理 完整代码下载地址 基于QT实现的超市收银管理系统源代码 数据库

  • ctf.show 通关秘籍

    文章目录 CTF show 1 web签到题 2 web2 3 web3 CTF show 1 web签到题 访问web签到题的地址 发现页面只有 where is flag 字样 使用Fn F12进入调试模式 或者页面空白处点击右键查看网

  • mysql默认的数据库和表_MySQL 自带4个默认数据库

    默认数据库分类 information schema performance schema mysql test informance schema 保存了MySQl服务所有数据库的信息 具体MySQL服务有多少个数据库 各个数据库有哪些表

  • Mrosoft visual c++6.0打开文件未响应，快速解决。【最新办法，初学者都会】

    1 下载filetool的vc6 0的辅助工具 下载地址 http download microsoft com download vc60ent s1 6 0 w9xnt4 en us filetool exe 快速下载filetool

  • 为SQL Server Always On可用性组配置域控制器和Active Directory

    In this series for SQL Server Always On availability groups we are covering end to end configurations for SQL Server 201

  • echarts横向个性化柱状图

    先看一下效果图 横向柱状图 顶部小圈是一个图片 下面我们就来看看如何实现 1 第一步 先把柱状图中需要插入的图片 转换成base64格式 百度搜一下 可以搜到在线工具直接转换 2 html中定义一个div 用来盛放柱状图 div style

  • 《STL源码剖析》（二）——空间配置器

    一 为什么要有空间配置器 1 小块内存带来的内存碎片问题 单从内存分配的角度来讲 由于频繁分配 释放小块内存容易在堆中造成外碎片 极端情况下 堆中空闲的总量满足一个要求 但是这些空闲的块都不连续 导致任何一个单独的空闲的块都无法满足请求 2

  • C练题笔记之：Leetcode-793. 阶乘函数后 K 个零

    题目 f x 是 x 末尾是 0 的数量 回想一下 x 1 2 3 x 且 0 1 例如 f 3 0 因为 3 6 的末尾没有 0 而 f 11 2 因为 11 39916800 末端有 2 个 0 给定 k 找出返回能满足 f x k 的

  • NO-CARRIER

    自己动手写了创建虚拟接口 删除虚拟接口程序 频繁调用创建删除时 有时将接口up起来时会报错 Name not unique on network 利用ip link命令来查看接口 及其对应的索引 可以查看到与其他接口不同的是有个标志为 NO

  • 利用多个panel重叠来代替tabcontrol方法

    这两天 在弄一个C 软件 看着tabcontrol那个讨厌的标签 真是火大 折腾了1个周末加今天1个白天 总是不行 其实我就是想要一排按纽 来控掉不同的界面显示 今天打算使用多个pannel来代替tabcontrol 自己在旁边再弄几个按纽

  • Angular_学习笔记_01

    3天Angular视频课程 之 第1天 学习笔记 1 安装 Node 官网下载 一路Next gt 2 安装Angular cli sudo npm install g angular cli 3 ng new app name 4 运行项

  • scrapy 快速入门

    安装Scrapy Scrapy是一个高级的Python爬虫框架 它不仅包含了爬虫的特性 还可以方便的将爬虫数据保存到csv json等文件中 首先我们安装Scrapy pip install scrapy 在Windows上安装时可能会出现

  • 【实用】Mysql 按照时间（年月周日）维度统计，不存在时间数据 自动补充 0 数值

    前言 ps 网上看了一大堆文章 介绍的东西真的是很够呛 就没一个能真正用起来的 各个都是自动补 然后很多都是不好用的 我自己整理一篇 这是真能用 本篇内容 按照 日 周 月 年 的维度 去对数据 做分组统计 不存在的数据自动补充 0 实用

热门标签