如何按日期统计EmployeeId+TenantId的连续出现次数？

表结构 #

CREATE TABLE EmployeeHistory
(
   ID INTEGER PRIMARY KEY AUTOINCREMENT,
   CurrentRun TEXT,
   LastRun TEXT,
   EmployeeId TEXT,
   TenantId TEXT
);

样本数据 #

DELETE FROM EmployeeHistory;

INSERT INTO EmployeeHistory ('CurrentRun', 'LastRun', 'EmployeeId', 'TenantId')

VALUES

-- August 19th run
( '2023-08-19 00:00:00.000000', '2023-08-18 00:00:00.000000', '1', 'A' ), -- Consecutive! (Employee exists in August 18th run)
( '2023-08-19 00:00:00.000000', '2023-08-18 00:00:00.000000', '3', 'A' ), -- Should not be included because was not in August 18th run
( '2023-08-19 00:00:00.000000', '2023-08-18 00:00:00.000000', '1', 'B' ), -- Should not be included because was not in August 18th run
( '2023-08-19 00:00:00.000000', '2023-08-18 00:00:00.000000', '2', 'B' ), -- Consecutive! (Employee exists in August 18th run)
( '2023-08-19 00:00:00.000000', '2023-08-18 00:00:00.000000', '4', 'A' ), -- Consecutive! (Employee exists in all runs)
( '2023-08-19 00:00:00.000000', '2023-08-18 00:00:00.000000', '2', 'A' ), -- Consecutive! (Employee exists in August 18th run)

-- August 18th run
( '2023-08-18 00:00:00.000000', '2023-08-17 00:00:00.000000', '1', 'A' ), -- Consecutive (Employee exists in August 19th run)!
( '2023-08-18 00:00:00.000000', '2023-08-17 00:00:00.000000', '2', 'A' ), -- Consecutive (Employee exists in August 19th run)!
( '2023-08-18 00:00:00.000000', '2023-08-17 00:00:00.000000', '2', 'B' ), -- Consecutive (Employee exists in August 19th run)!
( '2023-08-18 00:00:00.000000', '2023-08-17 00:00:00.000000', '4', 'A' ), -- Consecutive! (Employee exists in all runs)

-- August 17th run
( '2023-08-17 00:00:00.000000', '2023-08-16 00:00:00.000000', '3', 'A' ), -- Should not be included because was not in August 18th run
( '2023-08-17 00:00:00.000000', '2023-08-16 00:00:00.000000', '5', 'A' ), -- Should not be included because was not in August 18th run
( '2023-08-17 00:00:00.000000', '2023-08-16 00:00:00.000000', '6', 'A' ), -- Should not be included because was not in August 18th run
( '2023-08-18 00:00:00.000000', '2023-08-17 00:00:00.000000', '4', 'A' ); -- Consecutive! (Employee exists in all runs)

当前数据输出 #

ID  CurrentRun                  LastRun                     EmployeeId  TenantId
1   2023-08-19 00:00:00.000000  2023-08-18 00:00:00.000000  1           A
2   2023-08-19 00:00:00.000000  2023-08-18 00:00:00.000000  3           A
3   2023-08-19 00:00:00.000000  2023-08-18 00:00:00.000000  1           B
4   2023-08-19 00:00:00.000000  2023-08-18 00:00:00.000000  2           B
5   2023-08-19 00:00:00.000000  2023-08-18 00:00:00.000000  4           A
6   2023-08-19 00:00:00.000000  2023-08-18 00:00:00.000000  2           A
7   2023-08-18 00:00:00.000000  2023-08-17 00:00:00.000000  1           A
8   2023-08-18 00:00:00.000000  2023-08-17 00:00:00.000000  2           A
9   2023-08-18 00:00:00.000000  2023-08-17 00:00:00.000000  2           B
10  2023-08-18 00:00:00.000000  2023-08-17 00:00:00.000000  4           A
11  2023-08-17 00:00:00.000000  2023-08-16 00:00:00.000000  3           A
12  2023-08-17 00:00:00.000000  2023-08-16 00:00:00.000000  5           A
13  2023-08-17 00:00:00.000000  2023-08-16 00:00:00.000000  6           A
14  2023-08-18 00:00:00.000000  2023-08-17 00:00:00.000000  4           A

需求说明 #

需要统计从指定日期（当前运行日）开始，EmployeeId + TenantId组合在连续运行中的出现次数，仅关注次数大于1的情况。应用在工作日运行并插入数据，每条记录包含当前运行时间（CurrentRun）和上一次运行时间（LastRun）。尝试过LEAD和LAG函数但未得到预期结果，想知道添加字段追踪连续出现次数，根据上一次运行记录递增统计值的方法是否可行？

关于添加字段的可行性 #

添加字段追踪连续次数的方法是可行的，但需要在每次插入数据时额外计算并更新该字段，维护成本较高。实际上无需修改表结构，通过窗口函数就能高效实现需求。

基于窗口函数的实现步骤 #

核心思路是：按EmployeeId+TenantId分组，按CurrentRun倒序排序，判断当前记录的LastRun是否等于上一条记录的CurrentRun（即是否连续），然后累计连续次数。

具体SQL查询 #

WITH ranked_runs AS (
    SELECT 
        EmployeeId,
        TenantId,
        CurrentRun,
        LastRun,
        -- 标记当前记录是否与上一条连续
        CASE 
            WHEN LAG(CurrentRun) OVER (PARTITION BY EmployeeId, TenantId ORDER BY CurrentRun DESC) = LastRun 
            THEN 1 
            ELSE 0 
        END AS is_consecutive,
        -- 生成连续分组的标识
        SUM(CASE 
            WHEN LAG(CurrentRun) OVER (PARTITION BY EmployeeId, TenantId ORDER BY CurrentRun DESC) = LastRun 
            THEN 0 
            ELSE 1 
        END) OVER (PARTITION BY EmployeeId, TenantId ORDER BY CurrentRun DESC) AS consecutive_group
    FROM EmployeeHistory
),
consecutive_counts AS (
    SELECT 
        EmployeeId,
        TenantId,
        COUNT(*) AS consecutive_occurrences,
        MAX(CurrentRun) AS latest_run_date
    FROM ranked_runs
    GROUP BY EmployeeId, TenantId, consecutive_group
)
SELECT 
    EmployeeId,
    TenantId,
    consecutive_occurrences,
    latest_run_date
FROM consecutive_counts
WHERE consecutive_occurrences > 1
ORDER BY latest_run_date DESC, EmployeeId, TenantId;

结果说明 #

该查询会返回所有连续出现次数大于1的EmployeeId+TenantId组合，以及它们的连续次数和最近一次运行日期。针对样本数据，预期输出如下：

EmployeeId  TenantId  consecutive_occurrences  latest_run_date
1           A         2                        2023-08-19 00:00:00.000000
2           A         2                        2023-08-19 00:00:00.000000
2           B         2                        2023-08-19 00:00:00.000000
4           A         3                        2023-08-19 00:00:00.000000

若坚持添加字段的实现方式 #

如果一定要通过添加字段来实现，可以新增ConsecutiveCount字段，类型为整数。每次插入数据时，先查询该EmployeeId+TenantId组合在上一次运行（即LastRun对应的日期）是否存在记录：

• 如果存在，则新记录的ConsecutiveCount = 上一条记录的ConsecutiveCount + 1
• 如果不存在，则ConsecutiveCount = 1

示例插入逻辑（以SQLite为例）：

-- 假设当前运行日期为'2023-08-20 00:00:00.000000'，上一次为'2023-08-19 00:00:00.000000'
INSERT INTO EmployeeHistory (CurrentRun, LastRun, EmployeeId, TenantId, ConsecutiveCount)
SELECT 
    '2023-08-20 00:00:00.000000',
    '2023-08-19 00:00:00.000000',
    '1',
    'A',
    COALESCE((SELECT ConsecutiveCount FROM EmployeeHistory WHERE EmployeeId='1' AND TenantId='A' AND CurrentRun='2023-08-19 00:00:00.000000'), 0) + 1;

之后只需查询ConsecutiveCount > 1的记录即可。但这种方式需要在插入时额外查询，且如果存在批量插入场景，逻辑会更复杂，不如窗口函数方案灵活高效。

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