窗口函数的5种方法总结 窗口函数也称为OLAP函数，是对一组值进行操作，但是在对一组值操作时，又不需要使用group by子句，去达成分组计算的逻辑实现。而且还可以达成一条数据被分到多个组里去重复计算。 当遇到累加、累计、到什么为止这种场景时，优先考虑用窗口分析OVER解决。
一、聚合：sum,avg,max,min 数据 累积计算 得到结果：
模板 分组之后的聚合的逻辑，就是在计算某条记录时，要把哪些数据放在一组做聚合计算。我们拿pv4来举例说明： SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv4 先确定哪些数据是一组，以及在一组中，数据的顺序是怎样的 PARTITION BY cookieid ORDER BY：按照cookieid 来分组，按照createtime 来排序。这里的分组可以理解为开窗，窗口大小默认是两个。这步完成后，数据是这样的：
2. 再确定窗口的长度 窗口长度指的是现在开始遍历到哪条记录了，针对这个窗口所计算的数据有哪些。 窗口长度的模板是：Rows between A and B。 A有三种写法：unbounded preceding 表示从当前组的第一条开始，一直到这组的第一条X preceding 从当前行数开始，不算当前这条，往前再数X条current row 当前行B有三种写法：unbounded following 到分组的最后一行结束 ，从当前行到分组的最后一行X following 当前行的后*行，往后取X行current row 当前行 拿pv4来看：ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
当计算到4-13的数据时，往前推3个计算到当前行3，求sum：1+5+7+3=16 3. 最后再看对这个窗口执行什么计算 最后执行的计算为聚合函数，可以对窗口中的求sum,avg,max或min。 二、排名：row_number,rank,dense_rank 当遇到把表中的每一条数据都要放入对应的组里面做一个排序的场景。row_number, rank, dense_rank就非常有用。 数据 Row number ROW_NUMBER() 的功能是：从1开始，按照顺序，生成分组内记录的序列。比如： 可以按照pv降序排列，生成分组内每天的pv名次；分组内排序第一的记录；一个session中的第一条refer 所以如果需要取每一组的前3名，只需要rn<=3即可。
rank 和 dense_rank RANK() 生成数据项在分组中的排名，当排名相等时，会在名次中留下空位，新值排序会增加。如：335DENSE_RANK() 生成数据项在分组中的排名，当排名相等时，会在名次中不会留下空位，并列排名，新值排序不会变。如：334 
三、切片：ntile,cume_dist,percent_rank 数据 ntile 语法 NTILE(n)，用于将分组数据按照顺序切分成n片，返回当前切片值,如果切片不均匀，默认增加第一个切片的分布。 NTILE不支持窗口语法，即ROWS BETWEEN，比如 NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) 例子 
ntile的场景适合去统计百分数的top组，比如：查询某一天中时长最高的前60% 用户的平均时长 场景：查询某一天中时长最高的前60% 用户的平均时长 利用ntile函数按照时长降序将其分为五组，则排名为第1，2，3组的则是前60%的用户时长。 cume_dist CUME_DIST 小于等于当前值的行数/分组内总行数。 例子 对当前数据统计小于等于当前薪水的人数，所占总人数的比例。 
rn1: 没有partition,所有数据均为1组，总行数为5 第一行：小于等于3000的行数为3，因此，3/5=0.6第三行：小于等于1000的行数为1，因此，1/5=0.2 rn2: 按照部门分组，dpet=d1的行数为3, 第二行：小于等于2000的行数为2，因此，2/3=0.67 对于重复值，计算的时候，取重复值的最后一行的位置。 场景：统计不同部门小于等于当前薪水的人数，所占不同部门人数的比例 cume_dist的实际场景可以统计某个值在总值中的分布，如： 统计不同部门小于等于当前薪水的人数，所占不同部门人数的比例 percent_rank percent_rank：和cume_dist 的不同点在于计算分布结果的方法，计算方法为（相对位置-1）/（总行数-1） 
rn1: rn1 = (rn11-1) / (rn12-1) 第一行,(1-1)/(5-1)=0/4=0第二行,(2-1)/(5-1)=1/4=0.25第四行,(4-1)/(5-1)=3/4=0.75 rn2: 按照dept分组，dept=d1的总行数为3 第一行，(1-1)/(3-1)=0第三行，(3-1)/(3-1)=1 四、分组：grouping sets, grouping_id, cube, rollup 这几个分析函数通常用于OLAP中，不能累加，而且需要根据不同维度上钻和下钻的指标统计，比如， 分小时、天、月的UV数。 数据 grouping sets 在一个GROUP BY查询中，根据不同的维度组合进行聚合，等价于将不同维度的GROUP BY结果集进行UNION ALL。 GROUPING__ID，表示结果属于哪一个分组集合。 cube 根据GROUP BY的维度的所有组合进行聚合 
rollup 是CUBE的子集，以最左侧的维度为主，从该维度进行层级聚合。 比如，以month维度进行层级聚合，SQL语句 
得到的结果为：月天的UV->月的UV->总UV with rollup 最后会出现的 null，这一行是针对每次分组前 ，需要显示的某列运用分组后的集合运算得出的。值。 五、取前值或后值：lag,lead,first_value,last_value 数据 lag和lead 语法 这俩函数可以在同一次查询中取出同一字段的前N行的数据(Lag)和后N行的数据(Lead)作为独立的列。 1. LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值 第一个参数为列名， 第二个参数为往上第n行（可选，默认为1）， 第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL） 2. LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值 第一个参数为列名， 第二个参数为往下第n行（可选，默认为1）， 第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL） 场景：计算每个用户在访问某个页面停留的时间，以及每个页面的总停留时间 实际场景：计算每个用户在访问某个页面停留的时间，以及每个页面的总停留时间。 思路： 用户在某个页面停留的起始与结束时间 
2. 计算用户在页面停留的时间间隔 
3. 计算每个用户在访问某个页面停留的时间，以及每个页面的总停留时间 
first_value和last_value 语法 first_value：取分组内排序后，截止到当前行，第一个值 last_value：取分组内排序后，截止到当前行，最后一个值 这两个可以通用：对每组取正序last_value就是对每组排倒序，然后取first_value。 场景：查下每个用户，最先访问的url 
2024最新激活全家桶教程，稳定运行到2099年，请移步至置顶文章：https://sigusoft.com/99576.html
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请联系我们举报，一经查实，本站将立刻删除。 文章由激活谷谷主-小谷整理,转载请注明出处：https://sigusoft.com/49200.html
