如何在hive sql中获取每个组的最大row_number()

Question

使用hive SQL中的row_number()，我可以通过在where子句中选择1来过滤重复项/选择id的第一个实例，如下所示。这里我需要的是如何找到每个组中的最后一个实例。

select * from 
(select c1,c2,c3,c4,c5,id, row_number() over(partition by id ORDER BY id) as seq
from 
table) as cnt where seq = 1;

例如，我的要求是，如果id1212有3个实例，1313在表中有5个实例，如下所示，我可以使用上面的查询，并通过在where子句中选择1来仅获得一个实例。但是下面的id1212是3，id1313是5。

 c1,  c2,  c3,  c4,  c5,  ID     seq
2020 2020 2020 2020 2020 1212     1
2021 2020 2021 2020 2021 1212     2
2022 2020 2022 2020 2022 1212     3
2023 2020 2023 2020 2023 1313     1
2024 2020 2024 2020 2024 1313     2
2025 2020 2025 2020 2025 1313     3
2026 2020 2026 2020 2026 1313     4
2026 2020 2026 2020 2026 1313     5

Answer 1

select id,max(seq) over(partition by id ORDER BY id)from 
(select *, row_number() over(partition by id ORDER BY id) as seq
from 
table)maxseq
group by id

Answer 2

使用COUNT(*) OVER (PARTITION BY id) AS cnt添加额外的列。它将包含组中的行数，这也是组的最大ROW_NUMBER值。

Answer 3

在group by中使用所有这些列，并在row_number()上使用max

select c1,c2,c3,c4,c5,id,max(r_no) 
from 
(
    select c1,c2,c3,c4,c5,id, row_number() over (partition by id ORDER BY c1,c2,c3,c4,c5,id) as r_no
    from 
    table
) a
group by c1,c2,c3,c4,c5,id