基于分组内前一行记录筛选指定日期范围内的记录

问题要求 #

从给定数据集中筛选所有满足以下条件的记录：同一客户的当前记录的BEGIN_DATE，处于该客户按日期排序的前一条记录的END_DATE的30天范围内。需逐行比较同一客户的第1行与第2行（若存在）、第2行与第3行（若存在），以此类推。

原始数据集 #

CLIENTID      BEGIN_DATE          END_DATE
  14  2016-12-07 11:00:00.0  2017-04-06 09:28:00.0
  14  2017-04-07 06:00:00.0  2017-11-30 09:46:00.0
  22  2016-04-20 03:45:00.0  2017-11-30 09:47:00.0
  25  2016-07-25 04:00:00.0  2017-11-30 09:46:00.0
 121  2017-08-23 07:00:00.0  2017-11-30 09:45:00.0
2004  2017-12-31 08:58:00.0  2018-01-05 18:35:00.0
2004  2018-04-04 00:40:00.0  2018-04-11 18:29:00.0
2004  2018-05-11 03:37:00.0  2018-05-18 14:40:00.0
2004  2018-05-31 10:22:00.0  2018-06-06 09:25:00.0
2004  2018-08-26 06:27:00.0  2018-09-03 13:00:00.0
2034  2020-03-23 23:54:00.0  2020-04-01 13:29:00.0
2046  2018-01-10 07:21:00.0  2018-01-16 14:42:00.0
2072  2021-12-20 12:08:00.0  2021-12-23 13:22:00.0
2080  2019-01-04 12:55:00.0  2019-01-08 13:43:00.0
2082  2019-06-05 01:43:00.0  2019-06-10 16:15:00.0
2091  2018-02-16 10:28:00.0  2018-02-21 18:11:00.0
2091  2018-05-23 09:55:00.0  2018-05-31 13:37:00.0
2113  2018-10-03 08:10:00.0  2018-10-08 13:13:00.0
2148  2022-11-29 18:23:00.0  2022-12-13 10:10:00.0
2148  2023-02-14 17:08:00.0  2023-02-14 17:09:00.0
2148  2023-02-14 20:41:00.0  2023-02-24 11:11:00.0
2148  2023-06-24 08:05:00.0  2023-08-03 11:19:00.0
2148  2023-12-21 08:59:00.0  2023-12-30 11:05:00.0
2149  2018-03-07 05:28:00.0  2018-04-11 09:44:00.0
2154  2018-07-16 04:12:00.0  2018-08-07 16:33:00.0
2154  2018-09-03 02:14:00.0  2018-09-11 20:42:00.0
2154  2018-10-16 12:47:00.0  2019-05-30 11:21:00.0
2190  2018-02-06 01:40:00.0  2018-02-15 11:50:00.0
2195  2021-08-11 14:58:00.0  2021-08-17 09:52:00.0
2197  2018-02-19 09:47:00.0  2018-02-23 08:04:00.0
2197  2018-07-18 05:38:00.0  2018-07-25 11:33:00.0
2197  2018-10-04 03:00:00.0  2018-10-15 12:18:00.0
2197  2019-01-27 04:31:00.0  2019-01-31 09:36:00.0
2198  2018-02-09 02:57:00.0  2018-02-21 13:01:00.0
2227  2018-06-11 01:21:00.0  2018-06-14 11:57:00.0
2227  2019-10-29 14:04:00.0  2019-11-04 16:32:00.0
2282  2021-02-11 18:53:00.0  2021-03-04 10:23:00.0
2306  2020-01-28 12:57:00.0  2020-02-03 19:15:00.0
2315  2019-05-22 17:37:00.0  2019-05-23 19:30:00.0
2324  2018-07-25 01:35:00.0  2018-07-31 12:51:00.0
2330  2020-01-13 15:55:00.0  2020-01-21 18:27:00.0
2330  2020-03-04 23:40:00.0  2020-03-10 17:46:00.0
2356  2018-06-08 03:59:00.0  2018-06-15 12:38:00.0
2415  2017-12-18 07:06:00.0  2017-12-21 17:00:00.0
2423  2018-10-10 01:14:00.0  2018-10-17 15:46:00.0
2423  2019-09-16 21:53:00.0  2019-09-19 13:55:00.0
2460  2023-11-13 04:17:00.0  2023-11-16 11:17:00.0
2469  2019-09-14 09:36:00.0  2019-09-20 15:19:00.0
2497  2019-05-14 23:27:00.0  2019-05-22 11:13:00.0
2531  2017-12-26 07:46:00.0  2018-01-03 19:53:00.0
2531  2018-05-07 06:02:00.0  2018-05-14 20:54:00.0
2533  2019-02-18 19:43:00.0  2019-02-28 15:23:00.0
2533  2019-04-30 20:34:00.0  2019-09-12 08:29:00.0
2567  2018-01-25 01:49:00.0  2018-03-06 08:59:00.0
2567  2018-11-06 10:57:00.0  2018-11-17 13:22:00.0
2567  2018-12-23 10:38:00.0  2019-01-09 15:54:00.0
2567  2019-04-19 20:22:00.0  2019-07-06 16:18:00.0
2585  2018-01-02 11:35:00.0  2018-01-12 11:50:00.0

解决方法 #

使用SQL窗口函数LAG()可高效实现需求，步骤如下：

1. 按客户ID分组、按开始日期排序，获取每条记录的前一条记录的结束日期
2. 筛选当前记录的开始日期在前一条记录结束日期30天范围内的记录

示例SQL代码（时间函数需根据数据库调整，此为通用版本）：

WITH sorted_records AS (
    SELECT 
        CLIENTID,
        BEGIN_DATE,
        END_DATE,
        LAG(END_DATE) OVER (PARTITION BY CLIENTID ORDER BY BEGIN_DATE) AS prev_end_date
    FROM your_table_name
)
SELECT 
    CLIENTID,
    BEGIN_DATE,
    END_DATE
FROM sorted_records
WHERE 
    prev_end_date IS NOT NULL
    AND BEGIN_DATE <= DATEADD(day, 30, prev_end_date);

筛选结果 #

逐行比对后，满足条件的记录如下：

CLIENTID      BEGIN_DATE          END_DATE
  14  2017-04-07 06:00:00.0  2017-11-30 09:46:00.0
2004  2018-05-11 03:37:00.0  2018-05-18 14:40:00.0
2004  2018-05-31 10:22:00.0  2018-06-06 09:25:00.0
2148  2023-02-14 20:41:00.0  2023-02-24 11:11:00.0
2154  2018-09-03 02:14:00.0  2018-09-11 20:42:00.0

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