编写sql统计一段时间内，每天、每月、每年的统计数据（PostgreSQL）-Toy模板网

这篇具有很好参考价值的文章主要介绍了编写sql统计一段时间内，每天、每月、每年的统计数据（PostgreSQL）。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

编写sql统计一段时间内，每天、每月、每年的统计数据（PostgreSQL）

前言

在做数据统计页面时，总会遇到统计某段时间内，每天、每月、每年的数据视图（柱状图、折线图等）。这些统计数据一眼看过去也简单呀，不就是按照时间周期（天、月、年）对统计数据进行分个组就完了嘛？但是会有一个问题，简单的写个sql对周期分组，获取到的统计数据是缺失的，即没有数据的那天，整条记录也都没有了。如下图
需求：以当前月份（2023年2月）为起点，往后倒推一年，查询之前一年里每个月的统计数据。
可见图中的数据其实是缺少的，这条sql只查询到了有数据的月份（23年的1月、2月，22年的12月）
sql分类按月份汇总,sql,postgresql,数据库
如果我想要没有数据的那个月或者那一天、那一年也显示统计记录出来，即统计数据字段设为0.该怎么实现呢？
文章来源地址https://www.toymoban.com/news/detail-530624.html

解决思路

先单表查询目标表单，按时间周期分组，统计出每个月对应的统计数据。设为表e,这时结果数据是有缺失的
再单表查询出倒推一年内，每个月份的字段。设为表 w
最后将两张表根据月份字段进行关联查询，其中关联查询的侧重表应是w

编写sql

SELECT COALESCE(e.counts,0),w.wmonth
from (
			SELECT COUNT(*) counts,to_char(create_time, 'yyyy-MM') months
			FROM employee
			GROUP BY months
		) e 
		RIGHT JOIN
		(
			SELECT to_char(t, 'yyyy-MM') as wmonth
			from generate_series(now() - INTERVAL '1 year' ,now(), '1 MONTH') as t
		) w on e.months = w.wmonth
GROUP BY w.wmonth,e.counts
ORDER BY w.wmonth desc

函数解析

COALESCE(e.counts,0)：在查询表单的结果里，如果e.counts为null，则设置为0
to_char(create_time, ‘yyyy-MM’)：这个函数可以将时间对象转化为字符串形式，有点像java内的SimpleDateFormat.format()函数
now()：获取当前的时间戳
generate_series(startTime,endTime ‘1 MONTH’)：这个函数就是实现数据完整检查的关键之一。作用是专门获取一个时间区段内，每一天、每个月、每一年的时间’对象‘。该函数一共有三个参数
(起始时间，末尾时间，时间跨度)

	-SELECT to_char(t, 'yyyy-MM') as wmonth
			from generate_series(now() - INTERVAL '1 year' ,now(), '1 MONTH') as t

到了这里，关于编写sql统计一段时间内，每天、每月、每年的统计数据（PostgreSQL）的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！

编写sql统计一段时间内，每天、每月、每年的统计数据（PostgreSQL）