Как установить Postgresql на Linux и Windows с. 16

• Название: Как установить Postgresql на Linux и Windows с. 16
• Дата: 10.07.2022
• Размер: 1.38 Mb.
• Имя файла: potgresql.pdf
• Тип: Документы; #628132
• страница: 5 из 8

Решение.
В запросе надо учитывать только рейсы, кото- рые уже вылетели:
SELECT
f.flight_no,
f.scheduled_departure,
f.actual_departure,
f.actual_departure - f.scheduled_departure
AS delay
FROM
flights f
WHERE
f.actual_departure IS NOT NULL
ORDER BY f.actual_departure - f.scheduled_departure
DESC
LIMIT 10;
То же самое условие можно записать и на основе столб- ца status.
Агрегатные функции
Задача.
Какова минимальная и максимальная продол- жительность полета для каждого из возможных рейсов из Москвы в Санкт-Петербург, и сколько раз вылет рейса был задержан больше, чем на час?
Решение.
Здесь удобно воспользоваться готовым пред- ставлением flights_v, чтобы не выписывать соедине- ния таблиц. В запросе учитываем только уже выполнен- ные рейсы.
76

SELECT
f.flight_no,
f.scheduled_duration,
min(f.actual_duration),
max(f.actual_duration),
sum(CASE
WHEN f.actual_departure >
f.scheduled_departure +
INTERVAL '1 hour'
THEN 1 ELSE 0
END) delays
FROM
flights_v f
WHERE
f.departure_city = 'Москва'
AND
f.arrival_city = 'Санкт-Петербург'
AND
f.status = 'Arrived'
GROUP BY f.flight_no,
f.scheduled_duration;
Задача._Найдите_самых_дисциплинированных_пассажи-_ров,_которые_зарегистрировались_на_все_рейсы_первы-_ми._Учтите_только_тех_пассажиров,_которые_совершали_минимум_два_рейса.Решение.'>Задача.
Найдите самых дисциплинированных пассажи- ров, которые зарегистрировались на все рейсы первы- ми. Учтите только тех пассажиров, которые совершали минимум два рейса.
Решение.
Используем тот факт, что номера посадочных талонов выдаются в порядке регистрации.
SELECT
t.passenger_name,
t.ticket_no
FROM
tickets t
JOIN boarding_passes bp
ON bp.ticket_no = t.ticket_no
GROUP BY t.passenger_name,
t.ticket_no
HAVING
max(bp.boarding_no) = 1
AND
count(*) > 1;
77

Задача.
Сколько человек бывает включено в одно бро- нирование?
Решение.
Сначала посчитаем количество человек в каж- дом бронировании, а затем число бронирований для каждого количества человек.
SELECT
tt.cnt,
count(*)
FROM
(
SELECT
t.book_ref,
count(*) cnt
FROM
tickets t
GROUP BY t.book_ref
) tt
GROUP BY tt.cnt
ORDER BY tt.cnt;
Задача.'>Оконные функции
Задача.
Для каждого билета выведите входящие в него перелеты вместе с запасом времени на пересадку на следующий рейс. Ограничьте выборку теми билетами,
которые были забронированы неделю назад.
Решение.
Используем оконные функции, чтобы не обра- щаться к одним и тем же данным два раза.
Глядя в результаты приведенного ниже запроса, мож- но обратить внимание, что запас времени в некоторых случаях составляет несколько дней. Как правило, это билеты, взятые туда и обратно, то есть мы видим уже не время пересадки, а время нахождения в пункте на- значения. Используя решение одной из задач в разделе
«Массивы», можно учесть этот факт в запросе.
78

SELECT tf.ticket_no,
f.departure_airport,
f.arrival_airport,
f.scheduled_arrival,
lead(f.scheduled_departure) OVER w
AS next_departure,
lead(f.scheduled_departure) OVER w - f.scheduled_arrival AS gap
FROM
bookings b
JOIN tickets t
ON t.book_ref = b.book_ref
JOIN ticket_flights tf
ON tf.ticket_no = t.ticket_no
JOIN flights f
ON tf.flight_id = f.flight_id
WHERE b.book_date =
bookings.now()::date - INTERVAL '7 day'
WINDOW w AS (PARTITION BY tf.ticket_no
ORDER BY f.scheduled_departure);
Задача.
Какие сочетания имени и фамилии встречаются чаще всего и какую долю от числа всех пассажиров они составляют?
Решение.
Оконная функция используется для подсчета общего числа пассажиров.
SELECT
passenger_name,
round( 100.0 * cnt / sum(cnt) OVER (), 2)
AS percent
FROM
(
SELECT
passenger_name,
count(*) cnt
FROM
tickets
GROUP BY passenger_name
) t
ORDER BY percent DESC;
79

Задача.
Решите предыдущую задачу отдельно для имен и отдельно для фамилий.
Решение.
Приведем вариант для имен.
WITH p AS (
SELECT left(passenger_name,
position(' ' IN passenger_name))
AS passenger_name
FROM
tickets
)
SELECT
passenger_name,
round( 100.0 * cnt / sum(cnt) OVER (), 2)
AS percent
FROM
(
SELECT
passenger_name,
count(*) cnt
FROM
p
GROUP BY passenger_name
) t
ORDER BY percent DESC;
Вывод: не стоит объединять в одном текстовом поле несколько значений, если вы собираетесь работать с ни- ми по отдельности; по-научному это называется «первой нормальной формой».
Массивы
Задача.
В билете нет указания, в один ли он конец, или туда и обратно. Однако это можно вычислить, сравнив первый пункт отправления с последним пунктом назна- чения. Выведите для каждого билета аэропорты отправ- ления и назначения без учета пересадок, и признак,
взят ли билет туда и обратно.
80

Решение.
Пожалуй, проще всего свернуть список аэро- портов на пути следования в массив с помощью агре- гатной функции array_agg и работать с ним. В качестве аэропорта назначения для билетов «туда-обратно» вы- бираем средний элемент массива, предполагая, что пути
«туда» и «обратно» имеют одинаковое число пересадок.
WITH t AS (
SELECT ticket_no,
a,
a[1]
departure,
a[cardinality(a)]
last_arrival,
a[cardinality(a)/2+1] middle
FROM (
SELECT
t.ticket_no,
array_agg( f.departure_airport
ORDER BY f.scheduled_departure) ||
(array_agg( f.arrival_airport
ORDER BY f.scheduled_departure DESC)
)[1] AS a
FROM
tickets t
JOIN ticket_flights tf
ON tf.ticket_no = t.ticket_no
JOIN flights f
ON f.flight_id = tf.flight_id
GROUP BY t.ticket_no
) t
)
SELECT t.ticket_no,
t.a,
t.departure,
CASE
WHEN t.departure = t.last_arrival
THEN t.middle
ELSE t.last_arrival
END arrival,
(t.departure = t.last_arrival) return_ticket
FROM
t;
81

В таком варианте таблица билетов просматривается только один раз. Массив аэропортов выводится исклю- чительно для наглядности; на большом объеме данных имеет смысл убрать его из запроса.
Задача.
Найдите билеты, взятые туда и обратно, в кото- рых путь «туда» не совпадает с путем «обратно».
Задача.
Найдите такие пары аэропортов, рейсы между которыми в одну и в другую стороны отправляются по разным дням недели.
Решение.
Часть задачи по построению массива дней недели уже фактически решена в представлении routes.
Остается только найти пересечение с помощью опера- ции &&:
SELECT r1.departure_airport,
r1.arrival_airport,
r1.days_of_week dow,
r2.days_of_week dow_back
FROM
routes r1
JOIN routes r2
ON r1.arrival_airport = r2.departure_airport
AND r1.departure_airport = r2.arrival_airport
WHERE NOT (r1.days_of_week && r2.days_of_week);
Рекурсивные запросы
Задача.
Как с помощью минимального числа пересадок можно долететь из Усть-Кута (UKX) в Нерюнгри (CNN),
и какое время придется провести в воздухе?
82

Решение.
Здесь фактически нужно найти кратчайший путь в графе, что делается рекурсивным запросом.
WITH RECURSIVE p(
last_arrival,
destination,
hops,
flights,
flight_time,
found
) AS (
SELECT a_from.airport_code,
a_to.airport_code,
array[a_from.airport_code],
array[]::char(6)[],
interval '0',
a_from.airport_code = a_to.airport_code
FROM
airports a_from,
airports a_to
WHERE a_from.airport_code = 'UKX'
AND
a_to.airport_code = 'CNN'
UNION ALL
SELECT r.arrival_airport,
p.destination,
(p.hops || r.arrival_airport)::char(3)[],
(p.flights || r.flight_no)::char(6)[],
p.flight_time + r.duration,
bool_or(r.arrival_airport = p.destination)
OVER ()
FROM
p
JOIN routes r
ON r.departure_airport = p.last_arrival
WHERE NOT r.arrival_airport = ANY(p.hops)
AND
NOT p.found
)
SELECT hops,
flights,
flight_time
FROM
p
WHERE p.last_arrival = p.destination;
83

Зацикливание предотвращается проверкой по массиву пересадок hops.
Обратите внимание, что поиск происходит «в ширину»,
то есть первый же путь, который будет найден, будет кратчайшим по числу пересадок. Чтобы не перебирать остальные пути (которых может быть очень много), ис- пользуется признак «маршрут найден» (found), который рассчитывается с помощью оконной функции bool_or.
Поучительно сравнить скорость выполнения этого за- проса с более простым вариантом без флага.
Подробно про рекурсивные запросы вы можете посмот- реть в документации: postgrespro.ru/doc/queries- with.html
Задача.
Какое максимальное число пересадок может потребоваться, чтобы добраться из одного любого аэро- порта в любой другой?
Решение.
В качестве основы решения можно взять преды- дущий запрос. Но теперь начальная итерация должна содержать не одну пару аэропортов, а все возможные пары: каждый аэропорт соединяем с каждым. Для всех таких пар мы ищем кратчайший путь, а затем выбираем максимальный из них.
Конечно, так можно поступить, только если граф марш- рутов является связным.
В этом запросе также используется признак «маршрут найден», но здесь его необходимо рассчитывать отдель- но для каждой пары аэропортов.
84

WITH RECURSIVE p(
departure,
last_arrival,
destination,
hops,
found
) AS (
SELECT a_from.airport_code,
a_from.airport_code,
a_to.airport_code,
array[a_from.airport_code],
a_from.airport_code = a_to.airport_code
FROM
airports a_from,
airports a_to
UNION ALL
SELECT p.departure,
r.arrival_airport,
p.destination,
(p.hops || r.arrival_airport)::char(3)[],
bool_or(r.arrival_airport = p.destination)
OVER (PARTITION BY p.departure,
p.destination)
FROM
p
JOIN routes r
ON r.departure_airport = p.last_arrival
WHERE NOT r.arrival_airport = ANY(p.hops)
AND
NOT p.found
)
SELECT max(cardinality(hops)-1)
FROM
p
WHERE p.last_arrival = p.destination;
Задача.
Найдите кратчайший путь из Усть-Кута (UKX)
в Нерюнгри (CNN) с точки зрения чистого времени пе- релетов (игнорируя время пересадок).
Подсказка: этот путь может оказаться не оптимальным по числу пересадок.
85

Решение.
WITH RECURSIVE p(
last_arrival,
destination,
hops,
flights,
flight_time,
min_time
) AS (
SELECT a_from.airport_code,
a_to.airport_code,
array[a_from.airport_code],
array[]::char(6)[],
interval '0',
NULL::interval
FROM
airports a_from,
airports a_to
WHERE a_from.airport_code = 'UKX'
AND
a_to.airport_code = 'CNN'
UNION ALL
SELECT r.arrival_airport,
p.destination,
(p.hops || r.arrival_airport)::char(3)[],
(p.flights || r.flight_no)::char(6)[],
p.flight_time + r.duration,
least(
p.min_time, min(p.flight_time+r.duration)
FILTER (
WHERE r.arrival_airport = p.destination
) OVER ()
)
FROM
p
JOIN routes r
ON r.departure_airport = p.last_arrival
WHERE NOT r.arrival_airport = ANY(p.hops)
AND
p.flight_time + r.duration <
coalesce(p.min_time, INTERVAL '1 year')
)
86

SELECT hops,
flights,
flight_time
FROM
(
SELECT hops,
flights,
flight_time,
min(min_time) OVER () min_time
FROM
p
WHERE p.last_arrival = p.destination
) t
WHERE flight_time = min_time;
Функции и расширения
Задача.
Найдите расстояние между Калининградом (KGD)
и Петропавловском-Камчатским (PKC).
Решение.
У нас имеются координаты аэропортов. Чтобы рассчитать расстояние, можно воспользоваться расши- рением earthdistance (и перевести результат из милей в километры).
CREATE EXTENSION IF NOT EXISTS cube;
CREATE EXTENSION IF NOT EXISTS earthdistance;
SELECT round(
(a_from.coordinates <@> a_to.coordinates) *
1.609344
)
FROM
airports a_from,
airports a_to
WHERE a_from.airport_code = 'KGD'
AND
a_to.airport_code = 'PKC';
Задача.
Нарисуйте граф рейсов между аэропортами.
87

Дополнительные
возможности
Полнотекстовый поиск
Несмотря на мощь языка запросов SQL, его возмож- ностей не всегда достаточно для эффективной работы с данными. Особенно это стало заметно в последнее время, когда лавины данных, обычно плохо структури- рованных, заполнили хранилища информации. Изряд- ная доля Больших Данных приходится на тексты, плохо поддающиеся разбиению на поля баз данных. Поиск документов на естественных языках, обычно с сорти- ровкой результатов по релевантности поисковому за- просу, называют полнотекстовым поиском. В самом про- стом и типичном случае запросом считается набор слов,
а соответствие определяется частотой слов в докумен- те. Примерно таким поиском мы занимаемся, набирая фразу в поисковике Google или Яндекс.
Существует большое количество поисковиков, платных и бесплатных, которые позволяют индексировать всю вашу коллекцию документов и организовать вполне ка- чественный поиск. В этих случаях индекс — важнейший
88

инструмент и ускоритель поиска — не является частью базы данных. А это значит, что такие ценимые пользо- вателями СУБД особенности, как синхронизация содер- жимого БД, транзакционность, доступ к метаданным и использование их для ограничения области поиска, ор- ганизация безопасной политики доступа к документам и многое другое, оказываются недоступны.
Недостатки у все более популярных документо-ориен- тированных СУБД обычно в той же области: у них есть развитые средства полнотекстового поиска, но безопас- ность и заботы о синхронизации для них не приоритет- ны. К тому же обычно они (MongoDB, например) при- надлежат классу NoSQL СУБД, а значит по определению лишены всей десятилетиями накопленной мощи SQL.
С другой стороны традиционные SQL-СУБД имеют встро- енные средства текстового поиска. Оператор LIKE вхо- дит в стандартный синтаксис SQL, но гибкость его явно недостаточна. В результате производителям СУБД прихо- дилось добавлять собственные расширения к стандарту
SQL. У PostgreSQL это операторы сравнения ILIKE,

,
*, но и они не решают всех проблем, так как не уме- ют учитывать грамматические вариации слов, не при- способлены для ранжирования и не слишком быстро работают.
Если говорить об инструментах собственно полнотек- стового поиска, то важно понимать, что до их стандар- тизации пока далеко, в каждой реализации СУБД свой синтаксис и свои подходы. В этом контексте российский пользователь PostgreSQL получает немалые преиму- щества: расширения полнотекстового поиска для этой
89

СУБД созданы российскими разработчиками, поэтому возможность прямого контакта со специалистами или даже посещение их лекций поможет углубиться в техно- логические детали, если в этом возникнет потребность.
Здесь же мы ограничимся простыми примерами.
Для изучения возможностей полнотекстового поиска создадим еще одну таблицу в демонстрационной ба- зе данных. Пусть это будут наброски конспекта лекций преподавателя курсов, разбитые на главы-лекции:
test=# CREATE TABLE course_chapters(
c_no text REFERENCES courses(c_no),
ch_no text,
ch_title text,
txt text,
CONSTRAINT pkt_ch PRIMARY KEY(ch_no, c_no)
);
CREATE TABLE
Введем в таблицу тексты первых лекций по знакомым нам специальностям CS301 и CS305:
test=# INSERT INTO course_chapters(
c_no, ch_no,ch_title, txt)
VALUES
('CS301', 'I', 'Базы данных',
'С этой главы начинается наше знакомство ' ||
'с увлекательным миром баз данных'),
('CS301', 'II', 'Первые шаги',
'Продолжаем знакомство с миром баз данных. ' ||
'Создадим нашу первую текстовую базу данных'),
('CS305', 'I', 'Локальные сети',
'Здесь начнется наше полное приключений ' ||
'путешествие в интригующий мир сетей');
INSERT 0 3 90

Проверим результат:
test=# SELECT ch_no AS no, ch_title, txt
FROM course_chapters \gx
-[ RECORD 1 ]----------------------------------------- no
| I
ch_title | Базы данных txt
| С этой главы начинается наше знакомство с увлекательным миром баз данных
-[ RECORD 2 ]----------------------------------------- no
| II
ch_title | Первые шаги txt
| Продолжаем знакомство с миром баз данных.
Создадим нашу первую текстовую базу данных
-[ RECORD 3 ]----------------------------------------- no
| I
ch_title | Локальные сети txt
| Здесь начнется наше полное приключений путешествие в интригующий мир сетей
Найдем в таблице информацию по базам данных тради- ционными средствами SQL, то есть используя оператор
LIKE:
test=# SELECT txt
FROM course_chapters
WHERE txt LIKE '%базы данных%' \gx
Мы получим предсказуемый ответ: 0 строк. Ведь LIKE
не знает, что в родительном падеже следует искать «баз данных» или «базу данных» в творительном. Запрос test=# SELECT txt
FROM course_chapters
WHERE txt LIKE '%базу данных%' \gx
91

выдаст строку из главы II (но не из главы I, где база в другом падеже):
-[ RECORD 1 ]----------------------------------------- txt | Продолжаем знакомство с миром баз данных.
Создадим нашу первую текстовую базу данных
В PostgreSQL есть оператор ILIKE, который позволяет не заботиться о регистрах, а то бы пришлось еще ду- мать и о прописных и строчных буквах. Конечно, в рас- поряжении знатока SQL есть и регулярные выражения
(шаблоны поиска), составление которых занятие увле- кательное, сродни искусству. Но когда не до искусства,
хочется иметь инструмент, который думал бы за тебя.
Поэтому мы добавим к таблице глав еще один столбец со специальным типом данных — tsvector:
test=# ALTER TABLE course_chapters
ADD txtvector tsvector;
test=# UPDATE course_chapters
SET txtvector = to_tsvector('russian',txt);
test=# SELECT txtvector FROM course_chapters \gx
-[ RECORD 1 ]----------------------------------------- txtvector | 'баз':10 'глав':3 'дан':11 'знакомств':6
'мир':9 'начина':4 'наш':5 'увлекательн':8
-[ RECORD 2 ]----------------------------------------- txtvector | 'баз':5,11 'дан':6,12 'знакомств':2
'мир':4 'наш':8 'перв':9 'продолжа':1
'создад':7 'текстов':10
-[ RECORD 3 ]----------------------------------------- txtvector | 'интриг':8 'мир':9 'начнет':2 'наш':3
'полн':4 'приключен':5 'путешеств':6
'сет':10 92

Мы видим, что в строках:
1. слова сократились до их неизменяемых частей
(лексем),
2. появились цифры, означающие позицию вхожде- ния слова в текст (видно, что некоторые слова во- шли 2 раза),
3. в строку не вошли предлоги (а также не вошли бы союзы и прочие не значимые для поиска единицы предложения — так называемые стоп-слова).
Для более продвинутого поиска нам хотелось бы вклю- чить в поисковую область и названия глав. Причем,
дабы подчеркнуть их важность, мы наделим их весом при помощи функции setweight. Поправим таблицу:
test=#