SQL視窗函數詳解之排名視窗函數的使用-SQL-PHP中文網

首頁

資料庫

SQL

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

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Sep 08, 2022 pm 05:44 PM

sql

這篇文章為大家帶來了關於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 (&#39;苹果&#39;,&#39;201801&#39;,10159.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;苹果&#39;,&#39;201802&#39;,10211.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;苹果&#39;,&#39;201803&#39;,10247.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;苹果&#39;,&#39;201804&#39;,10376.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;苹果&#39;,&#39;201805&#39;,10400.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;苹果&#39;,&#39;201806&#39;,10565.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;苹果&#39;,&#39;201807&#39;,10613.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;苹果&#39;,&#39;201808&#39;,10696.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;苹果&#39;,&#39;201809&#39;,10751.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;苹果&#39;,&#39;201810&#39;,10842.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;苹果&#39;,&#39;201811&#39;,10900.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;苹果&#39;,&#39;201812&#39;,10972.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;苹果&#39;,&#39;201901&#39;,11155.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;苹果&#39;,&#39;201902&#39;,11202.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;苹果&#39;,&#39;201903&#39;,11260.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;苹果&#39;,&#39;201904&#39;,11341.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;苹果&#39;,&#39;201905&#39;,11459.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;苹果&#39;,&#39;201906&#39;,11560.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;香蕉&#39;,&#39;201801&#39;,10138.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;香蕉&#39;,&#39;201802&#39;,10194.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;香蕉&#39;,&#39;201803&#39;,10328.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;香蕉&#39;,&#39;201804&#39;,10322.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;香蕉&#39;,&#39;201805&#39;,10481.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;香蕉&#39;,&#39;201806&#39;,10502.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;香蕉&#39;,&#39;201807&#39;,10589.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;香蕉&#39;,&#39;201808&#39;,10681.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;香蕉&#39;,&#39;201809&#39;,10798.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;香蕉&#39;,&#39;201810&#39;,10829.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;香蕉&#39;,&#39;201811&#39;,10913.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;香蕉&#39;,&#39;201812&#39;,11056.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;香蕉&#39;,&#39;201901&#39;,11161.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;香蕉&#39;,&#39;201902&#39;,11173.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;香蕉&#39;,&#39;201903&#39;,11288.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;香蕉&#39;,&#39;201904&#39;,11408.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;香蕉&#39;,&#39;201905&#39;,11469.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;香蕉&#39;,&#39;201906&#39;,11528.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;桔子&#39;,&#39;201801&#39;,10154.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;桔子&#39;,&#39;201802&#39;,10183.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;桔子&#39;,&#39;201803&#39;,10245.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;桔子&#39;,&#39;201804&#39;,10325.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;桔子&#39;,&#39;201805&#39;,10465.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;桔子&#39;,&#39;201806&#39;,10505.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;桔子&#39;,&#39;201807&#39;,10578.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;桔子&#39;,&#39;201808&#39;,10680.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;桔子&#39;,&#39;201809&#39;,10788.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;桔子&#39;,&#39;201810&#39;,10838.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;桔子&#39;,&#39;201811&#39;,10942.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;桔子&#39;,&#39;201812&#39;,10988.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;桔子&#39;,&#39;201901&#39;,11099.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;桔子&#39;,&#39;201902&#39;,11181.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;桔子&#39;,&#39;201903&#39;,11302.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;桔子&#39;,&#39;201904&#39;,11327.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;桔子&#39;,&#39;201905&#39;,11423.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES (&#39;桔子&#39;,&#39;201906&#39;,11524.00);

推荐学习：《SQL教程》

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

陳述

本文轉載於：脚本之家。如有侵權，請聯絡admin@php.cn刪除

SQL和數據庫：完美的合作夥伴關係Apr 25, 2025 am 12:04 AM

SQL與數據庫的關係是緊密結合的，SQL是管理和操作數據庫的工具。 1.SQL是一種聲明式語言，用於數據定義、操作、查詢和控制。 2.數據庫引擎解析SQL語句並執行查詢計劃。 3.基本用法包括創建表、插入和查詢數據。 4.高級用法涉及復雜查詢和子查詢。 5.常見錯誤包括語法、邏輯和性能問題，可通過語法檢查和EXPLAIN命令調試。 6.優化技巧包括使用索引、避免全表掃描和優化查詢。

SQL與MySQL：澄清兩者之間的關係Apr 24, 2025 am 12:02 AM

SQL是一種用於管理關係數據庫的標準語言，而MySQL是一個使用SQL的數據庫管理系統。 SQL定義了與數據庫交互的方式，包括CRUD操作，而MySQL實現了SQL標準並提供了額外的功能，如存儲過程和触發器。

SQL的重要性：數字時代的數據管理Apr 23, 2025 am 12:01 AM

SQL在數據管理中的作用是通過查詢、插入、更新和刪除操作來高效處理和分析數據。 1.SQL是一種聲明式語言，允許用戶以結構化方式與數據庫對話。 2.使用示例包括基本的SELECT查詢和高級的JOIN操作。 3.常見錯誤如忘記WHERE子句或誤用JOIN，可通過EXPLAIN命令調試。 4.性能優化涉及使用索引和遵循最佳實踐如代碼可讀性和可維護性。

SQL入門：基本概念和技能Apr 22, 2025 am 12:01 AM

SQL是一種用於管理和操作關係數據庫的語言。 1.創建表：使用CREATETABLE語句，如CREATETABLEusers(idINTPRIMARYKEY,nameVARCHAR(100),emailVARCHAR(100));2.插入、更新、刪除數據：使用INSERTINTO、UPDATE、DELETE語句，如INSERTINTOusers(id,name,email)VALUES(1,'JohnDoe','john@example.com');3.查詢數據：使用SELECT語句，如SELEC

SQL：語言，MySQL：數據庫管理系統Apr 21, 2025 am 12:05 AM

SQL和MySQL的關係是：SQL是用於管理和操作數據庫的語言，而MySQL是支持SQL的數據庫管理系統。 1.SQL允許進行數據的CRUD操作和高級查詢。 2.MySQL提供索引、事務和鎖機制來提升性能和安全性。 3.優化MySQL性能需關注查詢優化、數據庫設計和監控維護。

SQL的作用：管理和操縱數據Apr 20, 2025 am 12:02 AM

SQL用於數據庫管理和數據操作，核心功能包括CRUD操作、複雜查詢和優化策略。 1)CRUD操作：使用INSERTINTO創建數據，SELECT讀取數據，UPDATE更新數據，DELETE刪除數據。 2)複雜查詢：通過GROUPBY和HAVING子句處理複雜數據。 3)優化策略：使用索引、避免全表掃描、優化JOIN操作和分頁查詢來提升性能。

SQL：對數據管理的初學者友好方法？Apr 19, 2025 am 12:12 AM

SQL適合初學者，因為它語法簡單，功能強大，廣泛應用於數據庫系統。 1.SQL用於管理關係數據庫，通過表格組織數據。 2.基本操作包括創建、插入、查詢、更新和刪除數據。 3.高級用法如JOIN、子查詢和窗口函數增強數據分析能力。 4.常見錯誤包括語法、邏輯和性能問題，可通過檢查和優化解決。 5.性能優化建議包括使用索引、避免SELECT*、使用EXPLAIN分析查詢、規範化數據庫和提高代碼可讀性。

SQL在行動中：現實世界中的示例和用例Apr 18, 2025 am 12:13 AM

SQL在實際應用中主要用於數據查詢與分析、數據整合與報告、數據清洗與預處理、高級用法與優化以及處理複雜查詢和避免常見錯誤。 1)數據查詢與分析可用於找出銷售量最高的產品；2)數據整合與報告通過JOIN操作生成客戶購買報告；3)數據清洗與預處理可刪除異常年齡記錄；4)高級用法與優化包括使用窗口函數和創建索引；5)處理複雜查詢可使用CTE和JOIN，避免常見錯誤如SQL注入。

See all articles