使用 SQL 创建柱状图,是真的吗?

作者: 不剪发的Tony老师
毕业于北京航空航天大学,十多年数据库管理与开发经验,目前在一家全球性的金融公司从事数据库架构设计。CSDN学院签约讲师以及GitChat专栏作者。csdn上的博客收藏于以下地址:https://tonydong.blog.csdn.net


 

大家好!我是只谈技术不剪发的 Tony 老师。

今天我们介绍如何使用 SQL 语句创建水平柱状图和垂直柱状图,使用的功能包括分组聚合、字符串函数、CASE 条件表达式以及窗口函数。本文描述的方法支持但不限于 Oracle、MySQL/MariaDB、Microsoft SQL Server、PostgreSQL 以及 SQLite 等数据库。

如果觉得文章有用,欢迎评论📝、点赞👍、推荐🎁

    📝本文使用的示例表和数据可以点此下载。

水平柱状图

员工表(employee)中存储了每个部门中的员工信息,部门表(department)中存储了部门的信息。假如我们想要统计每个部门的员工数量,并且以水平直方图的形式进行显示。以下是 MySQL 数据库中的实现:

-- MySQL/MariaDB
SELECT d.dept_name "部门名称",
       count(e.emp_id) "员工数量",
       repeat('▇', count(e.emp_id)) "柱状图"
FROM department d
LEFT JOIN employee e ON (d.dept_id = e.dept_id)
GROUP BY dept_name
ORDER BY count(*) DESC;

 

我们首先通过分组和 COUNT 聚合函数得到了每个部门的员工人数,左外连接确保部门没有员工时也会返回相应的信息。然后我们通过 repeat 函数创建一个基于员工人数的水平直方图。最终查询返回的结果如下:










对于其他数据库,实现的员工相同,只是需要替换相应的字符串函数:

-- Microsoft SQL Server
SELECT d.dept_name "部门名称",
       count(e.emp_id) "员工数量",
       replicate('▇', count(e.emp_id)) "柱状图"
FROM department d
LEFT JOIN employee e ON (d.dept_id = e.dept_id)
GROUP BY dept_name
ORDER BY count(*) DESC;

-- PostgreSQL
SELECT d.dept_name "部门名称",
       count(e.emp_id) "员工数量",
       repeat('▇', count(e.emp_id)::integer) "柱状图"
FROM department d
LEFT JOIN employee e ON (d.dept_id = e.dept_id)
GROUP BY dept_name
ORDER BY count(*) DESC;

-- Oracle
SELECT d.dept_name "部门名称",
       count(e.emp_id) "员工数量",
       lpad('▇', count(e.emp_id), '▇') "柱状图"
FROM department d
LEFT JOIN employee e ON (d.dept_id = e.dept_id)
GROUP BY dept_name
ORDER BY count(*) DESC;

-- SQLite
SELECT d.dept_name "部门名称",
       count(e.emp_id) "员工数量",
       replace(hex(zeroblob(count(e.emp_id))), '00', '█') "柱状图"
FROM department d
LEFT JOIN employee e ON (d.dept_id = e.dept_id)
GROUP BY dept_name
ORDER BY count(*) DESC;

 

对于 Microsoft SQL Server,我们可以使用 replicate 函数替换 repeat 函数。

对于 PostgreSQL,我们需要为 repeat 函数明确指定一个 integer 类型的参数。

对于 Oracle,我们可以使用 lpad 函数替换 repeat 函数。另外,MySQL/MariaDB 和 PostgreSQL 也支持 lpad 函数,也可以使用这种方式。

SQLite 不支持 repeat 函数或者替换的函数,我们可以先利用 zeroblob 函数生成一个由 0x00 组成的 BLOB,然后通过 replace 函数将其替换为柱状图。
垂直柱状图

如果我们将水平直方图逆时针旋转 90 度,就可以得到垂直柱状图。以下是 MySQL/MariaDB、Microsoft SQL Server 以及 SQLite 中的实现:

-- MySQL、Microsoft SQL Server以及SQLite
 WITH d AS (
  SELECT row_number() OVER (PARTITION BY dept_id ORDER BY emp_id) rn,
         CASE WHEN dept_id=1 THEN '█' END dept1,
         CASE WHEN dept_id=2 THEN '█' END dept2,
         CASE WHEN dept_id=3 THEN '█' END dept3,
         CASE WHEN dept_id=4 THEN '█' END dept4,
         CASE WHEN dept_id=5 THEN '█' END dept5,
         CASE WHEN dept_id=6 THEN '█' END dept6
  FROM employee
)
SELECT min(dept1) "行政管理部",
       min(dept2) "人力资源部",
       min(dept3) "财务部",
       min(dept4) "研发部",
       min(dept5) "销售部",
       min(dept6) "保卫部"
FROM d
GROUP BY rn
ORDER BY 1, 2, 3, 4, 5, 6;

 

我们首先创建了一个通用表表达式(CTE),利用 row_number 窗口函数得到了每个员工在各自部门中的编号,同时通过 CASE 表达式将对应的员工使用 █ 进行表示,此时生成的数据如下。

rn|dept1|dept2|dept3|dept4|dept5|dept6|
--|-----|-----|-----|-----|-----|-----|
 1|█    |     |     |     |     |     |
 2|█    |     |     |     |     |     |
 3|█    |     |     |     |     |     |
 1|     |    █|     |     |     |     |
 2|     |    █|     |     |     |     |
 3|     |    █|     |     |     |     |
 1|     |     |    █|     |     |     |
 2|     |     |    █|     |     |     |
 1|     |     |     |    █|     |     |
 2|     |     |     |    █|     |     |
 3|     |     |     |    █|     |     |
 4|     |     |     |    █|     |     |
 5|     |     |     |    █|     |     |
 6|     |     |     |    █|     |     |
 7|     |     |     |    █|     |     |
 8|     |     |     |    █|     |     |
 9|     |     |     |    █|     |     |
 1|     |     |     |     |    █|     |
 2|     |     |     |     |    █|     |
 3|     |     |     |     |    █|     |
 4|     |     |     |     |    █|     |
 5|     |     |     |     |    █|     |
 6|     |     |     |     |    █|     |
 7|     |     |     |     |    █|     |
 8|     |     |     |     |    █|     |

 

    📝关于窗口函数的介绍和案例分析可以参考这篇文章和这篇文章。
    📝关于通用表表达式以及各种数据库中的语法可以参考这篇文章,以及一个分析社交网络关系的实战案例。

最后我们在主查询语句中利用分组和 min 聚合函数创建一个基于员工人数的垂直柱状图。最终查询返回的结果如下:

行政管理部|人力资源部|财务部|研发部|销售部|保卫部|
---------|---------|-----|------|-----|------|
         |         |     |█     |█    |      |
         |         |     |█     |█    |      |
         |         |     |█     |█    |      |
         |         |     |█     |█    |      |
         |         |     |█     |█    |      |
         |         |     |█     |█    |      |
█        |█        |     |█     |█    |      |
█        |█        |█    |█     |█    |      |
█        |█        |█    |█     |█    |      |

 

对于 Oracle 和 PostgreSQL,查询的实现和上面的语句几乎相同,只需要将排序修改为降序即可:

--  Oracle 和 PostgreSQ
WITH d AS (
  SELECT row_number() OVER (PARTITION BY dept_id ORDER BY emp_id) rn,
         CASE WHEN dept_id=1 THEN '█' END dept1,
         CASE WHEN dept_id=2 THEN '█' END dept2,
         CASE WHEN dept_id=3 THEN '█' END dept3,
         CASE WHEN dept_id=4 THEN '█' END dept4,
         CASE WHEN dept_id=5 THEN '█' END dept5,
         CASE WHEN dept_id=6 THEN '█' END dept6
  FROM employee
)
SELECT min(dept1) "行政管理部",
       min(dept2) "人力资源部",
       min(dept3) "财务部",
       min(dept4) "研发部",
       min(dept5) "销售部",
       min(dept6) "保卫部"
FROM d
GROUP BY rn
ORDER BY 1 DESC, 2 DESC, 3 DESC, 4 DESC, 5 DESC, 6 DESC;

 

总结

本文利用 SQL 中的分组聚合、字符串函数、CASE 条件表达式以及窗口函数等功能实现了水平柱状图和垂直柱状图。