ANSI SQL 按印度财年（4月-次年3月）实现近5年YTD数据分组查询咨询

现有问题根因 #

你提供的现有查询在1-3月失效的原因非常明确：当当前月份处于1-3月区间时，month("Date") >=4 和 month("Date") <= month(today()) 两个条件完全互斥，直接导致没有符合条件的数据返回。

实现逻辑 #

基于印度财年（4月1日至次年3月31日）的规则，核心逻辑分为三步：

• 给每条数据打所属财年标签：日期月份>=4则财年为当前年份，否则为上一年年份
• 计算每个财年对应的YTD截止日期：如果当前月份>=4，截止日期为财年年份+当前月日；如果当前月份<4，截止日期为财年年份+1+当前月日
• 过滤近5年财年数据后按财年分组聚合即可

可用ANSI SQL代码 #

WITH fiscal_calculation AS (
    -- 给所有数据打上所属财年标签
    SELECT
        CASE 
            WHEN EXTRACT(MONTH FROM "Date") >= 4 THEN EXTRACT(YEAR FROM "Date")
            ELSE EXTRACT(YEAR FROM "Date") - 1
        END AS fiscal_year,
        "Daily data",
        "GA%",
        "MA%",
        "Date" AS record_date
    FROM aaa
),
current_date_config AS (
    -- 统一计算当前日期的参数，避免重复计算
    SELECT
        EXTRACT(YEAR FROM CURRENT_DATE) AS curr_year,
        EXTRACT(MONTH FROM CURRENT_DATE) AS curr_month,
        EXTRACT(DAY FROM CURRENT_DATE) AS curr_day,
        CASE 
            WHEN EXTRACT(MONTH FROM CURRENT_DATE) >= 4 THEN EXTRACT(YEAR FROM CURRENT_DATE)
            ELSE EXTRACT(YEAR FROM CURRENT_DATE) - 1
        END AS curr_fiscal_year
)
SELECT
    -- 生成FY-1到FY-5的财年标识，FY-1为最新财年
    CONCAT('FY-', curr_fiscal_year - fiscal_year + 1) AS fiscal_year,
    SUM("Daily data") AS sum_daily_data_period,
    SUM("GA%") AS sum_ga,
    SUM("MA%") AS sum_ma
FROM fiscal_calculation, current_date_config
WHERE
    -- 过滤最近5个财年的数据
    fiscal_year >= curr_fiscal_year - 4
    -- 只保留对应财年4月1日到YTD截止日的数据
    AND record_date <= CASE
        WHEN curr_month >= 4 THEN MAKE_DATE(fiscal_year, curr_month, curr_day)
        ELSE MAKE_DATE(fiscal_year + 1, curr_month, curr_day)
    END
GROUP BY fiscal_year, curr_fiscal_year
ORDER BY fiscal_year DESC;

兼容说明 #

如果你的数据库不支持ANSI的MAKE_DATE函数，替换为对应数据库的日期拼接函数即可，核心判断逻辑不需要调整。比如MySQL可以替换为STR_TO_DATE(CONCAT(年,'-',月,'-',日), '%Y-%m-%d')，SQL Server可以替换为DATEFROMPARTS(年,月,日)。

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