前言

很早之前就想寫一篇關于窗口函數的文章，因精力有限所以一直擱置了（一臉認真的自我檢討），在這篇文章的準備階段，我也拜讀了一些相關文章，總體來說基本上所涉及的窗口函數相關知識均有涉及，但一萬個讀者有一萬個哈姆雷特，每個人的文章角度不同，本文主要是面向基礎，希望能夠用通俗易懂的話語同時結合實際高頻面試題為大家打開一扇“窗”。

正文

一、窗口函數簡介

窗口函數也稱為OLAP函數。OLAP是online analytical processing的簡稱，意思是對數據庫數據進行實時分析處理?！俣劝倏?/p>

窗口函數的基本語法如下：

<窗口函數> over (partition by <用于分組的列名> order by <用于排序的列名>)

窗口函數是針對定義的行集合執行聚集分析，不減少原表的行數同時具備分組與排序的功能；通俗一點講，窗口函數會按照指定的列名分組，在組內進行排序，同時保持原表行數不發生變化；這與group by 分組聚合后每組返回一個值（原表行數改變）有明顯的區別。

窗口函數關鍵字：

over() : over子句中的內容為窗口函數的作用域（范圍）

partition by ：分組

order by : 排序

二、常用窗口函數介紹

1、排序函數

• rank() 如果有并列名次的行，會占用下一名次的位置
• dense_rank() 如果有并列名次的行，不會占用下一名次的位置
• row_number() 不考慮并列名次的情況

select *, rank() over (order by 金額 asc) as ranking, dense_rank() over (order by 金額 asc) as dens_rank, row_number() over (order by 金額 asc) as row_num from table

排序函數區別示意表

常用場景：

排序函數常用于組內排序問題，如：topN問題 (找出每個部門排名前N的員工)

2、偏移函數

--基本語法lag(exp_str,offset,defval) over()Lead(exp_str,offset,defval) over()--exp_str要取的列--offset取偏移后的第幾行數據--defval:沒有符合條件的默認值

• lag() lag是用于統計窗口內往上(向前偏移)第n行值
• lead() lead是用于統計窗口內往下（向后偏移）第n行值

select *,lag(要偏移的列) over(partition by department order by 要排序的列) as next_lag from table --------------------------------------------------------------------------------------- select *,lead(要偏移的列) over(partition by department order by 要排序的列) as next_lead from table

偏移函數lag示意圖

偏移函數lead示意圖

常用場景：

多用于解決用戶連續登錄問題，如連續7天都登陸的用戶數

拓展：

偏移函數還有first_value()、last_value()，感興趣的同學可以自行學習，本文不作介紹。

3、聚合函數

• sum()
• avg()
• count()
• max()
• min()

select * ,sum(需要計算的列名) over (order by 需要計算的列名) as sum, avg(需要計算的列名) over (order by 需要計算的列名) as avg, count(需要計算的列名) over (order by 需要計算的列名) as cot, max(需要計算的列名) over (order by 需要計算的列名) as max, min(需要計算的列名) over (order by 需要計算的列名) as min from table

聚合函數在窗口函數中是針對自身記錄、以及自身記錄之上的所有數據進行計算。

聚合函數作為窗口函數，可以直觀的看到，截止到本行數據，統計數據是多少（最大值、最小值等）；同時可以看出每一行數據，對整體統計數據的影響。

三、代碼實操（經典問題）

本文代碼只展示基本框架，如遇同類問題，直接代入使用即可

1、排序

topN問題 (找出每個部門排名前N的員工)

select * from (select *, row_number() over (partition by 要分組的列名order by 要排序的列名 desc) as rankingfrom 表名) as a where ranking <= N

2、偏移

user_name 用戶名（連續7天都登陸的用戶數）

思路：

首先利用偏移窗口函數lead()求得每個用戶在每個登陸時間向后偏移7行的登陸時間;（需要注意：date 按倒序排序）

再計算每個用戶在每個登陸時間滯后7天的登陸時間;

如果每個用戶向后偏移7行的登陸時間等于滯后7天的時間，說明該用戶連續登陸了7天

select b.user_name from (select user_name, date,lead(date,7,-1) over(partition by user_name order by date desc) as date_7 from user_login_table) b where date_sub(cast(b.date as date,7)) = cast(b.date_7 as date)

四、總結

本文主要介紹了窗口函數的基本概念以及幾種高頻窗口函數的用法，在實際工作中窗口函數是經常用到的，可能乍一看不是很好理解，這是正?，F象，多練習肯定沒問題。

以上，如果覺得有點用，請記得點贊關注，謝謝！

總結

以上是生活随笔為你收集整理的plsql打开sql窗口快捷键_SQL干货|为你打开一扇窗—窗口函数的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。