使用SQL窗口函数和日期差计算统计重复事件

使用SQL窗口函数和日期差计算统计重复事件

目的

本文档旨在指导如何通过SQL查询统计在特定时间范围内发生多次事件的记录。此查询方法适用于包含日期记录的各种数据库表，可以用于多种场景，例如统计重复入院的患者、计算频繁购买的客户、检测短时间内重复访问的用户等。

表结构假设

假设我们有一张名为 events 的表，包含以下字段：

• id：记录唯一标识
• entity_id：事件主体的唯一标识（如患者的身份证号、客户ID等）
• event_date：事件发生日期

解决方案概述

我们将使用SQL查询来识别在特定时间范围内多次发生事件的记录。查询的主要步骤包括：

1. 使用窗口函数 LAG 获取每个主体的上一次事件日期。
2. 计算每次事件和上一次事件之间的天数差。
3. 筛选出天数差小于等于指定时间范围的记录。
4. 对符合条件的记录进行分组和计数，找出在特定时间范围内多次发生事件的主体。

SQL查询详解

1. 获取上一次事件日期

首先，使用 LAG 窗口函数获取每个主体的上一次事件日期：

WITH events_with_lag AS (
    SELECT
        entity_id,
        event_date,
        LAG(event_date) OVER (PARTITION BY entity_id ORDER BY event_date) AS previous_event_date
    FROM
        events
)

• LAG(event_date)：获取当前行的 event_date 列的前一个值。
• OVER (PARTITION BY entity_id ORDER BY event_date)：按照 entity_id 分区，并在每个分区内按 event_date 排序。

2. 计算天数差并筛选记录

接下来，计算每次事件和上一次事件之间的天数差，并筛选出天数差小于等于指定时间范围的记录（以30天为例）：

, filtered_events AS (
    SELECT
        entity_id,
        event_date,
        previous_event_date,
        DATEDIFF(event_date, previous_event_date) AS days_between_events
    FROM
        events_with_lag
    WHERE
        previous_event_date IS NOT NULL
        AND DATEDIFF(event_date, previous_event_date) <= 30
)

• DATEDIFF(event_date, previous_event_date)：计算两次事件日期之间的天数差。
• previous_event_date IS NOT NULL：排除第一次事件的记录，因为它们没有上一次事件日期。
• DATEDIFF(event_date, previous_event_date) <= 30：筛选出天数差小于等于30天的记录。

3. 分组和计数

最后，对符合条件的记录进行分组和计数，找出在特定时间范围内多次发生事件的主体：

SELECT
    entity_id,
    COUNT(*) AS event_count
FROM
    filtered_events
GROUP BY
    entity_id
HAVING
    COUNT(*) >= 1;

• GROUP BY entity_id：按 entity_id 分组。
• COUNT(*) AS event_count：计算每个主体符合条件的事件记录数。
• HAVING COUNT(*) >= 1：筛选出在特定时间范围内有两次或以上事件记录的主体。

完整SQL查询

将上述步骤组合起来，得到完整的SQL查询：

WITH events_with_lag AS (
    SELECT
        entity_id,
        event_date,
        LAG(event_date) OVER (PARTITION BY entity_id ORDER BY event_date) AS previous_event_date
    FROM
        events
),
filtered_events AS (
    SELECT
        entity_id,
        event_date,
        previous_event_date,
        DATEDIFF(event_date, previous_event_date) AS days_between_events
    FROM
        events_with_lag
    WHERE
        previous_event_date IS NOT NULL
        AND DATEDIFF(event_date, previous_event_date) <= 30
)
SELECT
    entity_id,
    COUNT(*) AS event_count
FROM
    filtered_events
GROUP BY
    entity_id
HAVING
    COUNT(*) >= 1;

示例数据和结果

假设有以下事件数据：

entity_id	event_date
123	2024-01-01
123	2024-01-15
123	2024-02-10
456	2024-01-05
456	2024-01-20

查询结果将是：

entity_id	event_count
123	2
456	1

此结果表示 entity_id 为123的主体在一个月内有2次事件记录，entity_id 为456的主体在一个月内有1次事件记录。

结论

通过使用窗口函数 LAG 和日期差计算，我们可以有效地统计出在特定时间范围内多次发生事件的主体。此方法不仅高效，还能避免复杂的自联接操作。根据实际需求，可以进一步调整和优化查询，以适应不同场景的需求。