MySQL 視窗函數

来源:https://www.cnblogs.com/cjsblog/archive/2022/10/08/16743807.html
-Advertisement-
Play Games

1. 視窗函數概念和語法 視窗函數對一組查詢行執行類似聚合的操作。然而，聚合操作將查詢行分組到單個結果行，而視窗函數為每個查詢行產生一個結果: 函數求值發生的行稱為當前行 與發生函數求值的當前行相關的查詢行組成了當前行的視窗 相比之下，視窗操作不會將一組查詢行摺疊到單個輸出行。相反，它們為每一行生成 ...


1. 視窗函數概念和語法

視窗函數對一組查詢行執行類似聚合的操作。然而，聚合操作將查詢行分組到單個結果行，而視窗函數為每個查詢行產生一個結果:

  • 函數求值發生的行稱為當前行
  • 與發生函數求值的當前行相關的查詢行組成了當前行的視窗

相比之下，視窗操作不會將一組查詢行摺疊到單個輸出行。相反，它們為每一行生成一個結果。

SELECT 
    manufacturer, product, profit,
    SUM(profit) OVER() AS total_profit,
    SUM(profit) OVER(PARTITION BY manufacturer) AS manufacturer_profit
FROM sales;

查詢中的每個視窗操作都通過包含一個 OVER 子句來表示，該子句指定如何將查詢行劃分為組以供視窗函數處理：

  • 第一個 OVER 子句是空的，它將整個查詢行集視為一個分區。視窗函數因此產生一個全局和，但對每一行都這樣做。
  • 第二個 OVER 子句按 manufacturer 劃分行，產生每個分區（每個manufacturer）的總和。該函數為每個分區行生成此總和。

視窗函數只允許在查詢列表和 ORDER BY 子句中使用。

查詢結果行由 FROM 子句確定，在 WHERE、GROUP BY 和 HAVING 處理之後，視窗執行發生在 ORDER BY、LIMIT 和 SELECT DISTINCT 之前。

OVER子句被允許用於許多聚合函數，因此，這些聚合函數可以用作視窗函數或非視窗函數，具體取決於是否存在 OVER 子句：

AVG()
BIT_AND()
BIT_OR()
BIT_XOR()
COUNT()
JSON_ARRAYAGG()
JSON_OBJECTAGG()
MAX()
MIN()
STDDEV_POP(), STDDEV(), STD()
STDDEV_SAMP()
SUM()
VAR_POP(), VARIANCE()
VAR_SAMP()

MySQL還支持只能作為視窗函數使用的非聚合函數。對於這些，OVER子句是必須的

CUME_DIST()
DENSE_RANK()
FIRST_VALUE()
LAG()
LAST_VALUE()
LEAD()
NTH_VALUE()
NTILE()
PERCENT_RANK()
RANK()
ROW_NUMBER()

ROW_NUMBER() 它生成其分區內每一行的行號。預設情況下，分區行是無序的，行編號是不確定的。若要對分區行進行排序，請在視窗定義中包含一個ORDER BY子句。下麵的示例中，查詢使用無序分區和有序分區(row_num1和row_num2列)來說明省略和包含ORDER BY之間的區別:

SELECT 
    manufacturer, product, profit,
    ROW_NUMBER() OVER(PARTITION BY manufacturer) AS row_num1,
    ROW_NUMBER() OVER(PARTITION BY manufacturer ORDER BY profit) AS row_num2
FROM sales;

如前所述，要使用視窗函數（或將聚合函數視為視窗函數），需要在函數調用後包含OVER子句。OVER子句有兩種形式:

over_clause:
    {OVER (window_spec) | OVER window_name}

這兩種形式都定義了視窗函數應該如何處理查詢行。它們的區別在於視窗是直接在OVER子句中定義的，還是通過對查詢中其他地方定義的命名視窗的引用提供的:

  • 在第一種情況下，視窗規範直接出現在 OVER 子句中的括弧之間。
  • 在第二種情況下，window_name 是由查詢中其他地方的 WINDOW 子句定義的視窗規範的名稱。

對於 OVER (window_spec) 語法，視窗規範有幾個部分，都是可選的：

window_spec:
    [window_name] [partition_clause] [order_clause] [frame_clause]

如果 OVER() 為空，則視窗由所有查詢行組成，視窗函數使用所有行計算結果。否則，括弧中的子句決定了使用哪些查詢行來計算函數結果，以及它們是如何分區和排序的：

  • window_name: 由查詢中其他地方的window子句定義的視窗的名稱。如果window_name單獨出現在OVER子句中，則它完全定義了視窗。如果分區、排序或分幀子句也給出了，它們會修改被命名視窗的解釋。
  • partition_clause: PARTITION BY 子句指示如何將查詢行分組。給定行的視窗函數結果基於包含該行的分區的行。如果省略 PARTITION BY，則有一個由所有查詢行組成的分區。 
    partition_clause:
    PARTITION BY expr [, expr] ...
  • order_clause: ORDER BY 子句指示如何對每個分區中的行進行排序。根據 ORDER BY 子句相等的分區行被視為對等。如果省略 ORDER BY，則分區行是無序的，沒有隱含的處理順序，並且所有分區行都是對等的。 
    order_clause:
    ORDER BY expr [ASC|DESC] [, expr [ASC|DESC]] ...

每個ORDER BY表達式後面可以有選擇地跟著ASC或DESC來表示排序方向。NULL 值首先進行升序排序，最後進行降序排序。

視窗定義中的 ORDER BY 適用於各個分區。要將結果集作為一個整體進行排序，請在查詢頂層包含 ORDER BY。 

  • frame_clause: frame是當前分區的子集，frame子句指定如何定義該子集。

小結：

視窗，就是數據範圍，也可以理解為記錄集合，視窗函數就是在滿足某種條件的記錄集合上執行的特殊函數。即，應用在視窗內的函數。

  • 靜態視窗：視窗大小是固定的，視窗內的每條記錄都要執行此函數
  • 動態視窗：也叫滑動視窗，視窗大小是變化的

視窗函數有以下功能：

  • 同時具有分組和排序的功能
  • 不減少原表的行數

2. 視窗函數frame規範

一個frame是當前分區的一個子集，frame子句指定如何定義這個子集。

frame是根據當前行確定的，這使得frame可以根據當前行在分區中的位置在分區中移動。

  • 通過將一個frame定義為從分區開始到當前行的所有行，我們可以計算每一行的運行總數。
  • 通過將一個frame定義為在當前行的每一邊擴展N行，我們可以計算滾動平均。

下麵的查詢演示瞭如何使用移動幀來計算每組按時間順序排列的值的總和，以及從當前行和緊隨其後的行計算的滾動平均值:

SELECT 
  manufacturer, `month`, profit, 
  SUM(profit) OVER(
	    PARTITION BY manufacturer 
	    ORDER BY `month` 
	    ROWS unbounded PRECEDING
	  ) AS running_total, 
  AVG(profit) OVER(
	    PARTITION BY manufacturer 
	    ORDER BY `month` 
	    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
	  ) AS running_average 
FROM 
  sales;

frame 子句語法：

frame_clause:
    frame_units frame_extent

frame_units:
    {ROWS | RANGE}

在沒有frame子句的情況下，預設frame取決於是否存在ORDER BY子句。

frame_units值表示當前行和幀行之間的關係類型：

  • ROWS: frame由開始行和結束行位置定義。偏移量是行號與當前行號之間的差異。
  • RANGE: frame由值範圍內的行定義。偏移量是行值與當前行值之間的差異。

frame_extend 表示frame的起始點和結束點。可以只指定frame的開始（在這種情況下，當前行隱式地是結束）或使用BETWEEN指定frame的兩個端點：

frame_extent:
    {frame_start | frame_between}

frame_between:
    BETWEEN frame_start AND frame_end

frame_start, frame_end: {
    CURRENT ROW
  | UNBOUNDED PRECEDING
  | UNBOUNDED FOLLOWING
  | expr PRECEDING
  | expr FOLLOWING
}

使用BETWEEN語法，frame_start不能發生在frame_end之後。

允許的frame_start和frame_end值含義如下：

  • CURRENT ROW:  對於ROWS，邊界是當前行。對於RANGE，邊界是當前行的對等點。
  • UNBOUNDED PRECEDING:  邊界是第一個分區行。
  • UNBOUNDED FOLLOWING:  邊界是最後一個分區行。
  • expr PRECEDING:  對於ROWS，邊界是當前行之前的 expr 行。對於RANGE，邊界是值等於當前行值減去 expr 的行，如果當前行值為NULL，則綁定為該行的對等體。
  • expr FOLLOWING:  對於ROWS，邊界是當前行之後的 expr 行。對於RANGE，邊界是值等於當前行值加上 expr 的行，如果當前行值為NULL，則綁定為該行的對等體。

下麵是一些有效expr PRECEDING 和expr FOLLOWING 示例：

10 PRECEDING
INTERVAL 5 DAY PRECEDING
5 FOLLOWING
INTERVAL '2:30' MINUTE_SECOND FOLLOWING

在沒有frame子句的情況下，預設的frame取決於是否存在ORDER BY子句：

  • 有ORDER BY：預設frame包括從分區開始到當前行的行，包括當前行的所有對等點。與之等效的frame如下： 
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  • 沒有ORDER BY：預設frame包括所有的分區行（因為，如果沒有ORDER BY，所有的分區行都是對等的）。與之等效的frame如下： 
    RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

因為預設frame會根據是否存在ORDER BY而有所不同，所以向查詢添加ORDER BY以獲得確定性結果可能會更改結果。要獲得相同的結果，但按ORDER BY排序，無論ORDER BY是否存在，都要提供要使用的顯式frame規範。 

3. 視窗函數應用

示例數據

序號函數

select
	name, subject, score,
	rank() over w as 'rank',
	dense_rank() over w as 'dense_rank',
	row_number() over w as 'row_number'
from
	student 
window w as (partition by subject order by score desc);

可以看到，row_number就是個序號，rank在處理併列情況的時候會占用後面的序號，而dense_rank不會

同時，這個SQL中使用了命名視窗寫法

Top-N問題：每個類別中取前N條 

這類問題可以套用這個模板

SELECT * FROM (SELECT *,row_number() over (PARTITION BY 姓名 ORDER BY 成績 DESC) AS ranking FROM test) AS tmp WHERE tmp.ranking <= N;

查詢每科第一名

select * from ( 
	select
		name, subject, score,
		dense_rank() over(partition by subject order by score desc) as 'rn'
	from
		student 
) tmp where tmp.rn = 1;

每科前三名

select * from ( 
	select
		name,
		subject,
		score,
		row_number() over(partition by subject order by score desc) as 'rn'
	from
		student 
) tmp where tmp.rn <= 3;

每科高於平均分數（寫法一）

select * from (
	select
		name, subject, score,
		avg(score) over(partition by subject) as 'avg_score'
	from
		student
) tmp where tmp.score > tmp.avg_score;

高於每科平均分數（寫法二） 

select
	name, subject, score
from
	student s
where
	s.score > (select avg(score) from student s2 where s2.subject = s.subject) 
order by s.subject asc;

聚集函數作視窗函數

select
	name, subject, score,
	first_value(score) over(partition by subject order by score desc) as '單科最高分',
	max(score) over(partition by subject) as '科目最高分',
	min(score) over(partition by subject) as '科目最低分',
	avg(score) over(partition by subject) as '科目平均分',
	sum(score) over(partition by subject order by score desc rows between unbounded preceding and current row) as '總分',
	sum(score) over(partition by name) as '學生總分',
	count(subject) over (partition by name) as '參加的學科數'
from
	student order by subject;

假設90分算及格，求每個學生的及格率

select
	t1.name,
	t1.pass_num as '通過的科目數',
	t2.total_num as '參加的科目數',
	concat(round((t1.pass_num / t2.total_num) * 100, 2), '%') as '及格率'
from
	(select name, count(*) pass_num from student where score > 90 group by name) t1
left join (select name, count(*) total_num from student group by name) t2 
	on t1.name = t2.name;

最後，視窗函數只能在查詢或子查詢中使用，不能在UPDATE或DELETE語句中使用它們來更新行。


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

-Advertisement-
Play Games
更多相關文章
  • 從 C# 崩潰異常 中研究頁堆佈局
    一：背景 1.講故事 最近遇到一位朋友的程式崩潰，發現崩潰點在富編輯器 msftedit 上，這個不是重點，重點在於發現他已經開啟了 頁堆 ，看樣子是做了最後的掙扎。 0:000> !analyze -v EXCEPTION_RECORD: (.exr -1) ExceptionAddress: 8 ...
  • keepalived高可用
    一、keepalived是什麼 Keepalived 軟體起初是專為LVS負載均衡軟體設計的，用來管理並監控LVS集群系統中各個服務節點的狀態，後來又加入了可以實現高可用的VRRP功能。因此，Keepalived除了能夠管理LVS軟體外，還可以作為其他服務（例如：Nginx、Haproxy、MySQ ...
  • 痞子衡嵌入式：RT-MFB - 一種靈活的i.MXRT下多串列NOR Flash型號選擇的量產方案
    大家好，我是痞子衡，是正經搞技術的痞子。今天痞子衡給大家介紹的是一種靈活的i.MXRT下多串列NOR Flash型號選擇的量產方案。 對於以 i.MXRT 這類沒有內部 NVM (Non-Volatile Memory) 的 MCU 為主控的項目來說，為其選配一顆 NVM 作為代碼存儲器是頭等大事， ...
  • Art Text 4 Mac版(藝術字體製作和圖標設計軟體)中文
    如何設計藝術字？如何進行圖標設計？Art Text 4 Mac版是一款藝術字體製作和圖標設計軟體，它支持多圖層，可以輕鬆創造複雜圖形。可將該程式創建的圖形應用於iWork，Microsoft office、BeLight等應用程式,以及各種其他文本編輯和網頁設計程式。使用Art Text 4 Mac ...
  • 手把手教你使用LabVIEW OpenCV dnn實現圖像分類（含源碼）
    @(文章目錄) 前言 上一篇和大家一起分享瞭如何使用LabVIEW OpenCV dnn實現手寫數字識別，今天我們一起來看一下如何使用LabVIEW OpenCV dnn實現圖像分類。 一、什麼是圖像分類？ 1、圖像分類的概念 圖像分類，核心是從給定的分類集合中給圖像分配一個標簽的任務。實際上，這意 ...
  • Mac游戲：經典割繩子游戲Cut the Rope Remastered Mac版(休閑益智游戲)
    還在尋找一款好玩的休閑益智游戲嗎？現為大家分享一款經典割繩子游戲Cut the Rope Remastered Mac版，這款游戲的目標很簡單，就是合理的切割繩子，讓小怪物吃到糖果即可過關，但是要想得到高評價，就要想辦法吃到所有的星星。 詳情：經典割繩子游戲Cut the Rope Remaster ...
  • 聊聊Linux中CPU上下文切換
    作者：小牛呼嚕嚕 | https://xiaoniuhululu.com 電腦內功、JAVA底層、面試相關資料等更多精彩文章在公眾號「小牛呼嚕嚕 」 什麼是CPU上下文 Linux是一個多任務的操作系統，多任務操作系統是指多個進程運行在一個 CPU 中互不打擾，看起來像同時運行一樣。多任務的操作系 ...
  • day08-MySQL事務
    MySQL事務 先來看一個例子 有一張balance表: 需求：將tom的100塊錢轉到King賬戶中 執行的操作是： update balance set money = money -100 where id = 100 update balance set money = money +100 ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類