时间戳

首页 > 解决方案 > AWS Athena 是否支持在 Array_AGG 中排序？

amazon-web-services - AWS Athena 是否支持在 Array_AGG 中排序？

问题描述

我与 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，所以我不确定它是否支持。

在 Athena 中解决此问题的方法是什么？

标签： amazon-web-servicesprestoamazon-athena

解决方案

一种方法：

  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++的组合不多（例如不超过2000万pid）sequence

推荐阅读