如何在Oracle SQL Developer中通过YTD值计算MTD和QTD

核心思路 #

• MTD（当月累计）：如果是当年1月，MTD直接等于当月YTD值；其他月份，用当月YTD减去上个月的YTD值，差值就是当月的累计数据。
• QTD（当季累计）：如果是季度首月（1、4、7、10月），QTD等于当月YTD；其他月份，用当月YTD减去上一季最后一个月的YTD值，差值就是本季度到当前月的累计数据。

假设数据表结构 #

假设你的数据表名为business_data，包含两个关键字段：

• date：日期列，格式建议为每月第一天（如'2023-01-01'），方便月度/季度计算
• ytd：截至当月的年度累计数值

具体SQL实现 #

1. 单独计算MTD #

使用LAG()窗口函数获取上月YTD，再计算差值：

SELECT
    date,
    ytd,
    CASE
        WHEN MONTH(date) = 1 THEN ytd
        ELSE ytd - LAG(ytd) OVER (ORDER BY date)
    END AS mtd
FROM business_data
ORDER BY date;

2. 单独计算QTD #

先标记季度信息，再匹配上一季末的YTD做减法：

WITH quarterly_data AS (
    SELECT
        date,
        ytd,
        YEAR(date) AS year,
        QUARTER(date) AS quarter,
        -- 提取同一年中上个季度最后一个月的YTD
        MAX(CASE WHEN QUARTER(date) = (quarter - 1) THEN ytd END) OVER (PARTITION BY year) AS prev_qtr_ytd
    FROM business_data
)
SELECT
    date,
    ytd,
    CASE
        WHEN quarter = 1 THEN ytd
        ELSE ytd - prev_qtr_ytd
    END AS qtd
FROM quarterly_data
ORDER BY date;

3. 同时计算MTD和QTD #

合并两个逻辑，一次性输出所有目标字段：

WITH data_with_aux AS (
    SELECT
        date,
        ytd,
        MONTH(date) AS month,
        YEAR(date) AS year,
        QUARTER(date) AS quarter,
        -- 获取上月YTD
        LAG(ytd) OVER (ORDER BY date) AS prev_month_ytd,
        -- 获取同一年上一季末的YTD
        MAX(CASE WHEN QUARTER(date) = (quarter - 1) THEN ytd END) OVER (PARTITION BY year) AS prev_qtr_ytd
    FROM business_data
)
SELECT
    date,
    ytd,
    -- 计算MTD
    CASE
        WHEN month = 1 THEN ytd
        ELSE ytd - prev_month_ytd
    END AS mtd,
    -- 计算QTD
    CASE
        WHEN quarter = 1 THEN ytd
        ELSE ytd - prev_qtr_ytd
    END AS qtd
FROM data_with_aux
ORDER BY date;

注意事项 #

• 确保月度数据连续无缺漏，否则LAG()会取到上一条存在的记录，导致计算错误；若有缺月，需先补全数据。
• 不同数据库的函数存在细微差异：比如QUARTER()在SQL Server中需写为DATEPART(QUARTER, date)，Oracle中用TO_CHAR(date, 'Q')，请根据使用的数据库调整。

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