如何在BigQuery SQL中创建动态计算队列追踪收支平衡点

完全可以实现这种「先到先耗」的收入消耗逻辑，以下是具体的实现方案：

实现思路 #

1. 先把所有交易按时间排序，给每条记录打序号，保证处理顺序绝对正确。
2. 把收入（earned）和支出（spent）分开，分别计算累计收入和累计支出的金额。
3. 用递归CTE逐笔匹配支出到对应的收入，跟踪每笔收入的剩余金额和每笔支出的消耗分配情况，直到收入耗尽或支出处理完毕。

完整SQL代码 #

WITH sorted_transactions AS (
  -- 给所有交易按时间排序并分配唯一ID
  SELECT
    event,
    amount,
    time,
    ROW_NUMBER() OVER(ORDER BY time) AS transaction_id
  FROM
    `your-project.your-dataset.your-table` -- 替换成你的实际表路径
),
earned_list AS (
  -- 提取所有收入记录，计算累计收入额
  SELECT
    transaction_id,
    amount AS earned_amount,
    time AS earned_time,
    SUM(amount) OVER(ORDER BY transaction_id) AS total_earned_so_far
  FROM sorted_transactions
  WHERE event = 'earned'
),
spent_list AS (
  -- 提取所有支出记录，计算累计支出额
  SELECT
    transaction_id,
    amount AS spent_amount,
    time AS spent_time,
    SUM(amount) OVER(ORDER BY transaction_id) AS total_spent_so_far
  FROM sorted_transactions
  WHERE event = 'spent'
),
match_records AS (
  -- 递归初始节点：从第一笔收入和第一笔支出开始匹配
  SELECT
    el.transaction_id AS earned_id,
    el.earned_time,
    el.earned_amount,
    el.total_earned_so_far,
    sl.transaction_id AS spent_id,
    sl.spent_time,
    sl.spent_amount,
    sl.total_spent_so_far,
    -- 计算当前支出消耗的金额：取收入剩余和支出金额的较小值
    LEAST(el.earned_amount, sl.spent_amount) AS consumed,
    -- 更新收入剩余金额
    el.earned_amount - LEAST(el.earned_amount, sl.spent_amount) AS remaining_earned,
    -- 更新支出剩余金额
    sl.spent_amount - LEAST(el.earned_amount, sl.spent_amount) AS remaining_spent
  FROM earned_list el
  CROSS JOIN spent_list sl
  WHERE el.transaction_id = (SELECT MIN(transaction_id) FROM earned_list)
    AND sl.transaction_id = (SELECT MIN(transaction_id) FROM spent_list)
  UNION ALL
  -- 递归处理剩余的收入或支出
  SELECT
    -- 如果当前收入还有剩余，继续用这笔；否则取下一笔收入
    CASE WHEN mr.remaining_earned > 0 THEN mr.earned_id
         ELSE (SELECT MIN(transaction_id) FROM earned_list WHERE transaction_id > mr.earned_id) END AS earned_id,
    CASE WHEN mr.remaining_earned > 0 THEN mr.earned_time
         ELSE (SELECT earned_time FROM earned_list WHERE transaction_id > mr.earned_id LIMIT 1) END AS earned_time,
    CASE WHEN mr.remaining_earned > 0 THEN mr.remaining_earned
         ELSE (SELECT earned_amount FROM earned_list WHERE transaction_id > mr.earned_id LIMIT 1) END AS earned_amount,
    CASE WHEN mr.remaining_earned > 0 THEN mr.total_earned_so_far
         ELSE (SELECT total_earned_so_far FROM earned_list WHERE transaction_id > mr.earned_id LIMIT 1) END AS total_earned_so_far,
    -- 如果当前支出还有剩余，继续用这笔；否则取下一笔支出
    CASE WHEN mr.remaining_spent > 0 THEN mr.spent_id
         ELSE (SELECT MIN(transaction_id) FROM spent_list WHERE transaction_id > mr.spent_id) END AS spent_id,
    CASE WHEN mr.remaining_spent > 0 THEN mr.spent_time
         ELSE (SELECT spent_time FROM spent_list WHERE transaction_id > mr.spent_id LIMIT 1) END AS spent_time,
    CASE WHEN mr.remaining_spent > 0 THEN mr.remaining_spent
         ELSE (SELECT spent_amount FROM spent_list WHERE transaction_id > mr.spent_id LIMIT 1) END AS spent_amount,
    CASE WHEN mr.remaining_spent > 0 THEN mr.total_spent_so_far
         ELSE (SELECT total_spent_so_far FROM spent_list WHERE transaction_id > mr.spent_id LIMIT 1) END AS total_spent_so_far,
    -- 计算本次消耗金额
    LEAST(
      COALESCE(mr.remaining_earned, (SELECT earned_amount FROM earned_list WHERE transaction_id > mr.earned_id LIMIT 1)),
      COALESCE(mr.remaining_spent, (SELECT spent_amount FROM spent_list WHERE transaction_id > mr.spent_id LIMIT 1))
    ) AS consumed,
    -- 更新收入剩余
    COALESCE(mr.remaining_earned, (SELECT earned_amount FROM earned_list WHERE transaction_id > mr.earned_id LIMIT 1)) - LEAST(
      COALESCE(mr.remaining_earned, (SELECT earned_amount FROM earned_list WHERE transaction_id > mr.earned_id LIMIT 1)),
      COALESCE(mr.remaining_spent, (SELECT spent_amount FROM spent_list WHERE transaction_id > mr.spent_id LIMIT 1))
    ) AS remaining_earned,
    -- 更新支出剩余
    COALESCE(mr.remaining_spent, (SELECT spent_amount FROM spent_list WHERE transaction_id > mr.spent_id LIMIT 1)) - LEAST(
      COALESCE(mr.remaining_earned, (SELECT earned_amount FROM earned_list WHERE transaction_id > mr.earned_id LIMIT 1)),
      COALESCE(mr.remaining_spent, (SELECT spent_amount FROM spent_list WHERE transaction_id > mr.spent_id LIMIT 1))
    ) AS remaining_spent
  FROM match_records mr
  -- 终止条件：没有剩余收入或剩余支出，且没有后续记录可处理
  WHERE (mr.remaining_earned > 0 OR mr.remaining_spent > 0)
    AND EXISTS (SELECT 1 FROM earned_list WHERE transaction_id >= mr.earned_id)
    AND EXISTS (SELECT 1 FROM spent_list WHERE transaction_id >= mr.spent_id)
),
result_summary AS (
  -- 整理每笔收入的消耗详情和耗尽时间
  SELECT
    earned_time,
    earned_amount AS total_earned,
    -- 找到该笔收入完全耗尽时的支出时间
    MAX(CASE WHEN remaining_earned = 0 THEN spent_time END) AS exhausted_at,
    -- 汇总该笔收入被哪些支出消耗的明细
    ARRAY_AGG(STRUCT(spent_time, consumed) ORDER BY spent_time) AS consumption_details
  FROM match_records
  GROUP BY earned_time, earned_amount
)
SELECT * FROM result_summary

结果说明 #

针对你提供的示例数据，执行后会得到：

• 1:00到账的100元收入，exhausted_at为4:00，consumption_details包含2:00消耗50元、4:00消耗50元的记录
• 3:00到账的50元收入，exhausted_at为5:00，consumption_details包含5:00消耗50元的记录

如果有未耗尽的收入，exhausted_at会显示为NULL，可以根据实际需求调整结果展示形式。

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