PG索引系列文章中的Demo数据库
在Egor Rogov介绍索引的系列文章中，Egor Rogov使用了一个demo数据库。它源自Postgres Pro，主要供数据库的使用者学习使用，就像Oracle中自带的demo数据库一样。为了使读者更好的理解本系列的后续文章，译者在本文中对这个demo数据库做一些简要的介绍。读者可以自行查阅Postres Pro的文档，了解更详细信息。
这个demo数据库模拟了一个俄罗斯航空领域的系统。随着Postgres Pro版本的更新，这个demo数据库也会更新（新旧版本大同小异），本系列使用的版本中，包括8张表，和一些视图。读者可以从这里下载这个demo数据库中最新版（译者没有在官网找到老版本的数据）的数据（包括建表语句）。
Schema
这个系统的schema如下所示：
每张表的详细定义可以在这里查到:https://postgrespro.com/docs/postgrespro/9.6/apjs04
下面是译者导入新版本数据后，得到的schema。
demo=# \d
List of relations
Schema | Name | Type | Owner
----------+-----------------------+----------+-------
bookings | aircrafts | view | meng
bookings | aircrafts_data | table | meng
bookings | airports | view | meng
bookings | airports_data | table | meng
bookings | boarding_passes | table | meng
bookings | bookings | table | meng
bookings | flights | table | meng
bookings | flights_flight_id_seq | sequence | meng
bookings | flights_v | view | meng
bookings | routes | view | meng
bookings | seats | table | meng
bookings | ticket_flights | table | meng
bookings | tickets | table | meng
(13 rows)
晦涩的字段
表中大部分字段都比较容易理解，下面介绍几个晦涩的字段。
fare_conditions
fare_conditions表示舱位，共有Comfort、Business、Economy三种类型
demo=# select fare_conditions,count(*) from seats group by fare_conditions;
fare_conditions | count
-----------------+-------
Business | 152
Comfort | 48
Economy | 1139
(3 rows)
aircraft_code、model和range
aircrafts表中包括所有的机型。aircraft_code是IATA为这个机型分配的代码，可以从相关网站查询到。model表示机型。range表示续航里程。
demo=# select * from aircrafts limit 10;
aircraft_code | model | range
---------------+---------------------+-------
773 | Boeing 777-300 | 11100
763 | Boeing 767-300 | 7900
SU9 | Sukhoi Superjet-100 | 3000
320 | Airbus A320-200 | 5700
321 | Airbus A321-200 | 5600
319 | Airbus A319-100 | 6700
733 | Boeing 737-300 | 4200
CN1 | Cessna 208 Caravan | 1200
CR2 | Bombardier CRJ-200 | 2700
(9 rows)
示例数据
下面列出其它每张表中的一些数据。
demo=# select * from airports limit 10;
airport_code | airport_name | city | coordinates | timezone
--------------+-----------------------------------+-------------------+-----------------------------------------+--------------------
YKS | Yakutsk Airport | Yakutsk | (129.77099609375,62.093299865722656) | Asia/Yakutsk
MJZ | Mirny Airport | Mirnyj | (114.03900146484375,62.534698486328125) | Asia/Yakutsk
KHV | Khabarovsk-Novy Airport | Khabarovsk | (135.18800354004,48.52799987793) | Asia/Vladivostok
PKC | Yelizovo Airport | Petropavlovsk | (158.45399475097656,53.16790008544922) | Asia/Kamchatka
UUS | Yuzhno-Sakhalinsk Airport | Yuzhno-Sakhalinsk | (142.71800231933594,46.88869857788086) | Asia/Sakhalin
VVO | Vladivostok International Airport | Vladivostok | (132.1479949951172,43.39899826049805) | Asia/Vladivostok
LED | Pulkovo Airport | St. Petersburg | (30.262500762939453,59.80030059814453) | Europe/Moscow
KGD | Khrabrovo Airport | Kaliningrad | (20.592599868774414,54.88999938964844) | Europe/Kaliningrad
KEJ | Kemerovo Airport | Kemorovo | (86.1072006225586,55.27009963989258) | Asia/Novokuznetsk
CEK | Chelyabinsk Balandino Airport | Chelyabinsk | (61.5033,55.305801) | Asia/Yekaterinburg
(10 rows)
demo=# select * from boarding_passes limit 10;
ticket_no | flight_id | boarding_no | seat_no
---------------+-----------+-------------+---------
0005435212351 | 30625 | 1 | 2D
0005435212386 | 30625 | 2 | 3G
0005435212381 | 30625 | 3 | 4H
0005432211370 | 30625 | 4 | 5D
0005435212357 | 30625 | 5 | 11A
0005435212360 | 30625 | 6 | 11E
0005435212393 | 30625 | 7 | 11H
0005435212374 | 30625 | 8 | 12E
0005435212365 | 30625 | 9 | 13D
0005435212378 | 30625 | 10 | 14H
(10 rows)
demo=# select * from bookings limit 10;
book_ref | book_date | total_amount
----------+------------------------+--------------
00000F | 2017-07-05 08:12:00+08 | 265700.00
000012 | 2017-07-14 14:02:00+08 | 37900.00
000068 | 2017-08-15 19:27:00+08 | 18100.00
000181 | 2017-08-10 18:28:00+08 | 131800.00
0002D8 | 2017-08-08 02:40:00+08 | 23600.00
0002DB | 2017-07-29 11:30:00+08 | 101500.00
0002E0 | 2017-07-11 21:09:00+08 | 89600.00
0002F3 | 2017-07-10 10:31:00+08 | 69600.00
00034E | 2017-08-04 21:52:00+08 | 73300.00
000352 | 2017-07-06 07:02:00+08 | 109500.00
(10 rows)
-- flight_no表示航班号，(flight_no,scheduled_departure)是flights表的自然主键，为了其它表引用方便，使用flight_id作为主键。
demo=# select * from flights limit 10;
flight_id | flight_no | scheduled_departure | scheduled_arrival | departure_airport | arrival_airport | status | aircraft_code | actual_departure | actual_arrival
-----------+-----------+------------------------+------------------------+-------------------+-----------------+-----------+---------------+------------------+----------------
1185 | PG0134 | 2017-09-10 14:50:00+08 | 2017-09-10 19:55:00+08 | DME | BTK | Scheduled | 319 | |
3979 | PG0052 | 2017-08-25 19:50:00+08 | 2017-08-25 22:35:00+08 | VKO | HMA | Scheduled | CR2 | |
4739 | PG0561 | 2017-09-05 17:30:00+08 | 2017-09-05 19:15:00+08 | VKO | AER | Scheduled | 763 | |
5502 | PG0529 | 2017-09-12 14:50:00+08 | 2017-09-12 16:20:00+08 | SVO | UFA | Scheduled | 763 | |
6938 | PG0461 | 2017-09-04 17:25:00+08 | 2017-09-04 18:20:00+08 | SVO | ULV | Scheduled | SU9 | |
7784 | PG0667 | 2017-09-10 20:00:00+08 | 2017-09-10 22:30:00+08 | SVO | KRO | Scheduled | CR2 | |
9478 | PG0360 | 2017-08-28 14:00:00+08 | 2017-08-28 16:35:00+08 | LED | REN | Scheduled | CR2 | |
11085 | PG0569 | 2017-08-24 20:05:00+08 | 2017-08-24 21:10:00+08 | SVX | SCW | Scheduled | 733 | |
11847 | PG0498 | 2017-09-12 15:15:00+08 | 2017-09-12 19:55:00+08 | KZN | IKT | Scheduled | 319 | |
12012 | PG0621 | 2017-08-26 21:05:00+08 | 2017-08-26 22:00:00+08 | KZN | MQF | Scheduled | CR2 | |
(10 rows)
demo=# select * from seats limit 10;
aircraft_code | seat_no | fare_conditions
---------------+---------+-----------------
319 | 2A | Business
319 | 2C | Business
319 | 2D | Business
319 | 2F | Business
319 | 3A | Business
319 | 3C | Business
319 | 3D | Business
319 | 3F | Business
319 | 4A | Business
319 | 4C | Business
(10 rows)
-- 一张机票可能对应多个班次，可能因为转机，也可能因为是往返机票。
demo=# select * from ticket_flights limit 10;
ticket_no | flight_id | fare_conditions | amount
---------------+-----------+-----------------+----------
0005432159776 | 30625 | Business | 42100.00
0005435212351 | 30625 | Business | 42100.00
0005435212386 | 30625 | Business | 42100.00
0005435212381 | 30625 | Business | 42100.00
0005432211370 | 30625 | Business | 42100.00
0005435212357 | 30625 | Comfort | 23900.00
0005435212360 | 30625 | Comfort | 23900.00
0005435212393 | 30625 | Comfort | 23900.00
0005435212374 | 30625 | Comfort | 23900.00
0005435212365 | 30625 | Comfort | 23900.00
(10 rows)
demo=# select * from tickets limit 10;
ticket_no | book_ref | passenger_id | passenger_name | contact_data
---------------+----------+--------------+---------------------+----------------------------------------------------------------------------------
0005432000987 | 06B046 | 8149 604011 | VALERIY TIKHONOV | {"phone": "+70127117011"}
0005432000988 | 06B046 | 8499 420203 | EVGENIYA ALEKSEEVA | {"phone": "+70378089255"}
0005432000989 | E170C3 | 1011 752484 | ARTUR GERASIMOV | {"phone": "+70760429203"}
0005432000990 | E170C3 | 4849 400049 | ALINA VOLKOVA | {"email": "volkova.alina_03101973@postgrespro.ru", "phone": "+70582584031"}
0005432000991 | F313DD | 6615 976589 | MAKSIM ZHUKOV | {"email": "m-zhukov061972@postgrespro.ru", "phone": "+70149562185"}
0005432000992 | F313DD | 2021 652719 | NIKOLAY EGOROV | {"phone": "+70791452932"}
0005432000993 | F313DD | 0817 363231 | TATYANA KUZNECOVA | {"email": "kuznecova-t-011961@postgrespro.ru", "phone": "+70400736223"}
0005432000994 | CCC5CB | 2883 989356 | IRINA ANTONOVA | {"email": "antonova.irina04121972@postgrespro.ru", "phone": "+70844502960"}
0005432000995 | CCC5CB | 3097 995546 | VALENTINA KUZNECOVA | {"email": "kuznecova.valentina10101976@postgrespro.ru", "phone": "+70268080457"}
0005432000996 | 1FB1E4 | 6866 920231 | POLINA ZHURAVLEVA | {"phone": "+70639918455"}
(10 rows)
demo=#