如何编写SQL查询验证数据库中是否存在连续6个周日的记录

现有查询说明 #

目前已写出的查询语句可用于获取指定时间段内指定用户的所有周日记录：

SELECT DISTINCT ST1.DATAPU, ST1.NUMCAD, TO_CHAR(ST1.DATAPU, 'DAY') AS DIA
  FROM SENIOR.R066SIT ST1
 WHERE ST1.DATAPU BETWEEN '01/01/22' AND '23/11/22'
   AND ST1.NUMCAD = 10
   AND TO_CHAR(ST1.DATAPU, 'FMDAY') = 'DOMINGO' -- 西班牙语的“周日”
 ORDER BY ST1.DATAPU ASC

执行该查询后得到的结果为按日期升序排列的周日记录，包含的日期有02/01/22、09/01/22、16/01/22、23/01/22、30/01/22、06/02/22等。

实现连续6个周日验证的查询方案 #

要判断是否存在连续6个周日的记录，可通过窗口函数实现，以下提供两种可行方法：

方法1：基于分组统计连续序列长度 #

WITH sunday_records AS (
    SELECT DATAPU, NUMCAD,
           ROW_NUMBER() OVER (PARTITION BY NUMCAD ORDER BY DATAPU) AS rn
    FROM SENIOR.R066SIT
    WHERE TO_CHAR(DATAPU, 'FMDAY') = 'DOMINGO'
      AND DATAPU BETWEEN '01/01/22' AND '23/11/22'
      AND NUMCAD = 10
),
continuous_groups AS (
    SELECT DATAPU, NUMCAD,
           -- 同一连续周日序列的基准日期一致
           DATAPU - (rn - 1)*7 AS group_key
    FROM sunday_records
)
SELECT NUMCAD, group_key, COUNT(*) AS consecutive_count
FROM continuous_groups
GROUP BY NUMCAD, group_key
HAVING COUNT(*) >= 6;

若查询返回结果，说明存在至少连续6个周日的记录；无返回结果则不存在。

方法2：通过LAG函数直接检查间隔 #

WITH sunday_records AS (
    SELECT DATAPU, NUMCAD,
           -- 获取当前记录往前数第5个周日的日期
           LAG(DATAPU, 5) OVER (PARTITION BY NUMCAD ORDER BY DATAPU) AS prev_5th_sunday
    FROM SENIOR.R066SIT
    WHERE TO_CHAR(DATAPU, 'FMDAY') = 'DOMINGO'
      AND DATAPU BETWEEN '01/01/22' AND '23/11/22'
      AND NUMCAD = 10
)
SELECT DISTINCT NUMCAD
FROM sunday_records
WHERE DATAPU - prev_5th_sunday = 35; -- 5周间隔为35天，证明中间存在连续6个周日

该查询会直接返回存在连续6个周日记录的用户ID（此处为10），有结果即符合条件。

补充说明 #

• 以上语句基于Oracle语法编写（从日期函数和运算逻辑判断）
• 使用FMDAY是为了去除日期格式中的空格，保证匹配精度
• 可根据实际需求修改时间段、用户ID等参数

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