postgresql 根据时间补齐数据

在做系统审计访问趋势统计的时候遇到一个问题：日志的时间不是连续的，比如有2019年12月全月差三天的数据，但是在生成图表的时候必须是连续的。尤其是折线图和柱状图这种也是不能存在时间断点。

最开始作为后端开发人员使用的是在逻辑代码中做补全，但是这种效率极其低下。后面发现了下面这个方法：

select COALESCE(a.t, b.t) t, COALESCE(a.count_v, 0) count_v, COALESCE(a.count_u, 0) count_u from
(select to_char("time",'YYYY-mm-dd') t,count(1) count_v,count(distinct username) count_u from t_audit
where sysname in %s and "time" between %s and %s
GROUP BY t order by t) a
full join
(select to_char (b,'YYYY-MM-DD') as t from
generate_series(to_timestamp (%s,'YYYY-MM-DD'), to_timestamp (%s,'YYYY-MM-DD'),'1 days') as b
group by t order by t) b on a.t = b.t

ps:%s是参数的占位符，请忽略……

generate_series这个函数是核心的功能，就是在一个范围内，根据步长生成一个结果集。具体的用法我相信没有一篇博客能比https://www.cnblogs.com/mchina/archive/2013/04/03/2997722.html这个写的更全面了。非常感谢David Camp能把这个方法介绍的如此全面完整。

根据这个生成的结果集full join你要进行补全的数据，使用COALESCE这个方法对空数据进行处理（sql server使用isnull，mysql使用ifnull）。这样最后得到的结果就是你给定时间段内的所有日期的数据。

这个方法也可以类比到数字区间、时间区间（其他的时间间隔）。这个对于后端人员来说省掉的就是大篇幅的补全数据的代码。

个人感觉一个后端人员还是应该精通sql语句才能够更好的去解决很多数据操作的问题（相比于orm而言，原声sql虽然麻烦，但是功能确实强大）。