SQL学习笔记（二）窗口函数

窗口函数 

允许对查询结果的某个“窗口”进行计算。与聚合函数不同，窗口函数不会将多行结果压缩成一行，而是为每行返回一个值。它通常用于在不改变原始行数的情况下进行排名、累计和滑动平均等操作。

目录

窗口函数 

count_min_sketch(col, eps, confidence, seed)

分布类窗口函数

PERCENT_RANK

CUME_DIST（）

偏移类窗口函数

lag() over (order by ) as 

lead() over (order by ) as 

first_value()

窗口函数的语法

PARTITION BY

ORDER BY

ROWS BETWEEN（）AND（）

---

• count_min_sketch(col, eps, confidence, seed)

• 功能：Count-Min Sketch 是一种概率数据结构，用于估算列 col 中数据的频率。通过设置误差参数 eps、置信度 confidence 和种子值 seed，可以在内存占用较低的情况下提供数据的频率估算。
• 使用场景：适用于大数据环境下，估算元素频率而不需要存储所有元素
• 参数：col：计算结果为整型数值、STRING 或 BINARY 的表达式。eps：大于 0 的 DOUBLE 文本，描述相对错误。confidence：大于 0 且小于 1 的 DOUBLE 文本。seed：INTEGER 文本。

分布类窗口函数

• PERCENT_RANK

• 按照公式(rank-1) / (rows-1)进行计算。其中，rank为RANK()函数产生的序号，rows为当前窗口的记录总行数。

`customerID`;`tenure`;`Churn`;`rank`;percent_rank() over (order by`rank`) as PER

• CUME_DIST（）

• 用途：分组内小于、等于当前值的行数 / 分组内总行数
• 查询小于等于当前使用期（tenure）的比例

`customerID`;`tenure`;`Churn`;cume_dist() over (order by `tenure`) as cume_dist

偏移类窗口函数

• lag() over (order by ) as 

• 使用: 求出当前行之前的偏移行，传入参数：基准列名，偏移量，默认值，偏移量的默认值是1,default的默认值是null。偏移量和默认值可传可不传,不传按照默认值来处理。
• 参数: 参数input:要取的列 参数offset:取偏移后的第几行数据 参数default:没有符合条件的默认值
• 返回值: 返回前n行数据

`customerID`;`tenure`;`Churn`;lag(`customerID`) over (order by `tenure`) as LAG

• lead() over (order by ) as 

• 说明: 返回窗口中当前行之后的offsetth行的输入值。传入参数：基准列名，偏移量，默认值，偏移量的默认值是1,default的默认值是null。偏移量和默认值可传可不传,不传按照默认值来处理。
• 参数说明: 参数input:要取的列 参数offset:取偏移后的第几行数据 参数default:没有符合条件的默认值
• 返回值: 返回后n行数据

`customerID`;`tenure`;`Churn`;lead(`customerID`,2) over (order by `tenure`) as lead

• first_value()

• 说明: 返回一组数据的第一个值。如果第二个参数isIgnoreNull为真，则只返回非空值。第二个参数可不传
• 参数说明: 参数expr:可指定列名 参数isIgnoreNull：是否忽略null
• 返回值: 返回组内的第一个值

`customerID`;`tenure`;`Churn`;first_value(`customerID`) OVER (partition by `tenure` ORDER BY`tenure`) 
AS rn8

窗口函数的语法

• PARTITION BY

• 将数据划分为不同的分区，类似于 GROUP BY，但不会聚合数据。

• ORDER BY

• 定义窗口函数的排序方式。

• ROWS BETWEEN（）AND（）

• 窗口范围包含当前行和紧随其后的下一行。