mysql怎么显示结果窗口_mysql8中窗口函数
在以前的MySQL版本中是沒(méi)有窗口函數(shù)的，直到MySQL8.0才引入了窗口函數(shù)。窗口函數(shù)是對(duì)查詢中的每一條記錄執(zhí)行一個(gè)計(jì)算，并且這個(gè)計(jì)算結(jié)果是用與該條記錄相關(guān)的多條記錄得到的。
1.窗口函數(shù)與聚合函數(shù)
窗口函數(shù)與聚合函數(shù)很像，他們都是在一組記錄而不是整張表上執(zhí)行的。但是，一個(gè)聚合函數(shù)在一組記錄執(zhí)行后只返回一條結(jié)果而窗口函卻會(huì)對(duì)改分組內(nèi)的每行記錄都返回一個(gè)結(jié)果。
2.常見(jiàn)的窗口函數(shù)
MySQL8.0中定義的窗口函數(shù)主要有以下幾種：
函數(shù)名
參數(shù)
描述
cume_dist()
否
累計(jì)分布值。即分組值小于等于當(dāng)前值的行數(shù)與分組總行數(shù)的比值。取值范圍為(0,1]。
dense_rank()
否
不間斷的組內(nèi)排序。使用這個(gè)函數(shù)時(shí)，可以出現(xiàn)1,1,2,2這種形式的分組。
first_value()
是；first_value(expr)
返回分組內(nèi)截止當(dāng)前行的第一個(gè)值。
lag()
是;lag(expr,[N,[default]])
從當(dāng)前行開(kāi)始往前取第N行，如果N缺失默認(rèn)為1。若沒(méi)有沒(méi)有，則默認(rèn)返回default。default默認(rèn)值為NULL
last_value()
是;last_value(expr)
返回分組內(nèi)截止當(dāng)前行的最后一個(gè)值。
lead()
是;lead(expr,[N,[default]])
從當(dāng)前行開(kāi)始往后取第N行。函數(shù)功能與lag()相反，其余與lag()相同。
nth_value()
是;nth_value(expr,N)
返回分組內(nèi)截止當(dāng)前行的第N行。first_value\last_value\nth_value函數(shù)功能相似，只是返回分組內(nèi)截止當(dāng)前行的不同行號(hào)的數(shù)據(jù)。
ntile()
是;ntile(N)
返回當(dāng)前行在分組內(nèi)的分桶號(hào)。在計(jì)算時(shí)要先將改分組內(nèi)的所有數(shù)據(jù)劃分成N個(gè)桶，之后返回每個(gè)記錄所在的分桶號(hào)。返回范圍從1到N
percent_rank()
否
累計(jì)百分比。該函數(shù)的計(jì)算結(jié)果為：小于該條記錄值的所有記錄的行數(shù)/該分組的總行數(shù)-1. 所以改記錄的返回值為[0,1]
rank()
否
間斷的組內(nèi)排序。其排序結(jié)果可能出現(xiàn)如下結(jié)果：1,1,3,4,4,6
row_number()
否
當(dāng)前行在其分組內(nèi)的序號(hào)。不管其排序結(jié)果中是否出現(xiàn)重復(fù)值，其排序結(jié)果都為：1,2,3,4,5
注：‘參數(shù)’列說(shuō)明該函數(shù)是否可以加參數(shù)。“否”說(shuō)明該函數(shù)的括號(hào)內(nèi)不可以加參數(shù)。expr即可以代表字段，也可以代表在字段上的計(jì)算，比如sum(col)等。以下相同。
3. over子句
over子句可以指定如何將記錄劃分分區(qū)以供窗口函數(shù)處理。如果over()為空，則是將整個(gè)查詢記錄作為一個(gè)分組。如果over子句不為空，則其可以指定查詢記錄劃分分組的方式以及記錄在分組內(nèi)部的排序方式。除此之外，over子句也可以和聚合函數(shù)一起用。如果聚合函數(shù)后出現(xiàn)over子句，那么這些聚合函數(shù)也就變成了窗口函數(shù)。如果沒(méi)有over子句，則他們?nèi)匀皇蔷酆虾瘮?shù)。可以使用over子句的聚合函數(shù)主要有以下幾種：
avg()、bit_and()、bit_or()、bit_xor()、count()、max()、min()、stddev_pop()、stddev()、std()、stddev_samp()、sum()、var_pop()、variance()、var_samp()
而對(duì)于前一部分中介紹的窗口函數(shù)來(lái)說(shuō)，over()子句是強(qiáng)制必須要有的。
over子句中常見(jiàn)的語(yǔ)法形式為：
over_clause:
{OVER (window_spec) | OVER?window_name}
其中：
window_spec:
[window_name] [partition_clause] [order_clause] [frame_clause]
window_name: 是指在查詢語(yǔ)句定義的window子句。如果遇到group by、having子句order by子句，那么window子句要放到having子句和order by子句中間。其語(yǔ)法如下：
WINDOW?window_name?AS (window_spec)
[,?window_name?AS (window_spec)] ...
而
window_spec:
[window_name] [partition_clause] [order_clause] [frame_clause]
從語(yǔ)法結(jié)構(gòu)可以看出來(lái)window子句其實(shí)只是把放在over()括號(hào)中的內(nèi)容單獨(dú)抽出來(lái)。
partition_clause:即parittion by expr子句。用來(lái)指定記錄分組方式。語(yǔ)法中的expr不僅可以是字段本身，也可以是計(jì)算表達(dá)式。比如，記錄中有個(gè)timestramp類(lèi)型的字段 ts，在MySQL中，partition by ts 和partition by hour(ts)都是有效的。
order_clause: 即?order by expr desc|asc,expr desc|asc。 用來(lái)指定分組內(nèi)的排序方式。
frame_clause: 用來(lái)指定當(dāng)前分組中的子集劃分方式。frame可以在依據(jù)當(dāng)前行的位置在每個(gè)分組內(nèi)移動(dòng)。使用frame來(lái)計(jì)算流水流水總和(從分區(qū)開(kāi)始到當(dāng)前行)及滾動(dòng)平均(rolling averages)。
其語(yǔ)法結(jié)構(gòu)如下：
frame_clause:
frame_units?frame_extent
frame_units:
{ROWS | RANGE}
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
}
其中：
frame_units用來(lái)指示當(dāng)前行和frame的關(guān)系
ROWS: 用來(lái)定義frame的開(kāi)始行和結(jié)束行(偏移量依據(jù)的是位置)；RANGE: 定義frame的區(qū)間。(偏移量的基準(zhǔn)為當(dāng)前行的值)
frame_entent用來(lái)指示frame的開(kāi)始行和結(jié)束行。一種是通過(guò)指定start和end(frame_start,frame_end。frame_end可以不指定，沒(méi)有明確給出的話當(dāng)前行默認(rèn)為結(jié)束行)，另一種使用between(frame_between)。frame_between的語(yǔ)法很簡(jiǎn)單。下面來(lái)看frame_start和frame_end。
current row:和rows一起用時(shí)，邊界就是當(dāng)前行。和range一起用時(shí)，邊界是當(dāng)前行的對(duì)等點(diǎn)(個(gè)人理解，這里所說(shuō)的對(duì)等點(diǎn)應(yīng)為與當(dāng)前行的值相等的所有記錄)。
unbounded precceding:使用它時(shí)，每個(gè)分區(qū)的第一行即為邊界。
unbounded following:使用它時(shí)，每個(gè)分區(qū)的第一行即為邊界。
expr preceding\expr following: 可以由expr個(gè)性化的設(shè)置向上(preceding)向下(following)的偏移量。
4.代碼示例
表結(jié)構(gòu)如下：
4.1 row_number\dense_rank\?rank
select order_date,sum(quantity) as quantity,
rank()over(ORDER BY sum(quantity) desc) as rank_result,
dense_rank()over(ORDER BY sum(quantity) desc) as dense_result,
row_number()over(ORDER BY sum(quantity) desc) as row_result
from spm_order
group by order_date
-- 限定一部分?jǐn)?shù)據(jù)，沒(méi)有實(shí)際意義，能展示出這三個(gè)函數(shù)的區(qū)別就可以了
having quantity>=98
order by quantity desc
運(yùn)行結(jié)果如下：
從上面結(jié)果看出：
rank()函數(shù)一旦遇到重復(fù)值，序號(hào)會(huì)斷。比如2個(gè)7之后下個(gè)出現(xiàn)的序號(hào)是9。
dense_rank()函數(shù)中即使有重復(fù)值，但是序號(hào)是連續(xù)的。2個(gè)7之后下個(gè)出現(xiàn)的序號(hào)是8。
row_number()不會(huì)出現(xiàn)相同的序號(hào)。
4.2 cume_dist\percent_rank
select order_date,num,
cume_dist()over(order by num asc) as cume_result,
percent_rank()over(order by num asc) as percent_result
from (select order_date,count(1) as num
from spm_order
group by order_date
having num>=27)a
order by num asc
代碼運(yùn)行結(jié)果如下
分析如下：
cume_dist()：首先總的記錄有10條。當(dāng)num=27時(shí)，num小于等于27的值共有5個(gè)，所以其cume_dist()值為0.5；當(dāng)num=28時(shí)，小于等于28的值共有7個(gè)，所以cume_dist()值為0.7； 以此類(lèi)推。
percent_rank().當(dāng)num=27時(shí)，num小于27的記錄數(shù)為0，所以percent_rank()為0；當(dāng)num=28時(shí)，num<28的記錄數(shù)共有5個(gè)，所以percent_rank()的值為5/9； 而當(dāng)num=29時(shí)，其cume_dist()=7/9；以此類(lèi)推，直到最大值36對(duì)應(yīng)的值為1.
這兩個(gè)函數(shù)的作用有點(diǎn)像計(jì)算中位數(shù)。
4.3 first_value\last_value\nth_value
select sales_name,year_date,num,
first_value(num)over(PARTITION by sales_name order by year_date asc) as first_result,
last_value(num)over(PARTITION by sales_name order by year_date asc) as last_result,
nth_value(num,2)over(PARTITION by sales_name order by year_date asc) as nth_result
from (select sales_name,year(order_date)as year_date,count(1) as num
from spm_order
where sales_name in ('楊健','楚杰','洪光')
group by year(order_date),sales_name
order by sales_name asc,year_date asc)a
代碼運(yùn)行結(jié)果如下(要注意，這三個(gè)函數(shù)計(jì)算結(jié)果都是截止當(dāng)前行)
4.4 ntile()
select sales_name,year_date,num,
ntile(8)over(order by num asc) as n_bin
from (select sales_name,year(order_date)as year_date,count(1) as num
from spm_order
where sales_name in ('楊健','楚杰','洪光')
group by year(order_date),sales_name
order by sales_name asc,year_date asc)a
代碼運(yùn)行結(jié)果如下：
從結(jié)果上進(jìn)行分析：
首先，分桶號(hào)從1到N，都會(huì)出現(xiàn)；
其次，關(guān)于每個(gè)桶應(yīng)該有多少條記錄。可以假設(shè)有N個(gè)桶，m個(gè)球(球數(shù)為總記錄數(shù))，標(biāo)號(hào)從1到N，依次往1號(hào)桶到N號(hào)桶里投球，每次只投1個(gè)球。循環(huán)往復(fù)，直到m個(gè)球全都投入到N個(gè)桶中。最后每個(gè)桶里有多少球，現(xiàn)在每個(gè)桶里就有多少條記錄。
4.5 lag\lead
select sales_name,year_date,num,
lag(num,2)over(PARTITION by sales_name order by year_date asc) as lag_result,
lead(num,2)over(PARTITION BY sales_name order by year_date asc) as lead_result
from (select sales_name,year(order_date)as year_date,count(1) as num
from spm_order
where sales_name in ('楊健','楚杰','洪光')
group by year(order_date),sales_name
order by sales_name asc,year_date asc)a
代碼運(yùn)行結(jié)果如下：
注意，lag()和lead()函數(shù)中出現(xiàn)的字段可以與over()子句中order by中出現(xiàn)的字段不一致。在代碼lag(num,2)中2代表的想要取數(shù)的那一行相比當(dāng)前行的偏移量(lead中也類(lèi)似)。
4.6 聚合函數(shù)
select sales_name,year_date,num,
sum(num)over(PARTITION by sales_name) as sum_order,
avg(num)over(PARTITION by sales_name) as mean_order
from (select sales_name,year(order_date)as year_date,count(1) as num
from spm_order
where sales_name in ('楊健','楚杰','洪光')
group by year(order_date),sales_name
order by sales_name asc,year_date asc)a
代碼運(yùn)行結(jié)果如下：
4.7 order by子句
select sales_name,year_date,num,
sum(num)over(partition by sales_name) as count_1,
count(num)over(partition by sales_name order by num) as count_2
from (select sales_name,year(order_date)as year_date,count(1) as num
from spm_order
where sales_name in ('楊健','楚杰','洪光')
group by year(order_date),sales_name
order by sales_name asc,year_date asc)a
代碼運(yùn)行結(jié)果如下：
當(dāng)frame_clause不存在的時(shí)候，默認(rèn)的frame與order by子句是否存在有關(guān)：
如果有order by子句，則默認(rèn)的frame是從當(dāng)前分區(qū)第一行到當(dāng)前行。即在此種情況下，默認(rèn)的frame為 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
如果沒(méi)有order by子句，則默認(rèn)的frame是指該分區(qū)。如果此時(shí)也沒(méi)有partition by子句，則相當(dāng)于全部數(shù)據(jù)。
4.8?window子句
select sales_name,year(order_date) as year_1,count(1) as num,
sum(count(1)) over w as sales_order,
sum(count(1)) over (w_1) as year_order,
rank()over(w order by count(1) desc) as rank_order
-- 三種寫(xiě)法都是符合語(yǔ)法規(guī)范的
from spm_order
where sales_name in ('楊健','楚杰','洪光')
group by sales_name,year(order_date)
window w as (PARTITION by sales_name),
w_1 as (PARTITION by year(order_date))
order by sales_order
代碼運(yùn)行結(jié)果如下：
4.9?rows和range
rows和range是不能單獨(dú)使用的，但是因?yàn)閷?shí)在不理解這兩個(gè)用法上的區(qū)別，所以就進(jìn)行了單獨(dú)的驗(yàn)證。
select sales_name,month_1,rn_1,num,
sum(num)over(order by month_1 rows between 2 preceding and 1 preceding) as month_row,
sum(num)over(order by month_1 range between 2 preceding and 1 preceding) as month_range,
sum(num)over(order by rn_1 range between 2 preceding and 1 preceding) as rn_range
from (SELECT sales_name,month(order_date) as month_1,count(1) as num,
-- 由于rank()over()返回的是unsigned,當(dāng)相減結(jié)果為負(fù)時(shí)(between子句會(huì)用到減法)會(huì)報(bào)錯(cuò)，所以這里轉(zhuǎn)成signed類(lèi)型
cast(rank()over(order by month(order_date)) as signed) as rn_1
from spm_order
where sales_name in ('洪光','范彩')
group by sales_name,month(order_date))a
order by month_1 asc
代碼運(yùn)行結(jié)果如下：
對(duì)以上代碼分析：
首先，在這里我新建了一個(gè)rn_1列。rn_1列和month_1的區(qū)別在于，month_1的數(shù)據(jù)是連續(xù)的，而rn_1列是有中斷的(兩個(gè)1之后出現(xiàn)的是3，我是故意要?jiǎng)?chuàng)建一個(gè)中斷的序列，來(lái)分析一下range的作用范圍)
先來(lái)看month_row的區(qū)別，month_row列的計(jì)算結(jié)果為當(dāng)前行在分區(qū)中按month_1升序排序之后排在其前面的兩行(between and限定的)的sum求和值。所以rows后面的between and限定的偏移量是基于他們?cè)诜謪^(qū)中的排列位置的。
再來(lái)看month_range，通過(guò)分析其實(shí)驗(yàn)結(jié)果可以發(fā)現(xiàn)，month_range列的計(jì)算為分區(qū)內(nèi)month_1=當(dāng)前行-1和month_1=當(dāng)前行-2(-1，-2是由between an子句決定的。preceding代表負(fù)，following代表正)所有列的sum求和值。再來(lái)看rn_range, rn_range列的計(jì)算結(jié)果為分區(qū)內(nèi)month_1=當(dāng)前行-2的所有里列的sum求和值。所以，rang后面的between and限定的偏移量依據(jù)的是當(dāng)前行的數(shù)值。
總結(jié)
以上是生活随笔為你收集整理的mysql怎么显示结果窗口_mysql8中窗口函数的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。
- 上一篇： log4jdbc mysql_[简单]l
- 下一篇： 中信和中信建投什么关系