SQL視窗函數詳解之排名視窗函數的使用
這篇文章為大家帶來了關於SQL server的相關知識，其中主要介紹了SQL Server主鍵約束(PRIMARY KEY),主鍵是唯一標識表中每一行的一列或一組列,文章圍繞主題展開詳情，下面一起來看一下，希望對大家有幫助。
推薦學習：《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中文網其他相關文章！
熱AI工具
Undresser.AI Undress
人工智慧驅動的應用程序，用於創建逼真的裸體照片
AI Clothes Remover
用於從照片中去除衣服的線上人工智慧工具。
Undress AI Tool
免費脫衣圖片
Clothoff.io
AI脫衣器
Video Face Swap
使用我們完全免費的人工智慧換臉工具，輕鬆在任何影片中換臉！
熱門文章
熱工具
記事本++7.3.1
好用且免費的程式碼編輯器
SublimeText3漢化版
中文版，非常好用
禪工作室 13.0.1
強大的PHP整合開發環境
Dreamweaver CS6
視覺化網頁開發工具
SublimeText3 Mac版
神級程式碼編輯軟體(SublimeText3)
Hibernate 框架中 HQL 和 SQL 的差異是什麼？
Apr 17, 2024 pm 02:57 PM
HQL和SQL在Hibernate框架中進行比較：HQL（1.物件導向語法，2.資料庫無關的查詢，3.類型安全），而SQL直接操作資料庫（1.與資料庫無關的標準，2.可執行複雜查詢和資料操作）。
Oracle SQL中除法運算的用法
Mar 10, 2024 pm 03:06 PM
《OracleSQL中除法運算的用法》在OracleSQL中，除法運算是常見的數學運算之一。在資料查詢和處理過程中，除法運算可以幫助我們計算欄位之間的比例或得出特定數值的邏輯關係。本文將介紹OracleSQL中除法運算的用法，並提供具體的程式碼範例。一、OracleSQL中除法運算的兩種方式在OracleSQL中，除法運算可以用兩種不同的方式來進行
Oracle與DB2的SQL語法比較與區別
Mar 11, 2024 pm 12:09 PM
Oracle和DB2是兩個常用的關聯式資料庫管理系統，它們都有自己獨特的SQL語法和特性。本文將針對Oracle和DB2的SQL語法進行比較與區別，並提供具體的程式碼範例。資料庫連接在Oracle中，使用以下語句連接資料庫：CONNECTusername/password@database而在DB2中，連接資料庫的語句如下：CONNECTTOdataba
詳解MyBatis動態SQL標籤中的Set標籤功能
Feb 26, 2024 pm 07:48 PM
MyBatis動態SQL標籤解讀：Set標籤用法詳解MyBatis是一個優秀的持久層框架，它提供了豐富的動態SQL標籤，可以靈活地建構資料庫操作語句。其中，Set標籤是用來產生UPDATE語句中SET子句的標籤，在更新作業中非常常用。本文將詳細解讀MyBatis中Set標籤的用法，以及透過具體的程式碼範例來示範其功能。什麼是Set標籤Set標籤用於MyBati
SQL中的identity屬性是什麼意思？
Feb 19, 2024 am 11:24 AM
SQL中的Identity是什麼，需要具體程式碼範例在SQL中，Identity是一種用於產生自增數字的特殊資料類型，它常用於唯一識別表中的每一行資料。 Identity欄位通常與主鍵列搭配使用，可確保每筆記錄都有獨一無二的識別碼。本文將詳細介紹Identity的使用方式以及一些實際的程式碼範例。 Identity的基本使用方式在建立表格時，可以使用Identit
Springboot+Mybatis-plus不使用SQL語句進行多表新增怎麼實現
Jun 02, 2023 am 11:07 AM
在Springboot+Mybatis-plus不使用SQL語句進行多表添加操作我所遇到的問題準備工作在測試環境下模擬思維分解一下:創建出一個帶有參數的BrandDTO對像模擬對後台傳遞參數我所遇到的問題我們都知道,在我們使用Mybatis-plus中進行多表操作是極其困難的,如果你不使用Mybatis-plus-join這一類的工具,你只能去配置對應的Mapper.xml文件,配置又臭又長的ResultMap,然後再寫對應的sql語句,這種方法雖然看上去很麻煩,但具有很高的靈活性,可以讓我們
SQL出現5120錯誤怎麼解決
Mar 06, 2024 pm 04:33 PM
解決方法：1、檢查登入使用者是否具有足夠的權限來存取或操作該資料庫，確保該使用者俱有正確的權限；2、檢查SQL Server服務的帳戶是否具有存取指定檔案或資料夾的權限，確保該帳戶具有足夠的權限來讀取和寫入該文件或資料夾；3、檢查指定的資料庫文件是否已被其他進程打開或鎖定，嘗試關閉或釋放該文件，並重新運行查詢；4、嘗試以管理員身份運行Management Studio等等。
如何使用SQL語句在MySQL中進行資料聚合和統計？
Dec 17, 2023 am 08:41 AM
如何使用SQL語句在MySQL中進行資料聚合和統計？在進行資料分析和統計時，資料聚合和統計是非常重要的步驟。 MySQL作為一個功能強大的關聯式資料庫管理系統，提供了豐富的聚合和統計函數，可以很方便地進行資料聚合和統計操作。本文將介紹使用SQL語句在MySQL中進行資料聚合和統計的方法，並提供具體的程式碼範例。一、使用COUNT函數進行計數COUNT函數是最常用
