hive踩坑笔记 —— 开窗函数为聚合函数时，加order by 与不加 order by的区别

over()开窗函数前分排序函数和聚合函数两种

当为排序函数，如row_number(),rank()等时，over中的order by只起到窗口内排序作用。

当为聚合函数，如max，min，count等时，over中的order by不仅起到窗口内排序，还起到窗口内从当前行到之前所有行的聚合（多了一个范围）。

以 sum 举例，下面的结果中，左图为不加 order by的结果，右图为加上order by的结果

发现不加order by score的话，就是针对一整个分区进行sum求和。加上order by score后，就是根据当前行到之前所有行聚合

select *,sum(score) over (partition by clazz) as num from windowtest;
select *,sum(score) over (partition by clazz order by score) as num from windowtest;

也就是说上面的select *,sum(score) over (partition by clazz order by score) as num from windowtest;其实等价于下面这句

select *,sum(score) over (partition by clazz order by score range between unbounded preceding and current row) as num from windowtest;

测试后，发现结果一致，说明聚合函数加上order by，就等同于加上从当前行到之前所有行的窗口帧

再来看看排序函数，下面的排序函数结果中，左图为加上order by的结果，右图为不加order by的结果

select *,row_number() over (partition by clazz order by score) from windowtest;
select *,row_number() over (partition by clazz) from windowtest;