将自定义列添加到日历表中，其中包含每年12月的第二个星期日及之后的下一年的年号

Question

我已经创建了以下日历表:

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
)

我缺少一个遵循以下规则的自定义日历列:

• 从每年12月的第二个星期日(含)开始，该列应包含'X'和下一年的年号的串联。

例子:

tbody> <<tr>

calendar_date	custom_column
2022-12-10	X2022
2022-12-11	X2023
2022-12-12	X2023
…	…
2023-12-09	X2023
2023-12-10	X2024
2023-12-11	X2024

这里可能会对您有所帮助:

SELECT
*,
CASE
WHEN calendar_date between (select calendar_date from calendar_table
where month_of_year = 12 -- December
AND day_of_week_start_monday = 7 -- Sunday
AND day_of_week_ordinal = 2 ) and (select date_trunc('year',calendar_date + interval '1 year') - interval '1 day')
THEN 'X' || date_part('year', calendar_date) +1
ELSE 'X' || date_part('year', calendar_date)

END AS is_second_sunday_of_december从calendar_table;

https://dbfiddle.uk/WynGf5w_

问题是它只能按年工作，所以可能需要更多的调整!

更新:

给你:

CASE
WHEN 
year_of_date = (select year_of_date from calendar_table where month_of_year = 12 -- December
AND day_of_week_start_monday = 7 -- Sunday
AND day_of_week_ordinal = 2)  
AND calendar_date between (select calendar_date from calendar_table
where month_of_year = 12 -- December
AND day_of_week_start_monday = 7 -- Sunday
AND day_of_week_ordinal = 2 ) and (select date_trunc('year',calendar_date + interval '1 year') - interval '1 day')
THEN 'X' || date_part('year', calendar_date) + 1
else 'X' || date_part('year', calendar_date)
END AS is_second_sunday_of_december
from calendar_table 

小提琴

我希望这次链接可以工作，如果没有，在末尾有下划线。