amazon-web-services - AWS Athena 是否支持 Array_AGG 中的 Order by？

Question

我与 AWS Athena 合作将几行合并为一行。

示例表:(名称:卸载)

xid pid sequence    text
1   1   0           select * from
1   1   1           mytbl
1   1   2   
2   1   0           update test
2   1   1            set mycol=
2   1   2           'a';

所以想联系文字栏。

预期输出:

xid pid text
1   1   select * from mytbl
2   1   update test set mycol='a';

我运行以下查询以首先以正确的顺序对其进行分区并进行连接。

with cte as
    (SELECT  
         xid,
        pid,

         sequence,
         text,
        row_number()
        OVER (PARTITION BY  xid,pid
    ORDER BY  sequence) AS rank
    FROM unload 
    GROUP BY   xid,pid,sequence,text
    )
SELECT 
         xid,
        pid,
        array_join(array_agg(text),'') as text
FROM cte
GROUP BY   xid,pid

但是，如果您看到以下输出，则订单放错了位置。

xid pid text
1   1   mytblselect * from
2   1   update test'a'; set mycol=

我查看了 Presto 文档，最新版本支持 order by in array agg，但是 Athena 使用的是 Presto 0.172，所以我不确定是否支持。

雅典娜的解决方法是什么？

Answer 1

一种方法:

创建可排序格式为 text 的记录

聚合成一个未排序的数组

对数组进行排序

将每个元素转换回 text 的原始值

将排序后的数组转换为字符串输出列

WITH cte AS (
SELECT
xid, pid, text
-- create a sortable 19-digit ranking string
, SUBSTR(
  LPAD(
    CAST(
      ROW_NUMBER() OVER (PARTITION BY xid, pid ORDER BY sequence)
      AS VARCHAR)
    , 19
    , '0')
  , -19) AS SEQ_STR
FROM unload
)
SELECT
xid, pid
-- make sortable string, aggregate into array
-- then sort array, revert each element to original text
-- finally combine array elements into one string
, ARRAY_JOIN(
  TRANSFORM(
    ARRAY_SORT(
      ARRAY_AGG(SEQ_STR || text))
    , combined -> SUBSTR(combined, 1 + 19))
  , ' '
  , '') AS TEXT
FROM cte
GROUP BY xid, pid
ORDER BY xid, pid

此代码假设:

xid + pid + sequence对所有输入记录都是唯一的

xid的组合不多+ pid + sequence (例如，不超过 2000 万)