[hive] 窗口函数 ROW_NUMBER()

avatar
作者
筋斗云
阅读量:0

文章目录

ROW_NUMBER()

在 Hive SQL 中,ROW_NUMBER()是一个用于生成行号的窗口函数。

它可以为查询结果集中的每一行分配一个唯一的行号

以下是 ROW_NUMBER() 函数的基本语法:

ROW_NUMBER() OVER (PARTITION BY column1, column2, ... ORDER BY column3, column4, ...) 
  • PARTITION BY 子句可选,用于指定分区列,它将结果集划分为不同的分区。
    每个分区内的行都会有独立的行号计数,即行号从1开始重新计数。

  • ORDER BY 子句用于指定排序列,它决定了如何对分区内的行进行排序。
    行号将根据指定的排序顺序进行分配。

ROW_NUMBER() 函数可以与其他窗口函数(如 RANK()DENSE_RANK())一起使用,以便对查询结果进行更复杂的分析和排序操作。

下面是一个示例,演示如何在 Hive SQL 中使用 ROW_NUMBER() 函数:

SELECT column1, column2, ...,  	ROW_NUMBER() OVER (ORDER BY column3) as row_num FROM your_table_name; 

在这个示例中,column1、column2 等是查询结果集中的列名,your_table_name 是包含这些列的表名。

ROW_NUMBER() 函数通过 ORDER BY column3 按照 column3 列的值对结果集进行排序,

并为每一行分配一个行号,该行号将在结果集中的 row_num 列中显示。

请注意,ROW_NUMBER() 函数生成的行号是根据指定的排序顺序计算的,并且不会考虑分区之间的顺序。如果需要在分区内进行排序,请使用 ORDER BY 子句来定义排序列

文章目录

示例

当我们有一个名为 employees 的表,包含以下列:

  • employee_id
  • first_name
  • last_name

我们希望为每个员工生成一个行号,并按照 employee_id 进行升序排序。

可以使用 ROW_NUMBER() 函数来实现这个需求,示例代码如下:

SELECT employee_id, first_name, last_name,  	ROW_NUMBER() OVER (ORDER BY employee_id) AS row_num FROM employees; 

运行以上查询后,将会得到一个结果集,其中包含每个员工的 employee_id、first_name、last_name 以及对应的行号 row_num。

行号将根据 employee_id 的升序顺序分配。

示例输出可能如下所示:

+-------------+------------+-----------+---------+ | employee_id | first_name | last_name | row_num | +-------------+------------+-----------+---------+ | 1           | John       | Doe       | 1       | | 2           | Jane       | Smith     | 2       | | 3           | David      | Johnson   | 3       | | 4           | Sarah      | Williams  | 4       | +-------------+------------+-----------+---------+ 

在这个示例中,每个员工都被赋予了一个唯一的行号,行号按照 employee_id 的升序进行分配。

窗口函数

窗口函数(Window Function)是一种在查询结果的特定窗口或分区上进行计算的函数。

它可以对查询结果集中的每一行应用聚合排序或其他操作,并生成与该行相关的结果。

窗口函数通常与 OVER 子句一起使用,该子句定义了窗口的范围和排序方式。

通过指定 PARTITION BY 子句,可以将结果集划分为不同的分区,每个分区内的行将独立计算。

通过指定 ORDER BY 子句,可以对分区内的行进行排序,以确定窗口函数的计算顺序。

窗口函数可以用于执行各种分析任务,如计算排名、计算累计值、计算移动平均等。

常见的窗口函数包括 ROW_NUMBER()、RANK()、DENSE_RANK()、SUM()、AVG()、LEAD()、LAG() 等。

下面是一个示例,演示如何使用窗口函数在一个表中计算每个部门的销售总额,并按照销售总额降序排序:

SELECT department, SUM(sales)  	OVER (PARTITION BY department ORDER BY sales DESC) AS total_sales FROM sales_table; 

在这个示例中,sales_table 是包含销售数据的表,其中包含 department 列和 sales 列。通过使用 SUM() 窗口函数,我们在每个部门内按照销售额降序计算累计销售总额。

  • PARTITION BY department 子句将结果集划分为不同的部门,

  • ORDER BY sales DESC 子句根据销售额降序排序。

窗口函数的输出将作为查询结果的一部分返回,并与其他列一起显示。

它为每一行提供了一个与该行相关的计算结果,而不会对整个结果集进行聚合或过滤。这使得窗口函数成为一种强大的工具,可以在查询中进行灵活的分析和计算。

假设 sales_table 表包含以下数据:

+------------+-------+ | department | sales | +------------+-------+ | A          | 100   | | B          | 200   | | A          | 150   | | B          | 300   | | A          | 120   | | B          | 250   | +------------+-------+ 

使用上述示例查询,将会得到如下输出:

+------------+-------------+ | department | total_sales | +------------+-------------+ | A          | 370         | | A          | 270         | | A          | 100         | | B          | 750         | | B          | 550         | | B          | 200         | +------------+-------------+ 

在这个示例中,我们按照 department 列进行分区,并按照 sales 列降序排序。然后,对每个部门内的行进行累计求和,得到每个部门的销售总额。注意,结果集中的行顺序与原始数据的顺序可能不同,因为窗口函数会重新排序。

对于部门 A,第一行的销售总额是 370,它等于前三行的销售额之和(150 + 120 + 100)。第二行的销售总额是 270,它等于前两行的销售额之和(120 + 100)。第三行的销售总额是 100,它等于当前行的销售额。

对于部门 B,第一行的销售总额是 750,它等于前三行的销售额之和(300 + 250 + 200)。第二行的销售总额是 550,它等于前两行的销售额之和(250 + 200)。第三行的销售总额是 200,它等于当前行的销售额。

因此,查询的输出结果按照部门和销售总额进行显示。每个部门的销售总额按照降序排列。

广告一刻

为您即时展示最新活动产品广告消息,让您随时掌握产品活动新动态!