Snowflake中如何在ARRAY_AGG函数内使用ORDER BY子句？

测试表创建语句 #

create table test("CompanyID","GroupIDs","GroupNames")as 
select*from(values(1111      ,1         ,'ABC'),
                  (1111      ,5         ,'PRS'));

预期输出 #

上述示例中结果按升序排列，且GroupID=1对应GroupName=ABC，关联关系保持一致。

错误的查询写法（无法实现有序聚合） #

SELECT 
    companyid, 
    ARRAY_AGG(groupid)   AS groupid, 
    ARRAY_AGG(groupname) AS groupname
FROM (
SELECT cgd.companyid ,cg.groupid,cgd.companyname,cg.groupname
 FROM gd
 LEFT JOIN cg  ON cg.groupid = cgd.groupid
 ORDER BY cgd.companyid , cg.groupid, cg.groupname
) t
GROUP BY ALL;

错误查询的输出结果 #

问题原因 #

子查询中的ORDER BY无法确保聚合后的数组按GroupID有序。Snowflake在执行聚合操作时，不会保留子查询的排序结果，因此子查询的排序对ARRAY_AGG的最终输出顺序没有作用。

正确的解决方法：在ARRAY_AGG函数内部添加排序逻辑 #

SELECT 
    companyid, 
    ARRAY_AGG(groupid ORDER BY groupid ASC)   AS groupids, 
    ARRAY_AGG(groupname ORDER BY groupid ASC) AS groupnames
FROM (
SELECT cgd.companyid, cg.groupid, cg.groupname
 FROM gd
 LEFT JOIN cg ON cg.groupid = cgd.groupid
) t
GROUP BY companyid;

说明 #

• 在ARRAY_AGG函数内通过ORDER BY groupid ASC指定排序规则，可直接控制聚合数组的元素顺序。
• 对groupname的聚合同样使用ORDER BY groupid ASC，能保证GroupID与GroupName的关联关系始终匹配。
• 若需要降序排列，将ASC替换为DESC即可。

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