Recipe12.10.Creating Vertical Histograms

Recipe 12.10. Creating Vertical Histograms

Problem

You want to generate a histogram that grows from the bottom up. For example, you want to display the number of employees in each department as a vertical histogram with each employee represented by an instance of "*". You want to return the following result set:

 D10 D20 D30 --- --- ---         *     *   *     *   * *   *   * *   *   * *   *   * 

Solution

The technique used to solve this problem is built upon that used as the second recipe in this chapter: .

DB2, Oracle, and SQL Server

Use the ROW_NUMBER OVER function to uniquely identify each instance of "*" for each DEPTNO. Use the aggregate function MAX to pivot the result set and group by the values returned by ROW_NUMBER OVER (SQL Server users should not use DESC in the ORDER BY clause):

  1 select max(deptno_10) d10,  2        max(deptno_20) d20,  3        max(deptno_30) d30  4   from (  5 select row_number( )over(partition by deptno order by empno) rn,  6        case when deptno=10 then '*' else null end deptno_10,  7        case when deptno=20 then '*' else null end deptno_20,  8        case when deptno=30 then '*' else null end deptno_30  9   from emp 10        ) x 11  group by rn 12  order by 1 desc, 2 desc, 3 desc 

PostgreSQL and MySQL

Use a scalar subquery to uniquely identify each instance of "*" for each DEPTNO. Use the aggregate function MAX on the values returned by inline view X, while also grouping by RNK to pivot the result set. MySQL users should not use DESC in the ORDER BY clause:

  1 select max(deptno_10) as d10,  2        max(deptno_20) as d20,  3        max(deptno_30) as d30  4   from (  5 select case when e.deptno=10 then '*' else null end deptno_10,  6        case when e.deptno=20 then '*' else null end deptno_20,  7        case when e.deptno=30 then '*' else null end deptno_30,  8        (select count(*) from emp d  9          where e.deptno=d.deptno and e.empno < d.empno ) as rnk 10   from emp e 11        ) x 12  group by rnk 13  order by 1 desc, 2 desc, 3 desc 

Discussion

DB2, Oracle, and SQL Server

The first step is to use the window function ROW_NUMBER to uniquely identify each instance of "*" in each department. Use a CASE expression to return a "*" for each employee in each department:

  select row_number( )over(partition by deptno order by empno) rn,        case when deptno=10 then '*' else null end deptno_10,        case when deptno=20 then '*' else null end deptno_20,        case when deptno=30 then '*' else null end deptno_30   from emp RN DEPTNO_10  DEPTNO_20  DEPTNO_30 -- ---------- ---------- ---------  1 *  2 *  3 *  1            *  2            *  3            *  4            *  5            *  1                       *  2                       *  3                       *  4                       *  5                       *  6                       * 

The next and last step is to use the aggregate function MAX on each CASE expression, grouping by RN to remove the NULLs from the result set. Order the results ASC or DESC depending on how your RDBMS sorts NULLs:

  select max(deptno_10) d10,        max(deptno_20) d20,        max(deptno_30) d30   from ( Select row_number( )over(partition by deptno order by empno) rn,        case when deptno=10 then '*' else null end deptno_10,        case when deptno=20 then '*' else null end deptno_20,        case when deptno=30 then '*' else null end deptno_30   from emp        ) x  group by rn  order by 1 desc, 2 desc, 3 desc D10 D20 D30 --- --- ---         *     *   *     *   * *   *   * *   *   * *   *   * 

PostgreSQL and MySQL

The first step is to use a scalar subquery to uniquely identify each instance of "*" in each department. The scalar subquery ranks the employees by EMPNO in each DEPTNO, so there can be no duplicates. Use a CASE expression to generate a "*" for each employee in each department:

  select case when e.deptno=10 then '*' else null end deptno_10,        case when e.deptno=20 then '*' else null end deptno_20,        case when e.deptno=30 then '*' else null end deptno_30,        (select count(*) from emp d          where e.deptno=d.deptno and e.empno < d.empno ) as rnk   from emp e DEPTNO_10  DEPTNO_20  DEPTNO_30         RNK ---------- ---------- ---------- ----------            *                              4                       *                   5                       *                   4            *                              3                       *                   3                       *                   2 *                                         2            *                              2 *                                         1                       *                   1            *                              1                       *                   0            *                              0   *                                       0 

Then use the aggregate function MAX on each CASE expression, being sure to group by RNK to remove the NULLs from the result set. Order the results ASC or DESC depending on how your RDBMS sorts NULLs:

  select max(deptno_10) as d10,        max(deptno_20) as d20,        max(deptno_30) as d30   from ( Select case when e.deptno=10 then '*' else null end deptno_10,        case when e.deptno=20 then '*' else null end deptno_20,        case when e.deptno=30 then '*' else null end deptno_30,        (select count(*) from emp d          where e.deptno=d.deptno and e.empno < d.empno ) as rnk   from emp e        ) x  group by rnk  order by 1 desc, 2 desc, 3 desc D10 D20 D30 --- --- ---         *     *   *     *   * *   *   * *   *   * *   *   *