hive的窗口函数

窗口函数

窗口函数也称为OLAP。OLAP 是OnLine AnalyticalProcessing 的简称，意思是对数据库数据进行实时分析处理。

为了便于理解，称之为窗口函数。常规的SELECT语句都是对整张表进行查询，而窗口函数可以让我们有选择的去某一部分数据进行汇总、计算和排序。

窗口函数的通用形式：

<窗口函数> OVER ([PARTITION BY <列名>]
                     ORDER BY <排序用列名>)  

]中的内容可以省略。
窗口函数最关键的是搞明白关键字PARTITON BY和ORDER BY的作用。

PARTITON BY是用来分组，即选择要看哪个窗口， 能够设定窗口对象范围

ORDER BY 能够指定按照哪一列、何种顺序进行排序

SELECT
	product_name ,
	product_type ,
	sale_price ,
	RANK() OVER (PARTITION BY product_type
ORDER BY
	sale_price) AS ranking;

## 按照product_type进行选择部分数据，对sale_price进行排序

1.窗口函数种类

大致来说，窗口函数可以分为两类。

​ 一是 将SUM、MAX、MIN等聚合函数用在窗口函数中

​ 二是 RANK、DENSE_RANK等排序用的专用窗口函数

1.1排序窗口函数

• RANK函数（英式排序）

计算排序时，如果存在相同位次的记录,排名会并列

例）有 3 条记录排在第 1 位时：1 位、1 位、1 位、4 位……

• DENSE_RANK函数（中式排序）排名会并列。

• ROW_NUMBER函数

赋予唯一的连续位次。排名不会并列

例）有 3 条记录排在第 1 位时：1 位、2 位、3 位、4 位

SELECT
	product_name ,
	product_type ,
	sale_price ,
	RANK() OVER (PARTITION BY product_type
ORDER BY sale_price) AS ranking,
	DENSE_RANK () OVER (PARTITION BY product_type
ORDER BY sale_price) AS dense_ranking,
	ROW_NUMBER () OVER (PARTITION BY product_type
ORDER BY sale_price) AS row_numbering
  FROM hive_4_product_1 ;  

1.2聚合窗口函数

聚合函数在开窗函数中的使用方法和之前的专用窗口函数一样，只是出来的结果是一个累计的聚合函数值。

会计算当前行与前面所有行的记录进行聚合计算。

 SELECT  product_id
       ,product_name
       ,sale_price
       ,SUM(sale_price) OVER (ORDER BY product_id) AS current_sum
       ,AVG(sale_price) OVER (ORDER BY product_id) AS current_avg  
  FROM hive_4_product_1 ; 

指定更加详细的汇总范围。

<窗口函数> OVER (ORDER BY <排序用列名>
                 ROWS n PRECEDING )  
                 
<窗口函数> OVER (ORDER BY <排序用列名>
                 ROWS BETWEEN n PRECEDING AND n FOLLOWING)

PRECEDING（“之前”）， 将框架指定为 “截止到之前 n 行”，加上自身行

FOLLOWING（“之后”）， 将框架指定为 “截止到之后 n 行”，加上自身行

BETWEEN 1 PRECEDING AND 1 FOLLOWING，将框架指定为 “之前1行” + “之后1行” + “自身”

SELECT
	product_id ,
	product_name ,
	sale_price ,
	AVG(sale_price) OVER (
	ORDER BY product_id ROWS 2 PRECEDING) AS moving_avg ,
	AVG(sale_price) OVER (
	ORDER BY product_id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg
FROM
	hive_4_product_1

2.ROLLUP - 计算合计及小计

常规的GROUP BY 只能得到每个分类的小计，有时候还需要计算分类的合计，可以用 ROLLUP关键字。

SELECT
	product_type ,
	regist_date ,
	SUM(sale_price) AS sum_price
FROM
	hive_4_product_1
GROUP BY
	product_type,
	regist_date WITH ROLLUP

这里ROLLUP 对product_type, regist_date两列进行合计汇总。