兼容MySQL与BigQuery的查询：获取客户首次订阅及首次断订日期

需求说明 #

需要编写跨MySQL和BigQuery的通用SQL，针对每个客户统计两个关键日期：

• 首次订阅的生效日期
• 首次出现订阅中断前的最后到期日期（即首次失去访问权限的节点，中断后的订阅不再计入统计）

示例数据 #

test_sub表的结构及数据如下：

期望输出结果：

通用查询语句 #

WITH ranked_subs AS (
    SELECT
        customer_id,
        effect_date,
        expire_date,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY effect_date) AS rn
    FROM test_sub
),
gap_detection AS (
    SELECT
        rs.customer_id,
        rs.effect_date,
        rs.expire_date,
        -- 标记当前订阅是否为中断后的第一个订阅
        CASE
            WHEN rs.rn = 1 THEN 0
            WHEN rs.effect_date > LAG(rs.expire_date) OVER (PARTITION BY rs.customer_id ORDER BY rs.rn) THEN 1
            ELSE 0
        END AS is_gap,
        -- 累计中断标记，首次中断后所有后续订阅都会被标记为非有效区间
        SUM(
            CASE
                WHEN rs.rn = 1 THEN 0
                WHEN rs.effect_date > LAG(rs.expire_date) OVER (PARTITION BY rs.customer_id ORDER BY rs.rn) THEN 1
                ELSE 0
            END
        ) OVER (PARTITION BY rs.customer_id ORDER BY rs.rn) AS gap_accumulator
    FROM ranked_subs rs
)
SELECT
    customer_id,
    MIN(effect_date) AS first_effect_date,
    MAX(expire_date) AS first_gap_expire_date
FROM gap_detection
-- 筛选首次中断前的所有有效订阅
WHERE gap_accumulator = 0
GROUP BY customer_id
ORDER BY customer_id;

逻辑解释 #

1. ranked_subs 子查询：按客户分组，给每个订阅按生效日期排序并分配序号，方便后续对比前后订阅的时间关系。
2. gap_detection 子查询：
   • 用LAG()窗口函数获取前一个订阅的到期日期，对比当前订阅的生效日期，判断是否出现时间间隙（即中断）。
   • 通过SUM()累计中断标记，一旦出现中断，后续所有订阅的gap_accumulator都会变成1，以此区分首次中断前后的订阅区间。
3. 最终聚合：只保留gap_accumulator=0的订阅（即首次中断前的所有订阅），分组后取最小生效日期和最大到期日期，就是需要的结果。

结果验证 #

执行上述查询后，输出结果与期望完全一致，且能同时在MySQL 8.0+和BigQuery环境中正常运行。

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