如何用SQL筛选至订阅取消的行？实现客户生命周期数据截取

云上老周

2026-8-13

解决方案：筛选客户取消订阅前的营收记录

核心思路

先定位每个客户的最后一次取消订阅日期（若存在多次取消场景也适用），仅保留该日期及之前的所有收费记录；对于未取消订阅的客户，则保留全部记录。

调整后的SQL语句

结合你的现有查询，针对需求修改如下（以MySQL为例，其他数据库可调整日期转换函数）：

WITH customer_cancel_dates AS (
    -- 第一步：获取每个客户的取消日期（无取消记录则为NULL）
    SELECT 
        customer_id,
        MAX(CASE WHEN cancelled = 'yes' THEN STR_TO_DATE(charge_date, '%m/%d/%Y') END) AS cancel_date
    FROM metrics
    GROUP BY customer_id
)
SELECT
    t.customer_name,
    t.customer_id,
    t.cancelled,
    t.charge_date,
    a.revenue AS charged_amount
FROM metrics t
INNER JOIN drp.mrr a 
    ON t.customer_id = a.customer_id
    -- 可选：如果两张表按客户+日期一一对应，建议加日期关联避免重复数据
    AND STR_TO_DATE(t.charge_date, '%m/%d/%Y') = STR_TO_DATE(a.revenue_date, '%m/%d/%Y')
LEFT JOIN customer_cancel_dates c 
    ON t.customer_id = c.customer_id
-- 筛选逻辑：未取消的客户留全部记录；已取消的仅保留取消日期及之前的记录
WHERE c.cancel_date IS NULL OR STR_TO_DATE(t.charge_date, '%m/%d/%Y') <= c.cancel_date;

关键细节说明

日期格式转换：如果数据库中charge_date是字符串类型，必须转为日期格式才能正确比较，不同数据库转换函数参考：
- Oracle：TO_DATE(charge_date, 'MM/DD/YYYY')
- SQL Server：CONVERT(DATE, charge_date, 101)
取消日期取值：用MAX()取客户最后一次取消的日期，覆盖多次取消的场景；若仅需首次取消日期，替换为MIN()即可。
关联优化：如果metrics和drp.mrr的记录是按「客户+日期」一一对应的，务必加上日期关联条件，避免同一客户多日期匹配导致的重复数据。