极客教程SQL 填补缺失的日期，你需要为给定日期区间里的每一天（每一个月、每一周或者每一年）生成一行数据。类似的行集常用于生成汇总报表。例如，你想计算每个月新入职的员工人数，只要某个年份有新同事入职，则列出该年度内每个月的数字。仔细分析全体员工的入职日期的话，会发现他们的入职日期都介于 1980 年和 1983 年之间。
SQL 填补缺失的日期 问题描述
你需要为给定日期区间里的每一天（每一个月、每一周或者每一年）生成一行数据。类似的行集常用于生成汇总报表。例如，你想计算每个月新入职的员工人数，只要某个年份有新同事入职，则列出该年度内每个月的数字。仔细分析全体员工的入职日期的话，会发现他们的入职日期都介于 1980 年和 1983 年之间。
select distinct
extract(year from hiredate) as year
from emp
YEAR
-----
1980
1981
1982
1983
你希望获得从 1980 年到 1983 年间每个月新入职的员工人数，期待得到的部分结果集如下所示。
MTH NUM_HIRED
----------- ----------
01-JAN-1981 0
01-FEB-1981 2
01-MAR-1981 0
01-APR-1981 1
01-MAY-1981 1
01-JUN-1981 1
01-JUL-1981 0
01-AUG-1981 0
01-SEP-1981 2
01-OCT-1981 0
01-NOV-1981 1
01-DEC-1981 2
SQL 填补缺失的日期 解决方案
麻烦之处在于我们希望为每个月都返回一行数据，即使那个月没有新入职的员工（也就是说，某些月份的计数值可能为 0）。因为在 1980 年和 1983 年间并非每个月都有新入职的员工，我们必须自己生成每个月份对应的记录，然后和 EMP 表的 HIREDATE 做外连接（需要对 HIREDATE 做截断处理，使之精确到月，这样才能与我们生成的月份相匹配）。
DB2
使用 WITH 递归查询为每个月生成一行记录（每个月的第一天是从 1980 年 1 月 1 日到 1983 年 12 月 1 日）。准备好了所需日期区间内的全部月份记录之后，和 EMP 表进行外连接，并使用聚合函数 COUNT 计算每个月新入职的员工人数。
1 with x (start_date,end_date)
2 as (
3 select (min(hiredate) -
4 dayofyear(min(hiredate)) day +1 day) start_date,
5 (max(hiredate) -
6 dayofyear(max(hiredate)) day +1 day) +1 year end_date
7 from emp
8 union all
9 select start_date +1 month, end_date
10 from x
11 where (start_date +1 month) < end_date
12 )
13 select x.start_date mth, count(e.hiredate) num_hired
14 from x left join emp e
15 on (x.start_date = (e.hiredate-(day(hiredate)-1) day))
16 group by x.start_date
17 order by 1
Oracle
使用 CONNECT BY 子句生成 1980 年到 1983 年间每个月的记录。然后外连接 EMP 表，并使用聚合函数 COUNT 计算每个月新入职的员工人数。但是，Oracle 8i 及更早版本的数据库既不支持 ANSI 标准的外连接，也不能使用 CONNECT BY 作为行生成器。一个简单的变通办法是使用传统的数据透视表（参考 MySQL 的解决方案）。下面的的解决方案使用了 Oracle 的外连接语法。
1 with x
2 as (
3 select add_months(start_date,level-1) start_date
4 from (
5 select min(trunc(hiredate,'y')) start_date,
6 add_months(max(trunc(hiredate,'y')),12) end_date
7 from emp
8 )
9 connect by level <= months_between(end_date,start_date)
10 )
11 select x.start_date MTH, count(e.hiredate) num_hired
12 from x, emp e
13 where x.start_date = trunc(e.hiredate(+),'mm')
14 group by x.start_date
15 order by 1
接着，下面展示了 ANSI 语法风格的第二个 Oracle 解决方案。
1 with x
2 as (
3 select add_months(start_date,level-1) start_date
4 from (
5 select min(trunc(hiredate,'y')) start_date,
6 add_months(max(trunc(hiredate,'y')),12) end_date
7 from emp
8 )
9 connect by level <= months_between(end_date,start_date)
10 )
11 select x.start_date MTH, count(e.hiredate) num_hired
12 from x left join emp e
13 on (x.start_date = trunc(e.hiredate,'mm'))
14 group by x.start_date
15 order by 1
PostgreSQL
为了增加代码的可读性，本解决方案使用视图 V，该视图返回从第一个员工入职当年的 1 月 1 日开始，到最近一个新同事入职当年的 12 月 1 日为止有多少个月。调用 GENERATE_SERIES 函数时，把视图 V 的返回值作为第二个参数，这样就能生成适当数目的月份记录（行）了。准备好了所需日期区间内的全部月份记录之后，和 EMP 表进行外连接，并使用聚合函数 COUNT 计算每个月新入职的员工人数。
create view v
as
select cast(
extract(year from age(last_month,first_month))*12-1
as integer) as mths
from (
select cast(date_trunc('year',min(hiredate)) as date) as first_month,
cast(cast(date_trunc('year',max(hiredate))
as date) + interval '1 year'
as date) as last_month
from emp
) x
1 select y.mth, count(e.hiredate) as num_hired
2 from (
3 select cast(e.start_date + (x.id * interval '1 month')
4 as date) as mth
5 from generate_series (0,(select mths from v)) x(id),
6 ( select cast(
7 date_trunc('year',min(hiredate))
8 as date) as start_date
9 from emp ) e
10 ) y left join emp e
11 on (y.mth = date_trunc('month',e.hiredate))
12 group by y.mth
13 order by 1
MySQL
使用数据透视表 T500 为 1980 年到 1983 年间每一个月份生成一行记录。然后外连接 EMP 表，并使用聚合函数 COUNT 计算每个月新入职的员工人数。
1 select z.mth, count(e.hiredate) num_hired
2 from (
3 select date_add(min_hd,interval t500.id-1 month) mth
4 from (
5 select min_hd, date_add(max_hd,interval 11 month) max_hd
6 from (
7 select adddate(min(hiredate),-dayofyear(min(hiredate))+1) min_hd,
8 adddate(max(hiredate),-dayofyear(max(hiredate))+1) max_hd
9 from emp
10 ) x
11 ) y,
12 t500
13 where date_add(min_hd,interval t500.id-1 month) <= max_hd
14 ) z left join emp e
15 on (z.mth = adddate(
16 date_add(
17 last_day(e.hiredate),interval -1 month),1))
18 group by z.mth
19 order by 1
SQL Server
使用 WITH 递归查询为每个月生成一行记录（每个月的第一天是从 1980 年 1 月 1 日到 1983 年 12 月 1 日）。准备好了所需日期区间内的全部月份记录之后，和 EMP 表进行外连接，并使用聚合函数 COUNT 计算每个月新入职的员工人数。
1 with x (start_date,end_date)
2 as (
3 select (min(hiredate) -
4 datepart(dy,min(hiredate))+1) start_date,
5 dateadd(yy,1,
6 (max(hiredate) -
7 datepart(dy,max(hiredate))+1)) end_date
8 from emp
9 union all
10 select dateadd(mm,1,start_date), end_date
11 from x
12 where dateadd(mm,1,start_date) < end_date
13 )
14 select x.start_date mth, count(e.hiredate) num_hired
15 from x left join emp e
16 on (x.start_date =
17 dateadd(dd,-day(e.hiredate)+1,e.hiredate))
18 group by x.start_date
19 order by 1
SQL 填补缺失的日期 扩展知识
DB2
首先生成 1980 年到 1983 年间每一个月份（实际上是每个月第一天的日期）对应的记录行。先针对 HIREDATE 调用 MIN 和 MAX 函数，然后把计算结果分别传递给 DAYOFYEAR 函数。
select (min(hiredate) -
dayofyear(min(hiredate)) day +1 day) start_date,
(max(hiredate) -
dayofyear(max(hiredate)) day +1 day) +1 year end_date
from emp
START_DATE END_DATE
----------- -----------
01-JAN-1980 01-JAN-1984
下一步是不断地在 START_DATE 基础上加上 1 个月，生成所有必要的月份以构造出最终的结果集。上述 END_DATE 值比它实际应有的值多 1 天。不过，这也没有关系。因为我们要不断地在 START_DATE 基础上加上 1 个月，只要在抵达 END_DATE 之前中断递归操作即可。生成的部分月份如下所示。
with x (start_date,end_date)
as (
select (min(hiredate) -
dayofyear(min(hiredate)) day +1 day) start_date,
(max(hiredate) -
dayofyear(max(hiredate)) day +1 day) +1 year end_date
from emp
union all
select start_date +1 month, end_date
from x
where (start_date +1 month) < end_date
)
select *
from x
START_DATE END_DATE
----------- -----------
01-JAN-1980 01-JAN-1984
01-FEB-1980 01-JAN-1984
01-MAR-1980 01-JAN-1984
...
01-OCT-1983 01-JAN-1984
01-NOV-1983 01-JAN-1984
01-DEC-1983 01-JAN-1984
现在已经列出了我们所需的全部月份，接着要外连接到 EMP.HIREDATE。因为每一个 START_DATE 实际上是当前月份的第一天，做外连接时也要把 EMP.HIREDATE 截断变成当前月份的第一天。最后，要针对 EMP.HIREDATE 调用聚合函数 COUNT。
Oracle
首先生成 1980 年到 1983 年间每一个月份的第一天。同时使用 TRUNC 和 ADD_MONTHS 函数，并针对 HIREDATE 分别调用 MIN 和 MAX 函数，这样就能找到两端的月份。
select min(trunc(hiredate,'y')) start_date,
add_months(max(trunc(hiredate,'y')),12) end_date
from emp
START_DATE END_DATE
----------- -----------
01-JAN-1980 01-JAN-1984
然后，不断地在 START_DATE 基础上加上若干个月以返回最终结果所需的月份。上述 END_DATE 值比它实际应有的值多 1 天。不过，这样也没有关系。因为我们要不断地在 START_DATE 基础上加上若干个月，只要在抵达 END_DATE 之前中断递归操作即可。生成的部分月份如下所示。
with x as (
select add_months(start_date,level-1) start_date
from (
select min(trunc(hiredate,'y')) start_date,
add_months(max(trunc(hiredate,'y')),12) end_date
from emp
)
connect by level <= months_between(end_date,start_date)
)
select *
from x
START_DATE
-----------
01-JAN-1980
01-FEB-1980
01-MAR-1980
...
01-OCT-1983
01-NOV-1983
01-DEC-1983
现在已经列出了我们所需的全部月份，接着要外连接到 EMP.HIREDATE。因为每一个 START_DATE 实际上是当前月份的第一天，做外连接时也要把 EMP.HIREDATE 截断变成当前月份的第一天。最后，针对 EMP.HIREDATE 调用聚合函数 COUNT。
PostgreSQL
本解决方案使用 GENERATE_SERIES 函数返回我们所需的月份。如果手边没有支持 GENERATE_SERIES 函数的 PostgreSQL 版本，可以使用 MySQL 解决方案中的数据透视表的做法。首先要理解视图 V。视图 V 会计算出需要生成多少个月份，我们通过找出给定日期区间的边界值来实现这一点。视图 V 里的内嵌视图 X 针对 HIREDATE 调用 MIN 和 MAX 函数以计算出开始日期和结束日期，结果如下所示。
select cast(date_trunc('year',min(hiredate)) as date) as first_month,
cast(cast(date_trunc('year',max(hiredate))
as date) + interval '1 year'
as date) as last_month
from emp
FIRST_MONTH LAST_MONTH
----------- -----------
01-JAN-1980 01-JAN-1984
上述 LAST_MONTH 值比它实际应有的值要多 1 天。不过，这样也没有关系。在计算两个日期之间有多少个月时，只要在计算结果的基础上减去 1 即可。下一步要调用 AGE 函数找出两个日期之间相差多少年，然后乘以 12（要记得减去 1）。
select cast(
extract(year from age(last_month,first_month))*12-1
as integer) as mths
from (
select cast(date_trunc('year',min(hiredate)) as date) as first_month,
cast(cast(date_trunc('year',max(hiredate))
as date) + interval '1 year'
as date) as last_month
from emp
) x
MTHS
----
47
把视图 V 的返回值作为第 2 个参数传递给 GENERATE_SERIES 函数，这样就能得到所需数目的月份。下一步是找出开始日期。我们不断在开始日期的基础上加上若干个月以生成所需的月份区间。内嵌视图 Y 针对 MIN(HIREDATE) 调用 DATE_TRUNC 函数以找出开始日期，并利用 GENERATE_SERIES 函数的返回值逐次为该开始日期加上若干个月。部分结果如下所示。
select cast(e.start_date + (x.id * interval '1 month')
as date) as mth
from generate_series (0,(select mths from v)) x(id),
( select cast(
date_trunc('year',min(hiredate))
as date) as start_date
from emp
) e
MTH
-----------
01-JAN-1980
01-FEB-1980
01-MAR-1980
...
01-OCT-1983
01-NOV-1983
01-DEC-1983
现在得到了最终结果集所需的每一个月份，接着要外连接到 EMP.HIREDATE，并调用聚合函数 COUNT 计算每个月新入职员工的人数。
MySQL
首先，使用聚合函数 MIN 和 MAX 以及函数 DAYOFYEAR 和 ADDDATE 找出日期区间的边界值。内嵌视图 X 的查询结果如下所示。
select adddate(min(hiredate),-dayofyear(min(hiredate))+1) min_hd,
adddate(max(hiredate),-dayofyear(max(hiredate))+1) max_hd
from emp
MIN_HD MAX_HD
----------- -----------
01-JAN-1980 01-JAN-1983
下一步，对 MAX_HD 做加法以计算出当前年份的最后一个月。
select min_hd, date_add(max_hd,interval 11 month) max_hd
from (
select adddate(min(hiredate),-dayofyear(min(hiredate))+1) min_hd,
adddate(max(hiredate),-dayofyear(max(hiredate))+1) max_hd
from emp
) x
MIN_HD MAX_HD
----------- -----------
01-JAN-1980 01-DEC-1983
现在我们知道了日期边界值，接着使用数据透视表 T500 在 MIN_HD 基础上逐一加上若干个月，直到抵达 MAX_HD 值，这样就生成了我们所需要的行记录。部分结果如下所示。
select date_add(min_hd,interval t500.id-1 month) mth
from (
select min_hd, date_add(max_hd,interval 11 month) max_hd
from (
select adddate(min(hiredate),-dayofyear(min(hiredate))+1) min_hd,
adddate(max(hiredate),-dayofyear(max(hiredate))+1) max_hd
from emp
) x
) y,
t500
where date_add(min_hd,interval t500.id-1 month) <= max_hd
MTH
-----------
01-JAN-1980
01-FEB-1980
01-MAR-1980
...
01-OCT-1983
01-NOV-1983
01-DEC-1983
现在已经准备好最终结果所需的全部月份，接着外连接到 EMP.HIREDATE（要记得截断 EMP.HIREDATE 值，使之变成当前月份的第一天），并针对 EMP.HIREDATE 调用聚合函数 COUNT 以计算每个月新入职员工的人数。
SQL Server
首先为从 1980 年到 1983 年间每个月份（实际上是每个月的第一天）生成一行记录。然后，针对 HIREDATE 分别执行 MIN 和 MAX 函数，再调用 DAYOFYEAR 函数，这样就能计算出日期区间两端的月份。
select (min(hiredate) -
datepart(dy,min(hiredate))+1) start_date,
dateadd(yy,1,
(max(hiredate) -
datepart(dy,max(hiredate))+1)) end_date
from emp
START_DATE END_DATE
----------- -----------
01-JAN-1980 01-JAN-1984
下一步要不断地在 START_DATE 基础上加上若干个月以返回最终结果集所需的月份。上述 END_DATE 值比它实际应有的值多 1 天；不过没有关系，因为我们要不断地在 START_DATE 基础上加上若干个月，只要在抵达 END_DATE 之前中断递归操作即可。生成的部分月份如下所示。
with x (start_date,end_date)
as (
select (min(hiredate) -
datepart(dy,min(hiredate))+1) start_date,
dateadd(yy,1,
(max(hiredate) -
datepart(dy,max(hiredate))+1)) end_date
from emp
union all
select dateadd(mm,1,start_date), end_date
from x
where dateadd(mm,1,start_date) < end_date
)
select *
from x
START_DATE END_DATE
----------- -----------
01-JAN-1980 01-JAN-1984
01-FEB-1980 01-JAN-1984
01-MAR-1980 01-JAN-1984
...
01-OCT-1983 01-JAN-1984
01-NOV-1983 01-JAN-1984
01-DEC-1983 01-JAN-1984
现在已经列出了我们所需的全部月份，接着要外连接到 EMP.HIREDATE。因为每一个 START_DATE 实际上是当前月份的第一天，做外连接时也要把 EMP.HIREDATE 截断变成当前月份的第一天。最后，针对 EMP.HIREDATE 调用聚合函数 COUNT。