官方文档：LanguageManual WindowingAndAnalytics - Apache Hive - Apache Software Foundation
先看看
对于窗口函数，最重要的是理解窗口是怎么被选择的，窗口的选择范围是在over内定义的的，包含三部分：
总结下来就是，partition by确定池子有多大，order by和窗口子句确定怎么在这个池子里捞鱼
1. partition by，确定窗口范围，不写的话，就是全部行，全部行作为一个分区
2. order by，有2个功能，第1个是排序；第2个是没有窗口子句的时候，指定了 ORDER BY，默认使用分区内第一行到当前值，但是操作的列出现相同值，结果就会相同，但是单独指定窗口子句的话就不会出现相同值，见sum() sample3和sample4。如果指定了窗口子句，order by的窗口子句功能就失效了
2.1.有 partition by，则分区内排序
2.2.没有 partition by，则全部数据排序
3. 窗口子句，单独定义窗口；
如果不指定窗口子句，则默认采用以下的窗口定义：
3.1. 若不指定 ORDER BY，默认使用分区内所有行
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
3.2. 若指定了 ORDER BY，默认使用分区内第一行到当前值
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
窗口函数执行顺序
窗口函数在group by聚合之后，因此可以将数据先去重，再使用窗口函数
1. 排序
第一个括号内参数说明：无参数
| 窗口函数 | 返回类型 | 函数功能说明 |
|---|---|---|
| ROW_NUMBER() | BIGINT | 根据具体的分组和排序，为每行数据生成一个起始值等于1的唯一序列数 |
| RANK() | BIGINT | 对组中的数据进行排名，如果名次相同，则排名也相同，但是下一个名次的排名序号会出现不连续。 |
| DENSE_RANK() dense是稠密的意思，可以引申记忆 | BIGINT | dense_rank函数的功能与rank函数类似，dense_rank函数在生成序号时是连续的，而rank函数生成的序号有可能不连续。当出现名次相同时，则排名序号也相同。而下一个排名的序号与上一个排名序号是连续的。 |
| PERCENT_RANK() | DOUBLE | 计算给定行的百分比排名。可以用来计算超过了百分之多少的人;排名计算公式为：(当前行的rank值-1)/(分组内的总行数-1) |
| CUME_DIST() | DOUBLE | 计算某个窗口或分区中某个值的累积分布。假定升序排序，则使用以下公式确定累积分布：小于等于当前值x的行数 / 窗口或partition分区内的总行数。其中，x 等于 order by 子句中指定的列的当前行中的值 |
| NTILE() | INT | 已排序的行划分为大小尽可能相等的指定数量的排名的组，并返回给定行所在的组的排名。如果切片不均匀，默认增加第一个切片的分布，不支持ROWS BETWEEN |
percent_rank
适用于计算百分等级，超过多少学生
2.聚合
第一个括号内参数说明：1个参数
第一个参数--操作哪一列，必填
| 窗口函数 | 返回类型 | 函数功能说明 |
|---|---|---|
| AVG() | 参数类型为DECIMAL的返回类型为DECIMAL，其他为DOUBLE | AVG 窗口函数返回输入表达式值的平均值，忽略 NULL 值。 |
| COUNT() | BIGINT | COUNT 窗口函数计算输入行数。 COUNT(*) 计算目标表中的所有行，包括Null值；COUNT(expression) 计算特定列或表达式中具有非 NULL 值的行数。 |
| MAX() | 与传参类型一致 | MAX窗口函数返回表达式在所有输入值中的最大值，忽略 NULL 值。 |
| MIN() | 与传参类型一致 | MIN窗口函数返回表达式在所有输入值中的最小值，忽略 NULL 值。 |
| SUM() | 针对传参类型为DECIMAL的，返回类型一致；除此之外的浮点型为DOUBLE；传参类型为整数类型的，返回类型为BIGINT | SUM窗口函数返回所有输入值的表达式总和，忽略 NULL 值。 |
sum
3.取值
| 窗口函数 | 返回类型 | 函数功能说明 |
|---|---|---|
| LAG() | 与lead相反，用于统计窗口内往上第n行值。第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL. | |
| LEAD() | 用于统计窗口内往下第n行值。第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL. | |
| FIRST_VALUE | 取分组内排序后，截止到当前行，第一个值 | |
| LAST_VALUE | 取分组内排序后，截止到当前行，最后一个值 |
lag向上取，lead向下取
第一个括号内参数说明：3个参数
第一个参数--操作哪一列，必填
第二个参数--向前取几行，不写默认1行
第三个参数--取不到后默认值，不写则为null
first_value取第1个值
第一个括号内参数说明：2个参数
第一个参数--操作哪一列，必填
第二个参数--是否跳过null值，不写默认false，设置true则跳过null值
last_value取最后1个值
参数同first_value，取分区内最后一行的值，当前行是每一次计算的最后一行哦！所以和first_value不一样的地方是first_value求分区第一个，第一个是不变的。但是求last_value，最后一个值是变化的。
last_value默认的窗口是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
如果表示当前行永远是最后一个值，需改成RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。
4.关于order by字段值相同时
这个例子是看不同时间下直播间的在线人数，num=1代表直播间人数+1，反之。
sum(num)over(PARTITION BY live_id ORDER BY in_datetime)以前一直不理解为什么order by指定后，如果order by后的字段存在相同的值，其结果都是一样的有什么用啊。比如下面live_id为1的分区，order by的字段是in_datetime，前几个时间都是相同的，所以第一个累加值num_sofar是一个值，都是1。
ORDER BY字段具有相同值的时候：
- 状态显示：按照ORDER BY in_datetime排序，表示按照时间来捞鱼，那么显示的是在某个时间下的数值状态。某天具有多个值，将某天的最后状态值作为结果。无论取那一条出来，都可以准确获得当天最后的状态。
- 过程显示：如果按照ORDER BY in_datetime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，用窗口子句指定了捞鱼规则，是过程显示
反而加了窗口子句变得不合理了，15-19条记录，虽然最后结果都是1，但是第二个num_sofar明显不合理，直播间最多1个人，根本没有大于1个人的时候。
这个例子也比较特殊
5.案例
去重
背景：员工表里有重复的员工，去重
思路：使用row_number排序窗口函数，按照员工id分区，取排名=1的行
数仓拉链表
数仓增量抽取的数据和历史数据合并，使用排序窗口函数区分有无update数据
环比
这个简单使用lag窗口或者lead窗口函数都行
连续登录天数
这个比较有意思，如果你是想要求指定的连续登录天数，可以使用lag或者lead窗口函数，也可以使用row_number窗口函数。如果是不确定登录天数，想要分析用户登录行为数据，那么使用row_number窗口函数比较好，可以存一个临时表，不用反复计算。
row_number的思路就是将分区内排序，用时间减去排序，得到相同日期的就是连续登录
849
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
