hive中开窗函数row_number的使用

row_number()函数介绍

row_number()开窗函数的一种，和over()函数结合一起使用，可以实现对数据的分组和排序。

使用示例

现在有一张表，数据如下

+------+----------+-------+
| Year |  Region  | Sales |
+------+----------+-------+
| 2022 | East     | 100   |
| 2022 | West     | 150   |
| 2022 | East     | 200   |
| 2023 | West     | 120   |
| 2023 | West     | 180   |
| 2023 | East     | 250   |
+------+----------+-------+

现在，我们想为每个年份和地区计算销售额的行号，并按照销售额降序排列。我们可以使用ROW_NUMBER() OVER(PARTITION BY field1, field2 ORDER BY field3, field4)子句来实现：

SELECT Year, Region, Sales,
       ROW_NUMBER() OVER (PARTITION BY Year, Region ORDER BY Sales DESC) AS row_number
FROM sales;

查询结果为

+------+----------+-------+------------+
| Year |  Region  | Sales | row_number |
+------+----------+-------+------------+
| 2022 | East     | 200   | 1          |
| 2022 | East     | 100   | 2          |
| 2022 | West     | 150   | 1          |
| 2023 | East     | 250   | 1          |
| 2023 | West     | 180   | 1          |
| 2023 | West     | 120   | 2          |
+------+----------+-------+------------+

在查询结果中，row_number列包含了每个年份和地区组合中销售额的行号。注意，PARTITION BY子句将结果分为不同的分区，每个分区内的行号是独立计算的。ORDER BY子句指定了按销售额降序排列的顺序。

通过使用PARTITION BY和ORDER BY子句，我们可以在Hive中对查询结果进行分区和排序，并为每个分区生成唯一的行号。这对于进行分组排名和分区分析等任务非常有用。

如果over中没有partition by和order by，会发生什么？

SELECT Year, Region, Sales,
       ROW_NUMBER() OVER () AS row_number
FROM sales;

查询结果为

+------+----------+-------+------------+
| Year |  Region  | Sales | row_number |
+------+----------+-------+------------+
| 2022 | East     | 200   | 1          |
| 2022 | East     | 100   | 2          |
| 2022 | West     | 150   | 3          |
| 2023 | East     | 250   | 4          |
| 2023 | West     | 180   | 5          |
| 2023 | West     | 120   | 6          |
+------+----------+-------+------------+

会为每一行数据生成一个唯一的行号，可以把这当成id，可以用作后续对数据进行id识别。

注意事项

当row_number中order by的排序的字段，存储的是时间，比如’2024-05-05 13:20:46’，但是类型却是字符串时，要防止下面这两种数据

-- 当前字段为sn, date_time
'sn1', '2024-1-3 00:00:00'
'sn1', '2024-1-21 00:00:00'

上面这两个数据在下面的排序中，会出现错误
select sn, date_time, row_number() over (partition by sn order by date_time asc) as row_number;

'sn1', '2024-1-21 00:00:00', 1
'sn1', '2024-1-3 00:00:00', 2

本来’2024-1-3 00:00:00’的row_number应该为1，因为1月3号应该在1月21号前面，但是因为这种格式的字符串，导致了1月3号一行的row_number值变成了2。

应该怎么解决这种问题呢？有两种方法

• 将日期时间字符串，使用函数，统一转换成’yyyy-MM-dd HH:mm:ss’，可以参考下面的文章
  hive将时间字符串转换为timestamp的几种写法
• 将所有日期时间字符串统一转换成bigint类型，再进行比较，可以参考下面的文章
  hive将时间字符串转换为timestamp的几种写法