MySQL补全24小时数据统计:按天查询默认零填充
1星 需积分: 42 172 浏览量
更新于2024-09-07
收藏 2KB TXT 举报
在MySQL中,有时候我们需要统计一天内各个时间段的数据,例如24小时内的点击量或者活跃用户数。题目所给的SQL语句正是用来实现这一功能,并且当查询到某个时间段的数据缺失时,会自动填充0。这个查询涉及到两个主要部分:基础日期序列生成和与实际数据表的连接。
首先,基础部分是一个自连接查询(left join)生成了一个包含0到23的click_date序列,每个数字代表一天中的一个整点时刻(0表示午夜)。这通过一系列UNION ALL操作完成,确保了完整的一天时间覆盖。
```sql
SELECT 0 AS click_date
UNION ALL
SELECT 1
UNION ALL
...
SELECT 23
```
接着,第二个查询是实际的数据统计部分。它从名为`tb_log_login`的表中提取数据,统计每个整点(由HOUR()函数获取)的用户数量(user_id)。使用GROUP BY hours对数据进行分组,并计算每个时间段的总用户数。WHERE子句限制了只在指定日期('2018-01-18')进行统计。
```sql
SELECT HOUR(login_time) AS hours, SUM(user_id) AS count
FROM tb_log_login
WHERE DATE_FORMAT(login_time, '%Y-%m-%d') = DATE_FORMAT('2018-01-18', '%Y-%m-%d')
GROUP BY hours
```
最后,这两个查询通过click_date列进行连接。外部的click_date序列左连接内部的hours,这样即使外部表中没有对应的数据,也会得到一个NULL值。然后,使用IFNULL()函数将NULL值替换为0,实现了在没有数据时默认补零的效果。
```sql
LEFT JOIN (
... (上面的hour和count查询)
) b ON a.click_date = b.hours
```
完整的查询语句如下:
```sql
SELECT a.click_date, IFNULL(b.count, 0) as count
FROM (
SELECT 0 AS click_date
UNION ALL
SELECT 1
UNION ALL
...
SELECT 23
) a
LEFT JOIN (
SELECT HOUR(login_time) AS hours, SUM(user_id) AS count
FROM tb_log_login
WHERE DATE_FORMAT(login_time, '%Y-%m-%d') = DATE_FORMAT('2018-01-18', '%Y-%m-%d')
GROUP BY hours
) b ON a.click_date = b.hours;
```
这个查询的结果将返回每一天24个时段的点击数据,对于每个时段,如果没有登录数据,其count字段就会显示为0。这对于数据分析或报表生成非常有用,尤其是在需要完整展示一天所有时段的数据分布时。
点击了解资源详情
点击了解资源详情
点击了解资源详情
2022-04-26 上传
2020-09-09 上传
2012-03-05 上传
2023-01-26 上传
2020-12-15 上传
点击了解资源详情
xiaohenew
- 粉丝: 1
- 资源: 2
最新资源
- Java毕业设计项目:校园二手交易网站开发指南
- Blaseball Plus插件开发与构建教程
- Deno Express:模仿Node.js Express的Deno Web服务器解决方案
- coc-snippets: 强化coc.nvim代码片段体验
- Java面向对象编程语言特性解析与学生信息管理系统开发
- 掌握Java实现硬盘链接技术:LinkDisks深度解析
- 基于Springboot和Vue的Java网盘系统开发
- jMonkeyEngine3 SDK:Netbeans集成的3D应用开发利器
- Python家庭作业指南与实践技巧
- Java企业级Web项目实践指南
- Eureka注册中心与Go客户端使用指南
- TsinghuaNet客户端:跨平台校园网联网解决方案
- 掌握lazycsv:C++中高效解析CSV文件的单头库
- FSDAF遥感影像时空融合python实现教程
- Envato Markets分析工具扩展:监控销售与评论
- Kotlin实现NumPy绑定:提升数组数据处理性能