自动提取年初至今交易数据并同比去年同期的SQL实现需求

核心逻辑说明 #

无需硬编码日期范围，通过数据库内置函数自动计算同期区间：

• 当年统计区间：当年1月1日 至 当前日期
• 去年同期区间：去年1月1日 至 去年今日

方案1：行式展示（两年数据分行，带年份标识） #

适合按日期查看每日交易对比：

SELECT 
    m_number,
    SUM(amount) AS turnover,
    CAST(date_processed AS DATE) AS business_date,
    EXTRACT(YEAR FROM CAST(date_processed AS DATE)) AS year
FROM x.table1
WHERE 
    -- 筛选当年同期数据
    (CAST(date_processed AS DATE) BETWEEN DATE_TRUNC('year', CURRENT_DATE) AND CURRENT_DATE)
    -- 筛选去年同期数据
    OR (CAST(date_processed AS DATE) BETWEEN DATE_TRUNC('year', CURRENT_DATE) - INTERVAL '1 year' AND CURRENT_DATE - INTERVAL '1 year')
GROUP BY m_number, CAST(date_processed AS DATE), year
ORDER BY m_number, business_date, year DESC;

方案2：列式展示（同一行对比两年交易额） #

适合直接查看单主体的同期业绩差距，可附带同比增长率：

SELECT 
    m_number,
    -- 当年累计交易额
    SUM(CASE WHEN EXTRACT(YEAR FROM CAST(date_processed AS DATE)) = EXTRACT(YEAR FROM CURRENT_DATE) THEN amount ELSE 0 END) AS current_year_turnover,
    -- 去年同期累计交易额
    SUM(CASE WHEN EXTRACT(YEAR FROM CAST(date_processed AS DATE)) = EXTRACT(YEAR FROM CURRENT_DATE) - 1 THEN amount ELSE 0 END) AS last_year_turnover,
    -- 同比增长率（保留2位小数，避免除数为0报错）
    ROUND(
        (SUM(CASE WHEN EXTRACT(YEAR FROM CAST(date_processed AS DATE)) = EXTRACT(YEAR FROM CURRENT_DATE) THEN amount ELSE 0 END) 
        - SUM(CASE WHEN EXTRACT(YEAR FROM CAST(date_processed AS DATE)) = EXTRACT(YEAR FROM CURRENT_DATE) - 1 THEN amount ELSE 0 END))
        / NULLIF(SUM(CASE WHEN EXTRACT(YEAR FROM CAST(date_processed AS DATE)) = EXTRACT(YEAR FROM CURRENT_DATE) - 1 THEN amount ELSE 0 END), 0) * 100,
    2) AS yoy_growth_rate
FROM x.table1
WHERE 
    (CAST(date_processed AS DATE) BETWEEN DATE_TRUNC('year', CURRENT_DATE) AND CURRENT_DATE)
    OR (CAST(date_processed AS DATE) BETWEEN DATE_TRUNC('year', CURRENT_DATE) - INTERVAL '1 year' AND CURRENT_DATE - INTERVAL '1 year')
GROUP BY m_number
ORDER BY m_number;

不同数据库方言适配 #

如果你的数据库不支持上述语法，替换对应函数：

• MySQL：DATE_TRUNC('year', CURRENT_DATE) → DATE_FORMAT(CURRENT_DATE, '%Y-01-01')；INTERVAL '1 year' → INTERVAL 1 YEAR
• Oracle：DATE_TRUNC('year', CURRENT_DATE) → TRUNC(SYSDATE, 'YEAR')；CURRENT_DATE → SYSDATE；INTERVAL '1 year' → INTERVAL '1' YEAR
• SQL Server：DATE_TRUNC('year', CURRENT_DATE) → DATEFROMPARTS(YEAR(CURRENT_DATE), 1, 1)；INTERVAL '1 year' → DATEADD(YEAR, -1, ...)

原SQL错误修正 #

你提供的原SQL中ORDER BY子句未闭合，正确写法应为：

ORDER BY m_number, CAST(date_processed AS DATE)

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