常用視窗函數

来源:https://www.cnblogs.com/ys1109/archive/2023/12/20/17915224.html
-Advertisement-
Play Games

1.前言 又到了年底，最近在做年報數據拉取的需求，在這期間有一些數據的計算需要用到視窗函數，就去瞭解了一下常用的視窗函數的用法。 1.1.什麼是視窗函數 視窗函數是 SQL 中的一種特殊函數，它允許你在查詢結果中的某個視窗或視窗範圍上執行計算，而不是單獨針對整個結果集進行計算。 視窗函數通常與 OV ...


1.前言

又到了年底，最近在做年報數據拉取的需求，在這期間有一些數據的計算需要用到視窗函數，就去瞭解了一下常用的視窗函數的用法。

1.1.什麼是視窗函數

視窗函數是 SQL 中的一種特殊函數，它允許你在查詢結果中的某個視窗或視窗範圍上執行計算，而不是單獨針對整個結果集進行計算。

視窗函數通常與 OVER() 子句結合使用，用於定義視窗的範圍。這個範圍可以是整個結果集、分組的行、特定的物理行數範圍，或者是基於值範圍的視窗。

視窗函數通常在查詢的最後階段執行，即在結果集形成之後對結果集中的行應用視窗函數。視窗函數計算的結果是基於最終結果集的行，而不影響原始數據的分組，也就是說視窗函數的結果會添加到原查詢結果的每一行上，這是個人覺得這是OVEE(PARTITION BY COLUMN_NAME ORDER BY COLUMN_NAME) 與 傳統的 GROUP BY COLUMN ORDER BY COLUMN 差別最大的地方。

2.常用視窗函數

可以使用以下sql進行初始化

CREATE TABLE `video_play` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '用戶名',
  `video_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '視頻類型',
  `duration` int DEFAULT NULL COMMENT '播放時間',
  `date` datetime DEFAULT NULL COMMENT '開始播放時間',
  PRIMARY KEY (`id`)
);

INSERT INTO `video_play` (`id`, `user_name`, `video_type`, `duration`, `date`) VALUES (1, 'zhangsan', 'cartoon', 126, '2023-12-20 10:23:23');
INSERT INTO `video_play` (`id`, `user_name`, `video_type`, `duration`, `date`) VALUES (2, 'zhangsan', 'movie', 38, '2023-12-20 12:40:32');
INSERT INTO `video_play` (`id`, `user_name`, `video_type`, `duration`, `date`) VALUES (3, 'zhangsan', 'teleplay', 98, '2023-12-20 13:25:53');
INSERT INTO `video_play` (`id`, `user_name`, `video_type`, `duration`, `date`) VALUES (4, 'zhangsan', 'news', 45, '2023-12-20 15:24:43');
INSERT INTO `video_play` (`id`, `user_name`, `video_type`, `duration`, `date`) VALUES (5, 'lisi', 'cartoon', 83, '2023-12-20 09:28:17');
INSERT INTO `video_play` (`id`, `user_name`, `video_type`, `duration`, `date`) VALUES (6, 'lisi', 'movie', 56, '2023-12-20 12:11:11');
INSERT INTO `video_play` (`id`, `user_name`, `video_type`, `duration`, `date`) VALUES (7, 'lisi', 'teleplay', 79, '2023-12-20 13:56:23');
INSERT INTO `video_play` (`id`, `user_name`, `video_type`, `duration`, `date`) VALUES (8, 'wangwu', 'cartoon', 156, '2023-12-20 08:17:14');
INSERT INTO `video_play` (`id`, `user_name`, `video_type`, `duration`, `date`) VALUES (9, 'wangwu', 'movie', 89, '2023-12-20 09:25:35');
INSERT INTO `video_play` (`id`, `user_name`, `video_type`, `duration`, `date`) VALUES (10, 'wangwu', 'teleplay', 986, '2023-12-20 11:45:11');
INSERT INTO `video_play` (`id`, `user_name`, `video_type`, `duration`, `date`) VALUES (11, 'wangwu', 'news', 124, '2023-12-20 16:56:01');
INSERT INTO `video_play` (`id`, `user_name`, `video_type`, `duration`, `date`) VALUES (12, 'xiaoming', 'cartoon', 123, '2023-12-20 07:08:34');
INSERT INTO `video_play` (`id`, `user_name`, `video_type`, `duration`, `date`) VALUES (13, 'xiaoming', 'movie', 963, '2023-12-20 09:13:23');

2.1.SUM()

用法：SUM(COLUMN_NAME) OVER([PARTITION BY COLUMN_NAME] [ORDER BY COLUMN_NAME]);

如果存在PARTITION BY COLUMN_NAME, 則代表查詢結果根據COLUMN_NAME進行分組求和。

如果存在ORDER BY COLUMN_NAME, 則代表查詢結果則根據COLUMN_NAME排序後, 按照排序的結果進行累積求和。這裡的累積求和是指在當前視窗內，當前行的值加上前行的值得到的結果。如果不加ORDER BY同一視窗內每一行的值都相同，為當前視窗內所有值的和。

sql舉例：

SELECT 
	*, 
	SUM(duration) OVER() AS exp1, 
	SUM(duration) OVER(PARTITION BY user_name) AS exp2, 
	SUM(duration) OVER(ORDER BY `date` ASC) AS exp3, 
	SUM(duration) OVER(PARTITION BY user_name ORDER BY `date` ASC) AS exp4 
FROM 
	video_play;

2.2.COUNT()

用法：COUNT([DISTINCT] COLUMN_NAME) OVER([PARTITION BY COLUMN_NAME] [ORDER BY COLUMN_NAME])

整體用法跟SUM()函數類似，只是由求和變成了計數，並且支持DISTINCT去重，目前已知的是Mysql不支持。

sql舉例：

SELECT 
	*, 
	COUNT(duration) OVER() AS exp1, 
	COUNT(duration) OVER(PARTITION BY video_type) AS exp2, 
	COUNT(duration) OVER(ORDER BY duration) AS exp3, 
	COUNT(duration) OVER(PARTITION BY video_type ORDER BY duration ASC) AS exp4 
FROM 
	video_play;

2.3.AVG()

用法：AVG(COLUMN_NAME) OVER([PARTITION BY COLUMN_NAME] [ORDER BY COLUMN_NAME]);

整體用法跟SUM()函數類似，只是由求和變成了求平均值。

sql舉例：

SELECT 
	*, 
	AVG(duration) OVER() AS exp1, 
	AVG(duration) OVER(PARTITION BY user_name) AS exp2, 
	AVG(duration) OVER(ORDER BY `date` ASC) AS exp3, 
	AVG(duration) OVER(PARTITION BY user_name ORDER BY `date` ASC) AS exp4 
FROM 
	video_play;

2.4.ROW_NUMBER()

用法：ROW_NUMBER() OVER([PARTITION BY COLUMN_NAME] [ORDER BY COLUMN_NAME])

這個函數的功能是求結果的行號, 可以使用ORDER BY 進行內部排序之後再去求行號, 行號是連續的不會出現空缺

sql舉例：

SELECT 
	*, 
	ROW_NUMBER() OVER() AS exp1, 
	ROW_NUMBER() OVER(PARTITION BY user_name) AS exp2, 
	ROW_NUMBER() OVER(ORDER BY `date` ASC) AS exp3, 
	ROW_NUMBER() OVER(PARTITION BY user_name ORDER BY `date` ASC) AS exp4 
FROM 
	video_play;

這個函數是這次年報中用的比較多的函數，簡化版的需求就是求每個用戶播放時長TOPN的類型及播放時間。實現思路是現根據用戶分組，再使用該函數對時長進行排序並生成行號，最後取每個用戶的前N行數據，下麵是實現的sql及結果

WITH user_video_play_index AS (
	SELECT
		user_name,
		video_type,
		duration,
		ROW_NUMBER() OVER(PARTITION BY user_name ORDER BY duration DESC, date ASC) AS row_num
	FROM 
		video_play
)
	
SELECT 
	user_name,
	video_type,
	duration,
	row_num
FROM
	user_video_play_index
WHERE
	row_num <= 2

2.5.RANK()和DENSE_RANK()

用法：RANK()/DENSE_RANK() OVER([PARTITION BY COLUMN_NAME] [ORDER BY COLUMN_NAME])

這個函數的功能是求排名, 如果不使用ORDER BY指明要排序的列, 值會固定為1。如果排序之後的值相同, RANK值也會相同, 如果是RANK()函數，後面的值會跳過相同數量的間隔，DENSE_RANK()則不會（這次的數據集中的值沒有相同的，沒辦法體現出來這一點）。

sql舉例

SELECT 
	*, 
	RANK() OVER() AS exp1, 
	RANK() OVER(PARTITION BY user_name) AS exp2, 
	RANK() OVER(ORDER BY duration ASC) AS exp3, 
	RANK() OVER(PARTITION BY user_name ORDER BY duration ASC) AS exp4,
	DENSE_RANK() OVER() AS exp5, 
	DENSE_RANK() OVER(PARTITION BY user_name) AS exp6, 
	DENSE_RANK() OVER(ORDER BY duration ASC) AS exp7, 
	DENSE_RANK() OVER(PARTITION BY user_name ORDER BY duration ASC) AS exp8
FROM 
	video_play

2.6.FIRST_VALUE()和LAST_VALUE()

用法：FIRST_VALUE(COLUMN_NAME)/LAST_VALUE(COLUMN_NAME) OVER([PARTITION BY COLUMN_NAME] [ORDER BY COLUMN_NAME]);

這兩個函數是用來求分組和排序之後的第一個值和最後一個值, LAST_VALUE會隨著視窗大小的增加而變化, 所以如果進行排序一般是當前行的值。

sql舉例：

SELECT 
	*, 
	FIRST_VALUE(duration) OVER() AS exp1, 
	FIRST_VALUE(duration) OVER(PARTITION BY user_name) AS exp2, 
	FIRST_VALUE(duration) OVER(ORDER BY duration ASC) AS exp3, 
	FIRST_VALUE(duration) OVER(PARTITION BY user_name ORDER BY duration ASC) AS exp4,
	LAST_VALUE(duration) OVER() AS exp5,
	LAST_VALUE(duration) OVER(PARTITION BY user_name) AS exp6, 
	LAST_VALUE(duration) OVER(ORDER BY duration ASC) AS exp7, 
	LAST_VALUE(duration) OVER(PARTITION BY user_name ORDER BY duration ASC) AS exp8 
FROM 
	video_play

2.7.LAG()和LEAD()

用法：LAG(COLUMN_NAME, MOVE_LINE_NUM, DEFAULT_VALUE)/LEAD(COLUMN_NAME, MOVE_LINE_NUM, DEFAULT_VALUE) OVER([PARTITION BY COLUMN_NAME] [ORDER BY COLUMN_NAME]);

LAG和LEAD是分別用來向前或向後取值的函數; 函數裡面三個參數含義是從當前行向前或向後推MOVE_LINE_NUM行, 然後去取該行的COLUMN_NAME欄位, 如果向前或向後移動失敗(一般是超過視窗的頭部或超過視窗的尾部)則去取DEFAULT_VALUE的值。

sql舉例：

SELECT 
	*, 
	LAG(duration, 2, 0) OVER() AS exp1, 
	LAG(duration, 2, 0) OVER(PARTITION BY user_name) AS exp2, 
	LAG(duration, 2, 0) OVER(ORDER BY duration ASC) AS exp3, 
	LAG(duration, 2, 0) OVER(PARTITION BY user_name ORDER BY duration ASC) AS exp4,
	LEAD(duration, 2, 0) OVER() AS exp5,
	LEAD(duration, 2, 0) OVER(PARTITION BY user_name) AS exp6, 
	LEAD(duration, 2, 0) OVER(ORDER BY duration ASC) AS exp7, 
	LEAD(duration, 2, 0) OVER(PARTITION BY user_name ORDER BY duration ASC) AS exp8 
FROM 
	video_play

2.8.NTILE()

用法：NTILE(PART_NUM) OVER([PARTITION BY COLUMN_NAME] [ORDER BY COLUMN_NAME]);

NTILE函數是用來對數據進行分組的, 它會儘可能的把數據平均分為PART_NUM組，結果為當前行的組號。如果遇到無法均分的情況，mysql的邏輯時後面的組會比前面的組少1行數據，其他的資料庫暫時沒有測試。

sql舉例：

SELECT 
	*, 
	NTILE(2) OVER() AS exp1, 
	NTILE(2) OVER(PARTITION BY user_name) AS exp2, 
	NTILE(2) OVER(ORDER BY duration ASC) AS exp3, 
	NTILE(2) OVER(PARTITION BY user_name ORDER BY duration ASC) AS exp4
FROM 
	video_play

3.others

當使用視窗函數時，ROWS、RANGE 和 GROUPS 是用於定義視窗框架的不同方式，它們用於指定視窗函數操作中所考慮的數據範圍。

  • ROWS

    • SELECT
    id,
    value,
    SUM(value) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_values
FROM
    your_table;

    • 在這個例子中，ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING 指定了視窗函數 SUM() 的範圍，在當前行的前一行到後一行（共三行）的範圍內計算 value 的總和。

    • ROWS 是基於物理行數來定義視窗範圍，獨立於分組邏輯。

  • RANGE

    • SELECT
    date_column,
    value,
    SUM(value) OVER (ORDER BY date_column RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND INTERVAL 3 DAY FOLLOWING) AS sum_values
FROM
    your_table;

    • 在這個例子中，RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND INTERVAL 3 DAY FOLLOWING 指定了視窗函數 SUM() 的範圍，以當前行日期為中心，計算前七天到後三天內的 value 總和。

  • GROUPS

    • SELECT
    id,
    value,
    SUM(value) OVER (PARTITION BY category ORDER BY id GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_values
FROM
    your_table;

    • 在這個例子中，GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING 指定了視窗函數 SUM() 的範圍，以當前行所屬的 category 分組為基礎，在每個分組內的前一行到後一行的範圍內計算 value 的總和。

      這些不同的視窗框架定義方式允許在視窗函數中靈活地選擇不同的數據範圍進行計算和分析。

    • GROUPS 根據分組條件定義視窗範圍，對應於分組的邏輯；

github原文鏈接：https://github.com/YS1109/Note/blob/master/Mysql/window_func/window_func.md


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 識別指定window視窗的文本
    1. 簡單需求 通過圖文識別讀取一個指定window視窗的文本。 獲取視窗句柄，截圖保存成bitmap ,調用圖文識別庫. 測試結果是對中文下的識別不是特別好。 需要註意的是，tessdata要下載指定目錄頁下。 2. 引用包 a. 引用 tesseract4.1 b. Emgu.CV組件 3. 上 ...
  • .Net 8與硬體設備能碰撞出怎麼樣的火花（使用ImageSharp和Protobuf協議通過HidApi與設備通訊）
    前言 本人最近在社區里說想做稚暉君的那個瀚文鍵盤來著，結果遇到兩個老哥一個老哥送了我電路板，一個送了我焊接好元件的電路板，既然大家這麼捨得，那我也就真的投入製作了這把客制化鍵盤，當然我為了省錢也是特意把外殼模型重新切割，用3D印表機列印了整個外殼，不得不說省了八九百的CNC費用。鍵盤介紹我就不說了， ...
  • 第十單元 索引與視圖
    1. 常見的數據結構 1. 棧（stack） 特點：先進後出，後進先出 2. 隊列（Queue） 特點：先進先出 3. 數組（Array） 查詢速度快：通過地址值與索引可快速定位到數據 刪除效率低：刪除數據後，要將每個數據前移 添加效率極低：添加位置後，每個數據都後移，再添加數據。 4. 鏈表 鏈接 ...
  • c# 更改快捷方式文件圖標
    c# 更改快捷方式文件圖標c# 更改快捷方式文件圖標c# 更改快捷方式文件圖標c# 更改快捷方式文件圖標c# 更改快捷方式文件圖標c# 更改快捷方式文件圖標c# 更改快捷方式文件圖標c# 更改快捷方式文件圖標c# 更改快捷方式文件圖標 /// <summary> /// 更改快捷方式文件圖標 /// ...
  • 第四章 RPC 調用
    通過以上案例我們發現，Http請求調用服務實例屬實過於麻煩。其實對於請求同一個服務，很多步驟都是相同的，例如：服務名，地址，httpClient 創建步驟等。 RPC的出現，就是為瞭解決這一問題。 RPC: 即我們常說的遠程過程調用，就是像調用本地方法一樣調用遠程方法，通信協議大多採用二進位方式。 ...
  • 每個.NET開發都應掌握的C#網路編程知識點
    上篇文章講述了C#處理文件系統I/O知識點，本文將介紹C#網路編程知識點。隨著信息技術的不斷發展，網路編程在.NET開發中變得愈發重要。無論是構建Web應用、實現實時通信，還是處理分散式系統，C#網路編程都是必備的技能。以下是.NET開發者在網路編程中應該掌握的關鍵知識點。 一、http請求知識點 ...
  • 第三章 分散式配置中心
    1. 簡介 為什麼需要分散式配置中心 分散式配置中心是為瞭解決在分散式系統中進行配置管理的需求而引入的。在傳統的單體應用中，通常使用配置文件集中管理系統的配置信息。然而，在分散式系統中，由於系統規模變大、節點眾多，並且可能部署在不同的伺服器上，傳統的配置文件方式會面臨一些挑戰。 首先，配置文件的修改 ...
  • 構建實時通信應用：使用.NET和SignalR輕鬆實現前沿技術
    概述：學習.NET中使用SignalR實現實時通信功能。從安裝庫、創建Hub，到客戶端基礎功能，一步步構建實時聊天室。深入講解分組功能，使您能夠定向廣播消息。簡潔實用，助您輕鬆掌握實現創新Web應用的技能。 SignalR是一個強大的實時通信庫，為.NET應用程式提供輕鬆的實時功能。它支持雙向通信， ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類