如何按月份执行Partition Over并将月份分组至季度（财年始于6月）

没问题！针对你这种财年从6月起始的季度分区需求，我们可以分两步来实现：先把每个月份映射到对应的财年和财季，再结合PARTITION OVER窗口函数完成聚合计算。我给你几个不同场景的SQL示例，你可以直接套用或者根据你的表结构调整。

场景1：你的表有完整日期字段 #

假设你的表包含transaction_date（交易日期）和amount（交易金额）字段，我们先通过CTE生成财年、财季字段，再用窗口函数计算季度内的聚合值：

WITH sales_with_fiscal_info AS (
    SELECT 
        transaction_date,
        amount,
        -- 计算财年：6月及之后属于当年财年，之前属于上一年财年
        CASE 
            WHEN EXTRACT(MONTH FROM transaction_date) >= 6 THEN EXTRACT(YEAR FROM transaction_date)
            ELSE EXTRACT(YEAR FROM transaction_date) - 1 
        END AS fiscal_year,
        -- 计算财季：Q1(6-8)、Q2(9-11)、Q3(12-2)、Q4(3-5)
        CASE 
            WHEN EXTRACT(MONTH FROM transaction_date) BETWEEN 6 AND 8 THEN 1
            WHEN EXTRACT(MONTH FROM transaction_date) BETWEEN 9 AND 11 THEN 2
            WHEN EXTRACT(MONTH FROM transaction_date) IN (12, 1, 2) THEN 3
            ELSE 4 
        END AS fiscal_quarter
    FROM your_sales_table
)
SELECT 
    transaction_date,
    amount,
    fiscal_year,
    fiscal_quarter,
    -- 财季内的累计金额（按日期排序）
    SUM(amount) OVER (PARTITION BY fiscal_year, fiscal_quarter ORDER BY transaction_date) AS qtd_running_total,
    -- 财季内的平均金额
    AVG(amount) OVER (PARTITION BY fiscal_year, fiscal_quarter) AS qtr_average_amount,
    -- 财季内的最高单笔金额
    MAX(amount) OVER (PARTITION BY fiscal_year, fiscal_quarter) AS qtr_max_amount
FROM sales_with_fiscal_info
ORDER BY fiscal_year, fiscal_quarter, transaction_date;

场景2：你的表只有年份和月份数字字段 #

如果你的表是拆分的year（年份）和month（1-12的数字）字段，调整财年、财季的计算逻辑即可：

WITH data_with_fiscal_info AS (
    SELECT 
        year,
        month,
        metric_value, -- 替换成你要计算的字段
        -- 财年计算
        CASE WHEN month >= 6 THEN year ELSE year - 1 END AS fiscal_year,
        -- 财季计算
        CASE 
            WHEN month BETWEEN 6 AND 8 THEN 1
            WHEN month BETWEEN 9 AND 11 THEN 2
            WHEN month IN (12, 1, 2) THEN 3
            ELSE 4 
        END AS fiscal_quarter
    FROM your_data_table
)
SELECT 
    year,
    month,
    metric_value,
    fiscal_year,
    fiscal_quarter,
    -- 财季内的累计值（按月份排序）
    SUM(metric_value) OVER (PARTITION BY fiscal_year, fiscal_quarter ORDER BY month) AS qtr_running_total,
    -- 财季内的最小值
    MIN(metric_value) OVER (PARTITION BY fiscal_year, fiscal_quarter) AS qtr_min_value
FROM data_with_fiscal_info
ORDER BY fiscal_year, fiscal_quarter, month;

简化写法（部分SQL方言支持） #

如果你用的是PostgreSQL这类支持自定义日期截断的数据库，还可以用更简洁的方式生成财季：

SELECT 
    transaction_date,
    amount,
    -- 财年起始日期（比如2023-06-01）
    DATE_TRUNC('year', transaction_date + INTERVAL '6 months') - INTERVAL '6 months' AS fiscal_year_start,
    -- 财季起始日期（比如2023-06-01、2023-09-01等）
    DATE_TRUNC('quarter', transaction_date + INTERVAL '6 months') - INTERVAL '6 months' AS fiscal_quarter_start,
    -- 财季总金额
    SUM(amount) OVER (PARTITION BY DATE_TRUNC('quarter', transaction_date + INTERVAL '6 months')) AS qtr_total_amount
FROM your_sales_table;

核心思路就是：先把每个时间点映射到财年的季度分组，再用PARTITION BY fiscal_year, fiscal_quarter让窗口函数只在同一个财季内计算。如果你的需求是其他聚合逻辑，只需要替换窗口函数里的SUM/AVG/MAX等函数即可。

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