本文已参与「新人创作礼亅活动，一起开启掘金创作之路。

postgresql 数据库 generate_series() 函数实例 补全时间进行统计

@[TOC] 今天碰到一个问题 需要用generate_series()进行解决

问题

例：表1 结构如下

code	姓名	地址	...
1201	张三	青岛市	....
1202	李四	北京市	....
1203	韩信	上海市	....
1204	瑶	天津市	....
1205	孙策	重庆市	....
...	...	...	....

表2 结构如下

ID(uuid)	code	时间	...
...	1201	2020-1-10	....
...	1201	2020-1-14	....
...	1201	2020-2-11	....
...	1203	2020-1-10	....
...	1401	2020-1-10	....
...	1205	2020-1-10	....
...	1216	2020-1-10	....
...	...	...	....
表1与表2 用 code 关联 ，表2是当天谁完成了任务就添加一条记录 ，没有的完成的不记录，现在需要2020年谁没有任务统计（具体到天到人）。

首先进行分析 因为表2中没有未完成任务的统计，无法直接统计，需要进行补全。

补全日期

运用 postgresql 的 generate_series() 函数补全日期

1.1 generate_series()

函数	参数类型	返回类型	描述
generate_series(start, stop)	int 或 bigint	setof int 或 setof bigint(与参数类型相同)	生成一个数值序列，从start 到 stop，步进为一
generate_series(start, stop, step)	int 或 bigint	setof int 或 setof bigint(与参数类型相同)	生成一个数值序列，从start 到 stop，步进为step
generate_series(start, stop, step_interval)	timestamp or timestamp with time zone	timestamp 或 timestamp with time zone(same as argument type)	生成一个数值序列，从start 到 stop，步进为step

1.2 用法

SELECT 
to_char ( b, 'YYYY-MM-DD' ) AS time
FROM
generate_series ( to_timestamp ( '2020-1-01', 'YYYY-MM-DD hh24:mi:ss' ), to_timestamp ( '2020-12-31', 'YYYY-MM-DD hh24:mi:ss' ), '1 days' ) AS b
GROUP BY
time  ORDER BY time asc

查询结果 用到本题上

SELECT code,姓名 ,to_char ( b, 'YYYY-MM-DD' ) AS time
FROM
 (SELECT code,姓名 generate_series ( to_timestamp ( '2020-1-01', 'YYYY-MM-DD hh24:mi:ss' ), to_timestamp ( '2020-11-1', 'YYYY-MM-DD hh24:mi:ss' ), '1 days' ) AS B 
 FROM 表1  ) AS B 

二 处理方法

--创建临时表

with aaaa as(
SELECT code,姓名 ,to_char ( b, 'YYYY-MM-DD' ) AS time
FROM
 (SELECT code,姓名 generate_series ( to_timestamp ( '2020-1-01', 'YYYY-MM-DD hh24:mi:ss' ), to_timestamp ( '2020-11-1', 'YYYY-MM-DD hh24:mi:ss' ), '1 days' ) AS B 
 FROM 表1  ) AS B ）
 -- 这样临时表有了每个人的姓名和时间 就可以跟表2去做关联了  

SELECT code ,姓名 , time 时间 from aaaa a 
left join 表2 b on a.code=b.code and a.time=b.时间
where b.id is null;

如果有更好的方法，请留言，谢谢