SQL/Python如何筛选符合间隔要求的客户访问记录并计算30天交易总额

数据老陈

2026-9-25

方案实现

前置说明

你需要的核心是先给每个用户筛选出间隔超过30天的独立访问基准日期，再基于这些基准日期统计后续30天的交易总额即可，以下是可直接复用的SQL逻辑：

步骤1：筛选有效访问基准日期

用递归CTE处理每个用户的访问序列，仅保留与上一个有效访问日期间隔≥30天的访问记录：

WITH RECURSIVE ranked_visits AS (
    -- 先给每个用户的访问日期按升序排序
    SELECT 
        user_id,
        visit_date,
        ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY visit_date) AS rn
    FROM 网站浏览签到表
),
valid_visits AS (
    -- 第一个访问日期默认是有效基准日
    SELECT 
        user_id,
        visit_date AS valid_date,
        rn
    FROM ranked_visits
    WHERE rn = 1
    UNION ALL
    -- 后续访问日期与上一个有效基准日对比，间隔≥30天才保留
    SELECT 
        r.user_id,
        r.visit_date AS valid_date,
        r.rn
    FROM ranked_visits r
    JOIN valid_visits v ON r.user_id = v.user_id AND r.rn = v.rn + 1
    WHERE DATEDIFF(r.visit_date, v.valid_date) >= 30
)
SELECT * FROM valid_visits;

注：以上语法基于MySQL，若使用其他数据库仅需调整日期函数即可，核心逻辑通用。如果你之前已经给访问表加了间隔标记字段，直接筛选标记为1的记录，即可得到和上述逻辑一致的有效基准日期集合。

步骤2：关联交易表统计30天内交易总额

将上一步得到的有效基准日期表和会员交易表关联，统计每个基准日对应的交易金额：

SELECT 
    v.user_id,
    v.valid_date AS 统计基准访问日期,
    COALESCE(SUM(t.trade_amount), 0) AS 后续30天总交易金额
FROM valid_visits v
LEFT JOIN 会员交易表 t 
    ON v.user_id = t.user_id 
    AND t.trade_date BETWEEN v.valid_date AND DATE_ADD(v.valid_date, INTERVAL 30 DAY)
GROUP BY v.user_id, v.valid_date
ORDER BY v.user_id, v.valid_date;