個人中心
退出
SQL 案例分析之月度銷量冠軍
作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學，十多年數據庫管理與開發(fā)經驗，目前在一家全球性的金融公司從事數據庫架構設計。CSDN學院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net
文章目錄
問題描述
Oracle
MySQL
Microsoft SQL Server
PostgreSQL
SQLite
今天給大家介紹一個 SQL 實戰(zhàn)案例：通過橫向子查詢（LATERAL subquery）實現(xiàn)銷量冠軍分析。文章描述了 Oracle、MySQL、Microsoft SQL Server、PostgreSQL 以及 SQLite 中的實現(xiàn)方法。
問題描述
公司的銷售人員負責各種產品的銷售，emp_sales 表中記錄了每個銷售人員每個月份的銷量數據。以下是該表中的一些示例數據：
SELECT * FROM emp_sales;
emp_id|sale_year|sale_month|amount |
------|---------|----------|--------|
19| 2021| 1|15672.53|
20| 2021| 1|11160.46|
21| 2021| 1|13763.75|
22| 2021| 1|11210.34|
23| 2021| 1|14610.88|
24| 2021| 1|13747.64|
25| 2021| 1|12816.20|
19| 2021| 2|14413.77|
20| 2021| 2|14266.04|
21| 2021| 2|16984.42|
...
emp_sales 表中的字段分別表示員工編號、銷售年份、銷售月份以及銷售金額。
現(xiàn)在我們想要知道每個月份的銷售冠軍，也就是每個月份銷售金額最高的員工。請問應該如何使用 SQL 查詢實現(xiàn)？
從原理上來說，我們可以分別通過查詢找出每個月份銷量最高的員工。例如 2021 年 1 月份的銷量冠軍可以使用一下查詢語句：
-- MySQL、PostgreSQL 以及 SQLite
SELECT s.sale_year, s.sale_month, e.emp_name, s.amount
FROM emp_sales s
JOIN employee e ON (e.emp_id = s.emp_id)
WHERE s.sale_year = 2021
AND s.sale_month = 1
ORDER BY s.amount DESC
LIMIT 1;
-- Oracle 和 SQL Server
SELECT s.sale_year, s.sale_month, e.emp_name, s.amount
FROM emp_sales s
JOIN employee e ON (e.emp_id = s.emp_id)
WHERE s.sale_year = 2021
AND s.sale_month = 1
ORDER BY s.amount DESC
offset 0 ROWS
FETCH FIRST 1 ROWS ONLY;
sale_year|sale_month|emp_name|amount |
---------|----------|--------|--------|
2021| 1|龐統(tǒng) |15672.53|
然后使用同樣的方法獲取其他月份分銷量冠軍。
顯然，這種方法不夠智能，我們需要一種查詢語句可以為每個月份返回一個最高銷量的員工。下面我們就來介紹如何通過 SQL 橫向子查詢實現(xiàn)這一功能。
Oracle
Oracle 提供了橫向子查詢，子查詢可以使用 FROM 子句中出現(xiàn)在它之前的表或者查詢結果中的字段。例如：
SELECT d.sale_year, d.sale_month, e.emp_name, s.amount
FROM (SELECT DISTINCT sale_year, sale_month FROM emp_sales) d
CROSS JOIN
LATERAL (SELECT emp_id, amount
FROM emp_sales
WHERE sale_year = d.sale_year
AND sale_month = d.sale_month
ORDER BY amount DESC
FETCH FIRST 1 ROWS ONLY) s
JOIN employee e
ON (e.emp_id = s.emp_id)
ORDER BY d.sale_year, d.sale_month;
其中子查詢 d 的作用是獲取銷售數據中的所有年度和月份信息。LATERAL 關鍵字表示橫向子查詢，子查詢 s 通過使用左側查詢結果中的年度和月份數據返回了每個月銷售金額最高的員工和相應的金額，最后連接員工表獲得員工的姓名。查詢返回的結果如下。
SALE_YEAR|SALE_MONTH|EMP_NAME|AMOUNT |
---------|----------|--------|--------|
2021| 1|龐統(tǒng) |15672.53|
2021| 2|黃權 |16984.42|
2021| 3|鄧芝 |16377.44|
2021| 4|簡雍 |18744.78|
2021| 5|蔣琬 |19466.56|
2021| 6|龐統(tǒng) |20154.83|
除了 LATERAL 關鍵字之外，Oracle 還提供了另一種實現(xiàn)橫向子查詢功能的 CROSS APPLY 語法。例如：
SELECT d.sale_year, d.sale_month, e.emp_name, s.amount
FROM (SELECT DISTINCT sale_year, sale_month FROM emp_sales) d
CROSS APPLY (SELECT emp_id, amount
FROM emp_sales
WHERE sale_year = d.sale_year
AND sale_month = d.sale_month
ORDER BY amount DESC
OFFSET 0 ROWS
FETCH FIRST 1 ROWS ONLY) s
JOIN employee e
ON (e.emp_id = s.emp_id)
ORDER BY d.sale_year, d.sale_month;
該語句和上面的示例等價，返回的結果也相同。
??關于 Oracle 橫向子查詢和 CROSS APPLY 以及 OUTER APPLY 子句的詳細信息可以參考官方文檔。
另一種實現(xiàn)方法就是使用窗口函數，參考下文中的 SQLite 部分。
MySQL
MySQL 8.0 提供了橫向子查詢，子查詢可以使用 FROM 子句中出現(xiàn)在它之前的表或者查詢結果中的字段。例如：
SELECT d.sale_year, d.sale_month, e.emp_name, s.amount
FROM (SELECT DISTINCT sale_year, sale_month FROM emp_sales) d
CROSS JOIN
LATERAL (SELECT emp_id, amount
FROM emp_sales
WHERE sale_year = d.sale_year
AND sale_month = d.sale_month
ORDER BY amount DESC
LIMIT 1) s
JOIN employee e
ON (e.emp_id = s.emp_id)
ORDER BY d.sale_year, d.sale_month;
sale_year|sale_month|emp_name|amount |
---------|----------|--------|--------|
2021| 1|龐統(tǒng) |15672.53|
2021| 2|黃權 |16984.42|
2021| 3|鄧芝 |16377.44|
2021| 4|簡雍 |18744.78|
2021| 5|蔣琬 |19466.56|
2021| 6|龐統(tǒng) |20154.83|
其中子查詢 d 的作用是獲取銷售數據中的所有年度和月份信息。LATERAL 關鍵字表示橫向子查詢，子查詢 s 可以使用 FROM 子句中出現(xiàn)在它之前的表或者查詢結果中的字段。
??關于 MySQL 橫向子查詢的詳細信息，可以參考官方文檔。
另一種實現(xiàn)方法就是使用窗口函數，參考下文中的 SQLite 部分。
Microsoft SQL Server
Microsoft SQL Server 沒有實現(xiàn) LATERAL 關鍵字，但是可以使用和 Oracle 相同的 CROSS APPLY 子句實現(xiàn)橫向子查詢語句。例如：
SELECT d.sale_year, d.sale_month, e.emp_name, s.amount
FROM (SELECT DISTINCT sale_year, sale_month
FROM emp_sales) d
CROSS APPLY (SELECT emp_id, amount
FROM emp_sales
WHERE sale_year = d.sale_year
AND sale_month = d.sale_month
ORDER BY amount DESC
OFFSET 0 ROWS
FETCH FIRST 1 ROWS ONLY) s
JOIN employee e
ON (e.emp_id = s.emp_id)
ORDER BY d.sale_year, d.sale_month;
其中子查詢 d 的作用是獲取銷售數據中的所有年度和月份信息。CROSS APPLY 關鍵字表示子查詢 s 通過使用左側查詢結果中的年度和月份數據返回了每個月銷售金額最高的員工和相應的金額。以上查詢通過使用左側查詢結果（d）中的年度和月份數據返回了每個月銷售金額最高的員工和相應的金額：
sale_year|sale_month|emp_name|amount |
---------|----------|--------|--------|
2021| 1|龐統(tǒng) |15672.53|
2021| 2|黃權 |16984.42|
2021| 3|鄧芝 |16377.44|
2021| 4|簡雍 |18744.78|
2021| 5|蔣琬 |19466.56|
2021| 6|龐統(tǒng) |20154.83|
??關于 Microsoft SQL Server 中的 CROSS APPLY 和 OUTER APPLY 子句的詳細介紹，可以參考官方文檔。
另一種實現(xiàn)方法就是使用窗口函數，參考下文中的 SQLite 部分。
PostgreSQL
PostgreSQL 實現(xiàn)了橫向子查詢，子查詢可以使用 FROM 子句中出現(xiàn)在它之前的表或者查詢結果中的字段。例如：
SELECT d.sale_year, d.sale_month, e.emp_name, s.amount
FROM (SELECT DISTINCT sale_year, sale_month FROM emp_sales) d
CROSS JOIN
LATERAL (SELECT emp_id, amount
FROM emp_sales
WHERE sale_year = d.sale_year
AND sale_month = d.sale_month
ORDER BY amount DESC
LIMIT 1) s
JOIN employee e
ON (e.emp_id = s.emp_id)
ORDER BY d.sale_year, d.sale_month;
其中子查詢 d 的作用是獲取銷售數據中的所有年度和月份信息，橫向子查詢 s 通過使用左側查詢結果中的年度和月份數據返回了每個月銷售金額最高的員工和相應的金額，最后連接員工表獲得員工的姓名。查詢返回的結果如下。
sale_year|sale_month|emp_name|amount |
---------|----------|--------|--------|
2021| 1|龐統(tǒng) |15672.53|
2021| 2|黃權 |16984.42|
2021| 3|鄧芝 |16377.44|
2021| 4|簡雍 |18744.78|
2021| 5|蔣琬 |19466.56|
2021| 6|龐統(tǒng) |20154.83|
??關于 PostgreSQL 橫向子查詢的詳細介紹，可以參考官方文檔。
另一種實現(xiàn)方法就是使用窗口函數，參考下文中的 SQLite 部分。
SQLite
SQLite 目前沒有提供橫向子查詢，因此不能通過這種方式返回月度銷量冠軍。我們可以利用窗口函數實現(xiàn)同樣的功能，例如：
SELECT s.sale_year, s.sale_month, e.emp_name, s.amount
FROM (SELECT emp_id, sale_year, sale_month, amount,
RANK() OVER (PARTITION BY sale_year, sale_month ORDER BY amount desc) AS rk
FROM emp_sales) s
JOIN employee e
ON (e.emp_id = s.emp_id)
WHERE rk = 1
ORDER BY s.sale_year, s.sale_month;
窗口函數 RANK 返回了按照月份分組后的銷量排名，然后我們返回了排名第一的數據，也就是月度銷量冠軍。
以上窗口函數的實現(xiàn)語法也適用于其他數據庫，具體介紹可以參考這篇文章。
總結
本文通過一個月度銷量冠軍分析的案例，介紹了如何利用橫向子查詢引用子查詢左側表中的字段。另外，我們還給出了窗口函數實現(xiàn)相同功能的語法。
分類導航