526互联

hivesql 实现collect_list内排序

发布时间 2023-04-14 16:50:22作者: DB乐之者

假设我有一个看起来像这样的蜂巢表：

ID    event    order_num
------------------------
A      red         2
A      blue        1
A      yellow      3
B      yellow      2
B      green       1
...

 

我正在尝试使用 collect_list 为每个 ID 生成事件列表。所以类似于以下内容：

SELECT ID, 
collect_list(event) as events_list,
FROM table
GROUP BY ID;

 

但是，在我分组所依据的每个 ID 中，我需要按 order_num 进行排序。这样我的结果表将如下所示：

ID    events_list
------------------------
A      ["blue","red","yellow"]
B      ["green","red"]

我无法在 collect_list() 查询之前按 ID 和 order_num 进行全局排序，因为该表很大。有没有办法在 collect_list 中按 order_num 排序？

诀窍是使用带有 DISTRIBUTE BY 和 SORT BY 语句的子查询。见下文：

WITH table1 AS (
    SELECT 'A' AS ID, 'red' AS event, 2 AS order_num UNION ALL
    SELECT 'A' AS ID, 'blue' AS event, 1 AS order_num UNION ALL
    SELECT 'A' AS ID, 'yellow' AS event, 3 AS order_num UNION ALL
    SELECT 'B' AS ID, 'yellow' AS event, 2 AS order_num UNION ALL
    SELECT 'B' AS ID, 'green' AS event, 1 AS order_num
)

-- Collect it
SELECT subquery.ID, 
collect_list(subquery.event) as events_list
FROM (
SELECT
        table1.ID,
        table1.event,
        table1.order_num
    FROM table1
    DISTRIBUTE BY
        table1.ID
    SORT BY
        table1.ID,
        table1.order_num
) subquery
GROUP BY subquery.ID;