學習MySQL（五）：窗口函數

窗口函數介紹

窗口函數的引入是為了解決想要既顯示聚集前的數據，又要顯示聚集后的數據；窗口數對一組值進行操作，不需要使用GROUP BY子句對數據進行分組，能夠在同一行中同時返回基礎行的列和聚合列。

強調：使用MySQL 8.0版本方可實現

基本語法

函數名（列） over（選項） 選項為partition by 列 order by 列

解釋：

  • over(partition by xxx) 按xxx分組的所有行進行分組
  • over(partition by xxx order by aaa) 按列xxx分組，按列aaa排序
  • over(order by aaa) 按aaa列排序
  • over括號中的partition by和order by的使用根據具體情況選擇
-- 需求：計算每個學生的及格科目數
-- 使用聚合函數，類似數據透視表，原有表結構已發生變化
SELECT student_id,count( sid ) FROM score WHERE    num >= 60 GROUP BY student_id;-- 使用窗口函數，不會更改原表結構
SELECT student_id,count( sid ) over ( PARTITION BY student_id ORDER BY student_id ) AS 及格數
FROM score WHERE num >= 60;

聚合窗口函數

語法：聚合函數（列） over（partition by 列 order by 列）

常見的聚合函數：sum() count() avg() max() min()

排序窗口函數

  • row_number()：僅僅根據行號進行排序，相同結果則排序按照順序依次排
  • rank()：排名，與row_number函數不同的是，rank函數考慮到了over子句中排序字段值相同的情況，over子句中排序字段值相同的排序結果是一樣的，后面字段值不相同的序號將跳過相同的排名號排下一個。如：11335
  • dense_rank()：密集排序，用法跟rank類似，唯一不同是當排序結果相同時，排序不跳躍，而是緊跟排下一個。如：11223
  • ntile()：桶排序，首先，ntile會先根據你的分組依據，然后把每個組的總記錄數進行按照你給的ntile(n)里的數字n進行均分，這個數字就是桶數，例如一個組內總共12條記錄，若n=6，則等劃分成6桶，然后按照num的排序等級劃分，12/6=2則每個桶兩條記錄，也就是112233445566的排序結果，常用于提取前百分之多少的應用場景。

都是排名函數，不同之處在對于名次相同的數據處理方式

-- 對每門課程進行排序
SELECTs.sid,s1.sname,s1.gender,c.cname,s.num,row_number() over ( PARTITION BY c.cname ORDER BY num DESC ) AS row_number排名,rank() over ( PARTITION BY c.cname ORDER BY num DESC ) AS rank排名,dense_rank() over ( PARTITION BY c.cname ORDER BY num DESC ) AS dense_rank排名,ntile( 6 ) over ( PARTITION BY c.cname ORDER BY num DESC ) AS ntile排名 
FROMscore AS sJOIN student AS s1 ON s.student_id = s1.sidLEFT JOIN course AS c ON s.course_id = c.cid-- 計算每門課程前三，考慮排名相同的情況
SELECT * FROM (SELECT s.sid,s1.sname,s1.gender,c.cname,s.num,dense_rank() over ( PARTITION BY c.cname ORDER BY num DESC ) AS dense_rank排名 FROMscore AS sJOIN student AS s1 ON s.student_id = s1.sidLEFT JOIN course AS c ON s.course_id = c.cid ) AS a 
WHEREdense_rank排名 <=3

位置移動窗口函數

  • lag(col,n)：col列名，n行數，用于統計窗口內往上第n行值
  • lead(col,n)：col列名，n行數，用于統計窗口內往下第n行值

這兩個函數可以用于同列中相鄰行的數據計算

應用場景：

  • 計算作弊次數
-- 需求：對于下面的數據，對于同一用戶（uid）如果在2分鐘之內重新登陸，則判斷為作弊，統計哪些用戶有作弊行為，并計算作弊次數
-- 數據代碼
CREATE TABLE lead_table (id INT PRIMARY KEY,uid INT NOT NULL,login_time datetime NOT NULL );
INSERT INTO lead_table
VALUES( 1, 1, "2020-8-26 12:59:00" ),( 2, 1, "2020-8-26 13:02:23" ),( 3, 1, "2020-8-26 13:03:34" ),( 4, 1, "2020-8-26 13:09:00" ),( 5, 2, "2020-8-26 13:57:00" ),( 6, 2, "2020-8-26 13:59:00" ),( 7, 2, "2020-8-26 13:59:45" );

思路：根據題目要求，如果能把相鄰兩列的下面一列與上面那一列變成同一行，不久能實現相減了么，因此我們可以多生成一列，例如：把uid都為1的第二行記錄生成到第一行，以此類推，這就可以用到lead往下移動的操作了

-- 第一步
SELECT id,uid,login_time,
LEAD( login_time, 1 ) OVER ( PARTITION BY uid ORDER BY login_time ) lead_time
FROM lead_table;--第二步
SELECT id,uid,login_time,
LEAD( login_time, 1 ) OVER ( PARTITION BY uid ORDER BY login_time ) lead_time,
TIMESTAMPDIFF(SECOND,login_time,LEAD( login_time, 1 ) OVER ( PARTITION BY uid ORDER BY login_time )) AS 相差秒數 
FROM lead_table;-- 最終代碼
SELECT uid,COUNT( 1 ) AS 作弊次數
FROM(SELECT id,uid,login_time,LEAD( login_time, 1 ) OVER ( PARTITION BY uid ORDER BY login_time ) lead_time,TIMESTAMPDIFF(SECOND,login_time,lead( login_time, 1 ) OVER ( PARTITION BY uid ORDER BY login_time )) AS 相差秒數 
FROM lead_table ) AS e 
WHERE 相差秒數 <= 120 
GROUP BY uid;
  • 計算次日留存率

其他窗口函數

  • first_value(column)：取分組排序后第一個值
SELECT s.sid,s1.sname,s1.gender,c.cname,s.num,
FIRST_VALUE(num) OVER(PARTITION by c.cname ORDER BY num DESC) AS first_value用法
FROM score AS s
JOIN student AS s1 ON s.student_id = s1.sid
LEFT JOIN course AS c ON s.course_id = c.cid
  • last_value(column)：取分組排序后最后一個值
SELECT s.sid,s1.sname,s1.gender,c.cname,s.num,
LAST_VALUE(num) OVER(PARTITION by c.cname ORDER BY num DESC) AS last_value用法
FROM score AS s
JOIN student AS s1 ON s.student_id = s1.sid
LEFT JOIN course AS c ON s.course_id = c.cid

為什么和想要的結果不一樣呢？

實際上，窗口函數默認統計范圍是rows between unbounded preceding and current row，也就是取當前行數據與當前行之前的數據的比較。

修改SQL，在order by條件的后面加上語句：rows between unbounded preceding and unbounded following，可以理解為：當前分組數據中的所有數據進行比較，取最后一條記錄。

SELECT s.sid,s1.sname,s1.gender,c.cname,s.num,
LAST_VALUE(num) OVER(PARTITION by c.cname ORDER BY num DESC rows between unbounded preceding and unbounded following) AS last_value用法
FROM score AS s
JOIN student AS s1 ON s.student_id = s1.sid
LEFT JOIN course AS c ON s.course_id = c.cid

詳細介紹：

  • rows between XXX and XXX
  • unbounded 無限制的
  • preceding 分區的當前記錄的向前偏移量
  • current 當前
  • following 分區的當前記錄的向后偏移量

示例：累計計算每個月的銷售額

-- 示例數據
CREATE TABLE sale (id INT PRIMARY KEY auto_increment,年份 INT,月份 INT,
money FLOAT ( 10, 2 ));
INSERT INTO sale ( 年份, 月份, money )
VALUES( 2020, 1, 5840 ),( 2020, 2, 5780 ),( 2020, 3, 4300 ),( 2020, 4, 4760 ),( 2020, 5, 3630 ),( 2020, 6, 4130 ),( 2020, 7, 4350 );-- 語句
SELECT *,sum( money ) over ( ORDER BY 月份rows between unbounded preceding and current row) AS 累計銷售額 
FROM sale;

本章示例數據

CREATE DATABASE school;
USE school;
CREATE TABLE class (cid INT ( 11 ) NOT NULL auto_increment,caption VARCHAR ( 32 ) NOT NULL,PRIMARY KEY ( cid ) 
) ENGINE = INNODB charset = utf8;INSERT INTO class
VALUES( 1, '三年二班' ),( 2, '三年三班' ),( 3, '二年二班' ),( 4, '一年二班' ),( 5, '二年五班' );CREATE TABLE teacher (tid INT ( 11 ) NOT NULL auto_increment,tname VARCHAR ( 32 ) NOT NULL,PRIMARY KEY ( tid )
) ENGINE = INNODB DEFAULT charset = utf8;INSERT INTO teacher
VALUES( 1, '張磊老師' ),( 2, '李平老師' ),( 3, '劉蘭老師' ),( 4, '朱朱老師' ),( 5, '李杰老師' );CREATE TABLE course (cid INT ( 11 ) NOT NULL auto_increment,cname VARCHAR ( 32 ) NOT NULL,teacher_id INT ( 11 ) NOT NULL,PRIMARY KEY ( cid ),KEY fk_couurse_teacher ( teacher_id ),CONSTRAINT fk_course_teacher FOREIGN KEY ( teacher_id ) REFERENCES teacher ( tid ) 
) ENGINE = INNODB DEFAULT charset = utf8;
INSERT INTO course
VALUES( 1, '生物', 1 ),( 2, '物理', 2 ),( 3, '體育', 3 ),( 4, '美術', 2 );CREATE TABLE student (sid INT ( 11 ) NOT NULL auto_increment,gender CHAR ( 1 ) NOT NULL,class_id INT ( 11 ) NOT NULL,sname VARCHAR ( 32 ) NOT NULL,PRIMARY KEY ( sid ),KEY fk_class ( class_id ),CONSTRAINT fk_class FOREIGN KEY ( class_id ) REFERENCES class ( cid ) 
) ENGINE = INNODB DEFAULT charset = utf8;INSERT INTO student
VALUES( 1, '男', 1, '理解' ),( 2, '女', 1, '鋼蛋' ),( 3, '男', 1, '張三' ),( 4, '男', 1, '張思' ),( 5, '女', 1, '網易' ),( 6, '男', 1, '王二' ),( 7, '男', 2, '鐵道' ),( 8, '男', 2, '李武' ),( 9, '男', 2, '劉三' ),( 10, '女', 2, '劉一' ),( 11, '男', 2, '劉思' ),( 12, '男', 3, '王三' ),( 13, '男', 3, '小五' ),( 14, '男', 3, '小七' ),( 15, '女', 3, '如花' ),( 16, '男', 3, '張四' );CREATE TABLE score (sid INT ( 11 ) NOT NULL auto_increment,student_id INT ( 11 ) NOT NULL,course_id INT ( 11 ) NOT NULL,num INT ( 11 ) NOT NULL,PRIMARY KEY ( sid ),KEY fk_score_student ( student_id ),KEY fk_score_course ( course_id ),CONSTRAINT fk_score_course FOREIGN KEY ( course_id ) REFERENCES course ( cid ),CONSTRAINT fk_score_student FOREIGN KEY ( student_id ) REFERENCES student ( sid ) 
) ENGINE = INNODB DEFAULT charset = utf8;INSERT INTO score
VALUES( 1, 1, 1, 10 ),( 2, 1, 2, 9 ),( 5, 1, 4, 66 ),( 6, 2, 1, 8 ),( 8, 2, 3, 68 ),( 9, 2, 4, 99 ),( 10, 3, 1, 77 ),( 11, 3, 2, 66 ),( 12, 3, 3, 87 ),( 13, 3, 4, 99 ),( 14, 4, 1, 79 ),( 15, 4, 2, 11 ),( 16, 4, 3, 67 ),( 17, 4, 4, 100 ),( 18, 5, 1, 79 ),( 19, 5, 2, 11 ),( 20, 5, 3, 67 ),( 21, 5, 4, 100 );

來自: 學習MySQL（五）：窗口函數

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/web/13158.shtml
繁體地址，請注明出處：http://hk.pswp.cn/web/13158.shtml
英文地址，請注明出處：http://en.pswp.cn/web/13158.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！

相關文章

?學者觀察 | 從區塊鏈應用創新看長安鏈發展——CCF區塊鏈專委會榮譽主任斯雪明

?學者觀察 | 從區塊鏈應用創新看長安鏈發展——CCF區塊鏈專委會榮譽主任斯雪明

導語 2024年1月27日&#xff0c;斯雪明教授在長安鏈發布三周年慶暨生態年會上發表演講&#xff0c;認為在區塊鏈發展過程中&#xff0c;不僅需要技術創新&#xff0c;同時需要有價值、有特色、有示范意義的應用創新。斯雪明教授介紹了國內區塊鏈技術與應用發展的現狀、趨勢與挑…
閱讀更多...
【數據結構】排序(直接插入排序,希爾排序)

【數據結構】排序(直接插入排序,希爾排序)

目錄 一、排序的概念 二、常見的排序算法 三、插入排序 1.直接插入排序 1.直接插入排序實現 2.直接插入排序特性及復雜度 2.希爾排序 1.排序思路 2.希爾排序實現 3.希爾排序的特性及復雜度 一、排序的概念 排序&#xff1a;所謂排序&#xff0c;就是使一串記錄&#x…
閱讀更多...
python手寫數字識別（PaddlePaddle框架、MNIST數據集）

python手寫數字識別（PaddlePaddle框架、MNIST數據集）

python手寫數字識別&#xff08;PaddlePaddle框架、MNIST數據集&#xff09; import paddle import paddle.nn.functional as F from paddle.vision.transforms import Compose, Normalizetransform Compose([Normalize(mean[127.5],std[127.5],data_formatCHW)]) # 使用tran…
閱讀更多...
[Java基礎揉碎]多線程基礎

[Java基礎揉碎]多線程基礎

多線程基礎 什么是程序, 進程 什么是線程 什么是單線程,多線程 并發, 并行的概念 單核cpu來回切換, 造成貌似同時執行多個任務, 就是并發; 在我們的電腦中可能同時存在并發和并行; 怎么查看自己電腦的cpu有幾核 1.資源監視器查看 2.此電腦圖標右鍵管理- 設備管理器- 處理器…
閱讀更多...
k8s 二進制安裝 詳細安裝步驟

k8s 二進制安裝 詳細安裝步驟

目錄 一 實驗環境 二 操作系統初始化配置&#xff08;所有機器&#xff09; 1&#xff0c;關閉防火墻 2&#xff0c;關閉selinux 3&#xff0c;關閉swap 4, 根據規劃設置主機名 5, 做域名映射 6&#xff0c;調整內核參數 7&#xff0c; 時間同步 三 部署 dock…
閱讀更多...
uniapp vu3 scroll-view 滾動到指定位置

uniapp vu3 scroll-view 滾動到指定位置

設置 scroll-view <scroll-view :scroll-y"true" :scroll-with-animation"true" :scroll-top"scrollTop" :style"height:${height}px"><view v-for"item in 10" :id"box${item}">box {{item}}</v…
閱讀更多...
原生IP介紹

原生IP介紹

原生IP&#xff0c;顧名思義&#xff0c;即初始真實IP地址&#xff0c;是指從互聯網服務提供商獲得的IP地址&#xff0c;IP地址在互聯網與用戶之間直接建立聯系&#xff0c;不需要經過代理服務器代理轉發。 原生IP具備以下特點。 1.直接性 原生IP可以直接連接互聯網&#xff…
閱讀更多...
337_C++_內存對齊操作，內存分配、或其他需要數據對齊的場合中是很常見的操作

337_C++_內存對齊操作，內存分配、或其他需要數據對齊的場合中是很常見的操作

size_t ImagesCache::_alignSize(size_t srcSz, size_t alnSz) {if (0 == alnSz) {printf("[ImagesCache] Incorrect input parameters\n");return srcSz;
閱讀更多...
代碼隨想錄算法訓練營第五十四天

代碼隨想錄算法訓練營第五十四天

第二題我看了很久還是沒太明白&#xff0c;我發現理解動規有一點點吃力了啊&#xff0c;努努力。 392.判斷子序列 總感覺在不等于的時候&#xff0c;應該是dp[i][j] dp[i-1][j-2]; 這里其實按他那個圖會更好理解一點。 class Solution { public:bool isSubsequence(string s, …
閱讀更多...
Gone框架介紹19 -如何進行單元測試？

Gone框架介紹19 -如何進行單元測試？

gone是可以高效開發Web服務的Golang依賴注入框架 github地址&#xff1a;https://github.com/gone-io/gone 文檔地址&#xff1a;https://goner.fun/zh/ 請幫忙在github上點個 ??吧&#xff0c;這對我很重要 &#xff1b;萬分感謝&#xff01;&#xff01; 文章目錄 單元測試…
閱讀更多...
CentOs安裝

CentOs安裝

安裝 開發工具 &#xff1a;GCC、 JDK、mysql 如果出現藍屏&#xff0c;要在BIOS開啟虛擬化支持&#xff0c;或者移除打印機。
閱讀更多...
Google：站長移除無效網址

Google：站長移除無效網址

當您的網址不需要呈現在Google站長中時&#xff0c;您可以在站長工具中移除網址 操作步驟&#xff1a;登錄Google站長&#xff0c;綁定網站完成后&#xff0c;點擊左側刪除 >> 輸入網址 如果遇到一些網址&#xff0c;可以找尋網址間的規律&#xff0c;比如說&#xff0…
閱讀更多...
2024生日快樂祝福HTML源碼

2024生日快樂祝福HTML源碼

源碼介紹 2024生日快樂祝福HTML源碼&#xff0c;源碼由HTMLCSSJS組成&#xff0c;記事本打開源碼文件可以進行內容文字之類的修改&#xff0c;雙擊html文件可以本地運行效果&#xff0c;也可以上傳到服務器里面&#xff0c; 源碼截圖 源碼下載 2024生日快樂祝福HTML源碼
閱讀更多...
Shell腳本 ＜＜EOF ... EOF語法（Here Document）（特殊的輸入重定向方式）（定界符）

Shell腳本 ＜＜EOF ... EOF語法（Here Document）（特殊的輸入重定向方式）（定界符）

文章目錄 Here Document語法Here Document 的基本語法使用場景 關于定界符定界符不是變量定界符在 Here Document 中只是一個字符串&#xff0c;主要功能是標記輸入文本的開始和結束&#xff0c;使用時應遵循最佳實踐格式要求例子和說明如何使用定界符定界符可重復使用&#xf…
閱讀更多...
Spring數據訪問全攻略：從JdbcTemplate到聲明式事務

Spring數據訪問全攻略：從JdbcTemplate到聲明式事務

上文講到 —— 航向數據之海&#xff1a;Spring的JPA與Hibernate秘籍 本文目錄 四. JdbcTemplate的使用定義JdbcTemplate及其在Spring中的作用展示如何使用JdbcTemplate簡化數據庫操作1. 配置JdbcTemplate2. 使用JdbcTemplate查詢數據3. 打印查詢結果 五. Spring的事務管理介紹…
閱讀更多...
橋接模式

橋接模式

橋接模式&#xff1a;在這種模式下&#xff0c;虛擬機就像是局域網中一臺獨立的主機&#xff0c;能夠訪問網內任何一臺機器。在橋接模式下&#xff0c;必須為虛擬系統手動配置IP地址、子網掩碼&#xff0c;并且這些配置需要與宿主機器處于同一網段&#xff0c;以便虛擬系統和宿…
閱讀更多...
leetcode-42. 接雨水（雙指針，前綴）

leetcode-42. 接雨水（雙指針，前綴）

42. 接雨水 /*** param {number[]} height* return {number}*/ var trap function (height) {let len height.length;let pre_max new Array(len).fill(0);let suf_max new Array(len).fill(0);pre_max[0] height[0];suf_max[len - 1] height[len - 1];for (let i 1; i…
閱讀更多...
queue使用

queue使用

C的queue是一種先進先出&#xff08;FIFO&#xff09;的數據結構&#xff0c;可以用來存儲一系列元素。它屬于STL&#xff08;Standard Template Library&#xff09;的一部分&#xff0c;以queue模板類的形式提供。 要使用queue&#xff0c;需要包含頭文件&#xff0c;并使用…
閱讀更多...
Linux shell編程學習筆記49：strings命令

Linux shell編程學習筆記49：strings命令

0 前言 在使用Linux的過程中&#xff0c;有時我們需要在obj文件或二進制文件中查找可打印的字符串&#xff0c;那么可以strings命令。 1. strings命令 的功能、格式和選項說明 我們可以使用命令 strings --help 來查看strings命令的幫助信息。 pupleEndurer bash ~ $ strin…
閱讀更多...
在k8s中搭建elasticsearch高可用集群，并對數據進行持久化存儲

在k8s中搭建elasticsearch高可用集群，并對數據進行持久化存儲

&#x1f407;明明跟你說過&#xff1a;個人主頁 &#x1f3c5;個人專欄&#xff1a;《洞察之眼&#xff1a;ELK監控與可視化》&#x1f3c5; &#x1f516;行路有良友&#xff0c;便是天堂&#x1f516; 目錄 一、引言 1、Elasticsearch簡介 2、k8s簡介 二、環境準備 …
閱讀更多...
最新文章

Copyright @ 2022~2023