分析函数、窗口函数、开窗函数 整理一下 oracle、sqlserver、mysql三大数据库用法不一样。 ORACLE 开窗函数和分组函数 oracle开窗函数有很多，用的比较多的是over(…)，一般是和order、partition by、row_number()、rank()、dense_rank()几个函数一起使用 分析函数和聚合函数 非常相似，有一些函数是重名的。 聚合函数用group by分组，每个分组返回一个统计值； 分析函数用partition by分组，并且每组、每行都可以返回一个统计值 显而易见的区别是，从返回结果上看： 聚合后的记录数可能会减少，因为它对数据源进行了group by操作，所以对结果是有影响的，即返回的结果里去掉聚合的列后，跟聚合前的结果不一样 而分析后的记录数是不变的，因为它的作用仅仅在于分析，所以不会对结果有影响，即返回的结果里去掉分析的列后，跟分析前的结果是一样的 使用说明 分析函数要与开窗函数(over())一起使用，使用形式为：分析函数() over() 开窗函数(over())包含三个分析子句： 分组子句(partition by) 排序子句(order by) 窗口子句(rows) 子句可以根据需要全部省略或部分省略。 over() over(order by eage) over(partition by esex) over(partition by esex order by eage) 开窗函数里的”窗”，即”窗口”，表示分析函数分析时要处理的数据范围 窗口子句除了rows方式的窗口,还包括range方式和滑动窗口 分组子句(partition by) 可以省略 理解上和group by类似 省略分组子句，所以当前组的范围为整个表的数据行 排序子句(order by) 可以省略 和普通orderby用法一样。 开窗函数内的order by和sql语句中的order by的执行时机。 分析及开窗函数是在整个sql查询结束后再进行的, 即sql语句的order by也会影响分析函数的执行结果。 窗口子句(rows) 可以省略 窗口子句(rows)不能单独存在，必须有order by子句时才能出现 有order by时，有窗口子句(rows) 有order by时，无窗口子句(rows)，则默认的窗口是unbounded preceding and current row，即当前组的第一行到当前行 无order by时，无窗口子句(rows)，则默认的窗口是unbounded preceding and unbounded following，即整个组 窗口子句(rows)的相关关键字： preceding：表示在…之前 1 preceding：表示当前记录的前1条记录 2 preceding：表示当前记录的前2条记录 n preceding：表示当前记录的前n条记录 unbounded preceding：不受控制的，无限的, 若无分组，则表示所有记录的第1条记录 若有分组，则表示分组后，组内的第1条记录 following：表示在…之后 1 following：表示当前记录的后一条记录 2 following：表示当前记录的后两条记录 n following：表示当前记录的后n条记录 unbounded following：不受控制的，无限的, 若无分组，则表示所有记录的最后一条记录 若有分组，则表示分组后，组内的最后一条记录 常见分析函数 count()：统计分区中各组的行数，partition by 可选，order by 可选 sum()：统计分区中记录的总和，partition by 可选，order by 可选 avg()：统计分区中记录的平均值，partition by 可选，order by 可选 min()max()：统计分区中记录的最小大值，partition by 可选，order by 可选 以上4最基础，和常见的groupby中使用一样。 rank() over() ：跳跃排序，并列后跳跃，partition by 可选，order by 必选 dense_rank() ：连续排序，并列后连续，partition by 可选，order by 必选 row_number() over() ：排序，无重复值，无并列顺序号，partition by 可选，order by 必选 以上3个排名函数一般用来分组取topN等 lag() over() ：取出前n行数据，partition by 可选，order by 必选；lead() over() ：取出后n行数据，partition by 可选，order by 必选 lead三个参数：第一个为hive列，第二个为取往后第几行，第三个为取不到时默认值，lag与lead刚好相反，取前面的某行； 这两个函数通常用来计算页面停留时间，同比环比增长率等 first_value() over() ：取出分区中第一条记录的字段值，partition by 可选，order by 可选；last_value() over() ：取出分区中最后一条记录的字段值，partition by 可选，order by 可选 first ：从DENSE_RANK返回的集合中取出排在最前面的一个值的行；last ：从DENSE_RANK返回的集合中取出排在最后面的一个值的行 max(ccc) keep(dense_rank first order by bbb desc) over(partition by aaa) 按aaa分组，取bbb第一的记录的，ccc最大值。 ntile(n) over() ：partition by 可选，order by 必选 n表示将分区内记录平均分成n份，多出的按照顺序依次分给前面的组 参考资料 https://blog.csdn.net/ZZQHELLO2018/article/details/105563517 https://www.cnblogs.com/shiliye/p/12363300.html
2024最新激活全家桶教程，稳定运行到2099年，请移步至置顶文章：https://sigusoft.com/99576.html
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请联系我们举报，一经查实，本站将立刻删除。 文章由激活谷谷主-小谷整理,转载请注明出处：https://sigusoft.com/67297.html
