如何在BigQuery中计算电池剩余电量(SoC)？

需求背景 #

基于能耗/光伏发电量数据，按15分钟间隔逐行滚动计算电池剩余电量（SoC），规则如下：

• 光伏发电产生能量时（Energy进出为负值），电量存入电池，SoC上升
• 消耗能量时（Energy进出为正值），SoC下降
• SoC必须约束在 0 ≤ SoC ≤ 200 范围内，超出边界时取边界值
• 需依赖前一行的SoC结果逐行计算，普通窗口SUM()函数无法处理中间截断逻辑

解决方案：使用递归CTE #

由于SoC计算依赖前一行的结果，且需要实时约束上下限，BigQuery中可以用递归CTE实现该逻辑，以下是完整查询：

WITH sample_data AS (
  -- 模拟15分钟间隔的能耗/发电数据，替换为你的实际表
  SELECT 
    TIMESTAMP_ADD(TIMESTAMP('2024-01-01 00:00:00'), INTERVAL (row_num-1)*15 MINUTE) AS usage_at,
    energy_in_out
  FROM UNNEST([4.57, 2.5, 0.29, -2.29, -6.00, 3.29, 6.65, -2.15]) AS energy_in_out
  WITH OFFSET AS row_num
),
sorted_data AS (
  -- 按时间排序并添加行号，用于递归关联
  SELECT *, ROW_NUMBER() OVER (ORDER BY usage_at) AS rn 
  FROM sample_data
),
recursive_soc AS (
  -- 初始行：第一行的SoC从0开始计算并约束边界
  SELECT 
    usage_at,
    energy_in_out,
    GREATEST(0, LEAST(0 + (-energy_in_out), 200)) AS soc
  FROM sorted_data 
  WHERE rn = 1
  
  UNION ALL
  
  -- 递归计算后续每一行：依赖前一行的SoC结果
  SELECT 
    sd.usage_at,
    sd.energy_in_out,
    -- 先计算临时SoC，再用GREATEST/LEAST约束到0-200
    GREATEST(0, LEAST(rs.soc + (-sd.energy_in_out), 200)) AS soc
  FROM sorted_data sd
  JOIN recursive_soc rs ON sd.rn = rs.rn + 1
)
-- 输出与示例格式一致的结果
SELECT energy_in_out AS `Energy进出`, soc AS `电池SoC` 
FROM recursive_soc
ORDER BY usage_at;

代码说明 #

1. sample_data：模拟带时间戳的15分钟间隔数据，实际使用时替换为你的业务表（需包含时间字段和energy_in_out字段）
2. sorted_data：给数据按时间排序并添加行号，确保递归能按顺序逐行处理
3. recursive_soc：
   • 初始行：从SoC=0开始计算第一行的结果，用GREATEST(0, LEAST(...,200))强制约束上下限
   • 递归部分：每次关联前一行的SoC结果，计算当前临时SoC后再约束边界，完全符合逐行滚动的规则
4. 公式逻辑：rs.soc + (-sd.energy_in_out) 对应需求：负值发电时加电量，正值消耗时减电量

为什么窗口SUM不行？ #

普通窗口SUM(-energy_in_out) OVER (ORDER BY usage_at)只能计算累计电量变化，但无法处理中间的边界截断。比如前几行消耗导致SoC降到0后，后续的消耗不会再减少，但SUM会继续累计负数，最终结果不符合SoC不能低于0的约束。

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