📚 本文配套课程 · SQL 系列
🎬 B 站课程：DAX「别裁新解」 — https://www.bilibili.com/cheese/play/ss8780
- 【航季日历】航空日期维度表和节假日维度表（下） 202507
近期在航空分析中，遇到一个看似简单，实则不麻烦的问题：如何对比不同年度、同一节假日的航空收益和航班计划量。
由于中国的节假日每年变化，在 Tableau 中写 IF else 显然不显示，最好的方式当然是制作一个日期表，预先记录每个日期对应的“属性”，比如年度、月份、假节日。
后来发现，在对比“元旦”假期时，由于会存在跨年度的情况，我使用航班日期而来的年度，无法获得正确的结果。比如203年的“元旦”可能对应2022年12月31日。
于是，更稳妥的方式就是在日期表中增加“航季”。
航季相当于某些公司的“会计年度”，属于美国多时区国家的特色之一，国际航协显然也受了“美国特色”的影响，甚至蔓延到全世界的航空管理。
如何在 Postgresql 中创建一个日期表，并能自动更新每天对应的航季呢？
1、创建日期表
我参考了 Github 上的一个英文日历表（https://gist.github.com/duffn/38449526e00abb47f4ec292f0491313d#file-creating_a_date_dimension_table_in_postgresql-sql）。
在此基础上，我注释掉了不用的一些部分（不常用的字段，我更喜欢直接在 Tableau 中计算而来），同时增加了几个自定义航空字段。
DROP TABLE if exists d_date;
CREATE TABLE d_date
(
id INT NOT NULL,
"date" DATE NOT NULL,
--formatted_date VARCHAR(10) NOT NULL,
--full_date varchar(20) not null,
-- day_name VARCHAR(12) NOT NULL, -- 之前是7位，改为12位，
-- day_name_short VARCHAR(3) NOT NULL,
--day_of_week INT NOT NULL,
is_weekend BOOLEAN NOT NULL, -- 修改位置到前面
--day_of_month INT NOT NULL,
--day_of_quarter INT NOT NULL,
--day_of_year INT NOT NULL,
--week_of_month INT NOT NULL,
week_of_year INT NOT NULL,
week_of_year_iso CHAR(8) NOT NULL,
-- "month" INT NOT NULL,
month_name_cn VARCHAR(9) NOT NULL,
month_name_en CHAR(5) ,
-- "quarter" INT NOT NULL,
-- quarter_name CHAR(2) NOT NULL,
-- "year" INT NOT NULL,
-- year_month VARCHAR(12) not null,
-- year_quarter VARCHAR(12) not null,
--first_day_of_week DATE NOT NULL,
--last_day_of_week DATE NOT NULL,
--first_day_of_month DATE NOT NULL,
--last_day_of_month DATE NOT NULL,
--first_day_of_quarter DATE NOT NULL,
--last_day_of_quarter DATE NOT NULL,
--first_day_of_year DATE NOT NULL,
--last_day_of_year DATE NOT NULL,
flight_season_year VARCHAR(4) ,
flight_season VARCHAR(4) ,
is_Chinese_holiday BOOLEAN, -- 新增，是否中国节假日
Chinese_holiday CHAR(5) ,-- 新增，是否中国节假日
is_Chinese_workday BOOLEAN, -- 新增，是否工作日，t 是，包含调休
factor_workday INT -- 加班因子，工资两倍或者三倍
);
ALTER TABLE public.d_date ADD CONSTRAINT pk_d_date PRIMARY KEY (id);
----CREATE INDEX pk_d_date
-- ON d_date("date");
COMMIT; 2、手动方式更新航季属性
难点在于，如何更新航季？我尝试问了一下 GPT，它虽然答对了航季的区间，但是没有给出合理的计算逻辑。
准确地计算夏季和冬季，我们需要判断“三月最后一个周日”和“十月最后一个周六”。
假设我这里已经知道了各年度的“航季分割点”，可以使用如下的 update 更新数据表中的相关字段值。
UPDATE d_date
SET
flight_season_year =
CASE
WHEN "date" BETWEEN '2022-10-30' AND '2023-03-25' THEN '2022'
WHEN "date" BETWEEN '2023-03-26' AND '2023-10-28' THEN '2023'
WHEN "date" BETWEEN '2023-10-29' AND '2024-03-30' THEN '2023'
WHEN "date" BETWEEN '2024-03-31' AND '2024-10-26' THEN '2024'
WHEN "date" BETWEEN '2024-10-27' AND '2025-03-29' THEN '2024'
--ELSE NULL
END ,
flight_season =
CASE
WHEN "date" BETWEEN '2022-10-30' AND '2023-03-25' THEN '冬春'
WHEN "date" BETWEEN '2023-03-26' AND '2023-10-28' THEN '夏秋'
WHEN "date" BETWEEN '2023-10-29' AND '2024-03-30' THEN '冬春'
WHEN "date" BETWEEN '2024-03-31' AND '2024-10-26' THEN '夏秋'
WHEN "date" BETWEEN '2024-10-27' AND '2025-03-29' THEN '冬春'
--ELSE NULL
END ;
COMMIT;不过，这样很明显过于繁琐，能否直接用判断找到分割点清单呢？
3、找到冬夏航季区间的分割点“航季分割点”
我首先想确定的是，每年的三月份，有几个周日呢？如果都有四个就简单了，不过直觉中大概率是有五个周日的情况的——毕竟每年不是48个周，而是52个周。为此，可以在 Postgsql 的代码中验证一下：
-- 1\ how many sundays in each march each year ?
select -- distinct
EXTRACT(YEAR FROM "date") ,
EXTRACT(MONTH FROM "date") ,
count(*)
from d_date dd
where EXTRACT(ISODOW FROM "date") =7 and EXTRACT(MONTH FROM "date") in (3,10)
group by 1,2从结果可以看出，有不少月份都有5个 Sunday，因此，接下来难点就是“找到最后一个周日”，作为区间的起点。
year month count
2020 3 5 ———表示2020年3月有5个周日
2020 10 4
2021 3 4
2021 10 5
2022 3 4
2022 10 5
2023 3 4
2023 10 5
2024 3 5
1）找到三月和十月的分割点日期
“最后一个周日”是典型的表计算场景，可以使用 row_number 计算“每个月份中周日的序号”，如下所示：
-- find the last sunday in March and October ?
select -- distinct
"date",
EXTRACT(YEAR FROM "date") as "year",
EXTRACT(MONTH FROM "date") as "month",
TO_CHAR( "date",'Day' ) as "workday",
--EXTRACT(ISODOW FROM "date"),
row_number() over (partition by EXTRACT(YEAR FROM "date") ,EXTRACT(MONTH FROM "date") order by "date" ) as "N_in_month"
from d_date dd
where EXTRACT(ISODOW FROM "date") =7 and EXTRACT(MONTH FROM "date") in (3,10)
这样就会返回每个三月、十月，每个周日及其月内的次序。
Date Year Month Workday N_in_month
2022-03-13 2022 3 Sunday 2
2022-03-20 2022 3 Sunday 3
2022-03-27 2022 3 Sunday 4
2022-10-02 2022 10 Sunday 1
2022-10-09 2022 10 Sunday 2
2022-10-16 2022 10 Sunday 3
2022-10-23 2022 10 Sunday 4
2022-10-30 2022 10 Sunday 5
2023-03-05 2023 3 Sunday 1
2023-03-12 2023 3 Sunday 2
2023-03-19 2023 3 Sunday 3
2023-03-26 2023 3 Sunday 4
2023-10-01 2023 10 Sunday 1
新的难点来了，如何保留上面标红的部分？总不能在表计算基础上再来一个 MAX 表计算吧？
换个方式，这里的“最后一个周日”，可以变换为“有几个周日”的简单聚合问题。这样原本复杂的窗口函数嵌套，就变成了简单的直接聚合。
一个月有几个周日，这个在前面一步已经完成了。
2）使用 SQL获得航季区间的分割点
为了简化查询，这里是用 Common Table Expression 构建一个子查询，而后将其与明细表 join 获得每个日期对应的三月、十月的“Sunday”数量。
--第一次嵌套，在三月、十月的每个周日后，增加月内的次序和当月 Sunday 数量
with Sunday_count as (
select -- distinct
d."year",
d."month",
count(*) as sunday_count
from d_date d
where d.day_of_week =7 and d."month" in (3,10)
group by 1,2
)
select dd."date",dd."year",
dd."month",dd.day_of_week,
row_number() over (partition by dd."year",dd."month", dd.day_of_week) as "N_in_month",
Sunday_count.sunday_count as "sunday_count"
from d_date dd
join Sunday_count on dd."year" = Sunday_count."year" and dd."month" = Sunday_count."month"
where dd.day_of_week=7
order by dd."date"结果如下所示：
在此基础上，只保留最后两列相等的行，就能获得所有年度中，航季区间的分割点。
看来还需要一次嵌套：
-- 第二次嵌套，保留三月和十月 最后一个周日
with Sunday_count as (
select -- distinct
d."year",
d."month",
count(*) as sunday_count
from d_date d
where d.day_of_week =7 and d."month" in (3,10)
group by 1,2
)
select r."date"
from (
select dd."date",dd."year",
dd."month",dd.day_of_week,
row_number() over (partition by dd."year",dd."month", dd.day_of_week) as "N_in_month",
Sunday_count.sunday_count as "sunday_count"
from d_date dd
join Sunday_count on dd."year" = Sunday_count."year" and dd."month" = Sunday_count."month"
where dd.day_of_week=7
order by dd."date"
) as r
where r."N_in_month" = r.sunday_count
如此一来，就获得了一个清单了。 从2010年到2029年，航季的分割点如下所示：
2010-03-28
2010-10-31
2011-03-27
2011-10-30
2012-03-25
2012-10-28
2013-03-31
2013-10-27
2014-03-30
2014-10-26
2015-03-29
2015-10-25
2016-03-27
2016-10-30
2017-03-26
2017-10-29
2018-03-25
2018-10-28
2019-03-31
2019-10-27
2020-03-29
2020-10-25
2021-03-28
2021-10-31
2022-03-27
2022-10-30
2023-03-26
2023-10-29
2024-03-31
2024-10-27
2025-03-30
2025-10-26
2026-03-29
2026-10-25
2027-03-28
2027-10-31
2028-03-26
2028-10-29
2029-03-25
2029-10-28
有了这个日期，就可以使用 Update 更新数据表中的值了。
4、AI 参与：自动更新“航季分割点”标记
特别注意，后面补充了一个更简单的方法，不需要使用窗口函数！
回到 d_date 数据表，使用 update 更新每个日期对应的值，为了便于后期计算，可以先创建一个字段列：航季分割点，is_flight_season_inteval
1)航季分割点，标记为数据库标记
我承认，这个超过我目前的 SQL 水平了，我还没有系统学习 Update 的使用，不过我现在有了 GPT，我问了它：
我这里有一个 update 命令 ，用来把符合条件的日期标记为 true，表示为 航季的分割点。
UPDATE d_date
SET
is_flight_season_interval = true
where
"date" in ('2022-10-30' , '2023-03-25' ) ;
COMMIT; 我现在通过如下的 sql 找到了数据库中每一年的分割点，如何将上面的 update 命令更新一下。
-- 第二次嵌套，保留三月和十月 最后一个周日
-- 第二次嵌套，保留三月和十月 最后一个周日
with Sunday_count as (
select -- distinct
d."year",
d."month",
count(*) as sunday_count
from d_date d
where d.day_of_week =7 and d."month" in (3,10)
group by 1,2
)
select r."date"
from (
select dd."date",dd."year",
dd."month",dd.day_of_week,
row_number() over (partition by dd."year",dd."month", dd.day_of_week) as "N_in_month",
Sunday_count.sunday_count as "sunday_count"
from d_date dd
join Sunday_count on dd."year" = Sunday_count."year" and dd."month" = Sunday_count."month"
where dd.day_of_week=7
order by dd."date"
) as r
where r."N_in_month" = r.sunday_countGPT 告诉了我答案，不仅正确，而且帮我优化了代码！
我在上面使用了一次 CTE 和两次嵌套，虽然符合个人的写字习惯，但是并非容易后人理解，你看 GTP 的优化。
- 找到每年三月和十月的最后一个周日
WITH Sunday_count AS (
SELECT
d."year",
d."month",
COUNT(*) AS sunday_count
FROM d_date d
WHERE d.day_of_week = 7 AND d."month" IN (3, 10)
GROUP BY 1, 2
),
Last_Sundays AS (
SELECT
dd."date",
dd."year",
dd."month",
dd.day_of_week,
ROW_NUMBER() OVER (PARTITION BY dd."year", dd."month" ORDER BY dd."date" DESC) AS rn
FROM d_date dd
JOIN Sunday_count sc
ON dd."year" = sc."year" AND dd."month" = sc."month"
WHERE dd.day_of_week = 7
)
-- 更新操作，标记航季分割点
UPDATE d_date
SET
is_flight_season_interval = TRUE
WHERE
"date" IN (SELECT "date" FROM Last_Sundays WHERE rn = 1);
COMMIT;这样，我就在数据库中，把“每年三月和十月最后一个周日”标记为了分割点。
2）获得各个年度的航季开始、结束日期，关系型结构
接下来，我想要标记航季了。 这当然还需要 GPT 帮我。
GPT：
要标记每年的航季区间，将三月最后一个周日（包含）到十月最后一个周日（不含）区间对应的 flight_season 字段标记为“夏季”，其他时间标记为“冬季”，可以使用以下步骤：1. 确定每年的三月和十月的最后一个周日。
2. 更新这些日期之间的记录，将 flight_season 字段标记为“夏季”。
3. 将其他日期的 flight_season 字段标记为“冬季”。
上面的逻辑只标记了航季的夏季、冬季航季，没有区分年度。为此，还需增加一点逻辑。GPT 给出了优化结果。
-- 假设已经有 d_date 表，并包含 flight_season 和 flight_season_year 字段
ALTER TABLE d_date ADD COLUMN flight_season VARCHAR(10);
ALTER TABLE d_date ADD COLUMN flight_season_year INT;
-- 找到每年三月和十月的最后一个周日
WITH Sunday_count AS (
SELECT
d.year,
d.month,
COUNT(*) AS sunday_count
FROM d_date d
WHERE d.day_of_week = 7 AND d.month IN (3, 10)
GROUP BY 1, 2
),
Last_Sundays AS (
SELECT
dd.datum,
dd.year,
dd.month,
ROW_NUMBER() OVER (PARTITION BY dd.year, dd.month ORDER BY dd.datum DESC) AS rn
FROM d_date dd
JOIN Sunday_count sc
ON dd.year = sc.year AND dd.month = sc.month
WHERE dd.day_of_week = 7
),
Season_Boundaries AS (
SELECT
year,
MAX(CASE WHEN month = 3 AND rn = 1 THEN datum END) AS start_summer,
MAX(CASE WHEN month = 10 AND rn = 1 THEN datum END) AS end_summer,
LAG(MAX(CASE WHEN month = 3 AND rn = 1 THEN datum END)) OVER (ORDER BY year) AS prev_start_summer
FROM Last_Sundays
GROUP BY year
)
-- 更新操作，标记航季区间和年度
UPDATE d_date d
SET
flight_season = CASE
WHEN d.datum >= sb.start_summer AND d.datum < sb.end_summer THEN '夏季'
ELSE '冬季'
END,
flight_season_year = CASE
WHEN d.datum >= sb.start_summer THEN sb.year
ELSE sb.year - 1
END
FROM
Season_Boundaries sb
WHERE
d.year = sb.year OR d.year = sb.year - 1
AND (d.datum >= sb.prev_start_summer OR sb.prev_start_summer IS NULL);
-- 提交更新
COMMIT;相当不错！依次就更新了数据库中所有日期的航季标签了。
5、后补:找到分割点的新方法
客户的一位 SQL 高手提示，可以通过4月1日和11月1日所在周的周一去识别 开始日期。这个是一个非常巧妙的办法。
这里使用 SQL 查询，可以一次性获得每年4月1日和10月1日的属性，并根据它在本周的天数位置，倒推上周日的日期。
SELECT
"date" AS start_date,
"year",
day_of_week, --周几
--TO_CHAR( "date", '"W"IW'),
EXTRACT(DOW FROM "date") as dow, -- 本周的第几天？ 倒推可以获得上周日的日期
"date"- EXTRACT(DOW FROM "date")::int as start_d
FROM d_date
WHERE ("month" = 4 or "month" = 11 ) AND EXTRACT(DAY FROM "date") = 1如下所示，
于此，就可以这样更新了。
-- 找到航季日期的新方法：
-- 找到每年三月和11月的最后一个周日
WITH Week_Starts AS (
-- 找到每年4月1日和11月1日的日期
SELECT
"date" AS start_date,
"year",
day_of_week, --周几，相当于下面的 dow 值
--TO_CHAR( "date", '"W"IW'),
EXTRACT(DOW FROM "date") as dow, -- 本周的第几天？ 倒推可以获得上周日的日期
"date"- EXTRACT(DOW FROM "date")::int as Last_sunday
FROM d_date
WHERE ("month" = 4 or "month" = 11 ) AND EXTRACT(DAY FROM "date") = 1
)
-- 更新操作，标记航季分割点
UPDATE d_date
SET
is_flight_season_interval = TRUE
WHERE
"date" IN (SELECT Last_sunday FROM Week_Starts);
COMMIT;
2024-08-06 by 喜乐君