Teradata中如何通过循环实现按首单月份统计客户留存多列数据

变量声明 #

• 存储过程内部变量：使用DECLARE关键字声明，需指定数据类型，可设置默认值，声明必须放在存储过程最开头的逻辑块前：

DECLARE v_start_month DATE DEFAULT DATE '2017-01-01'; -- 要统计的第一个首单月
DECLARE v_end_month DATE DEFAULT DATE '2018-12-01'; -- 要统计的最后一个首单月
DECLARE v_current_month DATE; -- 循环过程中当前处理的首单月
DECLARE v_col_name VARCHAR(20); -- 动态拼接的列名

• 会话级临时变量：Teradata 14.10及以上版本支持，无需存储过程也可使用：

SET SESSION v_current_month = DATE '2017-01-01';

WHILE循环语法 #

循环逻辑建议封装在存储过程中使用，基础语法如下：

WHILE 循环判断条件 DO
    -- 循环体内要执行的逻辑
    SET v_current_month = ADD_MONTHS(v_current_month, 1); -- 变量步进，避免死循环
END WHILE;

你提到的逐列写入Volatile表的方案可按以下步骤实现，相比你原有的逐次修改日期的逻辑，效率提升明显：

1. 预先计算所有客户的首单月份，避免每次循环重复过滤历史数据
2. 创建空Volatile结果表，首列为month_after_cohort，代表距离首单的月份数（0代表首单当月，1代表首单后1个月，以此类推）
3. 循环遍历每个要统计的首单月，计算该组客户各月的留存客户数，作为新列追加到结果表
4. 最终查询结果表得到三角矩阵

完整代码实现 #

-- 1. 创建存储过程封装逻辑，your_trans_table替换为实际交易表名
REPLACE PROCEDURE cohort_retention_calc()
BEGIN
    -- 声明变量
    DECLARE v_start_month DATE DEFAULT DATE '2017-01-01';
    DECLARE v_end_month DATE DEFAULT DATE '2018-12-01';
    DECLARE v_current_month DATE;
    DECLARE v_col_name VARCHAR(20);
    DECLARE v_max_month_diff INT;

    -- 初始化当前处理月份为起始月份
    SET v_current_month = v_start_month;
    -- 计算最大的间隔月数，用于初始化结果表行数
    SET v_max_month_diff = MONTHS_BETWEEN(v_end_month, v_start_month);

    -- 2. 预先计算所有客户的首单月份，存入临时表
    CREATE VOLATILE TABLE customer_first_month AS (
        SELECT 
            ID,
            MIN(TRUNC(trans_date, 'MONTH')) AS first_trans_month
        FROM your_trans_table
        WHERE trans_date BETWEEN v_start_month AND LAST_DAY(v_end_month)
        GROUP BY ID
    ) WITH DATA PRIMARY INDEX(ID) ON COMMIT PRESERVE ROWS;

    -- 3. 创建空的结果表
    CREATE VOLATILE TABLE retention_result (
        month_after_cohort INT
    ) ON COMMIT PRESERVE ROWS;

    -- 预先填充间隔月数行，从0到最大间隔
    INSERT INTO retention_result
    SELECT CAST(ROW_NUMBER() OVER(ORDER BY 1) -1 AS INT)
    FROM SYS_CALENDAR.CALENDAR
    QUALIFY ROW_NUMBER() OVER(ORDER BY 1) <= v_max_month_diff +1;

    -- 4. 循环处理每个首单月
    WHILE v_current_month <= v_end_month DO
        -- 生成列名，格式为cohort_201701
        SET v_col_name = 'cohort_' || TO_CHAR(v_current_month, 'YYYYMM');
        -- 给结果表加新列
        EXECUTE IMMEDIATE 'ALTER TABLE retention_result ADD ' || v_col_name || ' INT';
        
        -- 计算当前首单月的各月留存，更新到结果表
        EXECUTE IMMEDIATE '
        UPDATE retention_result r
        SET ' || v_col_name || ' = cohort.cnt
        FROM (
            SELECT
                MONTHS_BETWEEN(TRUNC(t.trans_date, ''MONTH''), c.first_trans_month) AS month_diff,
                COUNT(DISTINCT t.ID) AS cnt
            FROM your_trans_table t
            JOIN customer_first_month c ON t.ID = c.ID
            WHERE c.first_trans_month = ''' || v_current_month || '''
            GROUP BY 1
        ) cohort
        WHERE r.month_after_cohort = cohort.month_diff
        ';

        -- 步进一个月
        SET v_current_month = ADD_MONTHS(v_current_month, 1);
    END WHILE;

    -- 5. 查询最终结果，month_after_cohort为0的行就是各首单月的总客户数，1是次月留存，以此类推
    SELECT * FROM retention_result ORDER BY month_after_cohort;
END;

调用方式 #

执行以下语句运行存储过程即可得到结果：

CALL cohort_retention_calc();

如果不需要存储过程，也可以用单次SQL直接生成留存宽表，无需循环，执行效率更高：

WITH customer_cohort AS (
    SELECT ID, MIN(TRUNC(trans_date, 'MONTH')) AS first_month
    FROM your_trans_table
    WHERE trans_date BETWEEN DATE '2017-01-01' AND DATE '2018-12-31'
    GROUP BY ID
),
trans_with_diff AS (
    SELECT
        c.first_month,
        MONTHS_BETWEEN(TRUNC(t.trans_date, 'MONTH'), c.first_month) AS month_diff,
        COUNT(DISTINCT t.ID) AS retention_cnt
    FROM your_trans_table t
    JOIN customer_cohort c ON t.ID = c.ID
    GROUP BY 1,2
)
-- 转置为宽表即可得到目标结构
SELECT 
    month_diff,
    MAX(CASE WHEN first_month = DATE '2017-01-01' THEN retention_cnt END) AS cohort_201701,
    MAX(CASE WHEN first_month = DATE '2017-02-01' THEN retention_cnt END) AS cohort_201702,
    -- 其余首单月以此类推，也可以用动态SQL自动生成所有case when
    MAX(CASE WHEN first_month = DATE '2018-12-01' THEN retention_cnt END) AS cohort_201812
FROM trans_with_diff
GROUP BY month_diff
ORDER BY month_diff;

内容的提问来源于stack exchange，提问作者Иван Иваныч