基于状态变化的Hive SQL查询：统计车辆充电结束次数

问题描述 #

现有车辆充电状态记录表，字段说明：

• vehicle：车辆ID
• record_time：记录时间
• charging_state：充电状态（0表示充电中）

需要统计每辆车的充电结束次数，统计规则：

对于同一辆车，当某条数据的前一条数据charging_state == 0，且**后一条数据charging_state != 0**时，视为一次充电结束。

原始数据如下：

预期统计结果：

解决方案 #

方法1：SQL实现

按车辆分组、记录时间排序，用窗口函数LAG()获取前一条充电状态，LEAD()获取后一条充电状态，筛选符合条件的记录并计数：

WITH ordered_data AS (
    SELECT 
        vehicle,
        charging_state,
        LAG(charging_state) OVER (PARTITION BY vehicle ORDER BY record_time) AS prev_state,
        LEAD(charging_state) OVER (PARTITION BY vehicle ORDER BY record_time) AS next_state
    FROM your_table_name
)
SELECT 
    vehicle,
    COUNT(*) AS count
FROM ordered_data
WHERE prev_state = 0 AND next_state != 0
GROUP BY vehicle
ORDER BY vehicle;

方法2：Python（Pandas）实现

先按车辆分组排序，计算前后状态后筛选统计：

import pandas as pd

# 假设数据已存入DataFrame df
df_sorted = df.sort_values(['vehicle', 'record_time'])

# 计算前后状态
df_sorted['prev_state'] = df_sorted.groupby('vehicle')['charging_state'].shift(1)
df_sorted['next_state'] = df_sorted.groupby('vehicle')['charging_state'].shift(-1)

# 统计符合条件的次数
result = df_sorted[(df_sorted['prev_state'] == 0) & (df_sorted['next_state'] != 0)] \
    .groupby('vehicle') \
    .size() \
    .reset_index(name='count')

print(result)

结果验证 #

两种方法都会得到与预期一致的结果：

• TEST0000000000001：1次（对应15:57:47.0的0状态→16:02:48.0的3状态）
• 12100000000000002：1次（对应15:40:34.0的0状态→15:41:50.0的3状态）
• TEST0000000000002：3次（分别对应15:57:39.0→16:02:41.0、16:35:02.0→16:35:08.0、17:14:27.0→17:18:58.0三组状态变化）

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