BigQuery中实现可重置计数器的连续减法方法咨询

场景说明 #

取Available列的首个值，持续减去固定值（示例为10）得到Required列；当Required变为0时，计数器重置，重新从Available的首个值开始计算。

方法一：窗口函数实现（规则场景） #

适用于Available首个值能被固定值整除，且重置逻辑为“减到0后立即从头开始”的场景。

假设你的表名为your_table，包含排序用的自增列id和Available列，执行以下SQL：

WITH base_data AS (
  SELECT
    id,
    Available,
    -- 获取Available列的首个值
    FIRST_VALUE(Available) OVER () AS initial_available,
    -- 生成行号（按id排序）
    ROW_NUMBER() OVER (ORDER BY id) AS row_num
  FROM your_table
),
grouped_data AS (
  SELECT
    *,
    -- 计算每个行所属的分组：每 (initial_available/固定值 +1) 行为一个分组
    FLOOR((row_num - 1) / (initial_available / 10 + 1)) AS reset_group,
    -- 计算分组内的偏移量
    MOD(row_num - 1, initial_available / 10 + 1) AS group_offset
  FROM base_data
)
SELECT
  id,
  Available,
  initial_available - group_offset * 10 AS Required
FROM grouped_data
ORDER BY id;

逻辑解释 #

1. base_data步骤：获取全局首个Available值，并生成有序行号。
2. grouped_data步骤：根据首个值和固定值计算分组（比如首个值为30、固定值10时，每4行一个分组），同时计算每行在分组内的偏移量。
3. 最终计算Required：用首个值减去偏移量乘以固定值，实现连续减法，减到0后自动重置。

方法二：递归CTE实现（灵活场景） #

如果Available值无法被固定值整除，或者需要支持中途出现新的Available值作为重置后的起始值，推荐用递归CTE：

WITH RECURSIVE subtraction_cte AS (
  -- 初始行：取表中第一行数据
  SELECT
    id,
    Available,
    Available AS Required,
    -- 计算第一次减法后的剩余值
    Available - 10 AS remaining_value
  FROM your_table
  WHERE id = (SELECT MIN(id) FROM your_table)

  UNION ALL

  -- 递归处理后续行
  SELECT
    t.id,
    t.Available,
    -- 剩余值<=0时重置：优先用当前行的Available，否则用全局首个值
    CASE
      WHEN c.remaining_value <= 0 THEN COALESCE(t.Available, (SELECT FIRST_VALUE(Available) FROM your_table))
      ELSE c.remaining_value
    END AS Required,
    -- 计算新的剩余值
    CASE
      WHEN c.remaining_value <= 0 THEN COALESCE(t.Available, (SELECT FIRST_VALUE(Available) FROM your_table)) - 10
      ELSE c.remaining_value - 10
    END AS remaining_value
  FROM your_table t
  JOIN subtraction_cte c ON t.id = c.id + 1
)
SELECT id, Required FROM subtraction_cte ORDER BY id;

逻辑解释 #

1. 初始行：从表的第一行开始，初始化Required为首个Available值，并计算第一次减法后的剩余量。
2. 递归迭代：逐行处理后续数据，若上一行的剩余量<=0则重置Required（优先用当前行的Available，否则用全局首个值），否则继续用上一行的剩余量作为当前Required，再计算新的剩余量。

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