SQL 填补缺失的日期

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