AWS雅典娜支持ARRAY_AGG中的Order by吗？

Question

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=

Answer 1

一种方法：

1. 创建可排序格式为文本
的记录
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

此代码假定：

null