如何用SQL统计售货机门开关完整事件的次数？

你的原代码完全没踩中需求点——group by Date Time会把每条时间记录拆成单独分组，既没处理传感器重复上传的连续同状态数据，也没统计有效开关事件的次数。

要实现需求，得按这三步来：

1. 给每台售货机的状态记录匹配上一条的状态，识别状态变化
2. 只保留从opened变closed的有效事件
3. 按地区、月份分组统计次数

直接上可用的SQL（注意字段名带空格的话，不同数据库要用不同符号包裹，比如MySQL用反引号`，SQL Server用方括号[]）：

-- 第一步：生成带前序状态的数据集
WITH status_with_prev AS (
    SELECT 
        `Machine ID`,
        `Region`,
        `Date Time`,
        `Door Status`,
        -- 按机器分组、时间排序，取上一条的门状态
        LAG(`Door Status`) OVER (PARTITION BY `Machine ID` ORDER BY `Date Time`) AS prev_status
    FROM myfile
    WHERE `Region` = 'City A'
),
-- 第二步：筛选有效的开关事件（从开变关）
valid_switch_events AS (
    SELECT *
    FROM status_with_prev
    WHERE `Door Status` = 'closed' AND prev_status = 'opened'
)
-- 第三步：按地区、月份统计次数
SELECT
    `Region`,
    DATE_FORMAT(`Date Time`, '%Y-%m') AS stat_month, -- MySQL的日期格式化，其他数据库看下面说明
    COUNT(*) AS switch_event_count
FROM valid_switch_events
GROUP BY `Region`, stat_month
ORDER BY stat_month;

不同数据库的日期格式化函数要调整：

• SQL Server：把DATE_FORMAT换成FORMAT(Date Time, 'yyyy-MM')
• PostgreSQL：换成TO_CHAR(Date Time, 'YYYY-MM')

如果你的字段名不带空格（比如MachineID而不是Machine ID），可以去掉所有反引号/方括号，代码更简洁。

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