hive开窗函数-row_number

数据仓库晨曦

发布于 2024-01-08 15:37:24

3480

发布于 2024-01-08 15:37:24

文章被收录于专栏：数据仓库技术数据仓库技术

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_num
FROM users;

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

user_id | city     | registered_at        | row_num
------- | --------| -------------------- | -------
1       | Beijing  | 2022-01-01 10:00:00  | 1
2       | Beijing  | 2022-01-02 12:00:00  | 2
3       | Shanghai | 2022-01-02 15:00:00  | 1
4       | Shanghai | 2022-01-03 08:00:00  | 2
5       | Shanghai | 2022-01-04 09:00:00  | 3
6       | Guangzhou| 2022-01-05 10:00:00  | 1
7       | Guangzhou| 2022-01-06 11:00:00  | 2

以上结果展示了来自不同城市的用户按照注册时间排序后的排名信息。

三、row_number 的常用应用场景

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

获取 TOP N 数据：可以使用 row_number 函数对数据进行排序并筛选出前 N 条数据；
获取分组内 TOP N 数据：可以使用 PARTITION BY 子句对数据进行分区，然后再使用 row_number 函数对每个分区内的数据进行排序，最后再筛选出前 N 条数据；
根据某些列的值进行条件筛选：可以在 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');

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2023-03-08，如有侵权请联系 cloudcommunity@tencent.com 删除

hive