基于前一行subgrp值生成分组列及BigQuery大数据量优化方案求助

针对你提出的按subgrp序列动态生成grp分组的需求，同时适配100M+级别的大数据集，我们可以用轻量窗口函数+累计求和的方案来实现，避免内存超限和高耗时问题：

核心思路 #

你的分组规则本质是：按id排序后，当当前行的subgrp小于前一行时，开启新分组；否则延续当前分组。我们可以通过以下步骤实现：

• 用LAG()窗口函数获取前一行的subgrp值
• 标记“新分组触发点”：当前subgrp < 前一行subgrp时标记为1，否则为0
• 对标记值做累计求和，再+1得到最终的grp编号（确保分组从1开始）

完整可执行代码 #

#standardSQL
WITH k AS (
 SELECT 1 id, 1 subgrp, 'stuff1' content
 UNION ALL
 SELECT 2, 2, 'stuff2'
 UNION ALL
 SELECT 3, 3, 'stuff3'
 UNION ALL
 SELECT 4, 4, 'stuff4'
 UNION ALL
 SELECT 5, 1, 'ostuff1'
 UNION ALL
 SELECT 6, 2, 'ostuff2'
 UNION ALL
 SELECT 7, 3, 'ostuff3'
 UNION ALL
 SELECT 8, 4, 'ostuff4'
),
add_group_marker AS (
 SELECT 
   *,
   -- 标记新分组：当前subgrp小于前一行则为1，否则0；第一行无前置，标记为0
   CASE 
     WHEN subgrp < LAG(subgrp) OVER (ORDER BY id) THEN 1 
     ELSE 0 
   END AS new_group_flag
 FROM k
)
SELECT 
 id,
 -- 累计求和标记值，加1得到grp编号
 SUM(new_group_flag) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + 1 AS grp,
 subgrp,
 content
FROM add_group_marker
ORDER BY id;

方案优势（针对大数据集） #

1. 低内存占用：仅使用基础的LAG()和SUM()窗口函数，没有复杂的窗口框架或聚合操作，BigQuery执行引擎可以高效处理，避免内存超限
2. 高性能：累计求和是顺序流式处理，适合大规模数据集，100M级数据的执行效率会远高于复杂窗口方案
3. 无需硬编码：完全基于subgrp的序列关系动态分组，不管每组包含多少个subgrp都能正确处理

后续嵌套结构生成 #

得到grp列后，你可以直接执行嵌套聚合：

#standardSQL
WITH k AS (
 -- 原表数据
 SELECT 1 id, 1 subgrp, 'stuff1' content
 UNION ALL
 SELECT 2, 2, 'stuff2'
 UNION ALL
 SELECT 3, 3, 'stuff3'
 UNION ALL
 SELECT 4, 4, 'stuff4'
 UNION ALL
 SELECT 5, 1, 'ostuff1'
 UNION ALL
 SELECT 6, 2, 'ostuff2'
 UNION ALL
 SELECT 7, 3, 'ostuff3'
 UNION ALL
 SELECT 8, 4, 'ostuff4'
),
add_group_marker AS (
 SELECT 
   *,
   CASE 
     WHEN subgrp < LAG(subgrp) OVER (ORDER BY id) THEN 1 
     ELSE 0 
   END AS new_group_flag
 FROM k
),
add_grp AS (
 SELECT 
   id,
   SUM(new_group_flag) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + 1 AS grp,
   subgrp,
   content
 FROM add_group_marker
)
SELECT 
 grp,
 ARRAY_AGG(STRUCT(subgrp, content) ORDER BY subgrp) AS rcd
FROM add_grp
GROUP BY grp
ORDER BY grp;

内容的提问来源于stack exchange，提问作者yan-hic