Hive 窗口函数

什么是 Hive 窗口函数？

Hive 窗口函数是一种在数据集合内进行计算的函数，可以用于分析数据集合中的某个子集，并返回一个结果集合。窗口函数和聚合函数类似，但与聚合函数不同的是，窗口函数可以在查询结果中保留源数据的行，而不是将它们归纳为单个行。

在 Hive 中，窗口函数可以使用 OVER 子句来定义，可以按照指定的排序规则（ORDER BY）和分区规则（PARTITION BY）来对数据进行分组，然后在每个分组内进行计算。

Hive 窗口函数的应用场景

Hive 窗口函数可以用于许多场景，例如：

• 排名和分组排名：可以使用窗口函数来计算数据集合中每个元素的排名或分组排名。
• 滑动平均和移动总和：可以使用窗口函数来计算数据集合中每个元素的滑动平均或移动总和。
• 分析函数：可以使用窗口函数来在数据集合内进行分析，例如计算累计总和、计算百分比等。

Hive 窗口函数的常见函数

下面介绍 Hive 窗口函数的一些常见函数。

ROW_NUMBER()

ROW_NUMBER() 函数返回按照指定排序规则排序后，每个行的排名。例如：

SELECT ROW_NUMBER() OVER (ORDER BY salary DESC) as rank, name, salary FROM employees;

上面的查询将根据 salary 字段的值对 employees 表中的行进行排序，并将每个行的排名作为 rank 列返回。

RANK() 和 DENSE_RANK()

RANK() 和 DENSE_RANK() 函数也是用于计算排名的函数，不同的是，RANK() 和 DENSE_RANK() 函数可以处理并列排名的情况。RANK() 函数返回并列排名的最高值，DENSE_RANK() 函数返回并列排名的最低值。例如：

SELECT RANK() OVER (ORDER BY salary DESC) as rank, name, salary FROM employees;
SELECT DENSE_RANK() OVER (ORDER BY salary DESC) as rank, name, salary FROM employees;

SUM() 和 AVG()

SUM() 和 AVG() 函数可以用于计算滑动平均和移动总和。例如：

SELECT salary, AVG(salary) OVER (ORDER BY hire_date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) as moving_avg FROM employees;
SELECT salary, SUM(salary) OVER (ORDER BY hire_date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) as moving_sum FROM employees;

上面的查询将计算每个员工的工资和过去三个月的工资总和和平均值。

LAG() 和 LEAD()

LAG() 和 LEAD() 函数可以用于获取前一个或后一个行的值。例如：

SELECT name, salary, LAG(salary, 1) OVER (ORDER BY hire_date) as prev_salary FROM employees;
SELECT name, salary, LEAD(salary, 1) OVER (ORDER BY hire_date) as next_salary FROM employees;

上面的查询将返回每个员工的工资以及他们的前一个或后一个员工的工资。

Hive 窗口函数的代码实现

下面是一个使用 Hive 窗口函数的示例，假设我们有一个 sales表，其中包含每个销售员的销售额和销售日期。我们可以使用窗口函数来计算每个销售员的销售额和月度销售总额。

首先，我们可以使用以下命令创建一个名为 sales 的表：

CREATE TABLE sales (
    salesman string,
    sale_date date,
    sale_amount double
);

然后，我们可以向 sales 表中插入一些数据：

INSERT INTO sales VALUES
    ('Alice', '2022-01-01', 100),
    ('Bob', '2022-01-02', 200),
    ('Charlie', '2022-02-01', 150),
    ('Alice', '2022-02-05', 300),
    ('Bob', '2022-03-01', 250),
    ('Charlie', '2022-03-10', 200),
    ('Alice', '2022-04-01', 150),
    ('Bob', '2022-04-15', 350),
    ('Charlie', '2022-05-01', 250);

接下来，我们可以使用以下查询来计算每个销售员的销售额和月度销售总额：

SELECT salesman, sale_date, sale_amount,
       SUM(sale_amount) OVER (PARTITION BY salesman ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as running_total,
       SUM(sale_amount) OVER (PARTITION BY salesman, DATE_TRUNC('MONTH', sale_date) ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as monthly_total
FROM sales
ORDER BY salesman, sale_date;

上面的查询将返回每个销售员的每个销售日期的销售额，以及每个销售员的销售额累计总和和每月的销售额总和。

Hive 窗口函数的面试点

在面试中，可能会涉及到 Hive 窗口函数的相关问题，例如：

Hive 窗口函数是什么？

Hive 窗口函数是一种在数据集合内进行计算的函数，可以用于分析数据集合中的某个子集，并返回一个结果集合。窗口函数和聚合函数类似，但与聚合函数不同的是，窗口函数可以在查询结果中保留源数据的行，而不是将它们归纳为单个行。

Hive 窗口函数和聚合函数有什么区别？

Hive 窗口函数和聚合函数的区别在于它们对源数据的处理方式不同。聚合函数将源数据归纳为单个行，并返回一个单一的值，而窗口函数则保留源数据的行，并返回一个结果集合。

Hive 窗口函数的常见函数有哪些？它们的作用是什么？

Hive 窗口函数的常见函数包括 ROW_NUMBER()、RANK()、DENSE_RANK()、SUM()、AVG()、LAG() 和 LEAD() 等。它们的作用如下：

• ROW_NUMBER()：为结果集中的每一行分配一个唯一的数字，按照指定的排序顺序排序。
• RANK()：计算每一行在排序结果中的排名，如果有多个行具有相同的值，则它们将被分配相同的排名，而排名后面的位置将被跳过。
• DENSE_RANK()：与 RANK() 类似，但它不会跳过排名后面的位置，而是为每个排名分配一个唯一的数字。
• SUM()：计算指定列的总和。
• AVG()：计算指定列的平均值。
• LAG()：返回指定列在当前行之前的指定偏移量处的值。
• LEAD()：返回指定列在当前行之后的指定偏移量处的值。

Hive 窗口函数的语法是什么？

Hive 窗口函数的语法如下：

<窗口函数> OVER ([PARTITION BY <分区键>] [ORDER BY <排序键> [<排序方式>]] [ROWS <行窗口>] [RANGE <值窗口>])

其中，<窗口函数> 指定所要执行的窗口函数，可以是 Hive 内置的窗口函数，也可以是用户自定义的窗口函数。PARTITION BY 子句指定要分区的列，ORDER BY 子句指定要排序的列和排序方式，ROWS 子句指定行窗口的大小，RANGE 子句指定值窗口的大小。

Hive 窗口函数在什么情况下使用？

Hive 窗口函数通常用于需要在结果集中对某个子集进行聚合计算的场景，例如计算排名、累计求和等。使用窗口函数可以避免在查询中使用子查询或连接操作，从而提高查询效率。

结论

Hive 窗口函数是一种强大的工具，可以用于分析数据集合中的子集，并返回一个结果集合。它可以用于计算排名、滑动平均、移动总和等，是数据分析和数据处理的重要工具。无论是在工作中还是在面试中，理解和掌握 Hive 窗口函数都是非常有价值的。