SQL数据分析极简入门_第二章 窗口分析函数

SQL数据分析极简入门

---

前言

参与CDA_SQL数据分析极简入门，记录学习进度，便于复习

---

一、窗口函数

窗口分析函数简介

窗口分析函数主要用来做数据统计分析，属于OLAP方式。

我们知道，OLAP联机分析处理和OLTP联机事务处理是两种常见的数据库处理方式，
通常情况下，分析师更喜爱OLAP(分析)，开发者更关注的是OLTP(事务)

窗口分析函数可以计算一定范围内、一定值域内、或者一段时间内的累积和以及移动平均值等，可以方便的实现复杂的数据统计分析需求。

• 窗口函数包括: lead、lag、first value、last value.
• 分析函数包括:rank、row_number、dense_rank、percent_rank、cume_dist、ntile.
• 可以结合聚集函数sum()、avg()、max(),min(),count()等使用。

---

一、窗口函数

lag, lead, first value, last value

①lag

学过Python的同学都知道，这个函数与pandas的 shift()十分相似

lag(col,n,default) 函数的作用是返回某列的值向下平移n行后的结果.
第一个参数为列名
第二个参数为当前行之前第n行(可选，默认为1)
第三个参数为缺失时默认值(当前行之前第n行为NULL没有时，返回该默认值，如不指定，则为NULL)。

# 创建表user_pv
create table user_pv
(
	uid text null,
	dt date null,
	pv int null
);

# 例如:对每个用户当天浏览次数与前一天的浏览次数进行比较
select 
	uid,
	dt,
	pv,
	lag(pv,1,0)over(partition by uid order by dt)as lag_1_pv
from user_pv
order by uid,dt

②lead

lead:函数的作用是返回某列的值向上平移n行后的结果。 第一个参数为列名 第二个参数为当前行后面第n行(可选，默认为1)第三个参数为缺失时默认值(当前行后面第n行为没有时，返回该默认值，如不指定，则为NULL)。例如:比较每个用户当天浏览次数和后一天的浏览次数。

#例如:比较每个用户当天浏览次数和后一天的浏览次数。
select 
	uid,
	dt,
	pv,
	lead(pv,1,0)over( partition by uid order by dt)as lead_1_pv
from user_pv
order by uid,dt;

③first value

#例如:比较每个用户当天浏览次数与第一天浏览次数。
select 
	uid,
	dt,
	pv,
	first_value(pv)over (partition by uid order by dt)first_value_pv
from user_pv
order by uid,dt;
#注:上面例子窗口为第一行到当前行(缺失窗口子句.有order by ,
# 默认为rows between unbounded preceding and current row)。 
# 所以 first value返回窗口的第一行，即第一天浏览次数。

④last value

# 例如:比较每个用户当天浏览次数与最后一天浏览次数进行比较。
select 
	uid,
	dt,
	pv,
	last_value(pv) over (partition by uid order by dt rows between current row and unbounded following) last_value _pv
from user_pv
order by uid,dt;

---

二、分析函数

rank、row_number、dense_rank、percent_rank、cume_dist、ntile

①row_number

按顺序排序，排序的值不会重复，总数不变;

select 
	uid,
	pV,
	row_number()over(partition by uid order by pv desc) as row_number_pv
from user_pv
order by uid, pv desc;

②rank

大小一样排序的值一样，但会占用排名的位置，总数不变;下面对用户每天浏览量进行一个排名。

select 
	uid,
	dt,
	pV,
	rank()over(partition by uid order by pv desc)as rank_pv
from user_pv
order by uid, pv desc;

③dense_rank

排序值相同时重复，排名并列，排名依次增加，排序相同时总数会减少;
例如，如果两行排名为3，则下一个排名为4，不同于RANK()函数返
回5。

#下面对用户每天浏览量进行一个排名:
select 
	uid,
	dt,
	pv,
	dense rank()over(partition by uid order by pv desc) dense_rank_pv
from user_pv
order by uid, pv desc;

#对比看下，row_number,rank, dense_rank的运行效果,
select 
	uid,
	dt,
	pV,
	row_number()over(partition by uid order by pv desc)as row_number_pv,
	rank()over(partition by uid order by pv desc)as rank_pv,
	dense_rank()over(partition by uid order by pv desc) dense_rank_pv
from user_pv
order by uid, pv desc;

总结来说，ROW NUMBER函数为每一行分配唯一的行号，而RANK函数和DENSE RANK函数在处理具有相同排序值的行时有所不同。

RANK函数会跳过下一个排名，而DENSE RANK函数会紧随其后。选择使用哪个函数取决于具体的需求和对重复值的处理方式。

④cume_dist

累积分布cume dist()函数，用于计算当前行在排序结果中的累积分布比例。
计算公式 = 前面的行数 / 窗口分区中的总行数

# 4、5的合并案例
select 
	uid,
	dt,
	pV,
	cume dist()over(partition by uid order by pv) cume_dist_pv
from user_pv
order by uid, pv;

⑤percent_rank

非常类似于cume_dist函数。同样用于计算当前行在排序结果中的累积分布比例
计算公式 = 前面的行数-1 / 窗口分区中的总行数-1

select 
	uid,
	dt,
	pV,
	percent rank()over(partition by uid order by pv) as percent_rank_uv
from user_pv
order by uid, pv;

# 对比看下，cume dist和 percent rank函数的运行效果
select 
	uid,
	dt,
	pv,
	cume_dist()over(partition by uid order by pv)cume_dist_pv,
	percent_rank()over(partition by uid order by pv) as percent_rank_uv
from user_pv
order by uid, pv;

⑥ntile

学过Python的同学都知道，组内分桶，不就是组内pd.cut()么

ntile()函数，将每个分区的行尽可能均匀地划分为指定数量的分组。
例如，ntile(4)表示划分为4个分组，分组取决于over子句中的order by子句。

select 
	uid,
	dt,
	pV,
	ntile(4)over(partition by uid order by pv)as nt_pv
from user_pv;

---

三、结合使用

暂无

---

总结

以上大部分内容转载自CDA，部分内容参考《SQL菜鸟教程》
有误可私，如侵联删！