日期范围内会员资格变更的数据转换方案咨询

我整理了会员资格数据表格（如下），想转换成指定的月度会员状态报表，请教实现方法。

原始会员数据 #

特殊规则说明 #

• 会员1001：2022年1-5月为Premium会员，5月底终止；6月起转为Basic会员，6月需同时统计Premium的Terminated状态和Basic的Active状态。
• 会员2001：两条Premium记录中，需排除终止日期为2022-03-31的错误记录，仅保留有效期到2022-12-31的正确记录。
• 会员3001：1-2月为Basic会员，2月底终止；4月重新激活Basic会员，3月需统计Terminated状态，4月起恢复Active。
• 通用规则：会员资格终止后，仅在终止当月展示Terminated状态，后续月份不再统计该资格类型。

期望输出报表（简化版） #

以下提供两种常用工具的实现方法，可根据实际环境选择：

方法一：SQL实现 #

步骤1：数据清洗（过滤错误记录） #

先排除会员2001的错误Premium记录，规则是：同一会员+同一会员类型下，优先保留Status为Active的记录；若没有Active记录，保留End_Date最晚的Terminated记录。

WITH cleaned_members AS (
    SELECT 
        Member,
        Membership_type,
        Start_Date,
        End_Date,
        Status,
        Year
    FROM (
        SELECT 
            *,
            ROW_NUMBER() OVER (
                PARTITION BY Member, Membership_type 
                ORDER BY CASE WHEN Status = 'Active' THEN 0 ELSE 1 END, End_Date DESC
            ) AS rn
        FROM original_members
    ) t
    WHERE rn = 1
)

步骤2：生成月度日期序列 #

生成2022年1-12月的月度列表，用于关联会员记录：

WITH months AS (
    SELECT 
        DATE_TRUNC('month', DATE '2022-01-01' + INTERVAL (n-1) MONTH) AS month_start,
        TO_CHAR(DATE '2022-01-01' + INTERVAL (n-1) MONTH, 'Mon') AS month_name,
        2022 AS year
    FROM generate_series(1,12) n
)

步骤3：关联生成状态记录 #

匹配每个会员资格覆盖的月份，并处理状态逻辑，同时补充终止当月的状态记录：

SELECT 
    c.Member,
    c.Membership_type AS Membership_Type,
    CASE 
        WHEN m.month_start >= DATE_TRUNC('month', c.End_Date) THEN 'Terminated'
        ELSE 'Active'
    END AS Status,
    m.month_name AS Month,
    m.year AS Year
FROM cleaned_members c
JOIN months m 
    ON m.month_start >= DATE_TRUNC('month', c.Start_Date)
    AND m.month_start <= DATE_TRUNC('month', c.End_Date)
UNION ALL
-- 补充终止当月的Terminated记录（避免重复）
SELECT 
    c.Member,
    c.Membership_type AS Membership_Type,
    'Terminated' AS Status,
    TO_CHAR(DATE_TRUNC('month', c.End_Date), 'Mon') AS Month,
    c.Year AS Year
FROM cleaned_members c
WHERE c.Status = 'Terminated'
AND NOT EXISTS (
    SELECT 1 FROM months m 
    WHERE m.month_start = DATE_TRUNC('month', c.End_Date)
)
ORDER BY Member, Membership_Type, m.month_start;

方法二：Excel实现 #

步骤1：数据清洗 #

1. 添加辅助列Priority，公式为：=IF(Status="Active",0,1)
2. 按Member、Membership_type、Priority、End_Date（降序）排序
3. 删除同Member+Membership_type下Priority不为0，或End_Date不是最大的重复行

步骤2：生成月度序列 #

在空白区域输入2022年1-12月的月份名称（Jan-Dec）和年份2022，比如F2:F13为月份名，G2:G13为2022。

步骤3：生成会员月度状态记录（Excel 365适用） #

使用动态数组函数生成所有匹配记录：

=LET(
    cleaned, A2:G7, -- 替换为清洗后的会员数据区域
    months, F2:G13,
    members, UNIQUE(cleaned[Member]),
    types, UNIQUE(cleaned[Membership_type]),
    cross, INDEX(members,SEQUENCE(ROWS(members)*ROWS(types),1,1,1))&"|"&INDEX(types,SEQUENCE(ROWS(members)*ROWS(types),1,1,1)),
    result, REDUCE("", cross, LAMBDA(a,b,
        LET(
            mem, LEFT(b,FIND("|",b)-1),
            type, RIGHT(b,LEN(b)-FIND("|",b)),
            start, XLOOKUP(mem&"|"&type, cleaned[Member]&"|"&cleaned[Membership_type], cleaned[Start_Date]),
            end, XLOOKUP(mem&"|"&type, cleaned[Member]&"|"&cleaned[Membership_type], cleaned[End_Date]),
            valid_months, FILTER(months, DATEVALUE(months[Month]&" 1,"&months[Year])>=start, DATEVALUE(months[Month]&" 1,"&months[Year])<=end),
            status, IF(EOMONTH(DATEVALUE(valid_months[Month]&" 1,"&months[Year]),0)=EOMONTH(end,0), "Terminated", "Active"),
            rows, HSTACK(REPT(mem,ROWS(valid_months)), REPT(type,ROWS(valid_months)), status, valid_months[Month], valid_months[Year]),
            VSTACK(a, rows)
        )
    )),
    DROP(result,1)
)

步骤4：调整终止状态显示 #

对于旧版Excel，可使用IF函数判断当前月份是否为终止月份：

=IF(EOMONTH(end_date_cell,0)=EOMONTH(DATEVALUE(month_cell&" 1,"&year_cell),0), "Terminated", "Active")

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