我正在尝试将具有保存值的行分为两列，并根据第三列对结果进行排名/排序。
结果应包含所有其他列。
对于表:
with sample as (
select 'A' as c1, 'B' as c2, '22:00' as c3, 'Da' as c4
union all
select 'A' as c1, 'B' as c2, '23:00' as c3, 'Db' as c4
union all
select 'A' as c1, 'B' as c2, '09:00' as c3, 'Dc' as c4
union all
select 'A' as c1, 'C' as c2, '22:00' as c3, 'Dd' as c4
union all
select 'B' as c1, 'C' as c2, '09:00' as c3, 'De' as c4
)
按在c3上按时间排列的c1和c2列进行分组或过滤，输出为:
row_number() over (partition by c1, c2 order by c3) as rnk
| c1, c2, c3, c4, rnk|
-----------------------
| A | B |09:00| Dc| 1 |
| A | B |22:00| Da| 2 |
| A | B |23:00| Db| 3 |
| A | C |22:00| Dd| 1 |
| B | C |09:00| De| 1 |
所有其他列(例如c4，c5 ..)都应保留，但对组标准或排名没有任何影响。
相信在c1和c2上进行分区并按c3排序的窗口函数可以工作，但是不确定在大型表和需要按更多列分组的情况下这是否是最佳方法。
最终输出将是UNIQUE行，其中rank为1(顶部)。列应与样本表完全相同(无等级)。
Select * from tableX where rnk = 1将完成工作，但保留列' rnk '。我想避免在select中将所有列写成，但不包括rnk的。
| c1, c2, c3, c4 |
-------------------
| A | B |09:00| Dc|
| A | C |22:00| Dd|
| B | C |09:00| De|
*已编辑，添加最终表
最佳答案
select inline(array(rec))
from (select struct(*) as rec
,row_number() over
(
partition by c1,c2
order by c3
) as rn
from sample t
) t
where rn = 1
;
+------+------+-------+------+
| col1 | col2 | col3 | col4 |
+------+------+-------+------+
| A | B | 09:00 | Dc |
| A | C | 22:00 | Dd |
| B | C | 09:00 | De |
+------+------+-------+------+
P.s.
请注意，由于使用了struct，因此列名已被别名。
关于hadoop - 配置单元-根据某些列选择唯一的行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/44866747/