编写SQL查询获取指定年份的节假日日期及对应天数位置

嘿，这个需求我之前处理过类似的，咱们一步步拆解来做就行。核心目标就是把那个由0和1组成的长字符串里，每个'1'的位置转换成当年的第几天，再算出对应的具体日期，同时关联上币种和年份信息。

通用思路 #

不管用哪种数据库，核心步骤都是这几步：

1. 生成一个数字序列，范围是1到对应年份的总天数（平年365，闰年366），用来匹配字符串的每个字符位置。
2. 把原表和这个数字序列做关联，让每一行「币种+年份」数据对应到每个天数位置。
3. 提取字符串中对应位置的字符，筛选出值为'1'的行（也就是节假日）。
4. 基于当年的第一天，加上「天数位置-1」的间隔，计算出具体的节假日日期。

分数据库具体实现 #

下面是几种主流数据库的SQL代码示例，你可以根据自己的数据库选择对应的版本：

PostgreSQL 版本 #

PostgreSQL自带generate_series函数，生成数字序列非常方便：

WITH days_of_year AS (
    SELECT 
        generate_series(1, 
            CASE WHEN EXTRACT(YEAR FROM make_date(t.year, 1, 1)) % 4 = 0 
                 AND (EXTRACT(YEAR FROM make_date(t.year, 1, 1)) % 100 != 0 
                      OR EXTRACT(YEAR FROM make_date(t.year, 1, 1)) % 400 = 0)
                 THEN 366 
                 ELSE 365 
            END) AS day_num
    FROM your_table t
    GROUP BY t.year
)
SELECT 
    t.currency,
    t.year,
    doy.day_num AS holiday_day_of_year,
    make_date(t.year, 1, 1) + (doy.day_num - 1) * INTERVAL '1 day' AS holiday_date
FROM your_table t
CROSS JOIN days_of_year doy
WHERE doy.day_num <= LENGTH(t."Date values")
  AND SUBSTRING(t."Date values" FROM doy.day_num FOR 1) = '1'
ORDER BY t.currency, t.year, doy.day_num;

SQL Server 版本 #

SQL Server可以用递归CTE生成数字序列：

WITH days_of_year AS (
    SELECT 
        year,
        CASE WHEN year % 4 = 0 AND (year % 100 != 0 OR year % 400 = 0) THEN 366 ELSE 365 END AS max_days,
        1 AS day_num
    FROM your_table
    GROUP BY year
    UNION ALL
    SELECT year, max_days, day_num + 1
    FROM days_of_year
    WHERE day_num + 1 <= max_days
)
SELECT 
    t.currency,
    t.year,
    doy.day_num AS holiday_day_of_year,
    DATEADD(DAY, doy.day_num - 1, DATEFROMPARTS(t.year, 1, 1)) AS holiday_date
FROM your_table t
JOIN days_of_year doy ON t.year = doy.year
WHERE doy.day_num <= LEN(t.[Date values])
  AND SUBSTRING(t.[Date values], doy.day_num, 1) = '1'
ORDER BY t.currency, t.year, doy.day_num
OPTION (MAXRECURSION 400); -- 最多366天，400的递归上限足够

MySQL 8.0+ 版本 #

MySQL 8.0及以上支持递归CTE，代码如下：

WITH RECURSIVE days_of_year AS (
    SELECT 
        year,
        CASE WHEN year % 4 = 0 AND (year % 100 != 0 OR year % 400 = 0) THEN 366 ELSE 365 END AS max_days,
        1 AS day_num
    FROM your_table
    GROUP BY year
    UNION ALL
    SELECT year, max_days, day_num + 1
    FROM days_of_year
    WHERE day_num + 1 <= max_days
)
SELECT 
    t.currency,
    t.year,
    doy.day_num AS holiday_day_of_year,
    DATE_ADD(DATE_FORMAT(CONCAT(t.year, '-01-01'), '%Y-%m-%d'), INTERVAL (doy.day_num - 1) DAY) AS holiday_date
FROM your_table t
JOIN days_of_year doy ON t.year = doy.year
WHERE doy.day_num <= LENGTH(t.`Date values`)
  AND SUBSTRING(t.`Date values`, doy.day_num, 1) = '1'
ORDER BY t.currency, t.year, doy.day_num;

关键注意事项 #

• 替换实际表名：把代码里的your_table换成你真实的表名；Date values列因为包含空格，不同数据库需要用不同符号包裹（PostgreSQL用双引号，SQL Server用方括号，MySQL用反引号）。
• 闰年判断：代码里已经包含了标准的闰年判断逻辑，确保生成的序列长度和字符串长度一致，避免出现越界错误。
• 字符串索引：所有代码都默认字符串的第1位对应当年的第1天（1月1日），和你的需求完全匹配。

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