SQL 案例分析之月度销量冠军

文章目录

        问题描述
        Oracle
        MySQL
        Microsoft SQL Server
        PostgreSQL
        SQLite


今天给大家介绍一个 SQL 实战案例：通过横向子查询（LATERAL subquery）实现销量冠军分析。文章描述了 Oracle、MySQL、Microsoft SQL Server、PostgreSQL 以及 SQLite 中的实现方法。


问题描述

公司的销售人员负责各种产品的销售，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 表中的字段分别表示员工编号、销售年份、销售月份以及销售金额。

现在我们想要知道每个月份的销售冠军，也就是每个月份销售金额最高的员工。请问应该如何使用 SQL 查询实现？

从原理上来说，我们可以分别通过查询找出每个月份销量最高的员工。例如 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|庞统    |15672.53|


然后使用同样的方法获取其他月份分销量冠军。

显然，这种方法不够智能，我们需要一种查询语句可以为每个月份返回一个最高销量的员工。下面我们就来介绍如何通过 SQL 横向子查询实现这一功能。
Oracle

Oracle 提供了横向子查询，子查询可以使用 FROM 子句中出现在它之前的表或者查询结果中的字段。例如：

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|庞统    |15672.53|
     2021|         2|黄权    |16984.42|
     2021|         3|邓芝    |16377.44|
     2021|         4|简雍    |18744.78|
     2021|         5|蒋琬    |19466.56|
     2021|         6|庞统    |20154.83|

 

除了 LATERAL 关键字之外，Oracle 还提供了另一种实现横向子查询功能的 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

更多请见：http://www.mark-to-win.com/tutorial/51649.html