如何创建捕获客户记录变更的字段？SQL/Tableau Prep实现咨询

一、SQL实现方法 #

核心思路是通过窗口函数关联同一客户的新旧记录，逐个字段对比后拼接变更字段名。

假设表结构包含：customer_id（客户ID）、name（客户姓名）、age（年龄）、phone_type（电话类型）、address（地址）、end_dt（记录结束日期）、current_flag（当前记录标记，'Y'为有效新记录，'N'为旧记录）。

示例代码 #

WITH customer_change_history AS (
    SELECT
        customer_id,
        name,
        -- 用LAG窗口函数获取同一客户的上一条旧记录字段值
        LAG(age) OVER (PARTITION BY customer_id ORDER BY end_dt DESC) AS prev_age,
        age AS curr_age,
        LAG(phone_type) OVER (PARTITION BY customer_id ORDER BY end_dt DESC) AS prev_phone_type,
        phone_type AS curr_phone_type,
        LAG(address) OVER (PARTITION BY customer_id ORDER BY end_dt DESC) AS prev_address,
        address AS curr_address,
        current_flag
    FROM customer_records
)
SELECT
    customer_id,
    name,
    -- 拼接所有变更的字段名，自动忽略无变更的字段
    CONCAT_WS(', ',
        CASE WHEN curr_age != prev_age THEN 'Age' END,
        CASE WHEN curr_phone_type != prev_phone_type THEN 'Phone type' END,
        CASE WHEN curr_address != prev_address THEN 'Address' END
    ) AS Change_field
FROM customer_change_history
WHERE current_flag = 'Y' -- 仅保留最新的有效记录
-- 过滤无变更的冗余记录
AND (curr_age != prev_age OR curr_phone_type != prev_phone_type OR curr_address != prev_address)

注意事项 #

• 不同数据库的字符串拼接函数有差异：MySQL用CONCAT_WS，PostgreSQL/SQL Server用STRING_AGG（若需聚合多行变更）；
• 若字段存在NULL值，需用COALESCE处理，比如CASE WHEN COALESCE(curr_age, '') != COALESCE(prev_age, '') THEN 'Age' END，避免NULL对比导致的错误；
• 若客户存在多次变更，需调整窗口函数的排序逻辑，确保关联到正确的上一条旧记录。

二、Tableau Prep/Prep Builder实现方法 #

步骤1：自连接关联新旧记录 #

1. 导入客户记录表，添加自连接步骤；
2. 连接条件设置：
   • customer_id = customer_id（同一客户）
   • 新记录：current_flag = 'Y'
   • 旧记录：current_flag = 'N'
   • [新记录].end_dt > [旧记录].end_dt（确保关联的是最新的旧记录）
3. 添加数据限制：对每个customer_id，仅保留旧记录中end_dt最大的一条（可通过添加计算字段RANK() OVER (PARTITION BY customer_id ORDER BY [旧记录].end_dt DESC)，然后筛选排名=1）。

步骤2：创建单个字段变更标记 #

为每个需要检查的字段创建计算字段，示例：

• Age_Changed：IF [新记录.age] != [旧记录.age] THEN 'Age' END
• PhoneType_Changed：IF [新记录.phone_type] != [旧记录.phone_type] THEN 'Phone type' END
• Address_Changed：IF [新记录.address] != [旧记录.address] THEN 'Address' END

步骤3：合并变更字段 #

创建计算字段Change_field，拼接所有变更标记：

CONCAT_WS(', ', IFNULL([Age_Changed], ''), IFNULL([PhoneType_Changed], ''), IFNULL([Address_Changed], ''))

CONCAT_WS会自动忽略空值，避免出现多余的逗号。

步骤4：过滤无变更记录 #

添加筛选器，排除Change_field为空字符串的记录。

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