窗口和分析功能

对 Hive QL 的增强

本节介绍了 Hive QL 对窗口和分析功能的增强。有关详细信息，请参见“ HQL 中的窗口规范”(附加到HIVE-4197)。 HIVE-896提供了更多信息，包括初始 Comments 中指向早期文档的链接。

所有的窗口和分析功能均按照 SQL 标准运行。

当前版本支持以下窗口和分析功能：

• Windowing functions

• LEAD

• 可以选择指定要导致的行数。如果未指定潜在顾客的行数，则潜在顾客为一行。

  • 当当前行的引线超出窗口末端时，返回 null。

• LAG

• 可以选择指定滞后的行数。如果未指定要滞后的行数，则滞后为一行。

  • 当当前行的滞后时间扩展到窗口开始之前时，返回 null。

• FIRST_VALUE

• 这最多需要两个参数。第一个参数是您想要第一个值的列，第二个(可选)参数必须是默认为false的布尔值。如果设置为 true，则跳过空值。

• LAST_VALUE

• 这最多需要两个参数。第一个参数是您想要最后一个值的列，第二个(可选)参数必须是默认为false的布尔值。如果设置为 true，则跳过空值。

• OVER 子句

• 超过标准聚合：

• COUNT

  • SUM

  • MIN

  • MAX

  • AVG

• 使用带有 PARTITION BY 语句的 OVER，该语句具有任何原始数据类型的一个或多个分区列。

• 带有 PARTITION BY 和 ORDER BY 的 OVER，具有一个或多个任何数据类型的分区和/或排序列。

• 带有窗口规范的 OVER。 Windows 可以在 WINDOW 子句中单独定义。窗口规范支持以下格式：

(ROWS | RANGE) BETWEEN (UNBOUNDED | [num]) PRECEDING AND ([num] PRECEDING | CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN CURRENT ROW AND (CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN [num] FOLLOWING AND (UNBOUNDED | [num]) FOLLOWING

当指定 ORDER BY 缺少 WINDOW 子句时，WINDOW 规范默认为RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

当缺少 ORDER BY 和 WINDOW 子句时，WINDOW 规范默认为ROW BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

• Analytics functions

• RANK

• ROW_NUMBER

• DENSE_RANK

• CUME_DIST

• PERCENT_RANK

• NTILE

• Hive 2.1.0 和更高版本中的不同支持(请参阅HIVE-9534)

聚合函数(包括 SUM，COUNT 和 AVG)支持 Distinct，这些聚合函数将对每个分区内的不同值进行聚合。当前的实现具有局限性，出于性能原因，分区子句中不支持 ORDER BY 或 window 规范。支持的语法如下。

COUNT(DISTINCT a) OVER (PARTITION BY c)

Hive 2.2.0 中支持 ORDER BY 和 window 规范(请参见HIVE-13453)。一个例子如下。

COUNT(DISTINCT a) OVER (PARTITION BY c ORDER BY d ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

• Hive 2.1.0 和更高版本中的 OVER 子句支持中的聚合函数(请参阅HIVE-13475)

添加了对 OVER 子句中引用聚合函数的支持。例如，当前我们可以在 OVER 子句中使用 SUM 聚合函数，如下所示。

SELECT rank() OVER (ORDER BY sum(b))
FROM T
GROUP BY a;

Examples

本节提供了有关如何在 SELECT 语句中使用 Hive QL 窗口和分析功能的示例。有关其他示例，请参见HIVE-896。

PARTITION BY，带有一个分区列，没有 ORDER BY 或窗口指定

SELECT a, COUNT(b) OVER (PARTITION BY c)
FROM T;

PARTITION BY，带有两个分区列，没有 ORDER BY 或窗口指定

SELECT a, COUNT(b) OVER (PARTITION BY c, d)
FROM T;

PARTITION BY，带有一个分区列，一个 ORDER BY 列，并且没有窗口指定

SELECT a, SUM(b) OVER (PARTITION BY c ORDER BY d)
FROM T;

PARTITION BY，具有两个分区列，两个 ORDER BY 列，并且没有窗口指定

SELECT a, SUM(b) OVER (PARTITION BY c, d ORDER BY e, f)
FROM T;

PARTITION BY，具有分区，ORDER BY 和窗口指定

SELECT a, SUM(b) OVER (PARTITION BY c ORDER BY d ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM T;

SELECT a, AVG(b) OVER (PARTITION BY c ORDER BY d ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
FROM T;

SELECT a, AVG(b) OVER (PARTITION BY c ORDER BY d ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING)
FROM T;

SELECT a, AVG(b) OVER (PARTITION BY c ORDER BY d ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM T;

单个查询中可以有多个OVER子句。单个OVER子句仅适用于前一个函数调用。在此示例中，第一个 OVER 子句适用于 COUNT(b)，第二个 OVER 子句适用于 SUM(b)：

SELECT
 a,
 COUNT(b) OVER (PARTITION BY c),
 SUM(b) OVER (PARTITION BY c)
FROM T;

无论是否使用关键字 AS，都可以使用别名：

SELECT
 a,
 COUNT(b) OVER (PARTITION BY c) AS b_count,
 SUM(b) OVER (PARTITION BY c) b_sum
FROM T;

WINDOW clause

SELECT a, SUM(b) OVER w
FROM T
WINDOW w AS (PARTITION BY c ORDER BY d ROWS UNBOUNDED PRECEDING);

LEAD 使用默认的 1 行引线，但未指定默认值

SELECT a, LEAD(a) OVER (PARTITION BY b ORDER BY C)
FROM T;

LAG 指定 3 行的滞后，默认值为 0

SELECT a, LAG(a, 3, 0) OVER (PARTITION BY b ORDER BY C)
FROM T;

每个分区的唯一计数

SELECT a, COUNT(distinct a) OVER (PARTITION BY b)
FROM T;