在线午夜精品自拍小视频_无码av无码专区线_亚洲无码精品人妻_人人澡欧美一区

SQL 案例分析之月度銷(xiāo)量冠軍

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


文章目錄

        問(wèn)題描述
        Oracle
        MySQL
        Microsoft SQL Server
        PostgreSQL
        SQLite
        總結(jié)

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

今天給大家介紹一個(gè) SQL 實(shí)戰(zhàn)案例：通過(guò)橫向子查詢(xún)（LATERAL subquery）實(shí)現(xiàn)銷(xiāo)量冠軍分析。文章描述了 Oracle、MySQL、Microsoft SQL Server、PostgreSQL 以及 SQLite 中的實(shí)現(xiàn)方法。

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

    ??本文使用的示例表可以點(diǎn)此下載。

問(wèn)題描述

公司的銷(xiāo)售人員負(fù)責(zé)各種產(chǎn)品的銷(xiāo)售，emp_sales 表中記錄了每個(gè)銷(xiāo)售人員每個(gè)月份的銷(xiāo)量數(shù)據(jù)。以下是該表中的一些示例數(shù)據(jù)：

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 表中的字段分別表示員工編號(hào)、銷(xiāo)售年份、銷(xiāo)售月份以及銷(xiāo)售金額。

現(xiàn)在我們想要知道每個(gè)月份的銷(xiāo)售冠軍，也就是每個(gè)月份銷(xiāo)售金額最高的員工。請(qǐng)問(wèn)應(yīng)該如何使用 SQL 查詢(xún)實(shí)現(xiàn)？

從原理上來(lái)說(shuō)，我們可以分別通過(guò)查詢(xún)找出每個(gè)月份銷(xiāo)量最高的員工。例如 2021 年 1 月份的銷(xiāo)量冠軍可以使用一下查詢(xún)語(yǔ)句：

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


然后使用同樣的方法獲取其他月份分銷(xiāo)量冠軍。

顯然，這種方法不夠智能，我們需要一種查詢(xún)語(yǔ)句可以為每個(gè)月份返回一個(gè)最高銷(xiāo)量的員工。下面我們就來(lái)介紹如何通過(guò) SQL 橫向子查詢(xún)實(shí)現(xiàn)這一功能。
Oracle

Oracle 提供了橫向子查詢(xún)，子查詢(xún)可以使用 FROM 子句中出現(xiàn)在它之前的表或者查詢(xún)結(jié)果中的字段。例如：

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;


其中子查詢(xún) d 的作用是獲取銷(xiāo)售數(shù)據(jù)中的所有年度和月份信息。LATERAL 關(guān)鍵字表示橫向子查詢(xún)，子查詢(xún) s 通過(guò)使用左側(cè)查詢(xún)結(jié)果中的年度和月份數(shù)據(jù)返回了每個(gè)月銷(xiāo)售金額最高的員工和相應(yīng)的金額，最后連接員工表獲得員工的姓名。查詢(xún)返回的結(jié)果如下。

SALE_YEAR|SALE_MONTH|EMP_NAME|AMOUNT  |
---------|----------|--------|--------|
     2021|         1|龐統(tǒng)    |15672.53|
     2021|         2|黃權(quán)    |16984.42|
     2021|         3|鄧芝    |16377.44|
     2021|         4|簡(jiǎn)雍    |18744.78|
     2021|         5|蔣琬    |19466.56|
     2021|         6|龐統(tǒng)    |20154.83|



除了 LATERAL 關(guān)鍵字之外，Oracle 還提供了另一種實(shí)現(xiàn)橫向子查詢(xún)功能的 CROSS APPLY 語(yǔ)法。例如：

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;


該語(yǔ)句和上面的示例等價(jià)，返回的結(jié)果也相同。

    ??關(guān)于 Oracle 橫向子查詢(xún)和 CROSS APPLY 以及 OUTER APPLY 子句的詳細(xì)信息可以參考官方文檔。

另一種實(shí)現(xiàn)方法就是使用窗口函數(shù)，參考下文中的 SQLite 部分。
MySQL

MySQL 8.0 提供了橫向子查詢(xún)，子查詢(xún)可以使用 FROM 子句中出現(xiàn)在它之前的表或者查詢(xún)結(jié)果中的字段。例如：

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|黃權(quán)    |16984.42|
     2021|         3|鄧芝    |16377.44|
     2021|         4|簡(jiǎn)雍    |18744.78|
     2021|         5|蔣琬    |19466.56|
     2021|         6|龐統(tǒng)    |20154.83|



其中子查詢(xún) d 的作用是獲取銷(xiāo)售數(shù)據(jù)中的所有年度和月份信息。LATERAL 關(guān)鍵字表示橫向子查詢(xún)，子查詢(xún) s 可以使用 FROM 子句中出現(xiàn)在它之前的表或者查詢(xún)結(jié)果中的字段。

    ??關(guān)于 MySQL 橫向子查詢(xún)的詳細(xì)信息，可以參考官方文檔。

另一種實(shí)現(xiàn)方法就是使用窗口函數(shù)，參考下文中的 SQLite 部分。
Microsoft SQL Server

Microsoft SQL Server 沒(méi)有實(shí)現(xiàn) LATERAL 關(guān)鍵字，但是可以使用和 Oracle 相同的 CROSS APPLY 子句實(shí)現(xiàn)橫向子查詢(xún)語(yǔ)句。例如：

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;



其中子查詢(xún) d 的作用是獲取銷(xiāo)售數(shù)據(jù)中的所有年度和月份信息。CROSS APPLY 關(guān)鍵字表示子查詢(xún) s 通過(guò)使用左側(cè)查詢(xún)結(jié)果中的年度和月份數(shù)據(jù)返回了每個(gè)月銷(xiāo)售金額最高的員工和相應(yīng)的金額。以上查詢(xún)通過(guò)使用左側(cè)查詢(xún)結(jié)果（d）中的年度和月份數(shù)據(jù)返回了每個(gè)月銷(xiāo)售金額最高的員工和相應(yīng)的金額：

sale_year|sale_month|emp_name|amount  |
---------|----------|--------|--------|
     2021|         1|龐統(tǒng)    |15672.53|
     2021|         2|黃權(quán)    |16984.42|
     2021|         3|鄧芝    |16377.44|
     2021|         4|簡(jiǎn)雍    |18744.78|
     2021|         5|蔣琬    |19466.56|
     2021|         6|龐統(tǒng)    |20154.83|



    ??關(guān)于 Microsoft SQL Server 中的 CROSS APPLY 和 OUTER APPLY 子句的詳細(xì)介紹，可以參考官方文檔。

另一種實(shí)現(xiàn)方法就是使用窗口函數(shù)，參考下文中的 SQLite 部分。
PostgreSQL

PostgreSQL 實(shí)現(xiàn)了橫向子查詢(xún)，子查詢(xún)可以使用 FROM 子句中出現(xiàn)在它之前的表或者查詢(xún)結(jié)果中的字段。例如：

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;



其中子查詢(xún) d 的作用是獲取銷(xiāo)售數(shù)據(jù)中的所有年度和月份信息，橫向子查詢(xún) s 通過(guò)使用左側(cè)查詢(xún)結(jié)果中的年度和月份數(shù)據(jù)返回了每個(gè)月銷(xiāo)售金額最高的員工和相應(yīng)的金額，最后連接員工表獲得員工的姓名。查詢(xún)返回的結(jié)果如下。

sale_year|sale_month|emp_name|amount  |
---------|----------|--------|--------|
     2021|         1|龐統(tǒng)    |15672.53|
     2021|         2|黃權(quán)    |16984.42|
     2021|         3|鄧芝    |16377.44|
     2021|         4|簡(jiǎn)雍    |18744.78|
     2021|         5|蔣琬    |19466.56|
     2021|         6|龐統(tǒng)    |20154.83|



    ??關(guān)于 PostgreSQL 橫向子查詢(xún)的詳細(xì)介紹，可以參考官方文檔。

另一種實(shí)現(xiàn)方法就是使用窗口函數(shù)，參考下文中的 SQLite 部分。
SQLite

SQLite 目前沒(méi)有提供橫向子查詢(xún)，因此不能通過(guò)這種方式返回月度銷(xiāo)量冠軍。我們可以利用窗口函數(shù)實(shí)現(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;



窗口函數(shù) RANK 返回了按照月份分組后的銷(xiāo)量排名，然后我們返回了排名第一的數(shù)據(jù)，也就是月度銷(xiāo)量冠軍。

以上窗口函數(shù)的實(shí)現(xiàn)語(yǔ)法也適用于其他數(shù)據(jù)庫(kù)，具體介紹可以參考這篇文章。
總結(jié)

本文通過(guò)一個(gè)月度銷(xiāo)量冠軍分析的案例，介紹了如何利用橫向子查詢(xún)引用子查詢(xún)左側(cè)表中的字段。另外，我們還給出了窗口函數(shù)實(shí)現(xiàn)相同功能的語(yǔ)法。