如何统计同ID下Room 1连续出现的记录次数？

嘿，我来帮你搞定这个问题！要统计同一ID下Room 1连续出现的记录次数，核心思路是先把连续的Room 1记录归为同一组，再对每组计数就行。我用SQL来给你演示具体实现，假设你的数据表叫room_records，包含字段：id（用户/实体ID）、room（房间号）、record_time（记录生成时间，用来确定记录的先后顺序）。

步骤1：标记连续组的起始点 #

首先筛选出所有Room 1的记录，然后用窗口函数LAG()获取每条记录的上一条房间号。如果上一条不是Room 1（或者是该ID的第一条记录），那就说明当前是一个新的连续组的开始，标记为1：

WITH room1_records AS (
    SELECT 
        id,
        room,
        record_time,
        CASE 
            WHEN LAG(room) OVER (PARTITION BY id ORDER BY record_time) != 'Room 1' OR LAG(room) IS NULL THEN 1
            ELSE 0
        END AS is_new_group
    FROM room_records
    WHERE room = 'Room 1'
)

步骤2：给连续组分配唯一ID #

通过对is_new_group做累计求和，就能给每个连续组分配一个唯一的组ID——每遇到一个新组起始点，求和结果就会加1，这样同一连续组的记录会拥有相同的组ID：

, grouped_records AS (
    SELECT 
        id,
        room,
        record_time,
        SUM(is_new_group) OVER (PARTITION BY id ORDER BY record_time) AS group_id
    FROM room1_records
)

步骤3：统计每个连续组的次数 #

最后按id和group_id分组，用COUNT(*)统计每组的记录数，就是该组连续出现Room 1的次数：

SELECT 
    id,
    group_id,
    COUNT(*) AS consecutive_room1_count
FROM grouped_records
GROUP BY id, group_id
ORDER BY id, group_id;

特殊需求：获取每个ID的最长连续次数 #

如果你的需求是每个ID下最长的连续Room 1次数，可以在上面的基础上再加一层统计，取每个ID的最大连续次数：

WITH room1_records AS (
    SELECT 
        id,
        room,
        record_time,
        CASE 
            WHEN LAG(room) OVER (PARTITION BY id ORDER BY record_time) != 'Room 1' OR LAG(room) IS NULL THEN 1
            ELSE 0
        END AS is_new_group
    FROM room_records
    WHERE room = 'Room 1'
),
grouped_records AS (
    SELECT 
        id,
        SUM(is_new_group) OVER (PARTITION BY id ORDER BY record_time) AS group_id
    FROM room1_records
),
counted_groups AS (
    SELECT 
        id,
        COUNT(*) AS consecutive_room1_count
    FROM grouped_records
    GROUP BY id, group_id
)
SELECT 
    id,
    MAX(consecutive_room1_count) AS max_consecutive_room1
FROM counted_groups
GROUP BY id;

小提示 #

如果你的表没有record_time，但有自增主键（比如record_id），直接把代码里的record_time换成record_id就行——只要能确定记录先后顺序的字段都可以用。

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