SQL窗口函數(shù)之取值窗口函數(shù)的使用
關(guān)于窗口函數(shù)的基礎(chǔ)，請(qǐng)看文章SQL窗口函數(shù)
取值窗口函數(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ū)作為分析的窗口。
案例分析
案例使用的示例表
下面的查詢(xún)中會(huì)用到一張表，sales_monthly表中存儲(chǔ)了商品銷(xiāo)量信息，product表示產(chǎn)品名稱(chēng)，ym表示年月，amount表示銷(xiāo)售金額（元）。
以下是該表中的部分?jǐn)?shù)據(jù)：
這個(gè)表的初始化腳本可以在文章底部獲取。
1.環(huán)比分析
環(huán)比增長(zhǎng)指的是本期數(shù)據(jù)與上期數(shù)據(jù)相比的增長(zhǎng)，例如，產(chǎn)品2019年6月的銷(xiāo)售額與2019年5月的銷(xiāo)售額相比增加的部分。
以下語(yǔ)句統(tǒng)計(jì)了各種產(chǎn)品每個(gè)月的環(huán)比增長(zhǎng)率：
SELECT s.product AS "產(chǎn)品", s.ym AS "年月", s.amount AS "銷(xiāo)售額",
(
(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）表示獲取上一期的銷(xiāo)售額，PARTITION BY選項(xiàng)表示按照產(chǎn)品分區(qū)，ORDER BY選項(xiàng)表示按照月份進(jìn)行排序。
當(dāng)前月份的銷(xiāo)售額amount減去上一期的銷(xiāo)售額，再除以上一期的銷(xiāo)售額，就是環(huán)比增長(zhǎng)率。
該查詢(xún)返回的結(jié)果如下：
2018年1月是第一期，因此其環(huán)比增長(zhǎng)率為空。
“桔子”2018年2月的環(huán)比增長(zhǎng)率約為0.2856%（（10183-10154）/10154×100），依此類(lèi)推。
2.同比分析
同比增長(zhǎng)指的是本期數(shù)據(jù)與上一年度或歷史同期相比的增長(zhǎng)，例如，產(chǎn)品2019年6月的銷(xiāo)售額與2018年6月的銷(xiāo)售額相比增加的部分。
以下語(yǔ)句統(tǒng)計(jì)了各種產(chǎn)品每個(gè)月的同比增長(zhǎng)率：
SELECT s.product AS "產(chǎn)品", s.ym AS "年月", s.amount AS "銷(xiāo)售額",
(
(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期的銷(xiāo)售額，也就是去年同月份的銷(xiāo)售額。
PARTITION BY選項(xiàng)表示按照產(chǎn)品分區(qū)，ORDER BY選項(xiàng)表示按照月份進(jìn)行排序。
當(dāng)前月份的銷(xiāo)售額amount減去去年同期的銷(xiāo)售額，再除以去年同期的銷(xiāo)售額，就是同比增長(zhǎng)率。
該查詢(xún)返回的結(jié)果如下：
2018年的12期數(shù)據(jù)都沒(méi)有對(duì)應(yīng)的同比增長(zhǎng)率，“桔子”2019年1月的同比增長(zhǎng)率約為9.3067%（（11099-10154）/10154×100），依此類(lèi)推。
提示：LEAD函數(shù)與LAG函數(shù)的使用方法類(lèi)似，不過(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)品銷(xiāo)售額為10000，2019年的產(chǎn)品銷(xiāo)售額為12500，2020年的產(chǎn)品銷(xiāo)售額為15000。那么這兩年的復(fù)合增長(zhǎng)率的計(jì)算方式如下：
以年度為單位計(jì)算的復(fù)合增長(zhǎng)率被稱(chēng)為年均復(fù)合增長(zhǎng)率，以月度為單位計(jì)算的復(fù)合增長(zhǎng)率被稱(chēng)為月均復(fù)合增長(zhǎng)率。
以下查詢(xún)統(tǒng)計(jì)了自2018年1月以來(lái)不同產(chǎn)品的月均銷(xiāo)售額復(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 "銷(xiāo)售額",
(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）的銷(xiāo)售額，ROW_NUMBER函數(shù)返回了每一期的編號(hào)。
主查詢(xún)中的POWER函數(shù)用于執(zhí)行開(kāi)方運(yùn)算，NULLIF函數(shù)用于處理第一期數(shù)據(jù)的除零錯(cuò)誤，常量1.0用于避免由整數(shù)除法所導(dǎo)致的精度丟失問(wèn)題。
該查詢(xún)返回的結(jié)果如下：
2018年1月是第一期，因此其產(chǎn)品月均銷(xiāo)售額復(fù)合增長(zhǎng)率為空。
“桔子”2018年2月的月均銷(xiāo)售額復(fù)合增長(zhǎng)率等于它的環(huán)比增長(zhǎng)率，2018年3月的月均銷(xiāo)售額復(fù)合增長(zhǎng)率等于0.4471%，依此類(lèi)推。
4.不同產(chǎn)品最高和最低銷(xiāo)售額
以下語(yǔ)句統(tǒng)計(jì)了不同產(chǎn)品最低銷(xiāo)售額、最高銷(xiāo)售額以及第三高銷(xiāo)售額所在的月份：
SELECT product AS "產(chǎn)品", ym AS "年月",amount AS "銷(xiāo)售額",
FIRST_VALUE(m.ym) OVER (
PARTITION BY m.product ORDER BY m.amount DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS "最高銷(xiāo)售額月份",
LAST_VALUE(m.ym) OVER (
PARTITION BY m.product ORDER BY m.amount DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS "最低銷(xiāo)售額月份",
NTH_VALUE(m.ym,3) OVER (
PARTITION BY m.product ORDER BY m.amount DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS "第三高銷(xiāo)售額月份"
FROM sales_monthly m
ORDER BY product, ym;三個(gè)窗口函數(shù)的OVER子句相同，PARTITION BY選項(xiàng)表示按照產(chǎn)品進(jìn)行分區(qū)，ORDER BY選項(xiàng)表示按照銷(xiāo)售額從高到低排序。
以上三個(gè)函數(shù)的默認(rèn)窗口都是從分區(qū)的第一行到當(dāng)前行，因此我們將窗口擴(kuò)展到了整個(gè)分區(qū)。
該查詢(xún)返回的結(jié)果如下：
“桔子”的最高銷(xiāo)售額出現(xiàn)在2019年6月，最低銷(xiāo)售額出現(xiàn)在2018年1月，第三高銷(xiāo)售額出現(xiàn)在2019年4月。
示例表和腳本
-- 創(chuàng)建銷(xiāo)量表sales_monthly
-- product表示產(chǎn)品名稱(chēng)，ym表示年月，amount表示銷(xiāo)售金額（元）
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ù)的使用的文章就介紹到這了,更多相關(guān)SQL 取值窗口函數(shù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
asp.net中如何調(diào)用sql存儲(chǔ)過(guò)程實(shí)現(xiàn)分頁(yè)
使用sql存儲(chǔ)過(guò)程實(shí)現(xiàn)分頁(yè)，在網(wǎng)上能找到好多種解決方案，但是如何用asp.net后臺(tái)調(diào)用呢，通過(guò)本篇文章小編給大家詳解asp.net中如何調(diào)用sql存儲(chǔ)過(guò)程實(shí)現(xiàn)分頁(yè)，有需要的朋友可以來(lái)參考下2015-08-08
這篇文章主要介紹了Sqlserver 死鎖查詢(xún)以及解鎖方法,通過(guò)實(shí)例代碼講解了sqlserver查看哪個(gè)表被鎖表了以及解鎖方法,感興趣的朋友跟隨小編一起看看吧2024-02-02
在SQL SERVER中導(dǎo)致索引查找變成索引掃描的問(wèn)題分析
SQL Server 中什么情況會(huì)導(dǎo)致其執(zhí)行計(jì)劃從索引查找（Index Seek）變成索引掃描（Index Scan）呢？ 下面從幾個(gè)方面結(jié)合上下文具體場(chǎng)景做了下測(cè)試、總結(jié)、歸納。需要的朋友可以參考下本文2015-09-09
詳解SQL Server中的數(shù)據(jù)類(lèi)型
本文主要講解了SQL中的數(shù)據(jù)類(lèi)型以及幾個(gè)需要注意的地方，簡(jiǎn)短的內(nèi)容，深入的理解。有興趣的朋友可以看下2016-12-12
設(shè)置SQLServer數(shù)據(jù)庫(kù)中某些表為只讀的多種方法分享
在某些情況下需要把SQLServer的表設(shè)為只讀，下面舉出幾種方法，需要的朋友可以參考下2012-06-06
對(duì)有insert觸發(fā)器表取IDENTITY值時(shí)發(fā)現(xiàn)的問(wèn)題
趕快查了下msdn,原來(lái)@@IDENTITY還有這么多講究2009-06-06
SQL?Server附加數(shù)據(jù)庫(kù)時(shí)出現(xiàn)錯(cuò)誤的處理方法
通過(guò)附加功能添加現(xiàn)成的數(shù)據(jù)庫(kù)是非常方便的,然而有時(shí)會(huì)出現(xiàn)附加數(shù)據(jù)庫(kù)失敗,下面這篇文章主要給大家介紹了關(guān)于SQL?Server附加數(shù)據(jù)庫(kù)時(shí)出現(xiàn)錯(cuò)誤的處理方法,需要的朋友可以參考下2022-12-12
解決sql server保存對(duì)象字符串轉(zhuǎn)換成uniqueidentifier失敗的問(wèn)題
這篇文章主要介紹了解決sql server保存對(duì)象字符串轉(zhuǎn)換成uniqueidentifier失敗的問(wèn)題，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2020-10-10
sqlserver中重復(fù)數(shù)據(jù)值只取一條的sql語(yǔ)句
sqlserver中有時(shí)候我們需要獲取多條重復(fù)數(shù)據(jù)的一條，需要的朋友可以參考下面的語(yǔ)句2012-05-05
SQL根據(jù)指定分隔符分解字符串實(shí)現(xiàn)步驟
想要在MS SQL中根據(jù)給定的分隔符把這個(gè)字符串分解成各個(gè)元素，本文將詳細(xì)介紹此功能的實(shí)現(xiàn),需要了解的朋友可以參考下2012-11-11