在2024/8的一篇博客中，我介绍了使用 pg 创建包含航季字段的航空日期维度表的方法，时隔讲解一年，我准备做一个新版本，改用新的方法。
参考：【民航航季日历】使用 Postgresql 和 AI 制作航空日期维度表 2024/8
在之前，我把航季 Flight_season 作为物理字段直接写入了 d_date 数据表，这样好处是稳定、查询性能高，考虑到航季是不变的日期属性，因此一次性写入十年也无妨。
不过，我希望换一个“视图 View”方式来提供第二方案，简化 D_date 的复杂性，改用视图提供航季字段；同时一并提供节假日数据。
一、日期表的表结构和关系
1、多个日期表的关系图
最重要的当然是日期表明细——d_date，其次这里新增一个节假日表——Chinese_holiday；同时，航季作为确定性的业务规则，不需要单独数据表存储，只需要考虑如何将「业务规则」转化为特定表或特定查询的字段标识即可。
- 夏航季 (Summer Season) 从每年 3 月最后一个星期日（含当天）开始，至 10 月最后一个星期六（含当天）结束。
- 冬航季 (Winter Season) 从 10 月最后一个星期日（含当天）开始，至次年 3 月最后一个星期六（含当天）结束。
比如，创建一个 view 查询，在查询 d_date 明细表基础上增加每一天的归属航季，这里标记为 V_date
因此，这里的数据表关系 如下：
2、创建日期表并插入数据
这里使用 sql 直接创建日期表如下，简化字段：
SQL
SET search_path TO xilejun;
-- 第一步，创建日期表
DROP TABLE if exists d_date;
CREATE TABLE xilejun.d_date -- ERROR: no schema has been selected to create in
(
id INT NOT NULL,
"date" DATE NOT NULL,
--formatted_date VARCHAR(10) NOT NULL,
--full_date varchar(20) not null,
day_of_week INT NOT NULL,
is_weekend BOOLEAN NOT NULL, -- 修改位置到前面
--day_of_month 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(12) NOT NULL,
"month" INT NOT NULL,
--month_name_cn VARCHAR(9) NOT NULL,
--month_name_en CHAR(5) ,
"year" INT NOT NULL
);
ALTER TABLE xilejun.d_date ADD CONSTRAINT pk_d_date PRIMARY KEY (id);
----CREATE INDEX pk_d_date
-- ON d_date("date");
COMMIT;而后，插入2018年之后10年的数据（这里使用了365*10，略有瑕疵）
SQL
-- 第二步，插入基础数据，这里只是关于日期本书的自定义属性
INSERT INTO xilejun.d_date
SELECT TO_CHAR(datum, 'yyyymmdd')::INT AS id,
datum AS "date",
--TO_CHAR(datum, 'dd/mm/yyyy') AS formatted_date,
EXTRACT(ISODOW FROM datum) AS day_of_week, -- Day of week based on ISO 8601 Monday (1) to Sunday (7)
CASE WHEN EXTRACT(ISODOW FROM datum) IN (6, 7) THEN true
ELSE false
END AS is_weekend ,
--EXTRACT(DAY FROM datum) AS day_of_month,
--datum - DATE_TRUNC('quarter', datum)::DATE + 1 AS day_of_quarter,
--EXTRACT(DOY FROM datum) AS day_of_year,
--TO_CHAR(datum, 'W')::INT AS week_of_month,
--EXTRACT(WEEK FROM datum)::INT AS week_of_year,
EXTRACT(ISOYEAR FROM datum) || TO_CHAR(datum, '"-W"IW-') || EXTRACT(ISODOW FROM datum) AS week_of_year_iso, -- ISO 8601 week number of year */
cast(EXTRACT(MONTH FROM datum) as INTEGER) AS "month",
EXTRACT(YEAR FROM datum) AS "year"
/*
datum + (1 - EXTRACT(ISODOW FROM datum))::INT AS first_day_of_week,
datum + (7 - EXTRACT(ISODOW FROM datum))::INT AS last_day_of_week,
datum + (1 - EXTRACT(DAY FROM datum))::INT AS first_day_of_month,
DATE_TRUNC('MONTH', datum) + INTERVAL '1 MONTH - 1 day')::DATE AS last_day_of_month,
DATE_TRUNC('quarter', datum)::DATE AS first_day_of_quarter,
(DATE_TRUNC('quarter', datum) + INTERVAL '3 MONTH - 1 day')::DATE AS last_day_of_quarter,
TO_DATE(EXTRACT(YEAR FROM datum) || '-01-01', 'YYYY-MM-DD') AS first_day_of_year,
TO_DATE(EXTRACT(YEAR FROM datum) || '-12-31', 'YYYY-MM-DD') AS last_day_of_year,
*/
FROM (SELECT '2020-01-01'::DATE + SEQUENCE.DAY AS datum -- 原来从时间戳开始 SELECT '1970-01-01'::DATE + SEQUENCE.DAY AS datum
FROM GENERATE_SERIES(0, 365*25) AS SEQUENCE (DAY)
GROUP BY SEQUENCE.DAY) DQ
ORDER BY 1;
COMMIT;
于此以来，我就创建了一个简化版本的日期表，如下所示：
接下来，我们先重点完成航季的标记，而后增加节假日数据。
二、在逻辑表中构建航季字段
在去年的文章中，使用了比较复杂的函数和循环（loop）计算航季，这个是 GPT 推荐的方法，其实很多地方存在明显冗余。
BEGIN
-- 计算当年3月最后一个周日
last_sunday_march := date_trunc('month', make_date(year_part, 3, 1)) + interval '1 month -1 day';
WHILE EXTRACT(DOW FROM last_sunday_march)::INT <> 0 LOOP
last_sunday_march := last_sunday_march - INTERVAL '1 day';
END LOOP;由于每年的3月份必然是31天，因此没有必要多此一举的 加一个月再减去一天，如果你要问 GPT，它会一本正经地告诉你这是传统上的稳定方法。
我想，国际航空协会之所以用3月计算最后一周，大概率也有这方面的考虑吧。
既然每年3月最后一天都是31日，那我们可以以此为起点，寻找3月的最后周日（last_Sun_Mar）
按照上面枚举循环的方式，其实只有7种情况：3月31日分别是周一、周二、周三、周四、周五、周六、周日。
| 年份 | 日期 | 星期 |
|---|---|---|
| 2017 | 2017-03-31 | Friday |
| 2018 | 2018-03-31 | Saturday |
| 2019 | 2019-03-31 | Sunday |
| 2020 | 2020-03-31 | Tuesday |
| 2021 | 2021-03-31 | Wednesday |
| 2022 | 2022-03-31 | Thursday |
| 2023 | 2023-03-31 | Friday |
| 2024 | 2024-03-31 | Sunday |
1、计算夏季航季的分割点
1）最简单的情况（3/31 = Sat）
2018年3月31日，都是周六，此时这一天及之后到9月都是“夏航季”。
我们可以视为这一天没有任何偏移。
2）增加偏移计算 （3/31 ≠ Sun）
以2023年3月31日来看，这一天是周五。
以这一天为锚点，往前能找到的最近的周六（2023/3/25），只需要减去6天。
同样的逻辑，如果3/31正好是周三，比如2021/3/31，那么就可以往前倒推3天。
按照上面的距离，我们需要的是：
- 如果 3/31 是周六 → 不需要偏移，3/31 就是最后一个周六。
- 如果 3/31 是周日 → 应该往前减 1 天，到 3/30。
- 如果 3/31 是周五 → 应该往前减 6 天，到 3/25。
- 若是周一 → 往前减 2 天。
- ……
目标：3/31 所在周内，最靠后的那个周六。
3）使用 dow 计算周几，而后倒推最后的周六
为了兼容每一种情况，就需要计算每年3月31日对应的周，pg 中提供了 dow（dayofweek ）和 isodow 两种标准，前者以周日为0，后者以周日为7；不过对于周六而言，结果是完全相同的。
| 星期 | extract(dow) | extract(isodow) |
|---|---|---|
| 星期日 | 0 | 7 |
| 星期一 | 1 | 1 |
| 星期二 | 2 | 2 |
| 星期三 | 3 | 3 |
| 星期四 | 4 | 4 |
| 星期五 | 5 | 5 |
| 星期六 | 6 | 6 |
如果3/31正好是周六（如2018年），这一天就不需要任何偏移，我们需要把6转化为 0，其他不变。如果使用 ISODOW，假设3/31是周日，那么 isodow=7 就会计算“上上周六”。
| sodow | (isodow + 1) % 7 | 应减天数 | 是否正确 |
|---|---|---|---|
| 6 | (6+1)%7=0 | 0 | ✅ 周六 |
| 7 | (7+1)%7=1 | 1 | ✅ 周日 |
| 5 | (5+1)%7=6 | 6 | ✅ 周五 |
| 1 | (1+1)%7=2 | 2 | ✅ 周一 |
因此，反复很多次之后，最佳实践依然是 dow 值，如下：
- 计算3月31日对应的 DOW值： EXTRACT(DOW FROM 3/31)::INT
- 如果是周六，转化为0，DOW +1 然后除以7计算余数 (isodow + 1) % 7
- 使用3/31 减去上面的 DOW 值： 2021/3/1 – (isodow + 1) % 7
- 结果标记为 last_Sat_Mar，表示三月的最后一个 周六，即“夏季航季分割点”
如下所示：
-- 第五步， 使用 view 查询航季信息
select
dd.date,
dd.day_of_week,
make_date(dd.year,3,31) ,
extract(isodow from make_date(dd.year,3,31) ) as isodow,
make_date(dd.year,3,31) - ((extract(isodow from make_date(dd.year, 3, 31))::int+1)%7) as last_Sat_Mar
from xilejun.d_date dd
where dd.year >= 2018 and dd.month =3
and dd.date in ('2018-03-31','2019-03-31','2020-03-31','2021-03-31','2022-03-31','2023-03-31','2024-03-31','2025-03-31'
)上述数据运行如下所示：
2、计算秋季航季分割点并增加航季字段
在完成夏季航季分割点之后，秋季也就同理简单了：
- 计算10月30日对应的 dow 值
- 如果是周六，转化为0，DOW +1 然后除以7计算余数 (isodow + 1) % 7
- 10/30减去上述 天数，标记“last_Sat_Oct”，表示十月的最后一个周六，即“秋季航季分割点”
- 使用last_Sat_Mar 和last_S at_Oct 将不同区间分割为航季和秋季
代码如下：
SQL
select
dd.date,
dd.day_of_week,
make_date(dd.year,3,31) ,
extract(isodow from make_date(dd.year,3,31) ) as isodow_0331,
make_date(dd.year,3,31) - ((extract(isodow from make_date(dd.year, 3, 31))::int+1)%7) as last_Sat_Mar,
make_date(dd.year,10,30) ,
extract(isodow from make_date(dd.year,10,30) ) as isodow_1030,
make_date(dd.year,10,30) - ((extract(isodow from make_date(dd.year, 10,30))::int+1)%7) as last_Sat_Oct
from xilejun.d_date dd
where dd.year >= 2018 and dd.month =3
and dd.date in ('2018-03-31','2019-03-31','2020-03-31','2021-03-31','2022-03-31','2023-03-31','2024-03-31','2025-03-31', '2026-03-31','2027-03-31','2028-03-31','2029-03-31'
, '2018-10-30','2019-10-30','2020-10-30','2021-10-30','2022-10-30','2023-10-30','2024-10-30','2025-10-30' , '2026-10-30','2027-10-30','2028-10-30','2029-10-30'
)如下所示：
有了上述两个分割点，判断航季就简单了。
需要注意的是，由于航季是跨年的，所以还需要一个字段区分是航季年度，从3月到当年10月是当年的夏季，而从10月到次年的3月则是当年的冬季——冬季跨越了公元纪年，因此不能从 date 字段拆分而来。
- 夏季对应的年度，就是每个日期的年度部分，可以从 extract 获得，可以直接使用d_date 表的 year 字段
- 冬季对应的年度，需要从“last_Sat_Oct”即冬季分割点提取，不能直接从 d_date 中获得对应年度。
逻辑如下所示：
SQL
--CTE
with season as (
select
dd.date,
dd.day_of_week,
dd.year,
make_date(dd.year,3,31) ,
extract(isodow from make_date(dd.year,3,31) ) as isodow_0331,
make_date(dd.year,3,31) - ((extract(isodow from make_date(dd.year, 3, 31))::int+1)%7) as last_Sat_Mar,
make_date(dd.year,10,30) ,
extract(isodow from make_date(dd.year,10,30) ) as isodow_1030,
make_date(dd.year,10,30) - ((extract(isodow from make_date(dd.year, 10,30))::int+1)%7) as last_Sat_Oct
from xilejun.d_date dd
where dd.year >= 2020
)
select
season.date,
season.day_of_week,
season.last_Sat_Oct,
case when season.date >=last_Sat_Mar and season.date < last_Sat_Oct then 'Summer' else 'Winter' end flight_season,
case when season.date <last_Sat_Mar then season."year" -1 else season."year" end flight_season_year
-- case when season.date >=last_Sat_Mar and season.date < last_Sat_Oct then season."year" else extract(YEAR from season.last_Sat_Oct) end flight_season_year
from season 这样就为每个日期增加了航季判断和航季年度了。
三、创建节假日信息表，并从 json写入数据
节假日信息也用类似方法创建，并可以直接将 json 数据解析写入，如下所示：
这里仅写入2025年的节假日信息。
SQL
-- 第三步 创建 节假日表
SET search_path TO xilejun;
DROP TABLE if exists xilejun.d_holiday;
CREATE TABLE xilejun.d_holiday
(
-- id INT NOT NULL,
"date" DATE NOT NULL -- 主键
, Chinese_holiday CHAR(10) -- 新增，是否中国节假日
,is_Chinese_workday BOOLEAN -- 新增，是否工作日，t 是，包含调休
--,factor_workday INT -- 加班因子，工资两倍或者三倍
);
ALTER TABLE xilejun.d_holiday ADD CONSTRAINT pk_holiday_d_date PRIMARY KEY (date);
----CREATE INDEX pk_d_date ON d_date("date");
-- 第四步 解析 json 并写入日期表
WITH cte_Chinese_holidays AS (
SELECT
(h.value->>'date')::DATE AS date,
(h.value->>'holiday')::BOOLEAN AS is_chinese_holiday,
h.value->>'name' AS chinese_holiday,
(h.value->>'wage')::INTEGER AS factor_workday
--(h.value->>'rest')::INTEGER as rest_cnt
from -- 2025年
json_each('{
"01-01":{"holiday":true,"name":"元旦","wage":3,"date":"2025-01-01","rest":22},
"01-26":{"holiday":false,"name":"春节前补班","wage":1,"after":false,"target":"春节","date":"2025-01-26","rest":25},
"01-28":{"holiday":true,"name":"除夕","wage":2,"date":"2025-01-28","rest":27},
"01-29":{"holiday":true,"name":"初一","wage":3,"date":"2025-01-29","rest":1},
"01-30":{"holiday":true,"name":"初二","wage":3,"date":"2025-01-30","rest":1},"01-31":{"holiday":true,"name":"初三","wage":3,"date":"2025-01-31","rest":1},"02-01":{"holiday":true,"name":"初四","wage":2,"date":"2025-02-01","rest":1},"02-02":{"holiday":true,"name":"初五","wage":2,"date":"2025-02-02","rest":1},"02-03":{"holiday":true,"name":"初六","wage":2,"date":"2025-02-03","rest":1},"02-04":{"holiday":true,"name":"初七","wage":2,"date":"2025-02-04","rest":1},"02-08":{"holiday":false,"name":"春节后补班","wage":1,"target":"春节","after":true,"date":"2025-02-08","rest":4},"04-04":{"holiday":true,"name":"清明节","wage":3,"date":"2025-04-04","rest":20},"04-05":{"holiday":true,"name":"清明节","wage":2,"date":"2025-04-05","rest":1},"04-06":{"holiday":true,"name":"清明节","wage":2,"date":"2025-04-06","rest":1},"04-27":{"holiday":false,"name":"劳动节前补班","wage":1,"target":"劳动节","after":false,"date":"2025-04-27","rest":12},"05-01":{"holiday":true,"name":"劳动节","wage":3,"date":"2025-05-01","rest":16},"05-02":{"holiday":true,"name":"劳动节","wage":2,"date":"2025-05-02","rest":1},"05-03":{"holiday":true,"name":"劳动节","wage":3,"date":"2025-05-03","rest":1},"05-04":{"holiday":true,"name":"劳动节","wage":3,"date":"2025-05-04","rest":1},"05-05":{"holiday":true,"name":"劳动节","wage":3,"date":"2025-05-05","rest":1},"05-31":{"holiday":true,"name":"端午节","wage":3,"date":"2025-05-31","rest":20},"06-01":{"holiday":true,"name":"端午节","wage":2,"date":"2025-06-01","rest":1},"06-02":{"holiday":true,"name":"端午节","wage":2,"date":"2025-06-02","rest":1},"09-28":{"holiday":false,"name":"国庆节前补班","after":false,"wage":1,"target":"国庆节","date":"2025-09-28","rest":58},"10-01":{"holiday":true,"name":"国庆节","wage":3,"date":"2025-10-01","rest":61},"10-02":{"holiday":true,"name":"国庆节","wage":3,"date":"2025-10-02","rest":1},"10-03":{"holiday":true,"name":"国庆节","wage":3,"date":"2025-10-03","rest":1},"10-04":{"holiday":true,"name":"国庆节","wage":2,"date":"2025-10-04","rest":1},"10-05":{"holiday":true,"name":"国庆节","wage":2,"date":"2025-10-05","rest":1},"10-06":{"holiday":true,"name":"中秋节","wage":2,"date":"2025-10-06","rest":1},"10-07":{"holiday":true,"name":"国庆节","wage":2,"date":"2025-10-07","rest":1},"10-08":{"holiday":true,"name":"国庆节","wage":2,"date":"2025-10-08","rest":1},
"10-11":{"holiday":false,"after":true,"wage":1,"name":"国庆节后补班","target":"国庆节","date":"2025-10-11"}
}'::json) AS h(key, value)
)
-- 解析 JSON 数据并插入到临时表中，2024年
insert into d_holiday (date,is_chinese_workday,chinese_holiday)
select
hol.date,
hol.is_chinese_holiday, -- chinese_holiday, factor_workday)(h.value->>'holiday')::BOOLEAN ,
hol.chinese_holiday -- h.value->>'name' ,
--hol.factor_workday -- (h.value->>'wage')::INTEGER
FROM
cte_Chinese_holidays hol这里的节假日信息，来自于网络。
四、基于日期、航季判断和节假日构建物化视图
至此，我们已经具备了接下来“整合表”的所有原材料：日期表、航季标签、节假日。
1、查询视图 view
基于上面航季字段（flight_season）和航季年度（flight_season_year）字段，结合节假日数据表（d_holidaty），以 d_date日期表为主表，就可以构建一个“逻辑大宽表”了。
SQL
CREATE OR REPLACE VIEW xilejun.v_flight_date as (
--CTE
with season as (
select
dd.date,
dd.day_of_week,
dd.year,
make_date(dd.year,3,31) ,
extract(isodow from make_date(dd.year,3,31) ) as isodow_0331,
make_date(dd.year,3,31) - ((extract(isodow from make_date(dd.year, 3, 31))::int+1)%7) as last_Sat_Mar,
make_date(dd.year,10,30) ,
extract(isodow from make_date(dd.year,10,30) ) as isodow_1030,
make_date(dd.year,10,30) - ((extract(isodow from make_date(dd.year, 10,30))::int+1)%7) as last_Sat_Oct
from xilejun.d_date dd
where dd.year >= 2021 and dd.year <= extract(year from current_date )-- 2021年之后，到今天年度才有 holiday 信息
)
select
season.date,
season.day_of_week,
season.last_Sat_Oct,
case when season.date >=last_Sat_Mar and season.date < last_Sat_Oct then 'Summer' else 'Winter' end flight_season,
case when season.date <last_Sat_Mar then season."year" -1 else season."year" end flight_season_year
-- case when season.date >=last_Sat_Mar and season.date < last_Sat_Oct then season."year" else extract(YEAR from season.last_Sat_Oct) end flight_season_year
,d_holiday.chinese_holiday
,d_holiday.is_chinese_holiday
from season
left join xilejun.d_holiday on season.date = d_holiday.date
)由于节假日数据只有2021年之后到今年的数据，因此上面的 View 增加了筛选器，只保留到当前年度。
这样就完成了 view 查询了，可以在明细中根据需要进一步处理，比如找到补班的工作日。
2、物化视图的必要性
在上述的处理过程中，最大的查询瓶颈是关于航季的判断。要知道，逻辑 view 语法上的查询简洁只是形式上的，而不是性能上的，每一次查询背后还是要重复计算所有日期的航季判断、航季标签、航季年度等等。
如果视图频繁被使用，这就会给数据库查询压力；我的腾讯云数据库竟然就直接关闭了我的查询。
因此，我们需要优化查询，最基本的方式就是“物化视图”（Materilized View），即把逻辑的查询结果物理地保存在数据库的特定磁盘位置，从而简化后续查询过程。
当然，凡事有利就有弊，物化视图加速了后续查询，但是“物化”过程本身需要额外调度，否则一次性物化很难跟上数据变化的需要；好在每一年的日历、航季、节假日在一年之中几乎不变，所以这里的物化视图就几乎都是好处了。
只需要把前面的 create VIEW name as 改为 create MATERIALIZED VIEW name as 即可。
SQL
-- xilejun.mv_flight_date source
CREATE MATERIALIZED VIEW xilejun.mv_flight_date
TABLESPACE pg_default
AS WITH season AS (
SELECT dd.date,
dd.day_of_week,
dd.year,
make_date(dd.year, 3, 31) AS make_date,
date_part('isodow'::text, make_date(dd.year, 3, 31)) AS isodow_0331,
make_date(dd.year, 3, 31) - (date_part('isodow'::text, make_date(dd.year, 3, 31))::integer + 1) % 7 AS last_sat_mar,
make_date(dd.year, 10, 30) AS make_date,
date_part('isodow'::text, make_date(dd.year, 10, 30)) AS isodow_1030,
make_date(dd.year, 10, 30) - (date_part('isodow'::text, make_date(dd.year, 10, 30))::integer + 1) % 7 AS last_sat_oct
FROM xilejun.d_date dd
WHERE dd.year >= 2021 AND dd.year::double precision <= date_part('year'::text, CURRENT_DATE)
)
SELECT season.date,
season.day_of_week,
season.last_sat_oct,
CASE
WHEN season.date >= season.last_sat_mar AND season.date < season.last_sat_oct THEN 'Summer'::text
ELSE 'Winter'::text
END AS flight_season,
CASE
WHEN season.date < season.last_sat_mar THEN season.year - 1
ELSE season.year
END AS flight_season_year,
d_holiday.chinese_holiday,
d_holiday.is_chinese_holiday
FROM season season(date, day_of_week, year, make_date, isodow_0331, last_sat_mar, make_date_1, isodow_1030, last_sat_oct)
LEFT JOIN xilejun.d_holiday ON season.date = d_holiday.date
WITH DATA;这样，数据表的查询效率就非常高了——物化的逻辑表虽然本质上还是逻辑表（主观生成的），但又具有了物理表的性能，可以说是“既有又要”的完全体。
总结
这样，xilejun 就结合航空日历，解释了逻辑查询、逻辑字段、逻辑表、物化逻辑表的诸多概念，分析师基于这样的完整理解，方可在分析世界游刃有余。
2025-07-05 XILEJUN
Pingback： 【民航航季日历】使用 Postgresql 和 AI 制作航空日期维度表-Tableau喜乐君-敏捷BI布道师
Pingback： 【行业分析】什么是PNR？航空订座知识详解-Tableau喜乐君-敏捷BI布道师
评论已关闭。