财年重叠月份场景下客户路径分析的SQL自动化实现求助

核心问题分析 #

你的需求是处理重叠财年数据（每个财年为前一年1月-当年6月，连续财年重叠6个月），但现有日历表的财年规则不符合该需求，且原SQL的关联逻辑存在语法错误（双等号）和逻辑错误（OR条件导致冗余关联），导致无法得到预期结果。

方案1：动态计算财年（无需修改现有表） #

直接在查询中生成每个访问日期对应的所有财年，无需依赖现有日历表的财年字段，实现自动化查询：

WITH encounter_with_fy AS (
    SELECT
        sys.CUSTOMER_SK,
        sys.encounter_ts::date AS date_visited,
        -- 生成当前日期所属的所有财年：1-6月属于当年和下一年财年，7-12月仅属于下一年财年
        UNNEST(
            CASE
                WHEN EXTRACT(MONTH FROM sys.encounter_ts) <= 6 
                THEN ARRAY[EXTRACT(YEAR FROM sys.encounter_ts), EXTRACT(YEAR FROM sys.encounter_ts) + 1]
                ELSE ARRAY[EXTRACT(YEAR FROM sys.encounter_ts) + 1]
            END
        ) AS FISCALYEAR,
        sys.is_new_customer_encounter,
        sys.is_reengaged_customer_encounter,
        sys.is_retained_customer_encounter
    FROM enriched.analytics.encounter_system_level sys
    -- 过滤需要分析的财年范围（可根据需求调整）
    WHERE EXTRACT(YEAR FROM sys.encounter_ts) + 1 BETWEEN 2023 AND 2024
)
SELECT
    CUSTOMER_SK AS Customers,
    date_visited AS "date visited",
    FISCALYEAR AS "Fiscal year",
    CASE
        WHEN MAX(is_new_customer_encounter) = TRUE THEN 'NEW'
        WHEN MAX(is_reengaged_customer_encounter) = TRUE THEN 'REENGAGED'
        WHEN MAX(is_retained_customer_encounter) = TRUE THEN 'RETAINED'
        ELSE 'UNKNOWN'
    END AS CUSTOMER_CATEGORY
FROM encounter_with_fy
GROUP BY CUSTOMER_SK, date_visited, FISCALYEAR
ORDER BY date_visited, FISCALYEAR;

方案2：创建重叠财年辅助表（重复使用场景） #

如果需要多次使用该财年规则，建议创建一个辅助表存储日期与重叠财年的映射，后续查询直接关联即可：

1. 创建辅助表

CREATE TABLE IF NOT EXISTS refined.core.overlapping_fiscal_calendar AS
SELECT
    calendar_date,
    UNNEST(
        CASE
            WHEN EXTRACT(MONTH FROM calendar_date) <= 6 
            THEN ARRAY[EXTRACT(YEAR FROM calendar_date), EXTRACT(YEAR FROM calendar_date) + 1]
            ELSE ARRAY[EXTRACT(YEAR FROM calendar_date) + 1]
        END
    ) AS fiscal_year_num
FROM refined.core.calendar;

2. 查询使用辅助表

SELECT
    sys.CUSTOMER_SK AS Customers,
    sys.encounter_ts::date AS "date visited",
    fc.fiscal_year_num AS "Fiscal year",
    CASE
        WHEN MAX(sys.is_new_customer_encounter) = TRUE THEN 'NEW'
        WHEN MAX(sys.is_reengaged_customer_encounter) = TRUE THEN 'REENGAGED'
        WHEN MAX(sys.is_retained_customer_encounter) = TRUE THEN 'RETAINED'
        ELSE 'UNKNOWN'
    END AS CUSTOMER_CATEGORY
FROM enriched.analytics.encounter_system_level sys
JOIN refined.core.overlapping_fiscal_calendar fc
    ON sys.encounter_ts::date = fc.calendar_date
WHERE fc.fiscal_year_num BETWEEN 2023 AND 2024
GROUP BY sys.CUSTOMER_SK, sys.encounter_ts::date, fc.fiscal_year_num
ORDER BY sys.encounter_ts::date, fc.fiscal_year_num;

关键优化点 #

• 移除了原SQL中错误的OR关联条件，避免冗余数据生成
• 使用UNNEST(ARRAY[])将单日期对应的多财年拆分为多行，满足重叠财年的统计需求
• 两种方案均无需手动编写自定义日期范围，仅需指定财年区间即可实现自动化查询

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