欧美成人精品手机在线观看_69视频国产_动漫精品第一页_日韩中文字幕网 - 日本欧美一区二区

LOGO OA教程 ERP教程 模切知識交流 PMS教程 CRM教程 開發(fā)文檔 其他文檔  
 
網(wǎng)站管理員

數(shù)據(jù)分析工作中常用的3類SQL開窗函數(shù)詳解
admin
2024年1月13日 17:33 本文熱度 783

在日常工作中，我們經(jīng)常要處理各種數(shù)據(jù)排名、累計求和、環(huán)比等問題。SQL窗口函數(shù)為我們提供了強(qiáng)大的工具來處理這些任務(wù)。

接下來，我們一起看看SQL常見窗口函數(shù)及其應(yīng)用案例吧。

窗口函數(shù)中必須有一個over子句。

01

排名類窗口函數(shù)

1.1 函數(shù)說明

排名類窗口函數(shù)

描述

ROW_NUMBER() OVER(PARTITION BY 分組列 ORDER BY 排序列)

在分組列下，按排序列的排名，相同值排名順延，返回結(jié)果1、2、3、4

RANK() OVER(PARTITION BY 分組列 ORDER BY 排序列)

在分組列下，按排序列的排名，相同值排名相同，后面排名不連續(xù)，返回結(jié)果1、2、2、4

DENSE_RANK() OVER(PARTITION BY 分組列 ORDER BY 排序列)

在分組列下，按排序列的排名，相同值排名相同，后面排名連續(xù)，返回結(jié)果1、2、2、3

NTILE(n) OVER(PARTITION BY 分組列 ORDER BY 排序列)

  • 將排序分區(qū)中的行劃分為特定數(shù)量的組，從每個組分配一個從一開始的桶號；

  • n是一個正整數(shù)，桶號的范圍是1到n；

  • 如果分區(qū)行的數(shù)量不能被整除n，則NTILE()函數(shù)將生成兩個大小的組，差異為1。        

  • 如果分區(qū)行的總數(shù)可被整除n，則行將在組之間平均分配。



1.2 案例說明

這里通過表data_learning.product_order（商品銷量表）、data_learning.product（商品信息表）、data_learning.product_category（商品二級分類信息表）進(jìn)行舉例，兩張表建表語句和示例數(shù)據(jù)插入語句可以查看我之前的文章。

數(shù)據(jù)表示例數(shù)據(jù)分別如下：

data_learning.product_order（商品銷量表）：

data_learning.product（商品信息表）：    

data_learning.product_category（商品二級分類信息表）：

問題：請將數(shù)據(jù)集按照商品二級類別（category_id）字段進(jìn)行分組后，根據(jù)上面列舉的窗口函數(shù)并按照sales_volume字段降序返回排名。

SQL：

SELECT       a.product_id       ,b.product_name       ,a.category_id       ,c.category_name           ,a.sales_volume       ,ROW_NUMBER() OVER(PARTITION BY a.category_id ORDER BY a.sales_volume DESC) AS ro_result       ,RANK() OVER(PARTITION BY a.category_id ORDER BY a.sales_volume DESC) AS rank_result       ,DENSE_RANK() OVER(PARTITION BY a.category_id ORDER BY a.sales_volume DESC) AS dense_rank_result       ,NTILE(10) OVER(PARTITION BY a.category_id ORDER BY a.sales_volume DESC) AS ntile_resultFROM(SELECT       *FROM       data_learning.product_order)aLEFT JOIN(SELECT       *FROM       data_learning.product    )bon a.product_id = b.product_idLEFT JOIN(SELECT       *FROM       data_learning.product_category)con a.category_id = c.category_id;

結(jié)果如下（部分截圖）：

上面的案例顯示，童裝類別分組下，商品毛衣針織衫和兒童羽絨服銷量都是1617，開窗函數(shù)row_number的排序結(jié)果是順延，rank的排序結(jié)果是相同值并列、后續(xù)排序序號不連續(xù)，dense_rank的排序結(jié)果是相同值并列、后續(xù)排序序號連續(xù)。

開窗函數(shù)NTILE(n) OVER(PARTITION BY 分組列 ORDER BY 排序列)用于將一個結(jié)果集分割成指定數(shù)量的桶（buckets），并分配一個桶號給每個值。它通常用于在分組數(shù)據(jù)中將數(shù)據(jù)等分為多個組。    

在上面的案例中，我們對商品分類分組數(shù)據(jù)集分割成10個桶，桶號從1開始。以童裝類別為例，童裝類別共17行數(shù)據(jù)，17不能被10整除，數(shù)據(jù)集無法均分成10個桶，所以結(jié)果集中前面7個桶包含2行數(shù)據(jù)，后面3個桶包含1行數(shù)據(jù)。

02

聚合類窗口函數(shù)

2.1 函數(shù)說明

聚合類窗口函數(shù)

描述

COUNT() OVER(PARTITION BY 分組列 ORDER BY 排序列)

分組計數(shù)

SUM() OVER(PARTITION BY 分組列 ORDER BY 排序列)

分組求和

AVG() OVER(PARTITION BY 分組列 ORDER BY 排序列)

分組求均值

MAX() OVER(PARTITION BY 分組列 ORDER BY 排序列)

分組求最大值

MIN() OVER(PARTITION BY 分組列 ORDER BY 排序列)   

分組求最小值

         

 

2.2 案例說明

問題：請將數(shù)據(jù)集按照商品二級類別（category_id）字段進(jìn)行分組、sales_volume字段進(jìn)行降序后，根據(jù)上面列舉的窗口函數(shù)對sales_volume進(jìn)行聚合。

SQL：

SELECT       a.product_id       ,b.product_name       ,a.category_id       ,c.category_name       ,a.sales_volume       ,COUNT(a.sales_volume) OVER(PARTITION BY a.category_id ORDER BY a.sales_volume DESC) AS cnt_result       ,SUM(a.sales_volume) OVER(PARTITION BY a.category_id ORDER BY a.sales_volume DESC) AS sum_result       ,AVG(a.sales_volume) OVER(PARTITION BY a.category_id ORDER BY a.sales_volume DESC) AS avg_result       ,MAX(a.sales_volume) OVER(PARTITION BY a.category_id ORDER BY a.sales_volume DESC) AS max_result       ,MIN(a.sales_volume) OVER(PARTITION BY a.category_id ORDER BY a.sales_volume DESC) AS min_result    FROM(SELECT       *FROM       data_learning.product_order)aLEFT JOIN(SELECT       *FROM       data_learning.product)bon a.product_id = b.product_idLEFT JOIN(SELECT       *FROM       data_learning.product_category    )con a.category_id = c.category_id;

結(jié)果如下（部分截圖）：

從案例查詢結(jié)果看，數(shù)據(jù)集按照category_id進(jìn)行分組后，按照sales_volume降序排序，將每組中的sales_volume依次聚合；

  • cnt_result字段表示分組降序依次計數(shù)結(jié)果；

  • sum_result字段表示分組降序依次累加求和結(jié)果；

  • avg_result字段表示分組降序依次求平均值結(jié)果；

  • max_result字段表示分組降序依次求最大值結(jié)果；

  • min_result字段表示分組降序依次求最小值結(jié)果；這里為什么分組的最小值不像最大值那樣，一組只有一個呢？

這涉及到窗口函數(shù)的窗口幀。窗口幀（Window Frame）是在SQL中用于定義開窗函數(shù)計算時要考慮的行的范圍，也就是開窗函數(shù)作用的范圍。窗口幀使用在OVER子句中，與PARTITION BY和ORDER BY聯(lián)合使用來分組，對組內(nèi)的數(shù)據(jù)進(jìn)行排序和聚合。語句緊接著寫在ORDER BY之后。

窗口幀包含以下類型：    

  • ROWS：在窗口幀中指定當(dāng)前行的位置為基準(zhǔn)。ROWS是根據(jù)行的物理位置進(jìn)行計算的。

  • RANGE：在窗口幀中通過應(yīng)用持續(xù)區(qū)域定義范圍。RANGE是根據(jù)數(shù)據(jù)值進(jìn)行計算的。

窗口幀表達(dá)式的語法如下：

  • range/rows between x and y

具體x、y可取值如下：

  • UNBOUNDED PRECEDING：PARTITION BY分組ORDER BY后 第一行

  • UNBOUNDED FOLLOWING：PARTITION BY分組ORDER BY后 最后一行

  • CURRENT ROW：PARTITION BY分組ORDER BY后 當(dāng)前行

  • N PRECEDING：PARTITION BY分組ORDER BY后 前n行

  • N FOLLOWING： PARTITION BY分組ORDER BY 后n行

若ORDER BY 后未指定框架，那么默認(rèn)框架將采用 range unbounded preceding and current row，表示從開窗后的第一行到當(dāng)前行。

若分組后不加ORDER BY 或者在ORDER BY 之后加上語句rows between unbounded preceding and unbounded following，也就是對分組中所有的數(shù)據(jù)進(jìn)行聚合運(yùn)算。具體示例和結(jié)果如下：

SELECT       a.product_id       ,b.product_name       ,a.category_id       ,c.category_name           ,a.sales_volume       ,COUNT(a.sales_volume) OVER(PARTITION BY a.category_id ORDER BY a.sales_volume DESC) AS cnt_result       ,COUNT(a.sales_volume) OVER(PARTITION BY a.category_id) AS cnt_result1       ,COUNT(a.sales_volume) OVER(PARTITION BY a.category_id ORDER BY a.sales_volume DESC rows between unbounded preceding and unbounded following) AS cnt_result2        ,SUM(a.sales_volume) OVER(PARTITION BY a.category_id ORDER BY a.sales_volume DESC) AS sum_result       ,SUM(a.sales_volume) OVER(PARTITION BY a.category_id ) AS sum_result1       ,SUM(a.sales_volume) OVER(PARTITION BY a.category_id ORDER BY a.sales_volume DESC rows between unbounded preceding and unbounded following) AS sum_result2        ,AVG(a.sales_volume) OVER(PARTITION BY a.category_id ORDER BY a.sales_volume DESC) AS avg_result       ,AVG(a.sales_volume) OVER(PARTITION BY a.category_id) AS avg_result1       ,AVG(a.sales_volume) OVER(PARTITION BY a.category_id ORDER BY a.sales_volume DESC rows between unbounded preceding and unbounded following) AS avg_result2            ,MAX(a.sales_volume) OVER(PARTITION BY a.category_id ORDER BY a.sales_volume DESC) AS max_result       ,MAX(a.sales_volume) OVER(PARTITION BY a.category_id) AS max_result1       ,MAX(a.sales_volume) OVER(PARTITION BY a.category_id ORDER BY a.sales_volume DESC rows between unbounded preceding and unbounded following) AS max_result2        ,MIN(a.sales_volume) OVER(PARTITION BY a.category_id ORDER BY a.sales_volume DESC) AS min_result       ,MIN(a.sales_volume) OVER(PARTITION BY a.category_id) AS min_result1       ,MIN(a.sales_volume) OVER(PARTITION BY a.category_id ORDER BY a.sales_volume DESC rows between unbounded preceding and unbounded following) AS min_result2FROM(SELECT       *FROM       data_learning.product_order)aLEFT JOIN    (SELECT       *FROM       data_learning.product)bon a.product_id = b.product_idLEFT JOIN(SELECT       *FROM       data_learning.product_category)con a.category_id = c.category_id;

運(yùn)行結(jié)果如下（部分截圖）：    

可以看到，不加ORDER BY的字段如sum_result1和 ORDER BY 后+rows between unbounded preceding and unbounded following的字段sum_result2結(jié)果一樣。

03

其他常見窗口函數(shù)


3.1 函數(shù)說明

其他常見窗口函數(shù)

描述

LAG(列名,n) OVER(PARTITION BY 分組列 ORDER BY 排序列)

移位窗口函數(shù)，返回列名當(dāng)前行向前第n行的值

LEAD(列名,n) OVER(PARTITION BY 分組列 ORDER BY 排序列)

移位窗口函數(shù)，返回列名當(dāng)前行向后第n行的值

FIRST_VALUE() OVER(PARTITION BY 分組列 ORDER BY 排序列)

取分組內(nèi)排序后，截止到當(dāng)前行，第一個值

LAST_VALUE() OVER(PARTITION BY 分組列 ORDER BY 排序列)

取分組內(nèi)排序后，截止到當(dāng)前行，最后一個值

          

3.2 案例說明

問題：請將數(shù)據(jù)集按照商品二級類別（category_id）字段進(jìn)行分組、并利用其他常見窗口函數(shù)對sales_volume字段進(jìn)行處理（主要是為了理解上述函數(shù)的作用效果）。    

SQL：

SELECT       a.product_id       ,b.product_name       ,a.category_id       ,c.category_name       ,a.sales_volume       ,LAG(a.sales_volume,1) OVER(PARTITION BY a.category_id ORDER BY a.sales_volume DESC) AS lag_result       ,LEAD(a.sales_volume,1) OVER(PARTITION BY a.category_id ORDER BY a.sales_volume DESC) AS lead_result       ,FIRST_VALUE(a.sales_volume) OVER(PARTITION BY a.category_id ORDER BY a.sales_volume DESC) AS firstvalue_result       ,LAST_VALUE(a.sales_volume) OVER(PARTITION BY a.category_id ORDER BY a.sales_volume DESC) AS lastvalue_result FROM(SELECT       *FROM           data_learning.product_order)aLEFT JOIN(SELECT       *FROM       data_learning.product)bon a.product_id = b.product_idLEFT JOIN(SELECT       *FROM       data_learning.product_category)con a.category_id = c.category_id;

結(jié)果如下（部分截圖）：    

其他常見窗口函數(shù)同聚合類型的窗口函數(shù)一樣，也可以結(jié)合窗口幀實現(xiàn)不同的數(shù)據(jù)統(tǒng)計效果。

04

小結(jié)

簡單總結(jié)下今天分享的內(nèi)容。

我們詳細(xì)介紹了SQL中的三類窗口函數(shù)，并且在每個函數(shù)下使用了相關(guān)的示例演示函數(shù)的使用效果。同時，介紹了窗口幀的使用，讓我們可以在SQL查詢中進(jìn)行更靈活和精確的數(shù)據(jù)分析和聚合。


該文章在 2024/1/13 17:33:11 編輯過
關(guān)鍵字查詢
相關(guān)文章
正在查詢...
點晴ERP是一款針對中小制造業(yè)的專業(yè)生產(chǎn)管理軟件系統(tǒng),系統(tǒng)成熟度和易用性得到了國內(nèi)大量中小企業(yè)的青睞。
點晴PMS碼頭管理系統(tǒng)主要針對港口碼頭集裝箱與散貨日常運(yùn)作、調(diào)度、堆場、車隊、財務(wù)費用、相關(guān)報表等業(yè)務(wù)管理，結(jié)合碼頭的業(yè)務(wù)特點，圍繞調(diào)度、堆場作業(yè)而開發(fā)的。集技術(shù)的先進(jìn)性、管理的有效性于一體，是物流碼頭及其他港口類企業(yè)的高效ERP管理信息系統(tǒng)。
點晴WMS倉儲管理系統(tǒng)提供了貨物產(chǎn)品管理,銷售管理,采購管理,倉儲管理,倉庫管理,保質(zhì)期管理,貨位管理,庫位管理,生產(chǎn)管理,WMS管理系統(tǒng),標(biāo)簽打印,條形碼,二維碼管理,批號管理軟件。
點晴免費OA是一款軟件和通用服務(wù)都免費，不限功能、不限時間、不限用戶的免費OA協(xié)同辦公管理系統(tǒng)。
Copyright 2010-2025 ClickSun All Rights Reserved