如何编写SQL查询按参数值划分连续周期并计算周期内日期差

以下是基于窗口函数的通用实现方案，可兼容Hive/Spark SQL/MySQL 8.0+/PostgreSQL等支持窗口函数的数据库，调整日期函数语法即可直接使用。

核心实现步骤 #

• 第一步：先对每个用户的通话记录按天去重，同一天的多条通话仅保留1条城市记录，避免重复计算影响周期判定
• 第二步：关联用户上一次通话的城市，标记从A市切换到B市的节点，作为B市停留周期的起始点
• 第三步：给每个B市停留周期分配唯一分组ID，每遇到一个新的起始点分组ID加1
• 第四步：计算每个分组的最小日期作为周期开始时间，取该分组之后第一个A市通话的日期作为周期结束时间；若该周期之后没有A市通话，可按需取用户最大通话日期或当前日期作为结束时间
• 第五步：用结束日期减去开始日期得到周期时长，可根据需求调整是否计算首尾日期

可运行代码示例 #

WITH user_daily_call AS (
    -- 按用户+日期去重，优先取当天最早通话的城市，可按需调整排序规则
    SELECT 
        user_id,
        call_date,
        city
    FROM (
        SELECT 
            user_id,
            call_date,
            city,
            ROW_NUMBER() OVER(PARTITION BY user_id, call_date ORDER BY call_time ASC) AS rn
        FROM call_record
    ) t
    WHERE rn = 1
),
user_call_with_prev AS (
    -- 关联上一次通话的城市，判定B周期起始点
    SELECT 
        user_id,
        call_date,
        city,
        LAG(city, 1, 'A') OVER(PARTITION BY user_id ORDER BY call_date ASC) AS prev_city
    FROM user_daily_call
),
user_b_period AS (
    -- 给每个B周期分配分组ID
    SELECT 
        user_id,
        call_date,
        city,
        SUM(CASE WHEN city = 'B' AND prev_city = 'A' THEN 1 ELSE 0 END) OVER(PARTITION BY user_id ORDER BY call_date ASC) AS period_id
    FROM user_call_with_prev
),
period_range AS (
    -- 计算每个周期的起止日期
    SELECT 
        user_id,
        period_id,
        MIN(call_date) AS period_start,
        COALESCE(
            LEAD(MIN(CASE WHEN city = 'A' THEN call_date ELSE NULL END), 1) OVER(PARTITION BY user_id ORDER BY period_id ASC),
            MAX(call_date) -- 若需以当前日期为结束时间，此处替换为CURRENT_DATE即可
        ) AS period_end
    FROM user_b_period
    WHERE period_id > 0
    GROUP BY user_id, period_id
)
-- 输出最终结果
SELECT 
    user_id,
    period_id,
    DATEDIFF(period_end, period_start) + 1 AS stay_days -- 若不需要计算首尾当天，去掉+1即可
FROM period_range
ORDER BY user_id, period_id;

特殊场景说明 #

如果用户最后一次通话为B市且之后没有A市通话，上述代码默认取用户所有通话的最大日期作为结束时间，可根据业务需求调整为当前日期。

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