背景 某潮流电商平台运营团队在做新品发售后的"首购→复购"留存分析。每当一个系列（series）上新，运营想知道：以用户在该系列的首次下单日为锚点（Day 0），在之后的第 1、3、7、14 天当日是否产生了该系列的复购（即同一用户、同一系列的再次下单）。产出一张系列 × 留存日的留存率矩阵，用于评估不同系列的长尾复购能力。 表 1：t_series（潮鞋系列字典表） series_id：BIGINT，系列 ID series_name：VARCHAR(64)，系列名称（如 "AJ1 Retro High"） launch_date：DATE，系列首发日期（2025 年内） 表 2：t_order（订单明细表） order_id：BIGINT，订单 ID user_id：BIGINT，用户 ID series_id：BIGINT，所购商品所属系列 ID（关联 t_series.series_id） pay_amount：DECIMAL(10,2)，实付金额 pay_time：DATETIME，支付时间（2025 年内，已剔除未支付订单） 2. 问题 统计每个系列的首购用户留存矩阵：对每个系列，先定位该系列的全体"首购用户集"（即每个用户在该系列的最早一笔订单的下单日记为该用户的 Day 0），再计算首购用户中在 Day 0 之后的第 1 日、第 3 日、第 7 日、第 14 日当天（以日历日为单位，按DATE(pay_time)对齐，不是 24 小时滚动窗口）产生了同一系列复购（复购 = 除首购订单之外的任意一笔该系列订单）的用户占比。 输出字段： series_id（系列 ID） series_name（系列名） first_buyer_cnt（首购用户数） d1_rate（Day+1 复购留存率） d3_rate（Day+3 复购留存率） d7_rate（Day+7 复购留存率） d14_rate（Day+14 复购留存率） 留存率 = 当天有复购的首购用户数 首购用户数，四舍五入保留 2 位小数（如 0.33），无首购用户的系列不输出。 排序规则：按d7_rate降序；若并列，按first_buyer_cnt降序；仍并列，按series_id升序。 3.示例数据表 t_series series_id series_name launch_date 1 AJ1 Retro High 2025-03-01 2 Yeezy 350 V2 2025-03-10 3 Dunk Low Panda 2025-04-01 t_order order_id user_id series_id pay_amount pay_time 10001 1001 1 1299.00 2025-03-02 10:15:00 10002 1001 1 1399.00 2025-03-03 20:00:00 10003 1001 1 1199.00 2025-03-09 09:30:00 10004 1002 1 1299.00 2025-03-02 11:00:00 10005 1002 1 1259.00 2025-03-16 14:00:00 10006 1003 1 1299.00 2025-03-05 08:00:00 10007 1004 2 999.00 2025-03-11 12:00:00 10008 1004 2 1059.00 2025-03-14 12:00:00 推导过程 系列 1（AJ1）首购用户 3 人：1001（Day0=03-02）、1002（Day0=03-02）、1003（Day0=03-05） Day+1：1001 在 03-03 复购 ✓；1002 无；1003 无 → 13 = 0.33 Day+3：1001 无（03-05 无单）；1002 无；1003 无 → 03 = 0.00 Day+7：1001 在 03-09 复购 ✓；1002 无；1003 无 → 13 = 0.33 Day+14：1001 无；1002 在 03-16 复购 ✓；1003 无 → 13 = 0.33 系列 2（Yeezy）首购用户 1 人：1004（Day0=03-11） Day+13714：03-12 无、03-14 有 ✓、03-18 无、03-25 无 → 0.00 1.00 0.00 0.00 系列 3（Dunk）无订单 → 不输出。 4.示例数据查询结果表 series_id series_name first_buyer_cnt d1_rate d3_rate d7_rate d14_rate 1 AJ1 Retro High 3 0.33 0.00 0.33 0.33 2 Yeezy 350 V2 1 0.00 1.00 0.00 0.00
示例1

输入

CREATE TABLE t_series (
    series_id    BIGINT,
    series_name  VARCHAR(64),
    launch_date  DATE
);

CREATE TABLE t_order (
    order_id    BIGINT,
    user_id     BIGINT,
    series_id   BIGINT,
    pay_amount  DECIMAL(10,2),
    pay_time    DATETIME
);

INSERT INTO t_series VALUES
(1, 'AJ1 Retro High',  '2025-03-01'),
(2, 'Yeezy 350 V2',    '2025-03-10'),
(3, 'Dunk Low Panda',  '2025-04-01');

INSERT INTO t_order VALUES
(10001, 1001, 1, 1299.00, '2025-03-02 10:15:00'),
(10002, 1001, 1, 1399.00, '2025-03-03 20:00:00'),
(10003, 1001, 1, 1199.00, '2025-03-09 09:30:00'),
(10004, 1002, 1, 1299.00, '2025-03-02 11:00:00'),
(10005, 1002, 1, 1259.00, '2025-03-16 14:00:00'),
(10006, 1003, 1, 1299.00, '2025-03-05 08:00:00'),
(10007, 1004, 2,  999.00, '2025-03-11 12:00:00'),
(10008, 1004, 2, 1059.00, '2025-03-14 12:00:00');

输出

series_id|series_name|first_buyer_cnt|d1_rate|d3_rate|d7_rate|d14_rate
1|AJ1 Retro High|3|0.33|0.00|0.33|0.33
2|Yeezy 350 V2|1|0.00|1.00|0.00|0.00
加载中...