为日历表添加自定义列：12月第二个周日起显示次年年份标识

已创建如下日历表：

WITH dates AS (
    SELECT EXPLODE(SEQUENCE(TO_DATE('1970-01-01'), TO_DATE('2100-12-31'), INTERVAL 1 DAY)) AS calendar_date
),

calendar_table AS (
    SELECT
      YEAR(calendar_date) * 10000 + MONTH(calendar_date) * 100 + DAY(calendar_date) AS date_integer,
      calendar_date,
      YEAR(calendar_date) AS year_of_date,
      QUARTER(calendar_date) AS quarter_of_year,
      MONTH(calendar_date) AS month_of_year,
      DAY(calendar_date) AS day_of_month,
      WEEKDAY(calendar_date) + 1 AS day_of_week_start_monday,
      DAYOFWEEK(calendar_date) AS day_of_week_start_sunday,
      CASE
        WHEN DAY(calendar_date) >= 1 AND DAY(calendar_date) <= 7 THEN 1
        WHEN DAY(calendar_date) >= 8 AND DAY(calendar_date) <= 14 THEN 2
        WHEN DAY(calendar_date) >= 15 AND DAY(calendar_date) <= 21 THEN 3
        WHEN DAY(calendar_date) >= 22 AND DAY(calendar_date) <= 28 THEN 4
        ELSE 5
      END AS day_of_week_ordinal,
      CASE
        WHEN WEEKDAY(calendar_date) < 5 THEN TRUE
        ELSE FALSE
      END AS is_week_day,
      CASE
        WHEN WEEKDAY(calendar_date) > 4 THEN TRUE
        ELSE FALSE
      END AS is_weekend,
      CASE
        WHEN calendar_date = DATE_TRUNC('month', calendar_date)::DATE THEN TRUE
        ELSE FALSE
      END AS is_first_day_of_month,
      CASE
        WHEN calendar_date = LAST_DAY(calendar_date) THEN TRUE
        ELSE FALSE
      END AS is_last_day_of_month,
      DAYOFYEAR(calendar_date) AS day_of_year,
      WEEKOFYEAR(calendar_date) AS iso_week_of_year,
      EXTRACT(YEAROFWEEK FROM calendar_date) AS iso_year_of_date
    FROM
      dates
)

需求 #

• 添加自定义列custom_column，规则为：每年12月的第二个周日（含当日）起，列值为'X'与次年年份的拼接结果；其余日期为'X'与当年年份的拼接结果。

示例 #

已实现的识别逻辑 #

可通过以下语句标记每年12月的第二个周日：

CASE
    WHEN
        month_of_year = 12
        AND day_of_week_ordinal = 2
        AND day_of_week_start_monday = 7 THEN TRUE
    ELSE FALSE
END AS second_sunday_in_month

解决方案 #

方案1：子查询直接判断 #

在calendar_table的SELECT列表中添加如下列：

CASE
    WHEN calendar_date >= (
        SELECT calendar_date
        FROM calendar_table ct
        WHERE ct.year_of_date = YEAR(calendar_date)
          AND ct.month_of_year = 12
          AND ct.day_of_week_ordinal = 2
          AND ct.day_of_week_start_monday = 7
    ) THEN 'X' || (YEAR(calendar_date) + 1)::TEXT
    ELSE 'X' || YEAR(calendar_date)::TEXT
END AS custom_column

方案2：预计算关联（性能更优） #

先预计算每年12月第二个周日的日期，再关联到日历表中使用：

WITH dates AS (
    SELECT EXPLODE(SEQUENCE(TO_DATE('1970-01-01'), TO_DATE('2100-12-31'), INTERVAL 1 DAY)) AS calendar_date
),
calendar_base AS (
    SELECT
      calendar_date,
      YEAR(calendar_date) AS year_of_date,
      MONTH(calendar_date) AS month_of_year,
      DAY(calendar_date) AS day_of_month,
      WEEKDAY(calendar_date) + 1 AS day_of_week_start_monday,
      CASE
        WHEN DAY(calendar_date) >= 1 AND DAY(calendar_date) <= 7 THEN 1
        WHEN DAY(calendar_date) >= 8 AND DAY(calendar_date) <= 14 THEN 2
        WHEN DAY(calendar_date) >= 15 AND DAY(calendar_date) <= 21 THEN 3
        WHEN DAY(calendar_date) >= 22 AND DAY(calendar_date) <= 28 THEN 4
        ELSE 5
      END AS day_of_week_ordinal,
      YEAR(calendar_date) * 10000 + MONTH(calendar_date) * 100 + DAY(calendar_date) AS date_integer,
      QUARTER(calendar_date) AS quarter_of_year,
      DAYOFWEEK(calendar_date) AS day_of_week_start_sunday,
      CASE WHEN WEEKDAY(calendar_date) < 5 THEN TRUE ELSE FALSE END AS is_week_day,
      CASE WHEN WEEKDAY(calendar_date) > 4 THEN TRUE ELSE FALSE END AS is_weekend,
      CASE WHEN calendar_date = DATE_TRUNC('month', calendar_date)::DATE THEN TRUE ELSE FALSE END AS is_first_day_of_month,
      CASE WHEN calendar_date = LAST_DAY(calendar_date) THEN TRUE ELSE FALSE END AS is_last_day_of_month,
      DAYOFYEAR(calendar_date) AS day_of_year,
      WEEKOFYEAR(calendar_date) AS iso_week_of_year,
      EXTRACT(YEAROFWEEK FROM calendar_date) AS iso_year_of_date
    FROM dates
),
december_second_sundays AS (
    SELECT
        year_of_date,
        calendar_date AS second_sunday_date
    FROM calendar_base
    WHERE month_of_year = 12
      AND day_of_week_ordinal = 2
      AND day_of_week_start_monday = 7
)
SELECT
    cb.*,
    CASE
        WHEN cb.calendar_date >= dss.second_sunday_date THEN 'X' || (cb.year_of_date + 1)::TEXT
        ELSE 'X' || cb.year_of_date::TEXT
    END AS custom_column
FROM calendar_base cb
JOIN december_second_sundays dss ON cb.year_of_date = dss.year_of_date;

逻辑说明 #

1. 通过december_second_sundays CTE提取每年12月的第二个周日日期；
2. 将基础日历表与该CTE按年份关联；
3. 判断每个日期是否大于等于当年的第二个周日：若是则拼接'X'和次年年份，否则拼接'X'和当年年份。

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