如何用SQL按指定规则将年度数据转换为季度数据

转换规则总结 #

源数据中年份Y对应的季度为：

• (Y+2)Q3、(Y+2)Q4、(Y+3)Q1、(Y+3)Q2

实现方案 #

以下提供两种通用SQL写法，适用于大多数主流数据库（PostgreSQL、SQL Server、MySQL等）。

方法1：UNION ALL展开季度

直接为每个年度记录生成4条对应季度的记录：

SELECT
    CONCAT(y.year + 2, 'Q3') AS YearQtr,
    y.FIPS,
    y.PercentagePopulation,
    y.NumberOfPeople
FROM annual_data y
UNION ALL
SELECT
    CONCAT(y.year + 2, 'Q4') AS YearQtr,
    y.FIPS,
    y.PercentagePopulation,
    y.NumberOfPeople
FROM annual_data y
UNION ALL
SELECT
    CONCAT(y.year + 3, 'Q1') AS YearQtr,
    y.FIPS,
    y.PercentagePopulation,
    y.NumberOfPeople
FROM annual_data y
UNION ALL
SELECT
    CONCAT(y.year + 3, 'Q2') AS YearQtr,
    y.FIPS,
    y.PercentagePopulation,
    y.NumberOfPeople
FROM annual_data y
ORDER BY YearQtr, FIPS;

方法2：CROSS JOIN + VALUES子句（更简洁）

通过交叉连接预定义的季度列表，一次性生成所有季度记录：

SELECT
    CONCAT(
        CASE q.qtr_num
            WHEN 3 THEN y.year + 2
            WHEN 4 THEN y.year + 2
            WHEN 1 THEN y.year + 3
            WHEN 2 THEN y.year + 3
        END,
        'Q', q.qtr_num
    ) AS YearQtr,
    y.FIPS,
    y.PercentagePopulation,
    y.NumberOfPeople
FROM annual_data y
CROSS JOIN (VALUES (3), (4), (1), (2)) q(qtr_num)
ORDER BY YearQtr, FIPS;

注意事项 #

1. 如果year字段是字符串类型，需先转为数值类型再计算，例如CAST(y.year AS INT) + 2
2. 不同数据库的字符串拼接语法可能有差异：
   • MySQL/PostgreSQL/SQL Server：使用CONCAT()
   • Oracle：使用||拼接符
3. YearQtr字段为字符串类型，按字典序排序即可保证季度顺序正确

测试结果 #

代入你提供的实际数据，两种方法均会输出预期结果：

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