如何在BigQuery中筛选近3年到访且近12个月未再到访的访客

核心逻辑 #

筛选过去3年内到访目标区域的访客，同时排除近12个月内再次到访该区域的访客，最终得到符合条件的访客列表。

查询语句 #

假设你的数据表为your-project.your-dataset.visitor_records，目标区域为'TargetZone'（自行替换为实际值），完整SQL如下：

WITH target_3y_visitors AS (
    -- 提取过去3年到访目标区域的访客，记录最后到访时间和城市
    SELECT
        visitor_name,
        `visitor's city` AS visitor_city,
        MAX(date) AS last_target_visit
    FROM
        `your-project.your-dataset.visitor_records`
    WHERE
        visited_area = 'TargetZone'
        AND date >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 YEAR)
    GROUP BY
        visitor_name, `visitor's city`
),
recent_1y_visitors AS (
    -- 提取近12个月到访目标区域的访客（去重）
    SELECT DISTINCT visitor_name
    FROM
        `your-project.your-dataset.visitor_records`
    WHERE
        visited_area = 'TargetZone'
        AND date >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)
)
-- 取交集以外的访客：3年内到访过，但近12个月没去过
SELECT
    tv.visitor_name,
    tv.visitor_city,
    tv.last_target_visit
FROM target_3y_visitors tv
LEFT JOIN recent_1y_visitors rv
    ON tv.visitor_name = rv.visitor_name
WHERE rv.visitor_name IS NULL
ORDER BY tv.last_target_visit DESC;

关键说明 #

• target_3y_visitors CTE：先锁定时间范围（过去3年）和目标区域的访客，通过MAX(date)记录他们最后一次到访该区域的时间，避免重复数据。
• recent_1y_visitors CTE：单独提取近12个月内有过目标区域访问记录的访客，用DISTINCT去重，减少关联时的数据量。
• 左连接筛选：通过左连接排除掉近12个月到访的访客，rv.visitor_name IS NULL的结果就是符合要求的访客。

注意事项 #

• 务必替换your-project.your-dataset.visitor_records为你的实际项目、数据集和表名。
• 如果visitor_name存在重名情况，建议结合visitor's city或其他唯一标识字段（比如访客ID）进行分组和关联，避免错误匹配。
• 若date字段是时间戳类型（TIMESTAMP），需要用DATE(date)转换为日期后再进行比较，例如DATE(date) >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 YEAR)。

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