首頁 > 軟體

SQL視窗函數之取值視窗函數的使用

2022-04-21 13:01:43

關於視窗函數的基礎，請看文章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 取值視窗函數內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！


相關文章

  • 德漢姆中學就讀條件 德漢姆中學就讀條件

    <em>Mac</em>Book项目 2009年学校开始实施<em>Mac</em>Book项目,所有师生配备一本<em>Mac</em>Book,并同步更新了校园无线网络。学校每周进行电脑技术更新,每月发送技术支持资料,极大改变了教学及学习方式。因此2011

    2021-06-01 09:32:01

  • 黑科技的輕簡出行，告別「電量焦慮症」：Anker氮化鎵超能充系列 黑科技的輕簡出行，告別「電量焦慮症」：Anker氮化鎵超能充系列

    综合看Anker超能充系列的性价比很高，并且与不仅和iPhone12/苹果<em>Mac</em>Book很配，而且适合多设备充电需求的日常使用或差旅场景，不管是安卓还是Switch同样也能用得上它，希望这次分享能给准备购入充电器的小伙伴们有所

    2021-06-01 09:31:42

  • 吳亦凡廠牌首秀，L4WUDU居然忘詞了，Rapper和明星比還是有差距！ 吳亦凡廠牌首秀，L4WUDU居然忘詞了，Rapper和明星比還是有差距！

    除了L4WUDU与吴亦凡已经多次共事，成为了明面上的厂牌成员，吴亦凡还曾带领20XXCLUB全队参加2020年的一场音乐节，这也是20XXCLUB首次全员合照，王嗣尧Turbo、陈彦希Regi、<em>Mac</em> Ova Seas、林渝植等人全部出场。然而让

    2021-06-01 09:31:34

  • IPFS、Chia、Bzz和ICP挖礦該怎麼選？哪一個更有優勢？ IPFS、Chia、Bzz和ICP挖礦該怎麼選？哪一個更有優勢？

    目前应用IPFS的机构：1 谷歌<em>浏览器</em>支持IPFS分布式协议 2 万维网 (历史档案博物馆)数据库 3 火狐<em>浏览器</em>支持 IPFS分布式协议 4 EOS 等数字货币数据存储 5 美国国会图书馆，历史资料永久保存在 IPFS 6 加

    2021-06-01 09:31:24

  • 有哪些事是買了雪佛蘭後才知道的？美事偷著樂，開拓者車主隨聊 有哪些事是買了雪佛蘭後才知道的？美事偷著樂，開拓者車主隨聊

    开拓者的车机是兼容苹果和<em>安卓</em>，虽然我不怎么用，但确实兼顾了我家人的很多需求：副驾的门板还配有解锁开关，有的时候老婆开车，下车的时候偶尔会忘记解锁，我在副驾驶可以自己开门：第二排设计很好，不仅配置了一个很大的

    2021-06-01 09:30:48

  • iPhone12在618最新定價，跌價幅度超過1400元，還等iPhone13嗎？ iPhone12在618最新定價，跌價幅度超過1400元，還等iPhone13嗎？

    不仅是<em>安卓</em>手机，苹果手机的降价力度也是前所未有了，iPhone12也“跳水价”了，发布价是6799元，如今已经跌至5308元，降价幅度超过1400元，最新定价确认了。iPhone12是苹果首款5G手机，同时也是全球首款5nm芯片的智能机，它

    2021-06-01 09:30:45

IT145.com E-mail:sddin#qq.com