统计分组列中event=1与event=2之间值为3的记录数量

核心思路 #

先给每个id下的每一组「event=1到event=2」的区间打上唯一分组标签，再基于标签统计区间内event=3的数量，同时保留同一id的多组结果。

实现SQL（通用窗口函数版本） #

假设表名为event_log，以下代码适用于支持窗口函数的数据库（如PostgreSQL、MySQL 8.0+、SQL Server等）：

WITH grouped_events AS (
    -- 给每个id下的event=1起始区间打分组编号
    SELECT 
        datetime,
        id,
        event,
        -- 每遇到一个event=1，分组编号+1，同一个区间内编号保持一致
        SUM(CASE WHEN event = 1 THEN 1 ELSE 0 END) OVER (
            PARTITION BY id 
            ORDER BY datetime 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS group_id
    FROM event_log
),
valid_groups AS (
    -- 筛选出存在event=2的有效分组，只保留区间内的记录（1之后，2之前）
    SELECT 
        ge.id,
        ge.group_id,
        ge.event
    FROM grouped_events ge
    JOIN (
        -- 先找出每个分组中是否存在event=2，确保是完整的1-2区间
        SELECT id, group_id
        FROM grouped_events
        WHERE event = 2
    ) g2 ON ge.id = g2.id AND ge.group_id = g2.group_id
    WHERE 
        -- 只保留分组内event=1之后的记录
        EXISTS (
            SELECT 1 FROM grouped_events ge1
            WHERE ge1.id = ge.id AND ge1.group_id = ge.group_id AND ge1.event = 1 AND ge1.datetime <= ge.datetime
        )
        -- 只保留分组内event=2之前的记录
        AND ge.datetime <= (
            SELECT MIN(datetime) FROM grouped_events ge2
            WHERE ge2.id = ge.id AND ge2.group_id = ge.group_id AND ge2.event = 2
        )
)
-- 统计每个分组内的event=3数量
SELECT 
    id,
    group_id,
    COUNT(CASE WHEN event = 3 THEN 1 END) AS count_event_3
FROM valid_groups
GROUP BY id, group_id
ORDER BY id, group_id;

执行结果 #

针对你提供的测试数据，执行后会得到如下结果：

逻辑说明 #

1. 分组编号生成：利用SUM() OVER()窗口函数，给每个id下的每一个event=1起始点分配递增的分组编号，确保同一1-2区间内的所有记录共享同一个编号。
2. 有效区间筛选：通过子查询过滤掉没有对应event=2的分组，同时只保留每个分组内从event=1到event=2之间的记录，排除无关的event（比如event=4）以及区间外的记录。
3. 统计目标值：最后按id和group_id分组，统计每组内event=3的数量。

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