使用 SQL 創(chuàng)建柱狀圖，是真的嗎？

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net


 

大家好！我是只談技術(shù)不剪發(fā)的 Tony 老師。

今天我們介紹如何使用 SQL 語句創(chuàng)建水平柱狀圖和垂直柱狀圖，使用的功能包括分組聚合、字符串函數(shù)、CASE 條件表達(dá)式以及窗口函數(shù)。本文描述的方法支持但不限于 Oracle、MySQL/MariaDB、Microsoft SQL Server、PostgreSQL 以及 SQLite 等數(shù)據(jù)庫。

如果覺得文章有用，歡迎評論??、點(diǎn)贊??、推薦??

    ??本文使用的示例表和數(shù)據(jù)可以點(diǎn)此下載。

水平柱狀圖

員工表（employee）中存儲了每個部門中的員工信息，部門表（department）中存儲了部門的信息。假如我們想要統(tǒng)計(jì)每個部門的員工數(shù)量，并且以水平直方圖的形式進(jìn)行顯示。以下是 MySQL 數(shù)據(jù)庫中的實(shí)現(xiàn)：

-- MySQL/MariaDB
SELECT d.dept_name "部門名稱",
       count(e.emp_id) "員工數(shù)量",
       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 聚合函數(shù)得到了每個部門的員工人數(shù)，左外連接確保部門沒有員工時也會返回相應(yīng)的信息。然后我們通過 repeat 函數(shù)創(chuàng)建一個基于員工人數(shù)的水平直方圖。最終查詢返回的結(jié)果如下：










對于其他數(shù)據(jù)庫，實(shí)現(xiàn)的員工相同，只是需要替換相應(yīng)的字符串函數(shù)：

-- Microsoft SQL Server
SELECT d.dept_name "部門名稱",
       count(e.emp_id) "員工數(shù)量",
       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) "員工數(shù)量",
       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) "員工數(shù)量",
       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) "員工數(shù)量",
       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 函數(shù)替換 repeat 函數(shù)。

對于 PostgreSQL，我們需要為 repeat 函數(shù)明確指定一個 integer 類型的參數(shù)。

對于 Oracle，我們可以使用 lpad 函數(shù)替換 repeat 函數(shù)。另外，MySQL/MariaDB 和 PostgreSQL 也支持 lpad 函數(shù)，也可以使用這種方式。

SQLite 不支持 repeat 函數(shù)或者替換的函數(shù)，我們可以先利用 zeroblob 函數(shù)生成一個由 0x00 組成的 BLOB，然后通過 replace 函數(shù)將其替換為柱狀圖。
垂直柱狀圖

如果我們將水平直方圖逆時針旋轉(zhuǎn) 90 度，就可以得到垂直柱狀圖。以下是 MySQL/MariaDB、Microsoft SQL Server 以及 SQLite 中的實(shí)現(xiàn)：

-- 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) "財務(wù)部",
       min(dept4) "研發(fā)部",
       min(dept5) "銷售部",
       min(dept6) "保衛(wèi)部"
FROM d
GROUP BY rn
ORDER BY 1, 2, 3, 4, 5, 6;

 

我們首先創(chuàng)建了一個通用表表達(dá)式（CTE），利用 row_number 窗口函數(shù)得到了每個員工在各自部門中的編號，同時通過 CASE 表達(dá)式將對應(yīng)的員工使用 █ 進(jìn)行表示，此時生成的數(shù)據(jù)如下。

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

 

    ??關(guān)于窗口函數(shù)的介紹和案例分析可以參考這篇文章和這篇文章。
    ??關(guān)于通用表表達(dá)式以及各種數(shù)據(jù)庫中的語法可以參考這篇文章，以及一個分析社交網(wǎng)絡(luò)關(guān)系的實(shí)戰(zhàn)案例。

最后我們在主查詢語句中利用分組和 min 聚合函數(shù)創(chuàng)建一個基于員工人數(shù)的垂直柱狀圖。最終查詢返回的結(jié)果如下：

行政管理部|人力資源部|財務(wù)部|研發(fā)部|銷售部|保衛(wèi)部|
---------|---------|-----|------|-----|------|
         |         |     |█     |█    |      |
         |         |     |█     |█    |      |
         |         |     |█     |█    |      |
         |         |     |█     |█    |      |
         |         |     |█     |█    |      |
         |         |     |█     |█    |      |
█        |█        |     |█     |█    |      |
█        |█        |█    |█     |█    |      |
█        |█        |█    |█     |█    |      |

 

對于 Oracle 和 PostgreSQL，查詢的實(shí)現(xiàn)和上面的語句幾乎相同，只需要將排序修改為降序即可：

--  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) "財務(wù)部",
       min(dept4) "研發(fā)部",
       min(dept5) "銷售部",
       min(dept6) "保衛(wèi)部"
FROM d
GROUP BY rn
ORDER BY 1 DESC, 2 DESC, 3 DESC, 4 DESC, 5 DESC, 6 DESC;

 

總結(jié)

本文利用 SQL 中的分組聚合、字符串函數(shù)、CASE 條件表達(dá)式以及窗口函數(shù)等功能實(shí)現(xiàn)了水平柱狀圖和垂直柱狀圖。