SQL分组过滤查询：筛选连接状态发生变更的Site_ID

现有一张分钟级粒度记录站点连接状态的业务大表，总数据量数百万级，表结构如下：

需要筛选出连接状态发生过变更（即同时存在True、False两种状态记录）的站点，输出格式为站点ID+固定值True代表存在状态变更。

优先选择单次聚合的写法，执行效率最高，不需要额外的子查询、窗口函数计算，只要给Site_ID字段建好索引，百万级数据可以秒级返回结果：

SELECT
  Site_ID,
  TRUE AS Connected_status_change
FROM 你的业务表名 -- 替换成实际的表名
GROUP BY Site_ID
HAVING MIN(Connected_status) != MAX(Connected_status);

逻辑说明 #

• Connected_status是布尔类型，只有True、False两个取值，布尔排序规则下False < True
• 按Site_ID分组后，如果同一个站点下该字段的最小值和最大值不相等，就说明两种状态都存在，也就是发生过状态变更
• 聚合计算时数据库只需要遍历一次数据，分组过程中直接记录每个分组的最值即可，不需要做去重、排序等额外操作，资源消耗最低

通用兼容写法 #

如果后续Connected_status可能新增其他状态值，也可以用去重计数的写法，逻辑是同一个站点下状态值种类数为2就代表发生过True/False的切换：

SELECT
  Site_ID,
  TRUE AS Connected_status_change
FROM 你的业务表名
GROUP BY Site_ID
HAVING COUNT(DISTINCT Connected_status) = 2;

注意：不要用多层EXISTS子查询、窗口函数逐行对比前后状态的写法，这类写法在大表场景下会生成大量临时计算结果，执行速度比上面的纯聚合写法慢3~10倍，很容易出现查询超时的问题。

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