Hive--sql中的窗口函数

今天对Hive–sql中的窗口函数及常用的一些内容做一个简单的总结。

1. 清楚sql的关键字顺序和执行顺序

关键字顺序

select … from … where … group by … having … order by … limit …

执行顺序

from where group by having select order by limit

group by 的底层是distinct

在使用group by时需要注意：select出来的必须列在groupby中，或者聚合函数中

2. 在Hive命令行中可以对函数功能进行查看

show  functions;     //查看自带的所有的内置函数

desc function upper;  //查看具体的某个函数的用法

desc function extended upper; //带有具体案例

3. 窗口函数

窗口函数大体可以分为以下两种

1.能够作为窗口函数的聚合函数（sum，avg，count，max，min）
2.rank( )，dense_rank( )，row_number( )等专用窗口函数。

row_number(): 没有并列，相同名次顺序排序

rank(): 有并列，相同名次空位(即类似于1 1 3)

dense_rank(): 有并列，相同名次不空位（即类似于1 1 2）

注意：窗口函数的别名位置是在over()子句之后。

<窗口函数> over ([partition by <列清单>]
                        order by <排序用列清单>)

窗口函数over()使得聚合函数count()可以在限定的窗口中进行聚合。窗口函数over()和group by 的最大区别，在于group by之后其余列也必须按照此分区进行计算，而over()函数使得单个特征可以进行分区。

窗口大小的设定

默认窗口大小是从起始行到当前行
partition by …order by…rows between unbounded preceding and current row
窗口大小为从起始行得到当前行。
partition by …order by… rows between 3 preceding and current row
窗口大小为从当前行到之前三行
partition by …order by… rows between 3 preceding and 1 following
窗口大小为当前行的前三行到之后的一行
partition by …order by… rows between 3 preceding and unbounded following
窗口大小为当前行的前三行到之后的所有行

3.1 rank( )，dens_rank( )，row_number()

select product_name, product_type, sale_price,
       rank () over (partition by product_type
	                        order by sale_price) as ranking
from Product;

partition by 能够设定排序的对象范围，类似于group by语句，这里就是以product_type划分排序范围。

order by能够指定哪一列，何种顺序进行排序。也可以通过asc，desc来指定升序降序。

窗口函数兼具分组和排序两种功能。通过partition by分组后的记录集合称为窗口。

然而partition by不是窗口函数所必须的，不限定时就是对所有数据进行排序。

select product_name, product_type, sale_price,
       rank () over (order by sale_price) as ranking
from Product;

1.rank函数：计算排序时，如果存在相同位次的记录，则会跳过之后的位次。
2.dense_rank函数：同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。
3.row_number函数：赋予唯一的连续位次。

select product_name, product_type, sale_price,
       rank () over (order by sale_price) as ranking,
	   dense_rank () over (order by sale_price) as dense_ranking,
	   row_number () over (order by sale_price) as row_num
from Product;

由于窗口函数无需参数，因此通常括号里都是空的。
窗口函数的适用范围：只能在select子句中使用。

3.2 sum( )，avg( )等

select product_id, product_name, sale_price,
       sum(sale_price) over (order by product_id) as current_sum
from Product;

以累计的方式进行计算。计算出商品编号小于自己的商品的销售单价的合计值。

4. 参考

https://www.bilibili.com/video/av50213838/?p=15
https://mp.weixin.qq.com/s/PsY1mjLXmqTDp_py75jDHQ
https://blog.csdn.net/qq_37296285/article/details/90940591
https://blog.csdn.net/qq_41805514/article/details/81772182