Hive数仓操作（十五）

Hive 开窗函数

Hive窗口函数是一种特殊的函数，允许用户在查询中对一组行进行计算，而不仅仅是单独的行。窗口函数可以在 SQL 查询中进行聚合、排名、累积计算等。这使得窗口函数在数据分析和报告生成中非常有用。

窗口函数的基本组成部分

1. 函数类型：如 ROW_NUMBER(), RANK(), DENSE_RANK(), SUM(), AVG() 等。
2. OVER 子句：定义窗口的范围和分区，用于指定在哪些行上应用窗口函数。

窗口边界标识符

1. CURRENT ROW:- 表示窗口的当前行。通常用于窗口的结束范围。
2. n PRECEDING:- 表示当前行之前的n行。例如，ROWS BETWEEN 1 PRECEDING AND CURRENT ROW 表示从当前行向上看一行到当前行。
3. n FOLLOWING:- 表示当前行之后的n行。例如，ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING 表示从当前行到当前行的后两行。
4. UNBOUNDED:- 表示没有边界，通常用于定义窗口的起点或终点。
5. UNBOUNDED PRECEDING:- 表示从窗口的起点开始，不限行数。
6. UNBOUNDED FOLLOWING:- 表示到窗口的终点结束，不限行数。

窗口边界函数

1. LAG(col, n):- 这个函数用于获取当前行的前n行中的指定列的值。它可以用于比较当前行与前几行的数据。SELECT employee_id, salary, LAG(salary,1)OVER(ORDERBY employee_id)AS previous_salaryFROM employees;这个示例显示了每个员工的当前工资和前一个员工的工资。
2. LEAD(col, n):- 这个函数用于获取当前行的后n行中的指定列的值。与 LAG 类似，但它是向下查找。SELECT employee_id, salary, LEAD(salary,1)OVER(ORDERBY employee_id)AS next_salaryFROM employees;这个示例显示了每个员工的当前工资和下一个员工的工资。

示例数据集

假设我们有一个名为

business

的表，内容如下：
nameorderdatecostAlice2017-04-01100Bob2017-04-05150Alice2017-04-10200Charlie2017-05-01300Bob2017-05-10100Alice2017-05-15250Charlie2017-06-01400

SQL 查询运行结果

1. 查询在2017年4月份购买过的顾客及总人数

SELECT name,COUNT(*)OVER()FROM business
WHERE SUBSTRING(orderdate,1,7)='2017-04';

结果：
nameCOUNT(*)Alice3Bob3Alice3

2. 查询顾客的购买明细及月购买总额

顾客购买明细及购买总额：

SELECT name, orderdate, cost,SUM(cost)OVER()FROM business;

结果：
nameorderdatecostSUM(cost)Alice2017-04-011001300Bob2017-04-051501300Alice2017-04-102001300Charlie2017-05-013001300Bob2017-05-101001300Alice2017-05-152501300Charlie2017-06-014001300
明细及月购买总额：

SELECT name, orderdate, cost,SUM(cost)OVER(PARTITIONBYMONTH(orderdate))FROM business;

结果：
nameorderdatecostSUM(cost)Alice2017-04-01100300Bob2017-04-05150300Alice2017-04-10200300Charlie2017-05-01300700Bob2017-05-10100700Alice2017-05-15250700Charlie2017-06-01400400
顾客购买明细及顾客购买总额：

SELECT name, orderdate, cost,SUM(cost)OVER(PARTITIONBY name)FROM business;

结果：
nameorderdatecostSUM(cost)Alice2017-04-01100550Bob2017-04-05150250Alice2017-04-10200550Charlie2017-05-01300700Bob2017-05-10100250Alice2017-05-15250550Charlie2017-06-01400400
顾客购买明细及顾客月购买总额：

SELECT name, orderdate, cost,SUM(cost)OVER(PARTITIONBY name,MONTH(orderdate))FROM business;

结果：
nameorderdatecostSUM(cost)Alice2017-04-01100300Bob2017-04-05150150Alice2017-04-10200300Charlie2017-05-01300300Bob2017-05-10100100Alice2017-05-15250250Charlie2017-06-01400400

3. 按照日期进行累加

按照日期逐步累加购买总额

SELECT name, orderdate, cost,SUM(cost)OVER(PARTITIONBY name ORDERBY orderdate)FROM business;

方法2（边界从起点到当前行）：

SELECT name, orderdate, cost,SUM(cost)OVER(PARTITIONBY name ORDERBY orderdate ROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW)AS sample4
FROM business;

结果：
nameorderdatecostSUM(cost)Alice2017-04-01100100Alice2017-04-10200300Alice2017-05-15250550Bob2017-04-05150150Bob2017-05-10100250Charlie2017-05-01300300Charlie2017-06-01400700
当前行和前面一行的聚合：

SELECT name, orderdate, cost,SUM(cost)OVER(PARTITIONBY name ORDERBY orderdate ROWSBETWEEN1PRECEDINGANDCURRENTROW)AS sample5
FROM business;

结果：
nameorderdatecostsample5Alice2017-04-01100100Alice2017-04-10200300Alice2017-05-15250450Bob2017-04-05150150Bob2017-05-10100250Charlie2017-05-01300300Charlie2017-06-01400400
当前行和前后各一行的聚合：

SELECT name, orderdate, cost,SUM(cost)OVER(PARTITIONBY name ORDERBY orderdate ROWSBETWEEN1PRECEDINGAND1FOLLOWING)AS sample6
FROM business;

结果：
nameorderdatecostsample6Alice2017-04-01100300Alice2017-04-10200550Alice2017-05-15250250Bob2017-04-05150250Bob2017-05-10100100Charlie2017-05-01300700Charlie2017-06-01400400
当前行及后面所有行：

SELECT name, orderdate, cost,SUM(cost)OVER(PARTITIONBY name ORDERBY orderdate ROWSBETWEENCURRENTROWANDUNBOUNDEDFOLLOWING)AS sample7
FROM business;

结果：
nameorderdatecostsample7Alice2017-04-01100550Alice2017-04-10200250Alice2017-05-15250250Bob2017-04-05150100Bob2017-05-10100100Charlie2017-05-01300400Charlie2017-06-01400400

4. 查询顾客上次的购买时间

使用 LAG 函数：

SELECT name, orderdate, cost,
LAG(orderdate,1)OVER(PARTITIONBY name ORDERBY orderdate)AS last_purchase_date
FROM business;

结果：
nameorderdatecostlast_purchase_dateAlice2017-04-01100NULLAlice2017-04-102002017-04-01Alice2017-05-152502017-04-10Bob2017-04-05150NULLBob2017-05-101002017-04-05Charlie2017-05-01300NULLCharlie2017-06-014002017-05-01