數(shù)據(jù)分析工作中常用的3類(lèi)SQL開(kāi)窗函數(shù)詳解
當(dāng)前位置：點(diǎn)晴教程→知識(shí)管理交流
→『 技術(shù)文檔交流 』
在日常工作中，我們經(jīng)常要處理各種數(shù)據(jù)排名、累計(jì)求和、環(huán)比等問(wèn)題。SQL窗口函數(shù)為我們提供了強(qiáng)大的工具來(lái)處理這些任務(wù)。 接下來(lái)，我們一起看看SQL常見(jiàn)窗口函數(shù)及其應(yīng)用案例吧。 窗口函數(shù)中必須有一個(gè)over子句。 01 1.1 函數(shù)說(shuō)明
1.2 案例說(shuō)明 這里通過(guò)表data_learning.product_order（商品銷(xiāo)量表）、data_learning.product（商品信息表）、data_learning.product_category（商品二級(jí)分類(lèi)信息表）進(jìn)行舉例，兩張表建表語(yǔ)句和示例數(shù)據(jù)插入語(yǔ)句可以查看我之前的文章。 數(shù)據(jù)表示例數(shù)據(jù)分別如下： data_learning.product_order（商品銷(xiāo)量表）：
data_learning.product（商品信息表）：
data_learning.product_category（商品二級(jí)分類(lèi)信息表）：
問(wèn)題：請(qǐng)將數(shù)據(jù)集按照商品二級(jí)類(lèi)別（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é)果如下（部分截圖）：
上面的案例顯示，童裝類(lèi)別分組下，商品毛衣針織衫和兒童羽絨服銷(xiāo)量都是1617，開(kāi)窗函數(shù)row_number的排序結(jié)果是順延，rank的排序結(jié)果是相同值并列、后續(xù)排序序號(hào)不連續(xù)，dense_rank的排序結(jié)果是相同值并列、后續(xù)排序序號(hào)連續(xù)。 開(kāi)窗函數(shù)NTILE(n) OVER(PARTITION BY 分組列 ORDER BY 排序列)用于將一個(gè)結(jié)果集分割成指定數(shù)量的桶（buckets），并分配一個(gè)桶號(hào)給每個(gè)值。它通常用于在分組數(shù)據(jù)中將數(shù)據(jù)等分為多個(gè)組。 在上面的案例中，我們對(duì)商品分類(lèi)分組數(shù)據(jù)集分割成10個(gè)桶，桶號(hào)從1開(kāi)始。以童裝類(lèi)別為例，童裝類(lèi)別共17行數(shù)據(jù)，17不能被10整除，數(shù)據(jù)集無(wú)法均分成10個(gè)桶，所以結(jié)果集中前面7個(gè)桶包含2行數(shù)據(jù)，后面3個(gè)桶包含1行數(shù)據(jù)。
02 2.1 函數(shù)說(shuō)明
2.2 案例說(shuō)明 問(wèn)題：請(qǐng)將數(shù)據(jù)集按照商品二級(jí)類(lèi)別（category_id）字段進(jìn)行分組、sales_volume字段進(jìn)行降序后，根據(jù)上面列舉的窗口函數(shù)對(duì)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é)果如下（部分截圖）：
從案例查詢(xún)結(jié)果看，數(shù)據(jù)集按照category_id進(jìn)行分組后，按照sales_volume降序排序，將每組中的sales_volume依次聚合；
這涉及到窗口函數(shù)的窗口幀。窗口幀（Window Frame）是在SQL中用于定義開(kāi)窗函數(shù)計(jì)算時(shí)要考慮的行的范圍，也就是開(kāi)窗函數(shù)作用的范圍。窗口幀使用在OVER子句中，與PARTITION BY和ORDER BY聯(lián)合使用來(lái)分組，對(duì)組內(nèi)的數(shù)據(jù)進(jìn)行排序和聚合。語(yǔ)句緊接著寫(xiě)在ORDER BY之后。 窗口幀包含以下類(lèi)型：
窗口幀表達(dá)式的語(yǔ)法如下：
具體x、y可取值如下：
若ORDER BY 后未指定框架，那么默認(rèn)框架將采用 range unbounded preceding and current row，表示從開(kāi)窗后的第一行到當(dāng)前行。 若分組后不加ORDER BY 或者在ORDER BY 之后加上語(yǔ)句rows between unbounded preceding and unbounded following，也就是對(duì)分組中所有的數(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 3.1 函數(shù)說(shuō)明
3.2 案例說(shuō)明 問(wèn)題：請(qǐng)將數(shù)據(jù)集按照商品二級(jí)類(lèi)別（category_id）字段進(jìn)行分組、并利用其他常見(jiàn)窗口函數(shù)對(duì)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é)果如下（部分截圖）：
其他常見(jiàn)窗口函數(shù)同聚合類(lèi)型的窗口函數(shù)一樣，也可以結(jié)合窗口幀實(shí)現(xiàn)不同的數(shù)據(jù)統(tǒng)計(jì)效果。 04 簡(jiǎn)單總結(jié)下今天分享的內(nèi)容。 我們?cè)敿?xì)介紹了SQL中的三類(lèi)窗口函數(shù)，并且在每個(gè)函數(shù)下使用了相關(guān)的示例演示函數(shù)的使用效果。同時(shí)，介紹了窗口幀的使用，讓我們可以在SQL查詢(xún)中進(jìn)行更靈活和精確的數(shù)據(jù)分析和聚合。 該文章在 2024/1/13 17:33:11 編輯過(guò) |
關(guān)鍵字查詢(xún)
相關(guān)文章
正在查詢(xún)... 
|
400 186 1886
