SQL 创建垂直直方图|极客教程

SQL 创建垂直直方图，你想生成一个从下向上增长的直方图。例如，你希望以垂直直方图的方式显示每个部门的员工人数，每个 * 代表一个员工。你希望返回如下所示的结果集。

SQL 创建垂直直方图问题描述

你想生成一个从下向上增长的直方图。例如，你希望以垂直直方图的方式显示每个部门的员工人数，每个 * 代表一个员工。你希望返回如下所示的结果集。

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

SQL 创建垂直直方图解决方案

我们将以 12.2 节中的方法为基础解决本问题。
DB2、Oracle 和 SQL Server
使用窗口函数 ROW_NUMBER OVER 为每个 DEPTNO 的每一个 * 生成唯一的序号。使用聚合函数 MAX 变换结果集，并针对 ROW_NUMBER OVER 函数的返回值执行 GROUP BY。SQL Server 用户不要在该 ORDER BY 子句中使用 DESC。

 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 和 MySQL
使用标量子查询为每个 DEPTNO 的每一个 * 生成唯一的序号。针对内嵌视图 X 的返回值调用聚合函数 MAX，同时也针对 RNK 执行 GROUP BY 以实现结果集变换。MySQL 用户不要在 ORDER BY 子句中使用 DESC。

 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

SQL 创建垂直直方图扩展知识

DB2、Oracle 和 SQL Server
首先使用窗口函数 ROW_NUMBER 为每个 DEPTNO 的每一个 * 生成唯一的序号。使用 CASE 表达式为每个部门的每个员工返回一个 *。

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                        *

下一步也是最后一步，针对每个 CASE 表达式调用聚合函数 MAX，并基于 RN 分组剔除掉 Null 值。以 ASC 或 DESC 方式排序取决于数据库如何对 Null 值排序。

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 和 MySQL
首先，使用标量子查询为每个 DEPTNO 的每一个 * 生成唯一的序号。该标量子查询基于 EMPNO 为每个 DEPTNO 的员工生成序号，因此不可能有重复项。使用 CASE 表达式为每个部门的每一个员工返回一个 *。

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

然后，针对每个 CASE 表达式调用聚合函数 MAX。这样一来，按照 RNK 分组后就能够从结果集中剔除掉 Null 值了。以 ASC 或 DESC 方式排序取决于数据库如何对 Null 值排序。

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
--- --- ---
        *
    *   *
    *   *
*   *   *
*   *   *
*   *   *