如何按充电间隔窗口计算电池平均消耗速率？

数据集 #

Battery %   time    charging
90          t1      yes
91          t2      yes
95          t3      no
89          t4      no
87          t5      no
80          t6      no
78          t7      yes
85          t8      yes
50          t9      no
40          t10     no
38          t11     no
20          t12     yes

需求 #

需要计算电池消耗速率（battery depletion rate），公式为：电池变化量 / 耗时。要求针对所有处于两个"yes"充电状态之间的"no"放电时段窗口，分别计算每个窗口的消耗速率，最后求所有速率的平均值。

针对上述数据集的计算示例：

• 速率1 = (95 - 80) / (t6 - t3)
• 速率2 = (50 - 38) / (t11 - t9)
• 平均速率 = (速率1 + 速率2) / 2

注：数据中可能存在2个以上的独立放电窗口。

现有问题代码 #

select ((max(battery_Percentage) - min (battery_Percentage)) / NULLIF(Extract(epoch FROM (max(time) - min(time))/3600),0)) as rate_of_battery_decline   
from table 
where 
table.charging = 'no'

这段代码会把所有"no"状态的记录混在一起计算，无法区分两个充电状态之间的独立放电窗口，因此无法得到符合需求的结果。

关键是先把连续的"no"记录划分到同一个放电窗口，再对每个窗口单独计算速率，最后求平均值。可以通过窗口函数实现窗口划分：

完整SQL代码 #

WITH tagged_data AS (
    SELECT 
        "Battery %" AS battery_percentage,
        time,
        charging,
        -- 累计统计当前行之前的"yes"次数，以此作为放电窗口的唯一ID
        SUM(CASE WHEN charging = 'yes' THEN 1 ELSE 0 END) OVER (ORDER BY time) AS window_id
    FROM your_table_name
),
window_rates AS (
    SELECT 
        window_id,
        -- 单个窗口的电池下降量：窗口起始最高电量 - 窗口结束最低电量
        MAX(battery_percentage) - MIN(battery_percentage) AS battery_drop,
        -- 单个窗口的耗时：转换为小时单位
        EXTRACT(EPOCH FROM (MAX(time) - MIN(time))) / 3600 AS hours_elapsed,
        -- 计算当前窗口的电池消耗速率
        (MAX(battery_percentage) - MIN(battery_percentage)) / NULLIF(EXTRACT(EPOCH FROM (MAX(time) - MIN(time))) / 3600, 0) AS depletion_rate
    FROM tagged_data
    WHERE charging = 'no'
    GROUP BY window_id
)
-- 计算所有放电窗口速率的平均值
SELECT AVG(depletion_rate) AS average_depletion_rate
FROM window_rates;

代码说明 #

1. tagged_data 部分：通过SUM() OVER(ORDER BY time)累计统计充电状态为"yes"的次数，每出现一次"yes"，后续的"no"记录就会被分到新的窗口组，自动把两个"yes"之间的"no"划分为独立的放电窗口。
2. window_rates 部分：筛选出所有"no"记录，按窗口ID分组，计算每个窗口的电量下降值、耗时和消耗速率，用NULLIF避免出现除以0的错误。
3. 最后一步对所有窗口的消耗速率取平均，得到最终的平均电池消耗速率。

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