· 6 years ago · Sep 30, 2019, 10:24 PM
1-- Stepik Course.
2-- database sample for task 5.3
3--
4DROP TABLE IF EXISTS Flight;
5DROP TABLE IF EXISTS Planet;
6DROP TABLE IF EXISTS PoliticalSystem;
7
8
9
10-- Справочник политических строев
11CREATE TABLE politicalsystem
12(
13 id SERIAL PRIMARY KEY,
14 value TEXT UNIQUE
15);
16--
17---- Планета, её название, расстояние до Земли, политический строй
18CREATE TABLE planet
19(
20 id SERIAL PRIMARY KEY,
21 name TEXT UNIQUE,
22 distance NUMERIC(5, 2),
23 psystem_id INT REFERENCES politicalsystem
24);
25
26-- Полет на планету в означенную дату
27CREATE TABLE flight
28(
29 id INT PRIMARY KEY,
30 planet_id INT REFERENCES planet,
31 date DATE
32);
33
34
35-- Table PoliticalSystem
36INSERT INTO politicalsystem (id, value)
37VALUES (1, 'Anarchy');
38INSERT INTO politicalsystem (id, value)
39VALUES (2, 'Monarchy');
40INSERT INTO politicalsystem (id, value)
41VALUES (3, 'Parliamentary republic');
42INSERT INTO politicalsystem (id, value)
43VALUES (4, 'Democracy');
44
45-- Table Planet
46INSERT INTO planet (id, name, distance, psystem_id)
47VALUES (1, 'Bioflex', 89.08, 1);
48INSERT INTO planet (id, name, distance, psystem_id)
49VALUES (2, 'Goldenflex', 131.70, 2);
50INSERT INTO planet (id, name, distance, psystem_id)
51VALUES (3, 'Opeholding', 90.44, 2);
52INSERT INTO planet (id, name, distance, psystem_id)
53VALUES (4, 'Tanzone', 52.83, 3);
54INSERT INTO planet (id, name, distance, psystem_id)
55VALUES (5, 'Flexplus', 105.87, 3);
56INSERT INTO planet (id, name, distance, psystem_id)
57VALUES (6, 'Volholdings', 5.99, 3);
58INSERT INTO planet (id, name, distance, psystem_id)
59VALUES (7, 'Hotla', 195.26, 4);
60INSERT INTO planet (id, name, distance, psystem_id)
61VALUES (8, 'Trioline', 15.58, 4);
62INSERT INTO planet (id, name, distance, psystem_id)
63VALUES (9, 'Stimex', 164.43, 4);
64INSERT INTO planet (id, name, distance, psystem_id)
65VALUES (10, 'Hotanbam', 126.37, 3);
66INSERT INTO planet (id, name, distance, psystem_id)
67VALUES (11, 'Rancare', 57.87, 1);
68INSERT INTO planet (id, name, distance, psystem_id)
69VALUES (12, 'Doubleice', 131.20, 3);
70INSERT INTO planet (id, name, distance, psystem_id)
71VALUES (13, 'Geocom', 114.40, 4);
72INSERT INTO planet (id, name, distance, psystem_id)
73VALUES (14, 'Techiplus', 164.72, 2);
74INSERT INTO planet (id, name, distance, psystem_id)
75VALUES (15, 'Carehigh', 172.01, 2);
76INSERT INTO planet (id, name, distance, psystem_id)
77VALUES (16, 'Medredfan', 130.84, 3);
78INSERT INTO planet (id, name, distance, psystem_id)
79VALUES (17, 'Dandindox', 13.37, 4);
80INSERT INTO planet (id, name, distance, psystem_id)
81VALUES (18, 'Kanron', 3.10, 3);
82INSERT INTO planet (id, name, distance, psystem_id)
83VALUES (19, 'Tanway', 75.28, 1);
84INSERT INTO planet (id, name, distance, psystem_id)
85VALUES (20, 'Vilakix', 40.57, 4);
86
87-- Table Flight
88INSERT INTO flight (id, planet_id, date)
89VALUES (1, 2, '2047-10-28');
90INSERT INTO flight (id, planet_id, date)
91VALUES (2, 5, '2047-12-16');
92INSERT INTO flight (id, planet_id, date)
93VALUES (3, 13, '2048-02-20');
94INSERT INTO flight (id, planet_id, date)
95VALUES (4, 6, '2048-03-02');
96INSERT INTO flight (id, planet_id, date)
97VALUES (5, 12, '2048-03-20');
98INSERT INTO flight (id, planet_id, date)
99VALUES (6, 9, '2048-03-26');
100INSERT INTO flight (id, planet_id, date)
101VALUES (7, 14, '2048-05-01');
102INSERT INTO flight (id, planet_id, date)
103VALUES (8, 3, '2048-05-22');
104INSERT INTO flight (id, planet_id, date)
105VALUES (9, 6, '2048-05-28');
106INSERT INTO flight (id, planet_id, date)
107VALUES (10, 6, '2048-06-04');
108INSERT INTO flight (id, planet_id, date)
109VALUES (11, 4, '2048-06-17');
110INSERT INTO flight (id, planet_id, date)
111VALUES (12, 2, '2048-08-10');
112INSERT INTO flight (id, planet_id, date)
113VALUES (13, 4, '2048-08-18');
114INSERT INTO flight (id, planet_id, date)
115VALUES (14, 6, '2048-09-03');
116INSERT INTO flight (id, planet_id, date)
117VALUES (15, 5, '2048-10-08');
118INSERT INTO flight (id, planet_id, date)
119VALUES (16, 8, '2048-10-20');
120INSERT INTO flight (id, planet_id, date)
121VALUES (17, 9, '2048-11-23');
122INSERT INTO flight (id, planet_id, date)
123VALUES (18, 7, '2048-12-17');
124INSERT INTO flight (id, planet_id, date)
125VALUES (19, 13, '2048-12-23');
126INSERT INTO flight (id, planet_id, date)
127VALUES (20, 19, '2048-12-28');
128INSERT INTO flight (id, planet_id, date)
129VALUES (21, 3, '2049-02-28');
130INSERT INTO flight (id, planet_id, date)
131VALUES (22, 11, '2049-03-28');
132INSERT INTO flight (id, planet_id, date)
133VALUES (23, 5, '2049-03-29');
134INSERT INTO flight (id, planet_id, date)
135VALUES (24, 1, '2049-04-15');
136INSERT INTO flight (id, planet_id, date)
137VALUES (25, 10, '2049-04-16');
138INSERT INTO flight (id, planet_id, date)
139VALUES (26, 10, '2049-04-16');
140INSERT INTO flight (id, planet_id, date)
141VALUES (27, 15, '2049-04-17');
142INSERT INTO flight (id, planet_id, date)
143VALUES (28, 2, '2049-04-17');
144INSERT INTO flight (id, planet_id, date)
145VALUES (29, 5, '2049-04-18');
146INSERT INTO flight (id, planet_id, date)
147VALUES (30, 10, '2049-04-19');
148WITH pf AS (
149 SELECT planet.id,
150 MIN(psystem_id) AS ps_id,
151 (CASE
152 WHEN flight.planet_id IS NOT NULL THEN COUNT(*)
153 ELSE 0
154 END) AS flight_count
155 FROM planet
156 LEFT JOIN flight ON planet.id = flight.planet_id
157 GROUP BY planet.id
158),
159 pg AS (
160 SELECT id,
161 ps_id,
162 flight_count,
163 rank() OVER (
164 ORDER BY
165 flight_count DESC
166 ) AS global
167 FROM pf
168 ),
169 ans AS (
170 SELECT id,
171 ps_id,
172 global,
173 flight_count,
174 rank() OVER (
175 PARTITION BY ps_id
176 ORDER BY flight_count DESC
177 ) AS local
178 FROM pg
179 )
180SELECT planet.name, politicalsystem.value, local, global
181FROM ans
182 JOIN planet ON ans.id = planet.id
183 JOIN politicalsystem ON ans.ps_id = politicalsystem.id;