如何用Trino SQL生成类型为array(row(tid array(varchar), res varchar))的结果

原表NS_Data的结构及数据如下：

需要生成如下格式的结果，其中finalMap的类型必须为array(row(tid array(varchar), res varchar))：

用户之前尝试的SQL（未达到预期）：

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

分步写法（更易理解） #

-- 第一步：按ns、nid、res分组，聚合同一res下的tid为数组
WITH grouped_res AS (
    SELECT
        ns,
        nid,
        res,
        array_agg(tid) AS tid_array
    FROM NS_Data
    GROUP BY ns, nid, res
)
-- 第二步：按ns、nid分组，将每组的(tid数组, res)聚合为row数组
SELECT
    ns,
    nid,
    array_agg(row(tid_array AS tid, res AS res)) AS finalMap
FROM grouped_res
GROUP BY ns, nid;

合并写法 #

SELECT
    ns,
    nid,
    array_agg(
        row(
            array_agg(tid) AS tid,
            res AS res
        )
    ) AS finalMap
FROM NS_Data
GROUP BY ns, nid, res
GROUP BY ns, nid;

• 原SQL错误原因：没有按res维度分组，而是硬指定res值做union，导致聚合的tid数组混入了不同res的数据，且无法形成正确的array(row(...))结构。
• 正确逻辑：先按ns、nid、res分组，得到每个res对应的tid数组；再按ns、nid分组，将这些（tid数组+res）的行聚合为目标数组类型，完全匹配需求的array(row(tid array(varchar), res varchar))。

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