如何在BigQuery中计算客户距上次收邮件的月份数？

核心逻辑 #

用LAG()窗口函数按客户分组、日期排序，获取上一次收到邮件的日期，再通过DATE_DIFF()计算月份间隔。以下分两种常见场景给出实现代码：

场景1：只统计实际收到邮件的记录 #

如果仅需展示客户收到邮件的月份，并计算与上一次收到的间隔（假设email_sent=1代表当月收到邮件）：

WITH valid_email_records AS (
    SELECT cust_id, eom_date
    FROM `your_project.your_dataset.customer_email_tracking`
    WHERE email_sent = 1
)
SELECT
    cust_id,
    eom_date,
    -- 首次收到邮件时返回0，否则计算月份差
    CASE
        WHEN LAG(eom_date) OVER (PARTITION BY cust_id ORDER BY eom_date) IS NULL THEN 0
        ELSE DATE_DIFF(eom_date, LAG(eom_date) OVER (PARTITION BY cust_id ORDER BY eom_date), MONTH)
    END AS months_since_last_email
FROM valid_email_records
ORDER BY cust_id, eom_date;

场景2：保留所有月度记录（含未收到邮件的月份） #

如果需要展示每个客户的所有月度记录，并计算当前月距上次收到邮件的间隔：

SELECT
    cust_id,
    eom_date,
    email_sent,
    -- 忽略未收到邮件的记录，取最近一次收到的日期
    CASE
        WHEN last_received_date IS NULL THEN NULL
        ELSE DATE_DIFF(eom_date, last_received_date, MONTH)
    END AS months_since_last_email
FROM (
    SELECT
        *,
        LAG(CASE WHEN email_sent = 1 THEN eom_date END) IGNORE NULLS
            OVER (PARTITION BY cust_id ORDER BY eom_date) AS last_received_date
    FROM `your_project.your_dataset.customer_email_tracking`
)
ORDER BY cust_id, eom_date;

常见错误排查 #

1. 未按客户分组：忘记加PARTITION BY cust_id会导致跨客户计算，结果完全错误
2. 日期类型问题：如果eom_date是字符串，先转换为DATE类型：PARSE_DATE('%Y-%m-%d', eom_date)
3. 未正确排序：ORDER BY eom_date必须存在，否则LAG()无法取到正确的上一次日期
4. 忽略未收到的记录：如果需要保留所有月度记录，必须用IGNORE NULLS参数跳过未收到邮件的行

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