oracle插补缺失日期,SQL 填补缺失的日期-CSDN博客

SQL 填补缺失的日期，你需要为给定日期区间里的每一天(每一个月、每一周或者每一年)生成一行数据。类似的行集常用于生成汇总报表。例如，你想计算每个月新入职的员工人数，只要某个年份有新同事入职，则列出该年度内每个月的数字。仔细分析全体员工的入职日期的话，会发现他们的入职日期都介于 1980 年和 1983 年之间。

SQL 填补缺失的日期问题描述

你需要为给定日期区间里的每一天(每一个月、每一周或者每一年)生成一行数据。类似的行集常用于生成汇总报表。例如，你想计算每个月新入职的员工人数，只要某个年份有新同事入职，则列出该年度内每个月的数字。仔细分析全体员工的入职日期的话，会发现他们的入职日期都介于 1980 年和 1983 年之间。

你希望获得从 1980 年到 1983 年间每个月新入职的员工人数，期待得到的部分结果集如下所示。

SQL 填补缺失的日期解决方案

麻烦之处在于我们希望为每个月都返回一行数据，即使那个月没有新入职的员工(也就是说，某些月份的计数值可能为 0)。因为在 1980 年和 1983 年间并非每个月都有新入职的员工，我们必须自己生成每个月份对应的记录，然后和 EMP 表的 HIREDATE 做外连接(需要对 HIREDATE 做截断处理，使之精确到月，这样才能与我们生成的月份相匹配)。

DB2

使用 WITH 递归查询为每个月生成一行记录(每个月的第一天是从 1980 年 1 月 1 日到 1983 年 12 月 1 日)。准备好了所需日期区间内的全部月份记录之后，和 EMP 表进行外连接，并使用聚合函数 COUNT 计算每个月新入职的员工人数。

Oracle

使用 CONNECT BY 子句生成 1980 年到 1983 年间每个月的记录。然后外连接 EMP 表，并使用聚合函数 COUNT 计算每个月新入职的员工人数。但是，Oracle 8i 及更早版本的数据库既不支持 ANSI 标准的外连接，也不能使用 CONNECT BY 作为行生成器。一个简单的变通办法是使用传统的数据透视表(参考 MySQL 的解决方案)。下面的的解决方案使用了 Oracle 的外连接语法。

接着，下面展示了 ANSI 语法风格的第二个 Oracle 解决方案。

PostgreSQL

为了增加代码的可读性，本解决方案使用视图 V，该视图返回从第一个员工入职当年的 1 月 1 日开始，到最近一个新同事入职当年的 12 月 1 日为止有多少个月。调用 GENERATE_SERIES 函数时，把视图 V 的返回值作为第二个参数，这样就能生成适当数目的月份记录(行)了。准备好了所需日期区间内的全部月份记录之后，和 EMP 表进行外连接，并使用聚合函数 COUNT 计算每个月新入职的员工人数。

MySQL

使用数据透视表 T500 为 1980 年到 1983 年间每一个月份生成一行记录。然后外连接 EMP 表，并使用聚合函数 COUNT 计算每个月新入职的员工人数。

SQL Server

SQL 填补缺失的日期扩展知识

DB2

首先生成 1980 年到 1983 年间每一个月份(实际上是每个月第一天的日期)对应的记录行。先针对 HIREDATE 调用 MIN 和 MAX 函数，然后把计算结果分别传递给 DAYOFYEAR 函数。

下一步是不断地在 START_DATE 基础上加上 1 个月，生成所有必要的月份以构造出最终的结果集。上述 END_DATE 值比它实际应有的值多 1 天。不过，这也没有关系。因为我们要不断地在 START_DATE 基础上加上 1 个月，只要在抵达 END_DATE 之前中断递归操作即可。生成的部分月份如下所示。

现在已经列出了我们所需的全部月份，接着要外连接到 EMP.HIREDATE。因为每一个 START_DATE 实际上是当前月份的第一天，做外连接时也要把 EMP.HIREDATE 截断变成当前月份的第一天。最后，要针对 EMP.HIREDATE 调用聚合函数 COUNT。

Oracle

首先生成 1980 年到 1983 年间每一个月份的第一天。同时使用 TRUNC 和 ADD_MONTHS 函数，并针对 HIREDATE 分别调用 MIN 和 MAX 函数，这样就能找到两端的月份。

然后，不断地在 START_DATE 基础上加上若干个月以返回最终结果所需的月份。上述 END_DATE 值比它实际应有的值多 1 天。不过，这样也没有关系。因为我们要不断地在 START_DATE 基础上加上若干个月，只要在抵达 END_DATE 之前中断递归操作即可。生成的部分月份如下所示。

现在已经列出了我们所需的全部月份，接着要外连接到 EMP.HIREDATE。因为每一个 START_DATE 实际上是当前月份的第一天，做外连接时也要把 EMP.HIREDATE 截断变成当前月份的第一天。最后，针对 EMP.HIREDATE 调用聚合函数 COUNT。

PostgreSQL

本解决方案使用 GENERATE_SERIES 函数返回我们所需的月份。如果手边没有支持 GENERATE_SERIES 函数的 PostgreSQL 版本，可以使用 MySQL 解决方案中的数据透视表的做法。首先要理解视图 V。视图 V 会计算出需要生成多少个月份，我们通过找出给定日期区间的边界值来实现这一点。视图 V 里的内嵌视图 X 针对 HIREDATE 调用 MIN 和 MAX 函数以计算出开始日期和结束日期，结果如下所示。

上述 LAST_MONTH 值比它实际应有的值要多 1 天。不过，这样也没有关系。在计算两个日期之间有多少个月时，只要在计算结果的基础上减去 1 即可。下一步要调用 AGE 函数找出两个日期之间相差多少年，然后乘以 12(要记得减去 1)。

把视图 V 的返回值作为第 2 个参数传递给 GENERATE_SERIES 函数，这样就能得到所需数目的月份。下一步是找出开始日期。我们不断在开始日期的基础上加上若干个月以生成所需的月份区间。内嵌视图 Y 针对 MIN(HIREDATE) 调用 DATE_TRUNC 函数以找出开始日期，并利用 GENERATE_SERIES 函数的返回值逐次为该开始日期加上若干个月。部分结果如下所示。

现在得到了最终结果集所需的每一个月份，接着要外连接到 EMP.HIREDATE，并调用聚合函数 COUNT 计算每个月新入职员工的人数。

MySQL

首先，使用聚合函数 MIN 和 MAX 以及函数 DAYOFYEAR 和 ADDDATE 找出日期区间的边界值。内嵌视图 X 的查询结果如下所示。

下一步，对 MAX_HD 做加法以计算出当前年份的最后一个月。

现在我们知道了日期边界值，接着使用数据透视表 T500 在 MIN_HD 基础上逐一加上若干个月，直到抵达 MAX_HD 值，这样就生成了我们所需要的行记录。部分结果如下所示。

现在已经准备好最终结果所需的全部月份，接着外连接到 EMP.HIREDATE(要记得截断 EMP.HIREDATE 值，使之变成当前月份的第一天)，并针对 EMP.HIREDATE 调用聚合函数 COUNT 以计算每个月新入职员工的人数。

SQL Server

首先为从 1980 年到 1983 年间每个月份(实际上是每个月的第一天)生成一行记录。然后，针对 HIREDATE 分别执行 MIN 和 MAX 函数，再调用 DAYOFYEAR 函数，这样就能计算出日期区间两端的月份。

下一步要不断地在 START_DATE 基础上加上若干个月以返回最终结果集所需的月份。上述 END_DATE 值比它实际应有的值多 1 天；不过没有关系，因为我们要不断地在 START_DATE 基础上加上若干个月，只要在抵达 END_DATE 之前中断递归操作即可。生成的部分月份如下所示。