什麼是視窗函數？
視窗函數就是類似于group by聚合函數，但又不同于聚合函數。聚合函數是将組内多個資料聚合成一個值，而視窗函數除了可以将組内資料聚合成一個值，還可以保留原始的每條資料。
比如說求平均數就是avg() over(partition by ** order by **)
其中over()函數的作用是将聚合函數的結果顯示在每條單獨的記錄當中。
partition by()函數的作用與group by()相似，在over()中運用partition by是指定哪一列進行分組，然後聚合函數就會在分好的組裡面進行聚合運算。
order by()函數就是順序聚合了。
下面簡單講一下序列函數：
ntile函數：
這是個切片分組函數，如ntile(3) over(partition by classroom order by student_id)
就是在每個班級内進行分組，同時按照學号排序，将每個班級的同學分為三組。
row_number,rank,dense_rank函數：
row_number:順序排序，按照排列不管數值相等于否，都是1，2，3，4，5，6
dense_rank:連續排序，如果兩個數值相同就會給相同的等級，如1，2，2，3，4，5
rank:跳躍排序，雖然有兩個相同的數值依然會給相同等級，但是過了這村沒這店了，該等級就跳過去了，如1，2，2，4，5，6
lag()和lead()函數：
這就是提前和滞後函數了。
如lag(student,1) over(partition by classroom )
就是以班級這一列分組，将班級中同學的學号整體向後移動一位。
first_value(),last_value函數：
顧名思義，這就是第一個值和最後一個值
如first_value(cale_date) over(partition by shapname order by sale_date)
先按照商店名分組，再在組内按照銷售時間排序，最後用first_value求出每個店鋪的最早銷售日期。
下面舉一些栗子，幾乎都是要用到視窗函數的。
一：商品訂單資料
資料表:
訂單表orders,
字段有('order_id'訂單号,
'user_id‘使用者編号’,
'order_pay‘訂單金額’ ,
'order_time‘下單時間’,
'商品一級類目commodity_level_I',
'商品二級類目commodity_level_2')
1.求最近7天内每一個一級類目下成交總額排名前3的二級類目:
思路：先找出最近7天的一級二級類目，以及總交易額，再在此基礎上用row_number排序，再按照排序找到前三即可。注意縮進哦。
select commodity_level_1, commodity_level_2, total_payfrom (select commodity_level_1, commodity_level_2, total_pay, row_number() over(partition by commodity_level_1 order by a.total_pay desc) as rank from (select commodity_level_1, commodity_level_2, sum(order_pay) as total_pay from orders where datediff(now() , order_time) <= 7 group by commodity_level_1,commodity_level_2 ) a ) bwhere rank <= 3
2.提取8.1-8.10每一天消費金額排名在101-195的user_id
思路：先找出8.1-8.10的資料以及總消費金額，再用row_number排序，最後提取相應要求的排名即可，記住要轉化日期和縮進哦。
select user_id, order_date, total_payfrom (select user_id, total_pay, row_number() over(partition by order_date order by a.total_pay desc) as rank from (select user_id, sum(order_pay) as total_pay, convert(order_time,date) as order_date from orders where convert(order_time，date) between '20180801' and '20180810' group by convert(order_time,date),user_id ) a ) bwhere rank between '101' and '195'
二：活動營運資料分析
資料表
表1——訂單表orders,
字段有(user_id‘使用者編号’,
order_pay‘訂單金額’ ,
order_time‘下單時間’)
表2——活動報名表act_apply,
字段有(act_id‘活動編号’,
user_id‘報名使用者’,
act_time‘報名時間’)
1.活動營運資料分析-統計每個活動對應所有使用者在報名後産生的總訂單金額，總訂單數
思路：不難，自己想想就行
select user_id, count(*) as '總訂單數', sum(order_pay) as '總訂單金額'from orders left join act_applyon orders.user_id = act_apply.user_idwhere order_time >= act_timegroup by user_id
2.統計每個活動從開始後到當天(考試日)平均每天産生的訂單數，活動開始時間定義為最早有使用者報名的時間。(涉及到時間的資料類型均為：datetime)
思路：先找出每個活動的開始時間，再找出活動開始時間到現在的時間差，用總訂單數除以該時間差即可。
select act_id,count(*)/datediff(now(),first_act) as '平均訂單'from orders aleft join (select user_id, act_id, min(act_time) over(partition by act_id ) as first_act from act_apply group by act_id ) bon a.user_id = b.user_idwhere order_time>=act_timegroup by act_id,first_act
三：使用者行為路徑分析
表1——使用者行為表tracking_log，
字段有(user_id‘使用者編号’,
opr_id‘操作編号’,
log_time‘操作時間’)
1.統計每天符合以下條件的使用者數：A操作之後是B操作，AB操作必須相鄰
思路：先把日期lag一下，新列出一列，再比較即可
select log_date, count(*) as '使用者數'from (select distinct user_id, opr_id as cur_opr, convert(log_time,date) as log_date, lag(opr_id,1) over(partition by user_id,convert(log_time,date) order by log_time) as next_opr from tracking_log ) awhere a.cur_opr==A and a.next_opr==Bgroup by log_date
2.統計使用者行為序列為A-B-D的使用者數,其中:A-B之間可以有任何其他浏覽記錄(如C,E等),B-D之間除了C記錄可以有任何其他浏覽記錄(如A,E等)
思路：用like+%判斷,外加group_concat函數
select count(*) as resultfrom(select user_id, group_concat(opr_id order by log_time) as user_behavior_pathfrom tracking_loggroup by user_idhaving (user_behavior_path like '%A%B%D') and (user_behavior_path not like '%A%B%C%D')) a
四：使用者留存分析
表：使用者登陸表user_log，
字段有(user_id‘使用者編号’，
log_date‘登陸時間’)
1.求每天新增使用者數，以及他們第2天、30天的留存率
思路一:不要視窗函數，用case when
select l.first_date as '日期', count(distinct l.user_id) as '新增使用者數', round(count(distinct case when (datediff(log_date,first_date) == 1 then l.user_id else null end)/ count(distinct l.user_id)),2 ) as '次日留存率'， round(count(distinct case when (datediff(log_date,first_date) == 29 then l.user_id else null end)/ count(distinct l.user_id)),2 ) as '30日留存率'from user_log lleft join(select distinct user_id, min(log_date) as first_datefrom user_loggroup by user_id) ton t.user_id = l.user_idgroup by first_date
思路二：用視窗函數找出第一天，第二天，第30天的使用者登入，相除即可
即先找到每個使用者第一個登入的時間，用第一次登入時間聚合，統計distinct使用者，即為每天的新增使用者
計算第二天回訪的使用者量，計算目前時間與第一次登入的時間差即可
第30天使用者回訪也這麼做即可
select a.date as '日期',b.2_back/a.new as '第二天的回訪率',c.3_back/a.new as '第30天的回訪率'from(select convert(log_date,date) as 'date' , count(distinct user_id) as newfrom ( select user_id, min(log_date) over(partition by user_id) as first_date, log_date from user_log ) aagroup by aa.first_date) aleft joinselect convert(log_date) as date, count(user_id) as 2_backfrom ( select user_id, date_log, min(log_date) over(partition by user_id) as first_date from user_log ) bbwhere datediff(bb.date_log,bb.first_date) = 1group by bb.first_date) bon a.date=b.dateleft joinselect convert(log_date) as date, count(user_id) as 30_backfrom ( select user_id, date_log, min(log_date) over(partition by user_id) as first_date from user_log ) ccwhere datediff(cc.date_log,cc.first_date) = 29group by bb.first_date) con a.date=c.date
2.找近90天,30天,7天的登入人數
思路：看了上面的，應該有思路了吧，用case when判斷即可
select count(distinct case when datediff(now(),log_date()) <= 90 then user_id else null end) as 90_log_users, count(distinct case when datediff(now(),log_date()) <= 30 then user_id else null end) as 30_log_users, count(distinct case when datediff(now(),log_date()) <= 7 then user_id else null end) as 7_log_usersfrom user_log
3.求使用者近一個月平均登入時間間隔(按天)
思路：用視窗函數lead建立一列，再進行比較，就能知道平均登陸時間間隔，記住在每個user單次下進行的哦。
select user_id ,avg(diff)from ( select user_id, lead(log_time,1) over(partition by user_id order by log_time) - log_time as diff from user_log ) twhere datediff(now(),log_time)<=30group by user_id
五：統計(中/四分位數,衆數等)
字段：店鋪id(shop_id),
銷量(sale),
商品id(commodity_id).
1.求每個店鋪商品銷量排名的中位數
思路一：設每個店鋪銷售量組成的序列長度為cnt，
當cnt為偶數的時候，中位數所在的序号是cnt/2，cnt/2+1；
當cnt為奇數的時候，中位數所在的序号為ceiling(cnt/2)
select shop_id,avg(sale) as resultfrom(select sale, count(1) over(partition by shop_id) as total, cast(count(1) over(partition by shop_id) as decimal) / 2 as even_mid, ceiling(cast(count(1) over(partition by shop_id) as decimal) / 2) as odd_mid, row_number() over(partition by shop_id) as sale_rankfrom orders) twhere (total%2=0 and t.sale_rank in (even_mid,even_mid+1)) or (total%2=1 and t.sale_rank = odd_mid)group by shop_id
思路二：abs(rn-(cnt+1)/2)<1
解釋下上面的公式，rn就是長度為cnt的序列的排序，即為row_number，
如果是奇數，1，2，3，4，5，那他的中位數就是3，|3-(5+1)|=0；
如果是偶數，1，2，3，4，那他的中位數所在的序号就是2，3，
那麼2-(4+1)/2=-0.5，3-(4+1)/2=0.5
是以可見，不管他是技術還是偶數，abs(rn-(cnt+1)/2)<1
select shop_id,avg(sale) as medianfrom ( select shop_id, sale, row_number() over(partition by shop_id order by sale) as rn, count(1) over(partition by shop_id) as cnt from orders ) awhere abs(rn - (cnt + 1) / 2) < 1group by shop_id
思路三：不用視窗函數，不排序，直接利用中位數定義
有點繞，盡量了解下：
當一個數組cnt為奇數的時候，大于中位數的數值個數等于小于中位數的數值個數。
當一個數組cnt為偶數的時候，那麼中位數就等于排序後中間兩個數的平均值，如果這個數組每個數都是唯一的，那麼就會發現對于這兩個數來說，大于他們的數值的個數和小于他們數值的個數的絕對值等于1，即為這兩個數出現的頻率。如果這個數組的值不是唯一的，你會發現也成立。
總結一下，不管他們是長度是奇數還是偶數，中位數出現的頻率一定大于等于(大于它的數和小于它的數的絕對值之差)
步驟如下：
1.自連結
2.計算各個數字出現的頻率：sum(case when t1.sale=t2.sale then 1 else 0 end)
3.算出大于它的數和小于它的數的絕對值之差，用于比較：abs(sum(sign(t1.sale-t2.sale)))
select shop_id,avg(sale) as medianfrom ( select t1.shop_id, t1.sale, from orders t1 left join orders t2 on t1.shop_id=t2.shop_id group by t1.shop_id,t2.shop_id having sum(case when t1.sale=t2.sale then 1 else 0 end) >= abs(sum(sign(t1.sale-t2.sale))) ) tgroup by t.shop_id
2.求每個店鋪訂購商品的衆數
思路：用視窗函數，求出每個店鋪商品的數量，再找出其中的最大值，就是衆數了
select .shop_id,commodity_id,order_numfrom ( select shop_id, commodity_id, order_num, max(order_num) over(partition by shop_id) as max_order_num from ( select shop_id, commodity_id, count(*) as order_num from orders group by shop_id,commodity_id ) t1 ) t2where order_num = max_order_num
3.求四分位數
三個表
T1：good_id,cate_id(分類)
T2:mall_id(店鋪), good_id
T3:mall_id, credit_score(信用分)
問，在不同分類中,店鋪的信用分的top25%
思路：先将三個表連起來，再用row_number排序，再用count找出每個分類的個數，最後提出前25%即可。
select cate_id,mall_id,credit_scorefrom(select cate_id,mall_id,credit_score,row_number() over(partition by cate_id order by credit_score desc) as score_rn,count(*) over(partition by cate_id) as mall_totalfrom ( select t1.cate_id, t2.mall_id, t3.credit_score from t1 left join (t2 left join t3 on t2.mall_id=t3.mall_id) on t1.good_id=t2.good_id group by t1.cate_id,t2.mall_id ) t)where score_rn <= mall_total*0.25
六：GMV周同比統計
字段:時間(sale_date),
店鋪類别(cate_id),
店鋪數量(mall_num),gmv
1.拼多多618前後一周内各店鋪類别gmv的日均提升幅度和比例
注:以下解法隻适用于資料連續情況，如果資料不連續，例如對于上一周沒有星期六，星期日的資料，lead(gmv,7)這樣規定移動視窗必然會出現錯誤。
思路：用lead視窗函數就行
select cate_id, avg(gmv_diff), avg(gmv_rate)from ( select sale_date, cate_id, mall_num, gmv, (lead(gmv,7) over(partition by cate_id order by sale_date) - gmv) as gmv_diff , (lead(gmv,7) over(partition by cate_id order by sale_date) - gmv)/gmv as gmv_rate from T where convert(sale_date,date) between '20190611' and '20190624' ) twhere convert(sale_date,date) between '20190611' and '20190624'group by cate_id
2.在618前一周gmv top20%，20-40%等這5類商鋪在618後一周内gmv日均提升幅度和比例
思路：1.算出每個店鋪的sum(gmv)
2.用case when标出gmv_quantile
3.和上面一樣，用lead算出gmv_diff和gmv_rate，用上面的gmv_quantile判斷是否在範圍内
4.代碼太長我懶得寫了，hahah
七：連續區間問題
關于這題，我看到過一個絕佳的栗子，和大家分享下：
題目：有一張使用者簽到表【user_attendence】，标記每天使用者是否簽到(說明：該表包含所有使用者所有工作日的出勤記錄)
包含三個字段：日期【date】，
使用者id【user_id】，
使用者當天是否簽到【user_sign_in：0否1是】；
問題1：請計算截至目前每個使用者已經連續簽到的天數(輸出表僅包含當天簽到的所有使用者，計算其連續簽到天數)
輸出表【t_user_consecutive_days】:使用者id【fuser_id】，使用者聯系簽到天數【fconsecutive_days】
思路：先找使用者最近一次未簽到日期，再用今天減那個日期
create table t_user_consecutive_days asselect user_id, datediff(now(), max_sign_date_0) as fconsecutive_daysfrom ( select user_id, max(date) as max_sign_date_0 from user_attendence where user_sign_in = 0 group by user_id ) tgroup by user_id
問題2：請計算每個使用者曆史以來最大的連續簽到天數(輸出表為使用者簽到表中所有出現過的使用者，計算其曆史最大連續簽到天數)
輸出表【user_max_days】:使用者id【user_id】，使用者最大連續簽到天數【max_days】
思路：把所有使用者的簽到轉化成一個0-1序列，找出其中連續1的最長序列的len()
create table user_max_days asselect user_id, max(len(cut_record)) as max_daysfrom ( select user_id, record, cut_record, from ( select user_id, group_concat(user_sign_in) as record from user_attendence group by user_id ) t1 lateral view explode(split(record,'0')) t as cut_record ) t2where cut_record <> ' 'group by user_id
八：學生成績分析
表：Enrollments
字段：student_id,course_id,grade
1.查詢每位學生獲得的最高成績和它所對應的科目，若科目成績并列，取 course_id 最小的一門。查詢結果需按 student_id 增序進行排序。
思路一：用視窗函數dense_rank來排序grade，再用row_number來排序course_id
select a.student_id,a.course_id,a.gradefrom ( select student_id,course_id,grade, row_number() over(partition by student_id order by course_id) as course_rank from ( select student_id,course_id,grade, dense_rank() over(partition by student_id order by grade) as grade_rank from Enrollments ) t where t.grade_rank = 1 ) awhere a.course_rank = 1order by student_id
思路二：用in解法，更快捷簡單
select student_id, min(course_id) as course_idfrom Enrollmentswhere (student_id,grade) in (select student_id , max(grade) from Enrollments group by stucent_id)group by student_idorder by student_id
九：學生做題情況分析
表t:做題日期(time),學生id(stu_id),題目id(exer_id)
統計10.1-10.10每天做新題的人的數量，重點在每天
思路：新題的判斷是先找出每位同學在做每道題的第一次時間，即為新題。再distinct後看是否連續做了十天即可
select count(1) as result(select stu_id, count(distinct a.first_time) as cntfrom ( select stu_id, exer_id, time, min(time) over(partition by stu_id,exer_id) as first_time from T where convert(time,date) between '20191001' and '10191010' ) awhere a.time = T.timegroup by stu_idhaving cnt = 10) b
參考文獻：
https://zhuanlan.zhihu.com/p/116012207
https://zhuanlan.zhihu.com/p/117498021
![c修改datatable單元格的值_Salesforce LWC學習(十八) datatable展示 image[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)