SQL窗口函數(shù)之排名窗口函數(shù)怎么使用

157次閱讀

共計(jì) 7389 個(gè)字符，預(yù)計(jì)需要花費(fèi) 19 分鐘才能閱讀完成。

這篇文章主要介紹“SQL 窗口函數(shù)之排名窗口函數(shù)怎么使用”的相關(guān)知識(shí)，丸趣 TV 小編通過(guò)實(shí)際案例向大家展示操作過(guò)程，操作方法簡(jiǎn)單快捷，實(shí)用性強(qiáng)，希望這篇“SQL 窗口函數(shù)之排名窗口函數(shù)怎么使用”文章能幫助大家解決問(wèn)題。

取值窗口函數(shù)可以用于返回窗口內(nèi)指定位置的數(shù)據(jù)行。常見(jiàn)的取值窗口函數(shù)如下：

LAG 函數(shù)可以返回窗口內(nèi)當(dāng)前行之前的第 N 行數(shù)據(jù)。LEAD 函數(shù)可以返回窗口內(nèi)當(dāng)前行之后的第 N 行數(shù)據(jù)。FIRST_VALUE 函數(shù)可以返回窗口內(nèi)第一行數(shù)據(jù)。LAST_VALUE 函數(shù)可以返回窗口內(nèi)最后一行數(shù)據(jù)。NTH_VALUE 函數(shù)可以返回窗口內(nèi)第 N 行數(shù)據(jù)。

其中，LAG 函數(shù)和 LEAD 函數(shù)不支持動(dòng)態(tài)的窗口大小，它們以整個(gè)分區(qū)作為分析的窗口。

案例分析案例使用的示例表

下面的查詢中會(huì)用到一張表，sales_monthly 表中存儲(chǔ)了商品銷量信息，product 表示產(chǎn)品名稱，ym 表示年月，amount 表示銷售金額（元）。

以下是該表中的部分?jǐn)?shù)據(jù)：

這個(gè)表的初始化腳本可以在文章底部獲取。

1. 環(huán)比分析

環(huán)比增長(zhǎng)指的是本期數(shù)據(jù)與上期數(shù)據(jù)相比的增長(zhǎng)，例如，產(chǎn)品 2019 年 6 月的銷售額與 2019 年 5 月的銷售額相比增加的部分。

以下語(yǔ)句統(tǒng)計(jì)了各種產(chǎn)品每個(gè)月的環(huán)比增長(zhǎng)率：

SELECT s.product AS  產(chǎn)品 , s.ym AS  年月 , s.amount AS  銷售額 ,
 ( 
 (s.amount - LAG(s.amount,1) OVER (PARTITION BY product ORDER BY s.ym))/
 LAG(s.amount,1) OVER (PARTITION BY product ORDER BY s.ym)
 ) * 100 AS  環(huán)比增長(zhǎng)率 (%) 
FROM sales_monthly s
ORDER BY s.product,s.ym

其中，LAG（amount，1）表示獲取上一期的銷售額，PARTITION BY 選項(xiàng)表示按照產(chǎn)品分區(qū)，ORDER BY 選項(xiàng)表示按照月份進(jìn)行排序。

當(dāng)前月份的銷售額 amount 減去上一期的銷售額，再除以上一期的銷售額，就是環(huán)比增長(zhǎng)率。

該查詢返回的結(jié)果如下：

2018 年 1 月是第一期，因此其環(huán)比增長(zhǎng)率為空。

“桔子”2018 年 2 月的環(huán)比增長(zhǎng)率約為 0.2856%（（10183-10154）/10154×100），依此類推。

2. 同比分析

同比增長(zhǎng)指的是本期數(shù)據(jù)與上一年度或歷史同期相比的增長(zhǎng)，例如，產(chǎn)品 2019 年 6 月的銷售額與 2018 年 6 月的銷售額相比增加的部分。

以下語(yǔ)句統(tǒng)計(jì)了各種產(chǎn)品每個(gè)月的同比增長(zhǎng)率：

SELECT s.product AS  產(chǎn)品 , s.ym AS  年月 , s.amount AS  銷售額 ,
 ( 
 (s.amount - LAG(s.amount,12) OVER (PARTITION BY product ORDER BY s.ym))/
 LAG(s.amount,12) OVER (PARTITION BY product ORDER BY s.ym)
 ) * 100 AS  同比增長(zhǎng)率 (%) 
FROM sales_monthly s
ORDER BY s.product,s.ym

其中，LAG（amount，12）表示當(dāng)前月份之前第 12 期的銷售額，也就是去年同月份的銷售額。

PARTITION BY 選項(xiàng)表示按照產(chǎn)品分區(qū)，ORDER BY 選項(xiàng)表示按照月份進(jìn)行排序。

當(dāng)前月份的銷售額 amount 減去去年同期的銷售額，再除以去年同期的銷售額，就是同比增長(zhǎng)率。

該查詢返回的結(jié)果如下：

2018 年的 12 期數(shù)據(jù)都沒(méi)有對(duì)應(yīng)的同比增長(zhǎng)率，“桔子”2019 年 1 月的同比增長(zhǎng)率約為 9.3067%（（11099-10154）/10154×100），依此類推。

提示：LEAD 函數(shù)與 LAG 函數(shù)的使用方法類似，不過(guò)它的返回結(jié)果是當(dāng)前行之后的第 N 行數(shù)據(jù)。

3. 復(fù)合增長(zhǎng)率

復(fù)合增長(zhǎng)率是第 N 期的數(shù)據(jù)除以第一期的基準(zhǔn)數(shù)據(jù)，然后開(kāi) N - 1 次方再減去 1 得到的結(jié)果。

假如 2018 年的產(chǎn)品銷售額為 10000，2019 年的產(chǎn)品銷售額為 12500，2020 年的產(chǎn)品銷售額為 15000。那么這兩年的復(fù)合增長(zhǎng)率的計(jì)算方式如下：

以年度為單位計(jì)算的復(fù)合增長(zhǎng)率被稱為年均復(fù)合增長(zhǎng)率，以月度為單位計(jì)算的復(fù)合增長(zhǎng)率被稱為月均復(fù)合增長(zhǎng)率。

以下查詢統(tǒng)計(jì)了自 2018 年 1 月以來(lái)不同產(chǎn)品的月均銷售額復(fù)合增長(zhǎng)率：

WITH s (product,ym,amount,first_amount,num) AS (
 SELECT m.product, m.ym, m.amount,
 FIRST_VALUE(m.amount) OVER (PARTITION BY m.product ORDER BY m.ym),
 ROW_NUMBER() OVER (PARTITION BY m.product ORDER BY m.ym)
 FROM sales_monthly m
 
SELECT product AS  產(chǎn)品 , ym AS  年月 ,amount AS  銷售額 ,
 (POWER( amount/first_amount, 1.0/NULLIF(num-1,0)) -1)*100 AS  月均復(fù)合增長(zhǎng)率 (%) 
FROM s
ORDER BY product, ym

首先定義了一個(gè)通用表表達(dá)式，其中 FIRST_VALUE（amount）返回了第一期（201801）的銷售額，ROW_NUMBER 函數(shù)返回了每一期的編號(hào)。

主查詢中的 POWER 函數(shù)用于執(zhí)行開(kāi)方運(yùn)算，NULLIF 函數(shù)用于處理第一期數(shù)據(jù)的除零錯(cuò)誤，常量 1.0 用于避免由整數(shù)除法所導(dǎo)致的精度丟失問(wèn)題。

該查詢返回的結(jié)果如下：

2018 年 1 月是第一期，因此其產(chǎn)品月均銷售額復(fù)合增長(zhǎng)率為空。

“桔子”2018 年 2 月的月均銷售額復(fù)合增長(zhǎng)率等于它的環(huán)比增長(zhǎng)率，2018 年 3 月的月均銷售額復(fù)合增長(zhǎng)率等于 0.4471%，依此類推。

4. 不同產(chǎn)品最高和最低銷售額

以下語(yǔ)句統(tǒng)計(jì)了不同產(chǎn)品最低銷售額、最高銷售額以及第三高銷售額所在的月份：

 SELECT product AS  產(chǎn)品 , ym AS  年月 ,amount AS  銷售額 ,
 
 FIRST_VALUE(m.ym) OVER (
 PARTITION BY m.product ORDER BY m.amount DESC
 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
 ) AS  最高銷售額月份 ,
 
 LAST_VALUE(m.ym) OVER (
 PARTITION BY m.product ORDER BY m.amount DESC
 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
 ) AS  最低銷售額月份 ,
 
 NTH_VALUE(m.ym,3) OVER (
 PARTITION BY m.product ORDER BY m.amount DESC
 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
 ) AS  第三高銷售額月份 
 
 FROM sales_monthly m
 ORDER BY product, ym;

三個(gè)窗口函數(shù)的 OVER 子句相同，PARTITION BY 選項(xiàng)表示按照產(chǎn)品進(jìn)行分區(qū)，ORDER BY 選項(xiàng)表示按照銷售額從高到低排序。

以上三個(gè)函數(shù)的默認(rèn)窗口都是從分區(qū)的第一行到當(dāng)前行，因此我們將窗口擴(kuò)展到了整個(gè)分區(qū)。

該查詢返回的結(jié)果如下：

“桔子”的最高銷售額出現(xiàn)在 2019 年 6 月，最低銷售額出現(xiàn)在 2018 年 1 月，第三高銷售額出現(xiàn)在 2019 年 4 月。

示例表和腳本

--  創(chuàng)建銷量表 sales_monthly
-- product 表示產(chǎn)品名稱，ym 表示年月，amount 表示銷售金額（元）CREATE TABLE sales_monthly(product VARCHAR(20), ym VARCHAR(10), amount NUMERIC(10, 2));
 
--  生成測(cè)試數(shù)據(jù)
INSERT INTO sales_monthly (product,ym,amount) VALUES (蘋(píng)果 , 201801 ,10159.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (蘋(píng)果 , 201802 ,10211.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (蘋(píng)果 , 201803 ,10247.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (蘋(píng)果 , 201804 ,10376.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (蘋(píng)果 , 201805 ,10400.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (蘋(píng)果 , 201806 ,10565.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (蘋(píng)果 , 201807 ,10613.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (蘋(píng)果 , 201808 ,10696.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (蘋(píng)果 , 201809 ,10751.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (蘋(píng)果 , 201810 ,10842.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (蘋(píng)果 , 201811 ,10900.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (蘋(píng)果 , 201812 ,10972.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (蘋(píng)果 , 201901 ,11155.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (蘋(píng)果 , 201902 ,11202.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (蘋(píng)果 , 201903 ,11260.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (蘋(píng)果 , 201904 ,11341.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (蘋(píng)果 , 201905 ,11459.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (蘋(píng)果 , 201906 ,11560.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (香蕉 , 201801 ,10138.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (香蕉 , 201802 ,10194.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (香蕉 , 201803 ,10328.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (香蕉 , 201804 ,10322.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (香蕉 , 201805 ,10481.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (香蕉 , 201806 ,10502.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (香蕉 , 201807 ,10589.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (香蕉 , 201808 ,10681.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (香蕉 , 201809 ,10798.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (香蕉 , 201810 ,10829.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (香蕉 , 201811 ,10913.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (香蕉 , 201812 ,11056.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (香蕉 , 201901 ,11161.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (香蕉 , 201902 ,11173.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (香蕉 , 201903 ,11288.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (香蕉 , 201904 ,11408.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (香蕉 , 201905 ,11469.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (香蕉 , 201906 ,11528.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (桔子 , 201801 ,10154.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (桔子 , 201802 ,10183.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (桔子 , 201803 ,10245.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (桔子 , 201804 ,10325.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (桔子 , 201805 ,10465.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (桔子 , 201806 ,10505.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (桔子 , 201807 ,10578.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (桔子 , 201808 ,10680.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (桔子 , 201809 ,10788.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (桔子 , 201810 ,10838.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (桔子 , 201811 ,10942.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (桔子 , 201812 ,10988.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (桔子 , 201901 ,11099.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (桔子 , 201902 ,11181.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (桔子 , 201903 ,11302.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (桔子 , 201904 ,11327.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (桔子 , 201905 ,11423.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (桔子 , 201906 ,11524.00);

關(guān)于“SQL 窗口函數(shù)之排名窗口函數(shù)怎么使用”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)，可以關(guān)注丸趣 TV 行業(yè)資訊頻道，丸趣 TV 小編每天都會(huì)為大家更新不同的知識(shí)點(diǎn)。

正文完