LAG函数需用PARTITION BY device_id ORDER BY event_time获取上一行在线状态，状态翻转用IS DISTINCT FROM比较并标记1，统计前须去重、校验数据类型及时区。
LAG函数怎么拿到上一行的在线状态
核心是用 LAG() 按设备ID和时间排序后，把前一条记录的 is_online 值拉下来。必须显式指定 ORDER BY，否则结果不可靠；如果漏掉 PARTITION BY device_id，不同设备的数据会混在一起比对。
常见错误是写成 LAG(is_online) 却没加 OVER 子句，直接报错 Window function requires OVER clause。正确写法：
LAG(is_online) OVER (PARTITION BY device_id ORDER BY event_time)
注意：event_time 必须能唯一排序（建议用带毫秒的时间戳），否则同秒内多条记录会导致 LAG() 返回顺序不确定。
怎么判断“状态翻转”并标记为1
翻转 = 当前行状态 ≠ 上一行状态。但第一行没有“上一行”，LAG() 返回 NULL，直接用 != 会得到 NULL（不是 TRUE），所以得用 IS DISTINCT FROM 或显式处理 NULL。
推荐写法（兼容 PostgreSQL / SQL Server / DuckDB）：
CASE WHEN is_online IS DISTINCT FROM LAG(is_online) OVER (PARTITION BY device_id ORDER BY event_time) THEN 1 ELSE 0 END AS flip_flag
如果只用 MySQL 8.0+，它不支持 IS DISTINCT FROM，就得拆开判断：
-
LAG(is_online) IS NULL→ 第一行，算一次翻转（比如设备首次上线） -
is_online != LAG(is_online)→ 后续行中状态变化
别用 is_online != COALESCE(LAG(is_online), NOT is_online) 这类取巧写法——逻辑难懂，且在 is_online 为 NULL 时行为异常。
统计每个设备的翻转总次数要注意什么
翻转标记（flip_flag）生成后，不能直接 SUM(flip_flag) GROUP BY device_id 就完事。因为原始表可能含脏数据：重复上报、乱序时间、测试数据等。
实操建议：
- 先用
ROW_NUMBER() OVER (PARTITION BY device_id ORDER BY event_time)排序去重，剔除ROW_NUMBER > 1 AND event_time相同的冗余行 - 确保
is_online是布尔或整型（0/1），避免字符串如'true'导致比较失效 - 如果设备长时间离线，中间无上报，
LAG()只能捕获“有记录”的翻转，不会补全“隐式下线”。这是设计使然，不是bug
最终聚合语句示例：
SELECT device_id, SUM(flip_flag) AS total_flips
FROM (
SELECT device_id, is_online,
CASE WHEN is_online IS DISTINCT FROM LAG(is_online)
OVER (PARTITION BY device_id ORDER BY event_time)
THEN 1 ELSE 0 END AS flip_flag
FROM device_events
WHERE event_time >= '2024-01-01'
) t
GROUP BY device_id;
为什么GROUP BY后翻转数比预期少
最常被忽略的是时间精度和时区。比如日志里 event_time 是字符串 '2024-01-01 10:30:00'，没带时区，而数据库默认用 UTC 解析，导致同一设备的两条记录被分到不同“逻辑分钟”，PARTITION BY 没问题，但 ORDER BY 错了顺序。
检查步骤：
- 用
SELECT device_id, event_time, is_online, LAG(is_online) OVER (...) FROM ... LIMIT 10看原始翻转标记是否符合肉眼判断 - 确认
event_time列类型是TIMESTAMP WITH TIME ZONE（PostgreSQL）或DATETIME2（SQL Server），不是TEXT或DATE - 如果上游是 IoT 平台推送，注意部分设备可能用本地时间打点，需统一转换
还有一个隐蔽点：某些数据库（如 Trino）对 LAG() 的 IGNORE NULLS 不支持，默认跳过 NULL 值——但你根本没传 NULL，只是没意识到 LAG() 在分区首行必然返回 NULL。这点必须心里有数，别误以为是数据丢了。
