柚子快報(bào)邀請碼778899分享：數(shù)據(jù)庫 運(yùn)維 SQL-窗口函數(shù)

http://yzkb.51969.com/

什么是窗口函數(shù)

可以像聚合函數(shù)一樣對一組數(shù)據(jù)進(jìn)行分析并返回結(jié)果，二者的不同之處在于，窗口函數(shù)不是將一組數(shù)據(jù)匯總成單個(gè)結(jié)果，而是為每一行數(shù)據(jù)都返回一個(gè)結(jié)果。

窗口函數(shù)組成部分

1.創(chuàng)建數(shù)據(jù)分區(qū)

窗口函數(shù)OVER子句中的PARTITION BY選項(xiàng)用于定義分區(qū)，其作用類似于查詢語句中的GROUP BY子句。如果我們指定了分區(qū)選項(xiàng)，窗口函數(shù)將會(huì)分別針對每個(gè)分區(qū)單獨(dú)進(jìn)行分析。

1.另外開一列，求出每個(gè)部門的平均年齡

select *,avg(age) over(partition by dept) as 平均年齡 from testfunc order by id;

解釋：另外添加一列，用于記錄以分組到每個(gè)部門的窗口中，以deft為窗口分區(qū)，計(jì)算出每個(gè)部門的平均年齡

2.每位學(xué)生的總成績

select s_id,sum(convert(score,double))as 總成績 from sc group by s_id;

3.#以總成績進(jìn)行排名：窗口函數(shù)

dense_rank()是的排序數(shù)字是連續(xù)的、不間斷。當(dāng)有相同的分?jǐn)?shù)時(shí)，它們的排名結(jié)果是并列的

select s_id,sum(convert(score,double))as 總成績,

dense_rank() over(order by sum(convert(score,double))desc)as 排名

from sc group by s_id;

解釋：指定總成績?yōu)榇翱诜謪^(qū)，并且總成績降序排序。再接著dense_rank()再一次排序

4.#每科目下的總成績進(jìn)行排名

select c_id,sum(convert(score,double))as 總成績,

dense_rank() over(partition by c_id order by sum(convert(score,double)) desc)as 排名

from sc group by c_id;

解釋：分組到c_id窗口，以總成績的降序排列，對c_id窗口分區(qū)進(jìn)行對每一行匹配，并且再一次排序

#以平均分降序排列成績信息：

select *,avg(convert(score,double)) over(partition by s_id)as 平均成績 from sc order by 平均成績 desc;

#按總成績進(jìn)行降序排列

-- 若按學(xué)生總成績進(jìn)行降序排序

select *,sum(convert(score,double)) over(partition by s_id) as 總成績 from sc order by 總成績 desc;

-- 若按科目的總成績進(jìn)行排序

select *,sum(convert(score,double)) over(partition by c_id) as 總成績 from sc order by 總成績 desc;

5.-- 求每個(gè)訪客每個(gè)月訪問次數(shù)，和累計(jì)訪問次數(shù)

select *from visitor;

select userId,month(visitDate)as 月,sum(visitCount)as 月訪問次數(shù) from visitor group by userId,月;

-- 月累計(jì)訪問次數(shù)，月累計(jì):sum（sum（visitCount））

select userId,month(visitDate)as 月,sum(visitCount)月訪問次數(shù),sum(sum(visitCount))over(partition by userId order by month(visitDate))as 該客戶月累計(jì)次數(shù)

from visitor group by use9999rId,月 order by userId;

6.-- 嘗試不使用窗口函數(shù)得到并列形式排名（1,2,2,4...)

select a.name ,a.subject ,max(a.score) 主成績 ,count(b.name)+1 行統(tǒng)計(jì)值【排名】

from score a left join score b on a.subject =b.subject and b.score >a.score

group by a.name, a.subject order by a.subject ,主成績 desc;

select * from books_goods;

7.-- 對同個(gè)類別【t_categor】的價(jià)格進(jìn)行降序排序，并給與排名值（但是row_number()不會(huì)跳過重復(fù)序號）

select row_number() over(partition by t_category order by t_price desc)as 排名,t_category,t_name,t_price,t_upper_time

from books_goods;

8.rank() 序號函數(shù)

能夠?qū)π蛱栠M(jìn)行并列排序，并且會(huì)跳過重復(fù)的序號，得到并列排名 --- 效果與 excel 中 rank.eq（）類似

select rank() over(partition by t_category order by t_price desc)as 排名,t_category,t_name,t_price,t_upper_time

from books_goods;

dense_rank() 函數(shù)

DENSE_RANK()函數(shù)對序號進(jìn)行并列排序，并且不會(huì)跳過重復(fù)的序號，比如序號為1、1、2。

select rank() over(partition by t_category order by t_price desc)as 排名,t_category,t_name,t_price,t_upper_time

from books_goods;

9.percent_rank() 分布函數(shù)

于計(jì)算分區(qū)或結(jié)果集中行的百分位數(shù)

percent_rank() 返回一個(gè)從0到1的數(shù)字

對于指定的行，?percent_rank()計(jì)算行的等級減1，除以評估的分區(qū)或查詢結(jié)果集中的行數(shù)減一

select percent_rank() over(partition by t_category order by t_price desc) as 排名百分位,

rank() over(partition by t_category order by t_price desc) as 排名,

t_category,t_name, t_price,t_upper_time

from books_goods；

即：當(dāng)前的排名-1/當(dāng)前的行量-1；

10.cume_dist() 分布函數(shù)

主用于查詢小于或等于某個(gè)值的比例

-- 比如統(tǒng)計(jì)大于等于當(dāng)前售價(jià)的產(chǎn)品數(shù)占總產(chǎn)品數(shù)的比例，其窗口函數(shù)中的排序?yàn)榻敌蚣纯?/p>

select cume_dist() over(order by t_price desc) as 占比,

t_category,t_name, t_price,t_upper_time

from books_goods;

- 比如統(tǒng)計(jì)小于等于當(dāng)前售價(jià)的產(chǎn)品數(shù)占總產(chǎn)品數(shù)的比例

select cume_dist() over(order by t_price asc) as 占比,

t_category,t_name, t_price,t_upper_time

from books_goods;

前后函數(shù)：lag(expr,n)/lead（expr,n)

11.現(xiàn)想查看統(tǒng)一組別中的價(jià)格差值

- 2、計(jì)算當(dāng)前價(jià)格與上一個(gè)價(jià)格之間的差值

select *,t_price-pre_price as 差值 from(

#1、得到當(dāng)前商品的前一個(gè)商品價(jià)格（價(jià)格先按低的排序）

select t_category_id t_category,t_name, t_price,

lag(t_price,1) over(partition by t_category order by t_price asc) as pre_price

from books_goods

) t

把over 后的窗口分組排序方式語句單獨(dú)提出來，設(shè)置別名：w 【名字可自取】，同時(shí)將其可應(yīng)用于多個(gè)窗口函數(shù)上

想要輸出分組后的前一個(gè)價(jià)格和后一個(gè)價(jià)格

select t_category_id t_category,t_name, t_price,

lag(t_price,1) over h as pre_price ,

lead(t_price,1) over h as last_price

from books_goods

window h as (partition by t_category order by t_price asc);

12.首尾函數(shù)FIRST_VALUE(expr)/LAST_VALUE(expr)

頭尾函數(shù)應(yīng)用于：返回第一個(gè)或最后一個(gè)expr的值；

應(yīng)用場景：截止到當(dāng)前，按照日期排序查詢當(dāng)前最大的月收入【LAST_VALUE】 或最小月收入值【FIRST_VALUE】是多少

比如：按價(jià)格排序，查詢每個(gè)類目中最低和最高的價(jià)格是多少，方便與后續(xù)計(jì)算當(dāng)前書籍的價(jià)格與最大價(jià)格 或最小價(jià)格的差值（但是沒有分組來返回值）

select t_category_id t_category,t_name, t_price,

first_value(t_price) over h as 最小價(jià)格 ,

last_value(t_price) over h as 最大價(jià)格

from books_goods

window h as (partition by t_category order by t_price asc );

但結(jié)果發(fā)現(xiàn)：last_value 的結(jié)果并沒有按照我們所想的以當(dāng)前分組的窗口表中的所有數(shù)據(jù)進(jìn)行判斷最大值的

原因：last_value默認(rèn)統(tǒng)計(jì)范圍是取當(dāng)前行數(shù)據(jù) 與 當(dāng)前行之前的數(shù)據(jù)做比較的

解決方案：over 中的排序 order by 條件后加上一個(gè)固定語句：rows between unbounded preceding and unbounded following ，也是前面無界 和 后面無界 之間的行比較

select t_category_id t_category,t_name, t_price,

first_value(t_price) over h as 最小價(jià)格 ,

last_value(t_price) over h as 最大價(jià)格

from books_goods

window h as (partition by t_category order by t_price asc rows between unbounded preceding and unbounded following);

13.請利用窗口函數(shù)找出每門學(xué)科的前三名【并列且連續(xù)的排名效果】

select t.* from(

select name,subject,score,dense_rank() over(partition by subject order by score desc) as 排名 from score

) t where t.排名 3;

柚子快報(bào)邀請碼778899分享：數(shù)據(jù)庫 運(yùn)維 SQL-窗口函數(shù)

http://yzkb.51969.com/

好文鏈接