Athena是否支持在ARRAY_AGG中使用ORDER BY?

0

【以下的问题经过翻译处理】 似乎Athena不支持在ARRAY_AGG函数中使用ORDER BY语句。以下是一个示例。

 WITH dataset AS (
       SELECT 1 AS breakdown_id, 1 AS history_order, 'OPEN' AS action
       UNION ALL
       SELECT 1 AS breakdown_id, 2 AS history_order, 'IN_PROGRESS' AS action
       UNION ALL
       SELECT 1 AS breakdown_id, 3 AS history_order, 'CLOSED' AS action
       UNION ALL
       SELECT 2 AS breakdown_id, 1 AS history_order, 'OPEN' AS action
       UNION ALL
       SELECT 2 AS breakdown_id, 2 AS history_order, 'IN_PROGRESS' AS action
       UNION ALL
       SELECT 3 AS breakdown_id, 1 AS history_order, 'OPEN' AS action
       UNION ALL
       SELECT 3 AS breakdown_id, 2 AS history_order, 'IN_PROGRESS' AS action
       UNION ALL
       SELECT 3 AS breakdown_id, 3 AS history_order, 'WAITING_FOR_INPUT' AS action
    
    )
    SELECT breakdown_id
         , ARRAY_AGG(action) AS actions
      FROM dataset
     GROUP BY breakdown_id

如果运行以下查询,记录将按预期聚合成数组。

然而,数组的元素需要按照history_order列指定的顺序排列。不幸的是,将ORDER BY添加到ARRAY_AGG函数中会导致无任何结果返回。

这是一个缺陷还是特性?更重要的是,是否有其他方法可以按history_order排序获取数组?

profile picture
专家
已提问 3 个月前7 查看次数
1 回答
0

【以下的回答经过翻译处理】 你好,

如果你将 Athena 引擎升级至 v2 版本,之前失败的查询应该可以正常工作了。

请参考以下链接:https://docs.aws.amazon.com/athena/latest/ug/engine-versions-changing.html

profile picture
专家
已回答 3 个月前

您未登录。 登录 发布回答。

一个好的回答可以清楚地解答问题和提供建设性反馈,并能促进提问者的职业发展。

回答问题的准则