如何在无电池ID时用SQL聚合日志并识别单电池数据

阿华AIGC实验室

2026-5-7

如何用纯SQL识别每个独立电池的生命周期数据

当然可以用纯SQL解决这个问题！核心思路是识别同一插槽内连续的非null百分比区间——每次出现null就意味着电池被更换，后续的非null记录属于新的电池实例。我们可以用窗口函数给每个连续的电池使用周期分配唯一的组ID，再基于这个组统计所需的指标。

1. 先理清数据中的电池更换信号

插槽0的null出现在2020-05-13和2020-05-16，这两个日期是电池更换的节点：
2020-05-08至2020-05-12：第一块电池（百分比从96变为97）
2020-05-14至2020-05-15：第二块电池（95变为96）
2020-05-17至2020-05-18：第三块电池（1变为2）
插槽1没有null，所以全程是同一块电池。

2. 用窗口函数生成电池组ID

我们可以通过累计统计每个插槽内null的数量，给每个连续的非null记录分配组ID——每出现一次null，后续的非null记录就会归属到新的组：

WITH battery_groups AS (
    SELECT
        slot,
        day,
        percentage,
        -- 累计当前插槽中已出现的null次数，作为电池组的唯一标识
        SUM(CASE WHEN percentage IS NULL THEN 1 ELSE 0 END) 
            OVER (PARTITION BY slot ORDER BY day) AS battery_group_id
    FROM batteries
    -- 过滤掉null行，这些是更换节点，不属于任何电池的使用记录
    WHERE percentage IS NOT NULL
)

这个CTE会给每个连续的电池使用周期分配唯一的battery_group_id，比如插槽0的第一组是0，第二组是1，第三组是2。

3. 统计每个电池的生命周期指标

基于上面的组ID，我们可以分组计算每个电池的首次报告日期、末次报告日期和最后一次的百分比：

WITH battery_groups AS (
    SELECT
        slot,
        day,
        percentage,
        SUM(CASE WHEN percentage IS NULL THEN 1 ELSE 0 END) 
            OVER (PARTITION BY slot ORDER BY day) AS battery_group_id
    FROM batteries
    WHERE percentage IS NOT NULL
)
SELECT
    slot,
    battery_group_id AS battery_id, -- 用slot+group_id作为电池的唯一标识
    MIN(day) AS first_report_date,
    MAX(day) AS last_report_date,
    -- 获取该组最后一天的百分比
    LAST_VALUE(percentage) OVER (
        PARTITION BY slot, battery_group_id 
        ORDER BY day 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS final_percentage
FROM battery_groups
GROUP BY slot, battery_group_id
ORDER BY slot, battery_group_id;