AWS Athena是否支持Array_AGG中的Order by

Question

我正在与AWS Athena合作，将几行连接到一行。

示例表：(名称：unload(

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';

我运行了下面的查询，首先以正确的顺序对其进行分区，然后进行concat。

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文档，最新版本支持按数组agg排序，但Athena使用的是Presto 0.172，所以我不确定它是否受支持。

在Athena中有什么解决方法？

Answer 1

一种方法：

1. 使用text的可排序格式创建记录
2. 聚合为未排序的数组
3. 对数组进行排序
4. 将每个元素转换回text的原始值
5. 将排序后的数组转换为字符串输出列

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

此代码假定：

1. xid+pid+sequence对于所有输入记录都是唯一的
2. xid+pid+sequence的组合不多(例如不超过2000万(