Trino SQL查询结果无法转为预期聚合格式，求修正方案

原查询与问题 #

原查询语句：

WITH dataset(ns, tid, nid, type) AS ( values ('PQR',  'ITKT20254',  'A','X'),
                                        ('PQR', 'ITKT20223',    'A','X'), 
                                        ('PQR', 'ABCD23456',    'B','X'), 
                                         ('PQR', 'ABCD54321',   'B','X'), 
                                        ('PQR', 'ITKT21111',    'A','X'),
                                        ('PQR', 'ITKT20000',    'A','Y')
                                         )
 select ns,nid, 
    cast(row(tradelist , res1) as row(tid array(varchar),res varchar)) as finalMap 
from
  (select ns,nid,tradelist,res1 
   from (
      select ns,nid,
          array_agg(cast(tid  as varchar)) as tradelist,
          'not include' as  res1 from
          (select ns,nid,tid from  dataset where type='X' )
       
      group by ns, nid 
     union 
      select ns,nid, 
          array_agg(cast(tid  as varchar)) as tradelist,
          'include' as  res1  from
           (select ns,nid, tid from  dataset where type='Y' )
       
      group by ns, nid
    )
   )

当前执行结果：

ns      nid      finalMap
PQR     A   {tid=[ITKT20254, ITKT20223, ITKT21111], res=not include}
PQR     A   {tid=[ITKT20000], res=include}
PQR     B   {tid=[ABCD23456, ABCD54321], res=not include}

预期输出：

ns      nid      finalMap
PQR     A   [{tid=[ITKT20254, ITKT20223, ITKT21111], res=not include},{tid=[ITKT20000], res=include}]
PQR     B   [{tid=[ABCD23456, ABCD54321], res=not include}]

需要将同一ns和nid下的多条finalMap合并为一个数组，尝试使用array_agg时出现错误。

修改后的查询语句 #

WITH dataset(ns, tid, nid, type) AS ( values ('PQR',  'ITKT20254',  'A','X'),
                                        ('PQR', 'ITKT20223',    'A','X'), 
                                        ('PQR', 'ABCD23456',    'B','X'), 
                                         ('PQR', 'ABCD54321',   'B','X'), 
                                        ('PQR', 'ITKT21111',    'A','X'),
                                        ('PQR', 'ITKT20000',    'A','Y')
                                         )
select ns, nid, array_agg(finalMap) as finalMap
from (
    select ns,nid, 
        cast(row(tradelist , res1) as row(tid array(varchar),res varchar)) as finalMap 
    from
      (select ns,nid,tradelist,res1 
       from (
          select ns,nid,
              array_agg(cast(tid  as varchar)) as tradelist,
              'not include' as  res1 from
              (select ns,nid,tid from  dataset where type='X' )
           
          group by ns, nid 
         union 
          select ns,nid, 
              array_agg(cast(tid  as varchar)) as tradelist,
              'include' as  res1  from
               (select ns,nid, tid from  dataset where type='Y' )
           
          group by ns, nid
        )
       )
) t
group by ns, nid;

说明 #

修改核心是在原查询的外层新增一层分组：

1. 保留原查询中生成单条finalMap的逻辑，将其作为子查询t
2. 对子查询t按ns和nid分组，使用array_agg(finalMap)将同一组内的所有finalMap行聚合为一个数组，直接输出这个数组作为最终的finalMap字段

这样就能得到预期的、将同组结果合并为数组的格式。

内容的提问来源于stack exchange，提问作者Prachi