Hive基础知识03-row_number函数

发表于 更新于 分类于
Hive 中的 row_number 函数是一个非常有用的窗口函数，它会对查询结果进行编号，并按照指定的排序方式对这些编号进行排序。在本文中，我们将介绍 row_number 函数的语法、样例及常用应用场景。

Hive 中的 row_number 函数是一个非常有用的窗口函数，它会对查询结果进行编号，并按照指定的排序方式对这些编号进行排序。在本文中，我们将介绍 row_number 函数的语法、样例及常用应用场景。

一、row_number 的语法

row_number 函数的语法如下所示：

ROW_NUMBER() OVER ([PARTITION BY partition_expression, ... [n]]                  ORDER BY sort_expression [ASC|DESC], ... [m])

PARTITION BY 子句表示需要进行分区的列，也就是说，每个分区内部都会重新计数。ORDER BY 子句则表示按照哪些列进行排序，可以同时指定多个排序方式。

二、row_number 的样例

下面是一个使用 row_number 函数的样例。假设我们有一个名为 users 的表，其中包含了用户 ID、注册时间、以及所在城市三列信息，我们想要按照城市对这些用户进行分组，并按照注册时间对每个城市内的用户进行排序并给他们编号：

SELECT user_id, city, registered_at,        ROW_NUMBER() OVER (PARTITION BY city ORDER BY registered_at) as row_numFROM users;

以上 SQL 语句将返回一个包含 user_id、city、registered_at 和 row_num 四个列的结果集，其中 row_num 表示该用户在所属城市中的排名。例如：

三、row_number 的常用应用场景

row_number 函数在 Hive 查询中广泛应用，特别是在需要获取排名信息的场景中。下面是一些常用的应用场景：

  1. 获取 TOP N 数据：可以使用 row_number 函数对数据进行排序并筛选出前 N 条数据；
  2. 获取分组内 TOP N 数据：可以使用 PARTITION BY 子句对数据进行分区，然后再使用 row_number 函数对每个分区内的数据进行排序，最后再筛选出前 N 条数据；
  3. 根据某些列的值进行条件筛选：可以在 WHERE 子句中使用 row_number 函数来筛选出满足一定条件的数据。

总之，row_number 函数是在 Hive 查询中非常有用的一个函数，可以让我们更加便捷地获取排名信息，并且在实际应用中具有广泛的应用场景。

四、样例数据建表语句及数据插入语句

建表语句

CREATE TABLE IF NOT EXISTS users (  user_id STRING,  city STRING,  registered_at TIMESTAMP) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE;

数据插入语句

INSERT INTO users VALUES ('1', 'Beijing', '2022-01-01 10:00:00'),('2', 'Beijing', '2022-01-02 12:00:00'),('3', 'Shanghai', '2022-01-02 15:00:00'),('4', 'Shanghai', '2022-01-03 08:00:00'),('5', 'Shanghai', '2022-01-04 09:00:00'),('6', 'Guangzhou', '2022-01-05 10:00:00'),('7', 'Guangzhou', '2022-01-06 11:00:00');