Запросы в Postgresql
LIKE
В следующем примере LIKE используется для подстановки шаблона к атрибуту model
demo=# select *from aircrafts where model like 'Аэробус%' ;
aircraft_code | model | range
---------------+------------------+-------
320 | Аэробус A320-200 | 5700
321 | Аэробус A321-200 | 5600
319 | Аэробус A319-100 | 6700
(3 rows)
Знак % это любой символ в любом кол-ве, даже 0 символов
Так же есть NOT LIKE - все что кроме шаблона
demo=# select * from aircrafts where model not like 'Бомбар%' and model not like 'Сессна%' ;
Для шаблона LIKE в качестве одного символа может использоваться символ подчеркивания '_'
demo=# select * from airports where airport_name like '___' ;
Кроме LIKE можно использовать регулярные выражения POSIX ~'выражение'
demo=# select * from airports where airport_name ~ 'Уф.*';
Можно задать диапазон значений
demo=# select * from aircrafts where range between 3000 and 6000 ;
Или задать диапазон времени:
# select * from table_name where start_date between '2012-11-19' and '2022-01-14' ;
В запросе select можно использовать выражение, например для перевода км в метры
demo=# select model, range * 1000 AS metrs from aircrafts ;
model | metrs
---------------------+----------
Боинг 777-300 | 11100000
Боинг 767-300 | 7900000
Сухой Суперджет-100 | 3000000
Аэробус A320-200 | 5700000
Аэробус A321-200 | 5600000
Аэробус A319-100 | 6700000
Боинг 737-300 | 4200000
Сессна 208 Караван | 1200000
Бомбардье CRJ-200 | 2700000
(9 rows)
При создании запросов можно использовать функцию округления до заданного кол-ва чисел после точки
SELECT model, range, round( range / 1.609, 2 ) AS miles FROM aircrafts;
Вывод значений в порядке убывания:
SELECT * FROM aircrafts ORDER BY range DESC;
*****************************************
JOIN
Важно различать способ соединения таблиц JOIN как общее понятие языка SQL и соединение наборов строк как механизм соединения строк, существует три способа соединения на основе строк:
Вложенный цикл ( nested loop ) - перебираются строки из внешнего набора со строками из внутреннего набора, метод эффективен для небольших выборок
Хеширование ( hash join ) - строки из одного набора помещаются в хеш-таблицу, строки из воторого набора перебираются и для каждой из этих строк проверяется наличие строк в хеш таблице. Ключем в хеш таблице является столбец по которому выполняется соединение. Метод буде эффективен для больших выборок.
Слияние ( merge join ) - оба набора строк должны быть отсортированы по тем столбцам, по которым производится соединение, после параллельно читаются строки из обоих наборов и сравниваются значения столбцов, по которым производится соединение, при совпадении значений формируется результатирующая строка. Данный метод также используется длябольших наборов строк.
demo=# select a.aircraft_code, a.model, s.seat_no, s.fare_conditions
from seats AS s
join aircrafts AS a
on s.aircraft_code = a.aircraft_code
where a.model ~ '^Ceссна'
order by s.seat_no ;
Смысл этой JOIN в том, чтобы по общий атрибутам, вывести атрибуты из другой таблицы, которых нет в текущей таблице
Необязательно выводить значения второй таблицы:
demo=# select s.seat_no, s.fare_conditions
from seats s
join aircrafts a
on s.aircraft_code = a.aircraft_code
where a.model ~ '^Сессна'
order by s.seat_no ;
Можно использовать аналогичный запрос без оператора JOIN:
demo=# select a.aircraft_code, a.model, s.seat_no, s.fare_conditions
from seats AS s, aircrafts AS a
where a.model ~ '^Сессна' and s.aircraft_code = a.aircraft_code
order by s.seat_no ;
Результатом реляционных операций над отношениями (таблицы, представления ) так же являются отношениями
Сводить вместе можно не только разные таблицы, но и одну таблицу с самой собой:
demo=# CREATE OR REPLACE VIEW flights_v AS
SELECT f.flight_id,
f.flight_no,
f.scheduled_departure,
timezone( dep.timezone, f.scheduled_departure )
AS scheduled_departure_local,
f.scheduled_arrival,
timezone( arr.timezone, f.scheduled_arrival )
AS scheduled_arrival_local,
f.scheduled_arrival - f.scheduled_departure
AS scheduled_duration,
f.departure_airport,
dep.airport_name AS departure_airport_name,
dep.city AS departure_city,
f.arrival_airport,
arr.airport_name AS arrival_airport_name,
arr.city AS arrival_city,
f.status,
f.aircraft_code,
f.actual_departure,
timezone( dep.timezone, f.actual_departure )
AS actual_departure_local,
f.actual_arrival,
timezone( arr.timezone, f.actual_arrival )
AS actual_arrival_local,
f.actual_arrival - f.actual_departure AS actual_duration
FROM flights f,
airports dep,
airports arr
WHERE f.departure_airport = dep.airport_code
AND f.arrival_airport = arr.airport_code;
CREATE VIEW
demo=#
Следующий пример иллюстрирует как с помощью соединения таблицы с самой собой можно подсчитать кол-во возможных рейсов из одного города в другой, при этом учитывая, что рейса из одного аэропорта в тот же самый аэропорт быть не должно.
Первый вариант
demo=# SELECT count( * )
demo-# FROM airports a1, airports a2
demo-# WHERE a1.city <> a2.city ;
count
-------
10704
(1 row)
Второй вариант с использованием JOIN
demo=# select * from airports ;
demo=# SELECT count( * )
demo-# FROM airports a1
demo-# JOIN airports a2 ON a1.city <> a2.city
demo-# ;
count
-------
10704
(1 row)
Третий вариант с явным использованием декартова произведения таблиц, для этого используется CROSS JOIN
demo=# SELECT count( * )
demo-# FROM airports a1 CROSS JOIN airports a2
demo-# WHERE a1.city <> a2.city ;
count
-------
10704
(1 row)
Во всех трех случаях запрос возвращает одинаковый результат. Вместо операнда <> можно использовать != ( не равно )
Внешние соединения
Допустим необходимо узнать сколько рейсов обслуживает самолет каждого типа и дополнительно вывести информацию о каждом самолете, для полной картины необходимо соединить две таблицы, маршруты и самолеты:
demo=# SELECT r.aircraft_code, a.model, count( * ) AS num_routes
FROM routes r
JOIN aircrafts a ON r.aircraft_code = a.aircraft_code
GROUP BY 1, 2
ORDER BY 3 DESC ;
aircraft_code | model | num_routes
---------------+---------------------+------------
CR2 | Бомбардье CRJ-200 | 232
CN1 | Сессна 208 Караван | 170
SU9 | Сухой Суперджет-100 | 158
319 | Аэробус A319-100 | 46
733 | Боинг 737-300 | 36
321 | Аэробус A321-200 | 32
763 | Боинг 767-300 | 26
773 | Боинг 777-300 | 10
(8 rows)
Запрос возвращает название самолета, только если есть маршруты, что если необходимо вывести даже те самолеты, у которых нет маршрутов, для таких запросов может использоваться операторы RIGHT JOIN LIFT JOIN совместно с оператором OUTER
Оператор LEFT и RIGHT указывают с какой стороны находится основная таблица, та которая слева или та которая справа.
demo=# SELECT r.aircraft_code, a.model, count( r.aircraft_code ) AS num_routes
FROM routes r
RIGHT OUTER JOIN aircrafts a ON r.aircraft_code = a.aircraft_code
GROUP BY 1, 2
ORDER BY 3 DESC ;
aircraft_code | model | num_routes
---------------+---------------------+------------
CR2 | Бомбардье CRJ-200 | 232
CN1 | Сессна 208 Караван | 170
SU9 | Сухой Суперджет-100 | 158
319 | Аэробус A319-100 | 46
733 | Боинг 737-300 | 36
321 | Аэробус A321-200 | 32
763 | Боинг 767-300 | 26
773 | Боинг 777-300 | 10
| Аэробус A320-200 | 0
(9 rows)
Таким образом даже при отсутствии соответствия в левой таблице, будет вывод модели самолета, к которому не привязано ни одного маршрута
Если у функции count() в скобках указан символ *, тогда будет подсчитано кол-во строк, а если указано название атрибута, тогда будет подсчитано кол-во строк в данном столбце за исключением null строкFULL OUTER JOIN
Комбинация сразу двух соединений RIGHT и LEFT, таким образом при использовании FULL OUTER JOIN выводятся строки из левой таблицы, соответствий которым нет в правой таблице и выводятся строки из правой таблицы, соответствий которым нет в левой таблице
Многотабличные запросы
Определить число пассажиров, не пришедших на регистрацию и как следствие не попали на самолет
demo=# SELECT count( * )
FROM ( ticket_flights t JOIN flights f ON t.flight_id = f.flight_id )
LEFT OUTER JOIN boarding_passes b
ON t.ticket_no = b.ticket_no AND t.flight_id = b.flight_id
WHERE f.actual_departure IS NOT NULL AND b.flight_id IS NULL ;
В данном примере первая таблица (в скобках) формируется с помощью JOIN , а вторая таблица формируется из полученной первой таблицы и таблицы после LEFT OUTER JOIN
VALUES
VALUES предназначен для формирования виртуальных таблиц, с помощью AS виртуальной таблице можно присвоить имя, после имени приводится список имен столбцов для виртуальной таблицы.
demo=# SELECT r.min_sum, r.max_sum, count(b.*)
FROM bookings b
RIGHT OUTER JOIN
( VALUES ( 0, 100000 ), ( 100000, 200000 ),
(200000, 300000 ), ( 300000, 400000 ),
(400000, 500000), ( 500000, 600000 ),
(600000, 700000), (700000, 800000)
) AS r (min_sum, max_sum)
ON b.total_amount >= r.min_sum AND b.total_amount < r.max_sum
GROUP BY r.min_sum, r.max_sum
ORDER BY r.min_sum;
min_sum | max_sum | count
---------+---------+---------
0 | 100000 | 1589446
100000 | 200000 | 380085
200000 | 300000 | 95943
300000 | 400000 | 26883
400000 | 500000 | 10588
500000 | 600000 | 5474
600000 | 700000 | 1948
700000 | 800000 | 453
(8 rows)
UNION
UNION - объединение множества строк, строка включается в итоговое множество, если она присутствует хотя бы в одном из них, строки дубликаты не включаются в результат, для их включения необходимо использовать UNION ALL
demo=#
SELECT arrival_city FROM routes
WHERE departure_city = 'Санкт-Петербург'
ORDER BY arrival_city ;
demo=# SELECT arrival_city FROM routes
WHERE departure_city = 'Москва' ;
В данном случае используется одна и та же таблица, выводится города в которые можно летать как из Москвы и если нет возможности лететь из Москвы тогда можно полететь из Санкт-Петербурга, при этом в выводе удаляются дубли
В таком запросе кол-во столбцов должно совпадать, иначе будет ошибка
INTERSECT
INTERSECT - пересечение множеств строк, строка включается в итоговое множество, если она присутствует в каждом из них, при этом строки дубликаты в результат не включается, если не указан ALL
EXCEPT
EXCEPT - разность множеств строк, строка включается в итоговое множество, если она присутствует в первом множестве, но отсутствует во втором, строки дубликаты в выводе не учитываются, если не указан ALL
В какие города можно летать из Питера, но нельзя из Москвы:
demo=# SELECT arrival_city FROM routes
WHERE departure_city = 'Санкт-Петербург'
EXCEPT
SELECT arrival_city FROM routes
WHERE departure_city = 'Москва'
ORDER BY arrival_city;
arrival_city
--------------
Иркутск
Калуга
Москва
Удачный
Череповец
Якутск
Ярославль
(7 rows)
Источники
Связанные темы
Оптимизация запросов в Postgresql
Установка и настройка MS SQL Server 2008 на Windows Server 2008 r
Системный каталог в Postgresql
Табличные пространства в Postgresql
Определение данных в Postgresql
Изменение временной зоны в Postgresql
Основные команды psql и sql Postgresql
Методы формирования соединений наборов строк
Агрегирование и группировка в Postgresql