7

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

 3 years ago
source link: https://blog.csdn.net/horses/article/details/112973550
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client
SQL 案例分析之月度销量冠军_Tony.Dong的专栏-CSDN博客

在这里插入图片描述

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

今天给大家介绍一个 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
         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 子句的详细信息可以参考官方文档。

另一种实现方法就是使用窗口函数，参考下文中的 SQLite 部分。

MySQL

MySQL 8.0 提供了横向子查询，子查询可以使用 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 
         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|庞统    |15672.53|
     2021|         2|黄权    |16984.42|
     2021|         3|邓芝    |16377.44|
     2021|         4|简雍    |18744.78|
     2021|         5|蒋琬    |19466.56|
     2021|         6|庞统    |20154.83|

其中子查询 d 的作用是获取销售数据中的所有年度和月份信息。LATERAL 关键字表示横向子查询，子查询 s 可以使用 FROM 子句中出现在它之前的表或者查询结果中的字段。

📝关于 MySQL 横向子查询的详细信息，可以参考官方文档。

另一种实现方法就是使用窗口函数，参考下文中的 SQLite 部分。

Microsoft SQL Server

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

📝关于 Microsoft SQL Server 中的 CROSS APPLY 和 OUTER APPLY 子句的详细介绍，可以参考官方文档。

另一种实现方法就是使用窗口函数，参考下文中的 SQLite 部分。

PostgreSQL

PostgreSQL 实现了横向子查询，子查询可以使用 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 
         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|庞统    |15672.53|
     2021|         2|黄权    |16984.42|
     2021|         3|邓芝    |16377.44|
     2021|         4|简雍    |18744.78|
     2021|         5|蒋琬    |19466.56|
     2021|         6|庞统    |20154.83|

📝关于 PostgreSQL 横向子查询的详细介绍，可以参考官方文档。

另一种实现方法就是使用窗口函数，参考下文中的 SQLite 部分。

SQLite

SQLite 目前没有提供横向子查询，因此不能通过这种方式返回月度销量冠军。我们可以利用窗口函数实现同样的功能，例如：

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 返回了按照月份分组后的销量排名，然后我们返回了排名第一的数据，也就是月度销量冠军。

以上窗口函数的实现语法也适用于其他数据库，具体介绍可以参考这篇文章。

本文通过一个月度销量冠军分析的案例，介绍了如何利用横向子查询引用子查询左侧表中的字段。另外，我们还给出了窗口函数实现相同功能的语法。


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK