Snowflake实现带行数限制的LISTAGG聚合方法

Snowflake可以通过聚合/窗口函数LISTAGG实现跨行字符串拼接。但有时我们需要按组仅取前若干条数据进行拼接，类似部分数据库中GROUP_CONCAT with LIMIT的效果，比如下面这种逻辑（仅作示例，非Snowflake原生语法）：

SELECT grp, COUNT(*) AS cnt,
       GROUP_CONCAT(col ORDER BY col SEPARATOR ',' LIMIT 3) AS first_3 
FROM tab
GROUP BY grp;

示例数据 #

先创建示例表：

CREATE OR REPLACE TABLE tab(grp TEXT, col TEXT) AS
SELECT * FROM VALUES 
  ('Grp1', 'A'),('Grp1', 'B'),('Grp1', 'C'),('Grp1', 'D'), ('Grp1', 'E'),
  ('Grp2', 'X'),('Grp2', 'Y'),('Grp2', 'Z'),('Grp2', 'V'),
  ('Grp3', 'M'),('Grp3', 'N'),('Grp3', 'O');

预期结果 #

Snowflake实现方案 #

我们可以通过子查询给每组内的行标记序号，再结合LISTAGG和条件判断来实现，同时不影响其他聚合函数的计算：

SELECT grp, 
  COUNT(*) AS cnt,
  LISTAGG(CASE WHEN rn <= 3 THEN col END, ',') WITHIN GROUP(ORDER BY col) AS first_3
FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY grp ORDER BY col) AS rn
      FROM tab)
GROUP BY grp;

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