hive中不能干什么

转载

mob64ca14082604 2024-11-05 16:48:54

文章标签 hive中不能干什么 hive 分类 hadoop 窗口函数 文章分类 Hive 大数据

Hive中窗口函数的定义分类适用场景示例难点权威详解

文章目录

Hive中窗口函数的定义分类适用场景示例难点权威详解

定义
分类和适用场景
为什么使用partition by
PARTITION BY和GROUP BY区别
官网示例详解

1. PARTITION BY with one partitioning column, no ORDER BY or window specification
2. PARTITION BY with two partitioning columns, no ORDER BY or window specification
3. PARTITION BY with one partitioning column, one ORDER BY column, and no window specification
4. PARTITION BY with two partitioning columns, two ORDER BY columns, and no window specification
5. PARTITION BY with partitioning, ORDER BY, and window specification
6. Multiple OVER clauses in a single query
7. Aliases with or without the keyword AS
8. WINDOW clause
9. LEAD and LAG functions
10. Distinct counting for each partition

官方链接：

定义

在Hive中，窗口函数（Window Functions）用于对查询结果集中的每一行应用计算，并可以访问和处理其他行的值。窗口函数的定义包括以下几个关键部分：

窗口函数的语法：

在SELECT语句中使用窗口函数时，通常按照以下语法格式进行定义：

<窗口函数>(<表达式>) OVER (PARTITION BY <分区列> ORDER BY <排序列> [窗口规范])

窗口函数的名称：

窗口函数可以是聚合函数（如SUM、COUNT、AVG等），也可以是特定的窗口函数（如LEAD、LAG等）。
选择合适的窗口函数根据需要执行相应的计算操作。

表达式：

表达式是窗口函数所作用的列或表达式，它决定了窗口函数计算的数据来源。

PARTITION BY子句：

PARTITION BY子句用于将数据分成逻辑上独立的分区，每个分区都有自己的计算范围。
可以指定一个或多个列作为分区依据，在同一个分区内的行将被视为一个组。

ORDER BY子句：

ORDER BY子句用于指定分区内行的排序方式，它决定了窗口函数计算的顺序。
可以指定一个或多个列作为排序依据，在同一个分区内的行将按照指定的排序规则进行排列。

窗口规范（Window Specification）：

窗口规范定义了窗口函数计算的范围和条件。
可以通过使用ROWS BETWEEN子句来指定计算范围，例如当前行和前几行、当前行和后几行等。
窗口规范可以在窗口函数内部直接定义，也可以使用WINDOW子句中命名并在后续的窗口函数中引用。

总结：

Hive中窗口函数的定义包括窗口函数的名称、表达式、PARTITION BY子句、ORDER BY子句和窗口规范。
PARTITION BY用于分区数据，ORDER BY用于排序，窗口规范用于指定计算范围。
窗口函数的语法格式是 <窗口函数>(<表达式>) OVER (PARTITION BY <分区列> ORDER BY <排序列> [窗口规范])。

理解Hive中窗口函数的定义非常重要，因为它们提供了处理复杂分析任务的强大工具，并能够实现对查询结果集的灵活操作。

分类和适用场景

在Hive中，窗口函数可以分为以下几类：

1.排名函数（Ranking Functions）：包括RANK、DENSE_RANK、ROW_NUMBER等，用于计算结果集中每一行的排名或编号。

适用场景：

需要对结果集进行排名或编号。
根据某个列的值对结果集进行排序并生成排名。

示例：

SELECT RANK() OVER (ORDER BY score DESC) AS rank, name FROM table;

2.聚合函数（Aggregate Functions）：包括SUM、COUNT、AVG、MIN、MAX等，在窗口内计算某个列的聚合值。

适用场景：

需要在窗口内进行聚合计算。
例如计算每个分区内的总和、平均值等。

示例：

SELECT SUM(sales) OVER (PARTITION BY category) AS total_sales FROM table;

3.偏移函数（Offset Functions）：包括LEAD、LAG等，用于获取当前行前后指定偏移量的行的值。

适用场景：

需要获取当前行前后的特定行的值。
例如查找前几行或后几行的数据。

示例：

SELECT name, LEAD(score, 1) OVER (ORDER BY date ASC) AS next_score FROM table;

4.窗口聚合函数（Window Aggregate Functions）：包括FIRST_VALUE、LAST_VALUE等，用于获取结果集中每个分区的第一个值或最后一个值。

适用场景：

需要获取每个分区的第一个值或最后一个值。
例如查找每个分区的首尾记录。

示例：

SELECT FIRST_VALUE(name) OVER (PARTITION BY category ORDER BY date ASC) AS first_name FROM table;

为什么使用partition by

在Hive中，窗口函数和聚合函数（如SUM、COUNT、AVG等）是两种不同的操作，它们使用PARTITION BY和GROUP BY来实现不同的功能。

窗口函数（Window Functions）：

窗口函数用于对查询结果集中的每一行应用计算，并可以访问和处理其他行的值。
PARTITION BY子句在窗口函数中用于将数据划分为逻辑上独立的分区，每个分区都有自己的计算范围。
使用PARTITION BY可以对每个分区内的数据进行排名、排序、偏移和聚合等操作。

聚合函数（Aggregate Functions）：

聚合函数用于对查询结果集进行分组，并对每个组应用聚合计算。
GROUP BY子句在聚合函数中用于将数据按照指定的列值进行分组。
使用GROUP BY可以生成每个组的汇总信息，如总计、平均值等。

为什么窗口函数大量使用PARTITION BY而不是GROUP BY？

窗口函数的计算是在每一行上进行的，而不是在分组后的结果上。因此，使用PARTITION BY可以更灵活地定义每个分区的计算范围，无需对数据进行重新分组。
PARTITION BY允许对结果集的任意部分进行计算，而不仅限于分组级别。
使用PARTITION BY可以在计算过程中保留原始的行和值顺序，而GROUP BY会将数据重新分组并可能改变顺序。

总结：

窗口函数使用PARTITION BY子句对每个分区进行计算，提供了更灵活的结果集操作方式。
聚合函数使用GROUP BY子句对结果集进行分组，并在每个组上应用聚合计算。

在实际使用时，根据具体需求选择适当的操作方式：如果需要对整个分组进行聚合操作，则使用GROUP BY；如果需要对每个分区内的行进行计算操作，则使用窗口函数和PARTITION BY。

PARTITION BY和GROUP BY区别

在Hive SQL中，PARTITION BY和GROUP BY是两个不同的关键字，用于不同的目的。下面是它们之间的区别：

PARTITION BY：

PARTITION BY是用于对表进行分区（Partition）的关键字。
使用PARTITION BY可以将表的数据划分成更小、更易管理的部分。
分区是根据指定的列值将数据拆分为不同的存储单元，每个分区都有自己的目录和文件。
分区可以提高查询性能，因为只需要扫描和处理特定分区的数据，而不是整个表。
在创建表时，使用PARTITIONED BY子句指定要分区的列，并在加载数据时指定分区值。
示例：创建一个按照日期分区的表，每个日期对应一个分区。

GROUP BY：

GROUP BY是用于对查询结果进行分组（Group）的关键字。
使用GROUP BY可以将查询结果根据一个或多个列的值进行分组。
分组操作将具有相同值的行放在一起，并将它们视为一个组。
在分组的基础上，可以使用聚合函数（如SUM、COUNT、AVG等）计算每个组的聚合结果。
GROUP BY通常与SELECT语句中的聚合函数一起使用，用于生成摘要信息、统计报表以及数据分析等任务。
示例：根据部门对员工表进行分组，并计算每个部门的平均薪水。

总结：

PARTITION BY用于对表进行分区，将数据划分为更小、更易管理的部分。
GROUP BY用于对查询结果进行分组，并进行聚合计算。

在Hive SQL中，可以同时使用PARTITION BY和GROUP BY来更灵活地组织和查询数据。PARTITION BY用于物理存储和数据访问的优化，而GROUP BY用于逻辑上对数据进行分组和聚合。

官网示例详解

以下是对示例的详细说明：

1. PARTITION BY with one partitioning column, no ORDER BY or window specification

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

使用一个分区列进行分区，没有ORDER BY或窗口规范。
示例中通过使用PARTITION BY子句将数据按照列c进行分区，并计算每个分区内列b的计数。

2. PARTITION BY with two partitioning columns, no ORDER BY or window specification

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

使用两个分区列进行分区，没有ORDER BY或窗口规范。
示例中通过使用PARTITION BY子句将数据按照列c和d进行分区，并计算每个分区内列b的计数。

3. PARTITION BY with one partitioning column, one ORDER BY column, and no window specification

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

使用一个分区列和一个排序列进行分区，并且没有窗口规范。
示例中通过使用PARTITION BY子句将数据按照列c进行分区，并按照列d进行排序，在每个分区内计算列b的总和。

4. PARTITION BY with two partitioning columns, two ORDER BY columns, and no window specification

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

使用两个分区列和两个排序列进行分区，并且没有窗口规范。
示例中通过使用PARTITION BY子句将数据按照列c和d进行分区，并按照列e和f进行排序，在每个分区内计算列b的总和。

5. PARTITION BY with partitioning, ORDER BY, and window specification

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;

使用分区、排序和窗口规范对数据进行操作。
示例中通过使用PARTITION BY子句将数据按照列c进行分区，并按照列d进行排序。窗口规范指定了计算范围，如从无限制的前导行到当前行。
在每个分区内按照指定的排序规则计算列b的总和或平均值。

6. Multiple OVER clauses in a single query

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

单个查询中可以有多个OVER子句，但每个OVER子句仅适用于紧接在其后的函数调用。
示例中第一个OVER子句应用于COUNT(b)，第二个OVER子句应用于SUM(b)。

7. Aliases with or without the keyword AS

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

可以使用别名，并可选择是否使用AS关键字。
示例中为COUNT(b)和SUM(b)添加了别名。

8. WINDOW clause

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

使用WINDOW子句定义窗口规范。
示例中使用WINDOW子句将窗口规范命名为w，并在后续的OVER子句中引用该命名。
在每个分区内按照指定的排序规则计算列b的总和。

9. LEAD and LAG functions

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

使用LEAD和LAG函数获取前一行或后一行的值。
示例中LEAD函数默认获取1行后面的值，没有指定默认值；LAG函数指定了3行的偏移量，并设置默认值为0。

10. Distinct counting for each partition

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

对每个分区进行不同值的计数。
示例中使用COUNT(distinct a) OVER (PARTITION BY b)对每个分区内的列a进行计数。

以上示例展示了Hive QL窗口函数和分析函数的不同用法，以及如何根据需要进行分区、排序和计算。这些示例可以帮助你理解如何在Hive中使用窗口函数和分析函数来处理数据。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：Android怎么导入json动画

下一篇：kmeans_pytorch 中 SSE评估指标怎么求

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯