hive窗口函数学习总结

1.窗口函数的定义

Hive中的窗口函数主要用于在数据集的子集（窗口）上执行计算。窗口函数可以解决一些复杂的数据分析问题，例如计算移动平均值、累计和、排名等。
例如，如果你想要计算每个用户的购买总额排名，或者计算每个月的销售额是前三个月的平均值的多少倍，这些问题都可以通过窗口函数来解决。
窗口函数的主要特点是可以在不改变原始数据行数的情况下，为每一行提供一个基于窗口的计算结果。这与聚合函数不同，聚合函数会将多行数据聚合成一行。

2. 窗口函数学习路径

• 理解窗口函数的基本概念：窗口函数是对数据集的子集（窗口）进行操作的函数。它们允许用户在数据集的子集上执行聚合操作，而不会减少行数。
• 学习窗口函数的语法：窗口函数的语法通常包括函数名称、OVER关键字、以及定义窗口的子句。例如，RANK() OVER (PARTITION BY column1 ORDER BY column2)。
• 学习各种窗口函数：有许多不同的窗口函数，包括RANK()、ROW_NUMBER()、LEAD()、LAG()、SUM()、AVG()等。每个函数都有其特定的用途，需要分别学习和理解。
• 通过实例进行学习：理论学习是基础，但通过实例进行学习是最有效的方法。可以找一些实际的问题，尝试使用窗口函数来解决。
• 深入理解窗口定义：窗口函数的强大之处在于其灵活的窗口定义，可以通过PARTITION BY、ORDER BY和ROWS/RANGE子句来定义。理解这些子句的含义和用法，对于掌握窗口函数至关重要。
• 多做练习：理论学习和实例学习之后，需要通过大量的练习来巩固和提高。可以从简单的问题开始，逐步提高难度。
• 阅读官方文档：官方文档通常是最权威、最全面的学习资源。在学习过程中遇到问题或者想要深入理解某个功能时，可以参考官方文档。

3.窗口函数的语法

<窗口函数> ( <表达式> ) OVER ( [PARTITION BY <列名1>, <列名2>, …] [ORDER BY <列名> [ASC | DESC]] [ROWS | RANGE <窗口范围>] )

各部分的含义如下：

<窗口函数>：窗口函数的名称，例如 SUM、AVG、ROW_NUMBER、RANK 等。
<表达式>：窗口函数的参数，通常是一个列名。
OVER：关键字，表示开始定义窗口。
PARTITION BY <列名1>, <列名2>, …：（可选）按照指定的一个或多个列将数据分区。每个分区都会单独进行窗口函数的计算。
ORDER BY <列名> [ASC | DESC]：（可选）在每个分区内，按照指定的列对数据进行排序。默认为升序（ASC），也可以指定降序（DESC）。
ROWS | RANGE <窗口范围>：（可选）定义窗口的范围。ROWS 表示按照物理行数定义窗口，RANGE 表示按照列的值定义窗口。窗口范围可以是 UNBOUNDED PRECEDING（从分区开始到当前行）、N PRECEDING（从当前行前N行到当前行）、BETWEEN N PRECEDING AND M FOLLOWING（从当前行前N行到当前行后M行）等。

一个示例：以下SQL查询返回每个部门的每个员工的工资，以及他们所在部门的平均工资：

SELECT depname, empno, salary,AVG(salary)OVER(PARTITIONBY depname)AS avg_salary 
FROM empsalary;

PARTITION BY 未指定如何分区？

• 如果在窗口函数中没有指定 PARTITION BY 子句，那么整个结果集将被视为一个单一的区进行计算。在这种情况下，窗口函数会在整个结果集上进行操作，而不是在各个分区上分别进行。这意味着，如果你使用的是排名函数（如 RANK() 或 ROW_NUMBER()），那么生成的排名将会是在整个结果集范围内的，而不是在各个分区内的。

表达式如何写？

• <表达式> 在窗口函数中的必要性取决于你使用的具体窗口函数。对于一些窗口函数，如 SUM()、AVG()、MAX()、MIN() 等，<表达式> 通常是必填的，因为这些函数需要对某个列或表达式进行计算。然而，对于一些其他的窗口函数，如 ROW_NUMBER()、RANK()、DENSE_RANK() 等，<表达式> 是不需要的，因为这些函数并不需要对某个特定的列进行计算，它们只是基于窗口中的行顺序生成一个值。

ORDER BY 如果不写默认是怎么排序

• 如果在窗口函数中没有指定 ORDER BY 子句，那么窗口函数将不会对数据进行排序，数据的顺序将由查询结果集的默认顺序决定。需要注意的是，如果你使用的窗口函数是 RANK()、ROW_NUMBER()、DENSE_RANK() 等需要依赖排序的函数，那么不指定 ORDER BY 子句可能会得到不符合预期的结果，因为这些函数的结果依赖于数据的顺序。如果你希望窗口函数的结果能够按照某种特定的顺序，那么最好显式地指定 ORDER BY 子句。

示例中SUM(rn_30) OVER (ORDER BY rn_30) 这个表达式中的order by的作用

SUM(rn_30) OVER (ORDER BY rn_30) 这个表达式中的 ORDER BY 是不能省略的。因为在使用窗口函数时，如果你想要计算的是累计和（也就是当前行及其之前的所有行的和），那么 ORDER BY 是必须的，因为它定义了数据的顺序。

如果省略 ORDER BY，那么 SUM(rn_30) OVER () 将会计算整个结果集的 rn_30 的总和，而不是每行及其之前的行的 rn_30 的总和。

rank函数的用法

RANK() 是一种窗口函数，用于为结果集中的每一行分配一个唯一的排名。排名的顺序由 ORDER BY 子句决定。

基本语法如下：

RANK() OVER ( [PARTITION BY <列名1>, <列名2>, …] ORDER BY <列名> [ASC | DESC] )
PARTITION BY <列名1>, <列名2>, …：（可选）按照指定的一个或多个列将数据分区。每个分区都会单独进行排名。
ORDER BY <列名> [ASC | DESC]：在每个分区内，按照指定的列对数据进行排序。默认为升序（ASC），也可以指定降序（DESC）。

例如，以下SQL查询返回每个部门的每个员工的工资，以及他们在部门内的工资排名：
SELECT depname, empno, salary, RANK() OVER (PARTITION BY depname ORDER BY salary DESC) AS rank
FROM empsalary;

需要注意的是，RANK() 函数在处理相同值时会跳过一些排名。例如，如果有两行具有相同的值，则它们都会被赋予相同的排名，但下一个排名将会被跳过。例如，如果两行都是排名第1，那么下一行的排名将会是第3。如果你不希望跳过任何排名，可以使用 DENSE_RANK() 函数。

ROW_NUMBER函数的用法

ROW_NUMBER() 是一种窗口函数，用于为结果集中的每一行分配一个唯一的序号。序号的顺序由 ORDER BY 子句决定。

基本语法如下：

ROW_NUMBER() OVER ( [PARTITION BY <列名1>, <列名2>, …] ORDER BY <列名>
[ASC | DESC] ) PARTITION BY <列名1>, <列名2>,
…：（可选）按照指定的一个或多个列将数据分区。每个分区都会单独进行序号的分配。

ORDER BY <列名> [ASC |
DESC]：在每个分区内，按照指定的列对数据进行排序。默认为升序（ASC），也可以指定降序（DESC）。

例如，以下SQL查询返回每个部门的每个员工的工资，以及他们在部门内的工资序号：

SELECT depname, empno, salary, ROW_NUMBER() OVER (PARTITION BY depname
ORDER BY salary DESC) AS row_number FROM empsalary;

需要注意的是，ROW_NUMBER() 函数会为每一行分配一个唯一的序号，即使两行的值完全相同。如果你希望对相同的值分配相同的序号，可以使用 RANK() 或 DENSE_RANK() 函数。

DENSE_RANK 和 RANK的区别

RANK() 和 DENSE_RANK() 都是 SQL 中的窗口函数，用于为结果集中的每一行分配一个排名。它们的主要区别在于如何处理相同值的情况。

RANK()：当遇到相同的值时，RANK() 会为这些值分配相同的排名。但是，它会跳过接下来的一些排名。例如，如果有两行都是排名第1，那么下一行的排名将会是第3。

DENSE_RANK()：与 RANK() 类似，DENSE_RANK() 也会为相同的值分配相同的排名。但是，它不会跳过任何排名。在上述的例子中，如果使用 DENSE_RANK()，那么下一行的排名将会是第2。

这是一个例子来说明它们的区别：

SELECTvalue, RANK()OVER(ORDERBYvalue)AS rank, DENSE_RANK()OVER(ORDERBYvalue)AS dense_rank
FROM(VALUES(1),(2),(2),(3),(4),(4),(4))AS t(value);
结果如下：

value    rank    dense_rank
111222222343454454454

可以看到，RANK() 在处理值为2的行时跳过了排名3，而 DENSE_RANK() 则没有跳过任何排名。