BigQuery中按顺序分组数字，每组总和为12的技术问询

BigQuery中有一张表，包含100+个数值，取值范围0.5到6，步长0.5，按order_num从1到100排序。需要按顺序将数值分组，要求每组数值总和恰好为12（示例中组1包含order_num为1、2、3、6的数值，总和为12，未纳入的4、5进入下一组）。

本人尝试先将数值分组为总和≤12的组，再补全至每组总和12，附上测试表及尝试的SQL代码，恳请提供思路与提示。

测试表 #

尝试的SQL代码 #

WITH cte AS (SELECT
    order_num,
    num,
    SUM(num) OVER (ORDER BY order_num) AS cumulative_sum
    FROM
    `ms_jzs6s5.test_numbers`)
, groupHours as (
    SELECT
        order_num,
        num,
        cumulative_sum,
        CASE
            WHEN cumulative_sum <= CAST(12 AS FLOAT64) THEN num
            ELSE num - GREATEST(cumulative_sum - CAST(12 AS FLOAT64), 0)
        END AS plan,
        IF(MOD(CAST(cumulative_sum AS NUMERIC), 12) <> 0, CEIL(cumulative_sum / 12), CEIL(cumulative_sum / 12) + 1) AS group_num,
    FROM
        cte 
)
, Lastgroup as (
SELECT
    groupHours.*
FROM
    groupHours
JOIN (
    SELECT MAX(group_num) maxgroup
    FROM groupHours
    UNION ALL
    SELECT MAX(group_num) - 1 maxgroup
    FROM groupHours) ON groupHours.group_num = maxgroup
)
, groupLastHours as (
SELECT
    group_num,
    12 - SUM(num) AS lastHour
FROM
    groupHours
GROUP BY group_num
)
(
SELECT 1 as iteration, * 
FROM groupHours
UNION ALL
SELECT 2 iteration, 
    Lastgroup.*
FROM Lastgroup
LEFT JOIN (
    SELECT 
        groupLastHours.group_num,
        Lastgroup.num,
        MIN(order_num) order_num,
    FROM groupLastHours
    LEFT JOIN Lastgroup ON lastHour = Lastgroup.num
    GROUP BY 
        groupLastHours.group_num,
        groupLastHours.lastHour,
        Lastgroup.num
) lsh ON Lastgroup.num = lsh.num
)
ORDER BY group_num, iteration

核心逻辑调整：动态分组而非静态累积 #

当前代码用全局累积和分组，无法灵活调整后续未纳入的数值（比如示例中的4、5号）。应该改用逐组追踪剩余容量+跨顺序补全的逻辑：

• 每个组初始剩余容量为12，优先纳入前面未分组的数值
• 当当前组累积和接近12时，从后续未分组的数值中选取合适的小数值补全，补全后这些数值标记为已使用，不再进入下一组

具体实现步骤 #

1. 用递归CTE构建分组
   BigQuery支持递归CTE，可以逐组构建：

   • 初始阶段：选取第一个未分组的数值作为组的起点，记录当前累积和、已纳入的order_num数组
   • 递归阶段：每次尝试加入下一个未分组的数值，若累积和+该数值≤12则直接加入；若累积和+该数值>12，则跳过该数值，继续查找后续数值，直到找到能让组总和刚好为12的数值，完成当前组后开启新组

2. 标记已使用的数值
   用数组记录每个组已纳入的order_num，后续递归时只选取数组中未出现的数值，避免重复使用。

3. 简化版示例代码

WITH RECURSIVE groups AS (
  -- 初始组：从第一个未使用的数值开始
  SELECT
    1 AS group_num,
    num AS current_sum,
    [order_num] AS included_orders
  FROM `ms_jzs6s5.test_numbers`
  WHERE order_num = 1
  UNION ALL
  -- 递归构建分组：补全当前组或开启新组
  SELECT
    CASE WHEN g.current_sum + t.num = 12 THEN g.group_num + 1 ELSE g.group_num END AS group_num,
    CASE WHEN g.current_sum + t.num <=12 THEN g.current_sum + t.num ELSE t.num END AS current_sum,
    CASE WHEN g.current_sum + t.num <=12 THEN ARRAY_CONCAT(g.included_orders, [t.order_num]) ELSE [t.order_num] END AS included_orders
  FROM groups g
  JOIN `ms_jzs6s5.test_numbers` t
    ON t.order_num > (SELECT MAX(o) FROM UNNEST(g.included_orders) o)
  WHERE g.current_sum < 12
)
-- 提取完整分组信息
SELECT
  group_num,
  included_orders,
  (SELECT SUM(num) FROM `ms_jzs6s5.test_numbers` WHERE order_num IN UNNEST(included_orders)) AS group_sum
FROM groups
WHERE group_sum = 12
OPTIONS(max_recursion_depth=200) -- 适配100+行的表

注意事项 #

• 由于数值步长为0.5，总和12是0.5的整数倍，只要剩余数值足够，一定能凑出符合要求的组；若最后一组剩余数值总和不足12，可单独处理
• 递归CTE需设置足够的递归深度，避免超出BigQuery默认限制

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