SQL queries: Filling in Missing Values in a Range (DB2, Oracle, SQL Server, MySQL, PostgreSQL)

SQL studing: Filling in Missing Values

Problem

You want to return the number of employees hired each year for the entire decade of the 1980s, but there are some years in which no employees were hired. You would like to return the following result set:

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

Solution

The trick to this solution is returning zeros for years that saw no employees hired. If no employee was hired in a given year, then no rows for that year will exist in table EMP. If the year does not exist in the table, how can you return a count, any count, even zero? The solution requires you to outer join. You must supply a result set that returns all the years you want to see, and then perform a count against table EMP to see if there were any employees hired in each of those years.

DB2

Use table EMP as a pivot table (because it has 14 rows) and the built-in function YEAR to generate one row for each year in the decade of 1980. Outer join to table EMP and count how many employees were hired each year:

	 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

	 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 and MySQL

Use table T10 as a pivot table (because it has 10 rows) and the built-in function EXTRACT to generate one row for each year in the decade of 1980. Outer join to table EMP and count how many employees were hired each year:

	 1 select y.yr, coalesce(x.cnt,0) as cnt
	 2   from (
	 3 selectmin_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

Use table EMP as a pivot table (because it has 14 rows) and the built-in function YEAR to generate one row for each year in the decade of 1980. Outer join to table EMP and count how many employees were hired each year:

	 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 )

 

Discussion

Despite the difference in syntax, the approach is the same for all solutions. Inline view X returns each year in the decade of the ’80s by first finding the year of the earliest HIREDATE. The next step is to add RN–1 to the difference between the earliest year and the earliest year modulus ten. To see how this works, simply execute inline view X and return each of the values involved separately. Listed below is the result set for inline view X using the window function MIN OVER (DB2, Oracle, SQL Server) and a scalar subquery (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

Inline view Y returns the year for each HIREDATE and the number of employees hired during that year:

	select year(hiredate) yr, count(*) cnt
	  from emp
	 group by year(hiredate)

	   YR        CNT
	----- ----------
	 1980          1
	 1981         10
	 1982          2
	 1983          1

Finally, outer join inline view Y to inline view X so that every year is returned even if there are no employees hired.

Вас заинтересует / Intresting for you:

A Brief History of SQL
A Brief History of SQL 2871 views Александров Попков Wed, 17 Oct 2018, 15:04:29
SQL: how to determine which ro...
SQL: how to determine which ro... 178 views Денис Tue, 06 Jul 2021, 18:47:53
Referencing an Aliased Column ...
Referencing an Aliased Column ... 253 views Денис Wed, 14 Jul 2021, 12:59:03
How to Retrieving a Subset of ...
How to Retrieving a Subset of ... 253 views Денис Wed, 14 Jul 2021, 04:45:37