使用partition by计算电池耗尽速率结果异常，求排查修正方案

数据集 #

battery_pct   tstamp    charging    phone_id
90            t1        yes         12
91            t2        yes         22
95            t3        no          22 
89            t4        no          22
87            t5        no          22
80            t6        no          22
78            t7        yes         22
85            t8        yes         4
50            t9        no          4
40            t10       no          4
38            t11       no          4
20            t12       yes         4

需求 #

计算所有处于两次“yes”充电状态之间的“no”充电时间段的电池耗尽速率（电池变化量/耗时），再求这些速率的平均值（覆盖所有phone_id）：

• 示例计算逻辑：
  • phone_id 22的速率=(95-80)/(t6-t3)
  • phone_id 4的速率=(50-38)/(t11-t9)
  • 平均速率=(速率1+速率2)/2
• 注意：单设备可能存在多个“no”时间段。

现有问题代码 #

当前SQL代码无报错，但返回结果不符合预期：

with discharge_intervals as (
  select battery_pct, tstamp, 
         sum((charging = 'yes')::int) over (partition by phone_id order by tstamp) as ival_number,
         charging = 'no' as keep
    from dataset
), interval_rates as (
  select ival_number, 
         (max(battery_pct) - min(battery_pct)) 
           / extract(epoch from max(tstamp) - min(tstamp)) as ival_rate
    from discharge_intervals
   where keep
   group by ival_number
)
select avg(ival_rate) 
  from interval_rates;

1. 区间分组维度缺失：ival_number按phone_id分区生成，但后续仅按ival_number分组，会导致不同设备的同序号区间被合并，完全打乱设备独立的放电区间。
2. 电量计算逻辑错误：用max(battery_pct)-min(battery_pct)计算电量变化，若区间内电量有波动（如临时回升），会得到错误的变化值；需求明确要求取区间起始（第一次no）和结束（最后一次no）的电量差。
3. 区间标记逻辑隐患：虽然用sum((charging='yes')::int)标记区间，但未确保仅针对放电阶段的记录做正确分组，容易混入非目标区间的计算。

with discharge_intervals as (
  select 
    phone_id,
    battery_pct,
    tstamp,
    charging,
    -- 按设备分区，累加充电事件次数，标记每个放电区间
    sum((charging = 'yes')::int) over (partition by phone_id order by tstamp) as discharge_group
  from dataset
),
interval_stats as (
  select
    phone_id,
    discharge_group,
    -- 取放电区间的起始电量（第一次no的电量）
    first_value(battery_pct) over (partition by phone_id, discharge_group order by tstamp) as start_battery,
    -- 取放电区间的结束电量（最后一次no的电量）
    last_value(battery_pct) over (partition by phone_id, discharge_group order by tstamp rows between unbounded preceding and unbounded following) as end_battery,
    min(tstamp) as start_time,
    max(tstamp) as end_time
  from discharge_intervals
  where charging = 'no' -- 仅保留放电阶段记录
  group by phone_id, discharge_group, battery_pct, tstamp
),
interval_rates as (
  select
    distinct phone_id,
    discharge_group,
    -- 计算耗尽速率：(起始电量-结束电量)/时间差（秒）
    (start_battery - end_battery) / extract(epoch from end_time - start_time) as discharge_rate
  from interval_stats
)
select avg(discharge_rate) as average_discharge_rate
from interval_rates;

代码说明 #

1. 区间分组修正：discharge_group绑定phone_id，确保每个设备的放电区间独立分组，不会跨设备合并。
2. 电量取值修正：用first_value和last_value精准获取放电区间的起始、结束电量，完全匹配需求中的计算逻辑。
3. 过滤逻辑明确：仅保留charging='no'的记录，聚焦于两次充电之间的放电阶段。
4. 去重处理：用distinct确保每个设备的每个放电区间只生成一条速率记录，避免重复计算。

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