CDA打卡活动-SQL数据分析极简入门-课时3

第2节 窗口分析函数

一、主要疑问：什么是窗口？

窗口分析函数主要用来做数据统计分析，属于OLAP方式（联机分析处理）

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

窗口函数包括：lead、lag、first_value、last_value

分析函数包括：rank、row_number、percent_rank、cume_dist、ntile

可以结合聚集函数sum()、avg()、max()、min()、count()等使用

二、窗口分析函数的介绍

1. lag()函数——比较每个用户当天浏览次数与前一天浏览次数

lag(col, n, default)函数的作用是返回某列的值向下平移n行后的结果。

根据这个例子可以更好地理解，明明是前几行，但为什么是“向下移”：

第一个参数为列名

第二个参数为指定当前行之前的第n行（可选项，默认为1）

第三个参数为第n行（为NULL）缺失时，使用的默认值（如不指定，则为NULL）

使用方法：lag(pv, 1, 0) over (partition by uid order by dt) as lag_1_pv

• PARTITION BY（可选）：将结果集划分为多个分区，并在每个分区内独立地计算LAG。
• ORDER BY：定义每个分区内的排序顺序，以便确定“前一行”。

2. lead()函数——比较每个用户当天浏览次数与后一天浏览次数

lead()函数的作用是返回某列的值向上平移n行的结果，参数与lag()函数相同

3. first_value()函数——比较每个用户当天浏览次数与第一天浏览次数

返回窗口的第一行

first_value(pv) over (partition by uid order by dt) as first_value_pv

4. last_value()函数——比较每个用户当天浏览次数与最后一天浏览次数

last_value(pv) over (partition by uid order by dt rows between current row and unbounded following) last_value_pv

上面的例子为当前行到最后一行（rows between current row and unbounded following），last_value返回的是窗口最后一行，即最新一天的浏览次数

三、分析函数介绍

1. row_number()

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

row_number() over (partition by uid order by pv desc) as row_number_pv

2. rank()

大小一样的值，对应的排序值也一样，但会占用排名的位置，总数不变。

rank() over (partition by uid order by pv desc) as rank_pv

3. dense_rank()

排序值相同时重复，排名并列，排名依次增加，排序相同时总数会减少。

dense_rank() over (partition by uid order by pv desc) as dense_rank_pv

例如，如果两行排名为3，则下一个排名为4，rank()函数的下一个排名为5。

row_number()函数为每一行分配唯一的行号，而rank()函数和dense_rank()函数在处理相同值的下一行行号时会有区别：rank()函数跳过下一个排名，而dense_rank()函数会紧随其后。选择使用哪个函数取决于具体的需求和对重复值的处理方式。

4. cume_dist()

累积分布函数，用于计算当前行在排序结果中的累积分布比例

计算公式=前面的行数/窗口分区中的总行数

cume_dist() over (partition by uid order by pv) as cume_dist_pv

5. percent_rank()

也用于计算当前行在排序结果中的累积分布比例

计算公式=(前面的行数-1)/(窗口分区中的总行数-1)

percent_rank() over (partition by uid order by pv) as percent_rank_pv

6. ntile()

该函数将每个分区的行尽可能均匀地划分为指定数量的分组

ntile(4)表示划分为4个分组，分组取决于over子句中的order by子句

ntile(4) over (partition by uid order by pv) as nt_pv