如何在SQL中统计连续工作日并筛选14天及以上的会员？

需求说明：统计会员的连续工作日（仅计入周一至周五，周末不计入连续序列中断），最终筛选出连续工作日天数≥14天的会员。

数据集示例 #

注：会员1的前3条记录属于连续工作日（周五→周一→周二，周末不计入中断），但2/17/23的记录需重新计数，因为和上一条记录间隔了周末+周三周四，不属于连续序列。

初始尝试代码 #

WITH pre AS
(
    SELECT 
        member, date, 
        DATE_ADD(date, - ROW_NUMBER() OVER (ORDER BY member, date)) DateGroup
    FROM 
        dataset1
    GROUP BY 
        member, date
),
pre2 as
(
    SELECT 
        MIN(u.date) startdt,
        MAX(u.date) enddt, 
        a.DateGroup, 
        DATEDIFF(MAX(u.date), MIN(u.date)) + 1 Consecutive_days, 
        u.date, u.member
    FROM 
        pre a 
    JOIN 
        dataset1 u ON u.date = a.date AND u.member = a.member
    GROUP BY 
        DateGroup, u.member
    ORDER BY 
        startdt
)
SELECT 
    startdt, enddt, Consecutive_days, member 
FROM 
    PRE2 
WHERE 
    Consecutive_days >= 14; 

修正后解决方案代码 #

-- 步骤1：仅保留工作日（周一至周五）记录，按会员+日期排序
WITH work_days AS (
    SELECT 
        member,
        date,
        WEEKDAY(date) AS day_of_week -- 不同数据库函数可能不同：MySQL是WEEKDAY(周一=0/周日=6)，SQL Server用DATEPART(WEEKDAY, date)（周日=1），需按需调整
    FROM dataset1
    WHERE WEEKDAY(date) BETWEEN 0 AND 4 -- 筛选周一到周五，根据函数返回值调整条件
),
-- 步骤2：判断当前记录是否属于上一个连续序列
consecutive_groups AS (
    SELECT 
        *,
        CASE 
            -- 间隔1天，属于连续
            WHEN DATEDIFF(date, LAG(date) OVER (PARTITION BY member ORDER BY date)) = 1 THEN 0
            -- 上一条是周五、当前是周一（间隔3天），属于连续
            WHEN DATEDIFF(date, LAG(date) OVER (PARTITION BY member ORDER BY date)) = 3 
                 AND LAG(day_of_week) OVER (PARTITION BY member ORDER BY date) = 4 
                 AND day_of_week = 0 THEN 0
            -- 其他情况开启新序列
            ELSE 1
        END AS is_new_group
    FROM work_days
),
-- 步骤3：为每个连续序列分配唯一组ID
grouped_days AS (
    SELECT 
        *,
        SUM(is_new_group) OVER (PARTITION BY member ORDER BY date) AS group_id
    FROM consecutive_groups
),
-- 步骤4：统计每个序列的连续工作日天数
group_stats AS (
    SELECT 
        member,
        MIN(date) AS start_date,
        MAX(date) AS end_date,
        COUNT(*) AS consecutive_work_days -- 组内记录数即为连续工作日天数
    FROM grouped_days
    GROUP BY member, group_id
)
-- 步骤5：筛选出连续工作日≥14天的结果
SELECT 
    member,
    start_date,
    end_date,
    consecutive_work_days
FROM group_stats
WHERE consecutive_work_days >= 14
ORDER BY member, start_date;

注意事项 #

• 不同SQL方言的日期函数存在差异，需根据使用的数据库（如MySQL、SQL Server、BigQuery等）调整WEEKDAY相关的判断逻辑。
• 该逻辑仅排除周末，不处理节假日，符合需求要求。

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