首頁  >  文章  >  資料庫  >  SQL視窗函數詳解之排名視窗函數的使用

SQL視窗函數詳解之排名視窗函數的使用

WBOY
WBOY轉載
2022-09-08 17:44:472119瀏覽

這篇文章為大家帶來了關於SQL server的相關知識,其中主要介紹了SQL Server主鍵約束(PRIMARY KEY),主鍵是唯一標識表中每一行的一列或一組列,文章圍繞主題展開詳情,下面一起來看一下,希望對大家有幫助。

SQL視窗函數詳解之排名視窗函數的使用

推薦學習:《SQL教學

#關於視窗函數的基礎,請看文章SQL視窗函數

#取值視窗函數可以用來傳回視窗內指定位置的資料行。常見的取值視窗函數如下:

LAG函數可以傳回視窗內目前行之前的第N行資料。 LEAD函數可以傳回視窗內目前行之後的第N行資料。 FIRST_VALUE函數可以傳回視窗內第一行資料。 LAST_VALUE函數可以傳回視窗內最後一行資料。 NTH_VALUE函數可以傳回視窗內第N行資料。

其中,LAG函數和LEAD函數不支援動態的視窗大小,它們以整個分割區作為分析的視窗。

案例分析

案例使用的範例表

#下面的查詢中會用到一張表,sales_monthly表中儲存了商品銷量信息,product表示產品名稱, ym表示年月,amount表示銷售金額(元)。

以下是該表中的部分資料:

這個表的初始化腳本可以在文章底部取得。

1.環比分析

環比成長指的是本期資料與上期資料相比的成長,例如,產品2019年6月的銷售額與2019年5月的銷售額相比增加的部分。

以下語句統計了各種產品每個月的環比增長率:

SELECT s.product AS "产品", 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 "环比增长率(%)"
FROM sales_monthly s
ORDER BY s.product,s.ym

其中,LAG(amount,1)表示取得上一期的銷售額,PARTITION BY選項表示依照產品分區,ORDER BY選項表示依照月份進行排序。

當月的銷售額amount減去上一期的銷售額,再除以上一期的銷售額,就是環比成長率。

此查詢回傳的結果如下: 

2018年1月為第一期,因此其季成長率為空。

「桔子」2018年2月的環比成長率約為0.2856%((10183-10154)/10154×100),依此類推。

2.年比分析

年成長指的是本期資料與上一年或歷史同期相比的成長,例如,產品2019年6月的銷售額與2018年6月的銷售額相比增加的部分。

以下語句統計了各種產品每個月的同比增長率:

SELECT s.product AS "产品", 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 "同比增长率(%)"
FROM sales_monthly s
ORDER BY s.product,s.ym

其中,LAG(amount,12)表示當前月份之前第12期的銷售額,也就是去年同月份的銷售量。

PARTITION BY選項表示依照產品分割區,ORDER BY選項表示依照月份進行排序。

當月的銷售額amount減去去年同期的銷售額,再除以去年同期的銷售額,就是同比成長率。

此查詢傳回的結果如下:

2018年的12期資料都沒有對應的年成長率,「桔子」2019年1月的年比成長率約為9.3067%((11099-10154)/10154×100),依此類推。

提示:LEAD函數與LAG函數的使用方法類似,不過它的回傳結果是目前行之後的第N行資料。

3.複合成長率

複合成長率是第N期的數據除以第一期的基準數據,然後開N-1次方再減去1得到的結果。

假如2018年的產品銷售額為10000,2019年的產品銷售額為12500,2020年的產品銷售額為15000。那麼這兩年的複合成長率的計算方式如下:

以年度為單位計算的複合成長率稱為年平均複合成長率,以月度為單位計算的複合成長率稱為月均複合成長率

以下查詢統計了自2018年1月以來不同產品的月均銷售額複合增長率:

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 "产品", ym AS "年月",amount AS "销售额",
       (POWER( amount/first_amount, 1.0/NULLIF(num-1,0)) -1)*100 AS "月均复合增长率(%)"
FROM s
ORDER BY product, ym

首先定義了一個通用表表達式,其中FIRST_VALUE(amount)返回了第一期(201801)的銷售額,ROW_NUMBER函數傳回了每一期的編號。

主查詢中的POWER函數用於執行開方運算,NULLIF函數用於處理第一期資料的除零錯誤,常數1.0用於避免由整數除法所導致的精度遺失問題。

此查詢回傳的結果如下:

2018年1月為第一期,因此其產品月均銷售額複合成長率為空。

“桔子”2018年2月的月均销售额复合增长率等于它的环比增长率,2018年3月的月均销售额复合增长率等于0.4471%,依此类推。 

4.不同产品最高和最低销售额

以下语句统计了不同产品最低销售额、最高销售额以及第三高销售额所在的月份:

  SELECT product AS "产品", 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;

三个窗口函数的OVER子句相同,PARTITION BY选项表示按照产品进行分区,ORDER BY选项表示按照销售额从高到低排序。

以上三个函数的默认窗口都是从分区的第一行到当前行,因此我们将窗口扩展到了整个分区。

该查询返回的结果如下: 

“桔子”的最高销售额出现在2019年6月,最低销售额出现在2018年1月,第三高销售额出现在2019年4月。

示例表和脚本

-- 创建销量表sales_monthly
-- product表示产品名称,ym表示年月,amount表示销售金额(元)
CREATE TABLE sales_monthly(product VARCHAR(20), ym VARCHAR(10), amount NUMERIC(10, 2));
 
-- 生成测试数据
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201801',10159.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201802',10211.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201803',10247.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201804',10376.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201805',10400.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201806',10565.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201807',10613.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201808',10696.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201809',10751.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201810',10842.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201811',10900.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201812',10972.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201901',11155.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201902',11202.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201903',11260.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201904',11341.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201905',11459.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','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);

推荐学习:《SQL教程

以上是SQL視窗函數詳解之排名視窗函數的使用的詳細內容。更多資訊請關注PHP中文網其他相關文章!

陳述:
本文轉載於:jb51.net。如有侵權,請聯絡admin@php.cn刪除