Oracle字符串列赋值及含工作日/节假日标记的月度字符串解析

映射规则对齐说明 #

这个字段存储的W、H标记是和当月日期顺序严格一一对应的：

• 字符串从左到右的第N个字符，对应当前自然月的第N天
• 字符值为W代表当天是工作日，为H代表当天是休息日
• 比如给出的示例标记串WHHHHHHHWHHHHHHHWHHHHHHHWHHHHH总长度为29，刚好匹配闰年2月的全月天数，第一个位置的W对应2月1日，后续位置逐天对齐即可
• 解析的核心就是定位每个字符的位置，通过位置偏移算出对应的实际日期值

可直接复用的解析SQL #

假设业务表存储了两类核心信息：目标统计月（统一存当月1日的日期值，避免精度偏差）、对应月份的holiday_list标记串，可通过逐字符拆分的方式完成解析。
如果针对单条月度记录做解析，可以直接用层级查询生成位置序号，写法更简洁：

SELECT
  -- 计算位置对应的实际日期：当月1日 + (字符位置-1)天
  TRUNC(target_month, 'MM') + (LEVEL - 1) AS calendar_date,
  -- 取出指定位置的标记值
  SUBSTR(holiday_list, LEVEL, 1) AS flag_value,
  -- 转成业务可读的日期类型
  CASE SUBSTR(holiday_list, LEVEL, 1)
    WHEN 'W' THEN '工作日'
    WHEN 'H' THEN '节假日'
    ELSE '非法标记值'
  END AS date_type
FROM biz_month_holiday
-- 替换为实际需要解析的月度数据条件
WHERE target_month = DATE '2024-06-01'
-- 基于标记串的总长度生成连续位置序号
CONNECT BY LEVEL <= LENGTH(holiday_list)
  -- 避免层级连接产生的重复笛卡尔积
  AND PRIOR target_month = target_month
  AND PRIOR SYS_GUID() IS NOT NULL

如果需要一次解析整表的所有月度数据，可以用独立序号表关联的写法，长周期跑批性能更稳定。

结果筛选操作 #

拿到逐天的解析结果后，可根据业务需求快速筛出对应日期：

• 筛选所有工作日日期：在结果集后追加过滤条件WHERE flag_value = 'W'
• 筛选所有节假日日期：将过滤条件替换为WHERE flag_value = 'H'

落地校验注意事项 #

• 提前校验标记串长度和当月总天数是否完全匹配，避免因长度不一致出现日期错位
• 提前排查标记串中是否存在W、H以外的非法字符，避免解析结果出现偏差
• 计算日期前必须先把目标月的日期截断到当月第一天，防止原存储值带时分秒导致的日期偏移

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