SQL 为值区间填充缺失值|极客教程

SQL 为值区间填充缺失值，你想列出整个 20 世纪 80 年代里每年新入职的员工人数，但有一些年份并没有新增员工。你希望返回如下所示的结果集。

SQL 为值区间填充缺失值问题描述

你想列出整个 20 世纪 80 年代里每年新入职的员工人数，但有一些年份并没有新增员工。你希望返回如下所示的结果集。

YR          CNT
---- ----------
1980          1
1981         10
1982          2
1983          1
1984          0
1985          0
1986          0
1987          0
1988          0
1989          0

SQL 为值区间填充缺失值解决方案

本解决方案的关键之处在于如何为那些没有新增员工的年份返回 0。如果在一个给定的年份里没有新入职的员工，那么 EMP 表里就不存在对应的行。既然表里不包含这一年，我们该如何为这一年返回计数值 0 呢？本解决方案需要用到外连接操作。我们要拼凑一个包含了所有目标年份的结果集，然后针对 EMP 表执行 COUNT 查询，以判断每一年里是否新增了员工。
DB2
把 EMP 表作为数据透视表（因为它有 14 行数据），并调用内置函数 YEAR，为 20 世纪 80 年代的每一个年份生成一行数据。然后，外连接 EMP 表，并计算每年新增了多少名员工。

 1 select x.yr, coalesce(y.cnt,0) cnt
 2   from (
 3 select year(min(hiredate)over()) -
 4        mod(year(min(hiredate)over()),10) +
 5        row_number()over()-1 yr
 6   from emp fetch first 10 rows only
 7        )x
 8   left join
 9        (
10 select year(hiredate) yr1, count(*) cnt
11   from emp
12  group by year(hiredate)
13        ) y
14     on ( x.yr = y.yr1 )

Oracle
把 EMP 表作为数据透视表（因为它有 14 行数据），并调用内置函数 TO_NUMBER 和 TO_CHAR，为 20 世纪 80 年代的每一个年份生成一行数据。然后，外连接 EMP 表，并计算每年新增了多少名员工。

 1  select x.yr, coalesce(cnt,0) cnt
 2    from (
 3  select extract(year from min(hiredate)over()) -
 4         mod(extract(year from min(hiredate)over()),10) +
 5         rownum-1 yr
 6   from emp
 7  where rownum <= 10
 8        ) x,
 9        (
10  select to_number(to_char(hiredate,'YYYY')) yr, count(*) cnt
11    from emp
12   group by to_number(to_char(hiredate,'YYYY'))
13         ) y
14   where x.yr = y.yr(+)

如果使用的是 Oracle 9i 及后续版本，则不妨使用新提供的 JOIN 子句。

 1  select x.yr, coalesce(cnt,0) cnt
 2    from (
 3  select extract(year from min(hiredate)over()) -
 4         mod(extract(year from min(hiredate)over()),10) +
 5         rownum-1 yr
 6    from emp
 7   where rownum <= 10
 8         ) x
 9    left join
10         (
11  select to_number(to_char(hiredate,'YYYY')) yr, count(*) cnt
12    from emp
13   group by to_number(to_char(hiredate,'YYYY'))
14         ) y
15      on ( x.yr = y.yr )

PostgreSQL 和 MySQL
把 T10 表作为数据透视表（因为它有 10 行数据），并调用内置函数 EXTRACT，为 20 世纪 80 年代的每一个年份生成一行数据。然后，外连接 EMP 表，并计算每年新增了多少名员工。

 1  select y.yr, coalesce(x.cnt,0) as cn
 2    from (
 3  select min_year-mod(cast(min_year as int),10)+rn as yr
 4    from (
 5  select (select min(extract(year from hiredate))
 6            from emp) as min_year,
 7        id-1 as rn
 8    from t10
 9         ) a
10         ) y
11    left join
12         (
13  select extract(year from hiredate) as yr, count(*) as cnt
14    from emp
15   group by extract(year from hiredate)
16         ) x
17      on ( y.yr = x.yr )

SQL Server
把 EMP 表作为数据透视表（因为它有 14 行数据），并调用内置函数 YEAR，为 20 世纪 80 年代的每一个年份生成一行数据。然后，外连接 EMP 表，并计算每年新增了多少名员工。

 1  select x.yr, coalesce(y.cnt,0) cnt
 2    from (
 3  select top (10)
 4         (year(min(hiredate)over()) -
 5          year(min(hiredate)over( ))%10)+
 6          row_number()over(order by hiredate)-1 yr
 7    from emp
 8         ) x
 9    left join
10         (
11  select year(hiredate) yr, count(*) cnt
12    from emp
13   group by year(hiredate)
14         ) y
15      on ( x.yr = y.yr )

SQL 为值区间填充缺失值扩展知识

尽管各个数据库的解决方案在语法上有所不同，做法却并无二致。内嵌视图 X 先找出最早的 HIREDATE 值对应的年份，进而返回 20 世纪 80 年代的每一年。下一步是用最早的年份减去该年份模 10 计算的结果，然后再加上 RN–1。为了更清楚地了解其工作原理，我们不妨实际运行一下内嵌视图 X，并分别返回其中涉及的每一个值。下面列出了两个版本的内嵌视图 X，它们分别使用窗口函数 MIN OVER（DB2、Oracle 和 SQL Server）和标量子查询（MySQL 和 PostgreSQL）来得到对应的结果集。

select year(min(hiredate)over()) -
       mod(year(min(hiredate)over()),10) +
       row_number()over()-1 yr,
       year(min(hiredate)over()) min_year,
       mod(year(min(hiredate)over()),10) mod_yr,
       row_number()over()-1 rn
  from emp fetch first 10 rows only
　
  YR   MIN_YEAR     MOD_YR         RN
---- ---------- ---------- ----------
1980       1980          0          0
1981       1980          0          1
1982       1980          0          2
1983       1980          0          3
1984       1980          0          4
1985       1980          0          5
1986       1980          0          6
1987       1980          0          7
1988       1980          0          8
1989       1980          0          9
　
select min_year-mod(min_year,10)+rn as yr,
       min_year,
       mod(min_year,10) as mod_yr
       rn
  from (
select (select min(extract(year from hiredate))
          from emp) as min_year,
       id-1 as rn
  from t10
       ) x
　
  YR   MIN_YEAR     MOD_YR         RN
---- ---------- ---------- ----------
1980       1980          0          0
1981       1980          0          1
1982       1980          0          2
1983       1980          0          3
1984       1980          0          4
1985       1980          0          5
1986       1980          0          6
1987       1980          0          7
1988       1980          0          8
1989       1980          0          9

内嵌视图 Y 返回每一个 HIREDATE 对应的年份，以及那一年新增的员工人数。

select year(hiredate) yr, count(*) cnt
  from emp
 group by year(hiredate)
　
   YR        CNT
----- ----------
 1980          1
 1981         10
 1982          2
 1983          1