请求编写HANA SQL实现跨月TECHNICAL_WEEK的生成逻辑

需求规则梳理 #

• 常规日期：TECH_WEEK填充该日期所在周的周一日期
• 跨月周：当某一周包含两个不同月份时，属于当月的日期（及该周内剩余的同月份日期）的TECH_WEEK统一填充为当月1日
• 跨月周后的下一周，恢复按周一日期填充

示例结果 #

HANA SQL实现代码 #

WITH date_range AS (
    -- 生成目标日期范围，示例为2023年5月29日至6月4日，可替换为实际业务数据源
    SELECT TO_DATE('20230529', 'YYYYMMDD') + INTERVAL (n - 1) DAY AS day
    FROM SERIES_GENERATE_INTEGER(1, 1, 7)
),
week_info AS (
    SELECT
        day,
        -- 获取当前日期所在周的周一（ISO周，周一为周起始）
        TRUNC(day, 'IW') AS week_monday,
        -- 获取当前日期所属月份的第一天
        TRUNC(day, 'MM') AS month_first_day,
        -- 判断当前日期所在周是否为跨月周
        CASE
            WHEN EXISTS (
                SELECT 1
                FROM date_range dr
                WHERE TRUNC(dr.day, 'IW') = TRUNC(day, 'IW')
                AND EXTRACT(MONTH FROM dr.day) != EXTRACT(MONTH FROM day)
            ) THEN 1
            ELSE 0
        END AS is_cross_month_week
    FROM date_range
)
SELECT
    TO_VARCHAR(day, 'YYYYMMDD') AS DAY,
    CASE
        -- 跨月周场景，使用当月1日作为TECH_WEEK
        WHEN is_cross_month_week = 1 THEN TO_VARCHAR(month_first_day, 'YYYYMMDD')
        -- 常规场景，使用所在周的周一日期
        ELSE TO_VARCHAR(week_monday, 'YYYYMMDD')
    END AS TECH_WEEK
FROM week_info
ORDER BY day;

代码说明 #

• date_range CTE：生成待处理的日期序列，实际使用时可直接替换为你的业务日期表或数据源
• week_info CTE：计算每个日期的周起始日、当月首日，并标记该日期所在周是否为跨月周
• 最终查询根据跨月标记，选择对应的TECH_WEEK填充值

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