podzaprosy_v_postgresql

Подзапросы в Postgresql

Логический порядок обработки запроса SELECT

В запросе SELECT сначала вычисляются элементы после ключевого слова FROM, это может быть таблица, декартово произведение нескольких таблиц или виртуальная таблица (VALUES).
Далее обрабатывается условие WHERE.
Если есть GROUP BY тогда результирующие строки группируются, вычисляются значения агрегатных функций.
HAVING отфильтровывает строки, которые не подходят под условия. SELECT DISTINCT удаляет дубликаты.
Наличие ORDER BY сортирует строки на основе значений одного или более атрибутов.
LIMIT или OFFSET возвращает подмножество строк из выборки

Примеры подзапросов

Подсчитать кол-во операций, в которых значение атрибута превышает среднее значение другого атрибута той же таблицы

demo=# SELECT count( * ) FROM bookings
WHERE total_amount > ( SELECT avg( total_amount ) FROM bookings );

Подзапрос заключается в круглые скобки
Подзапросы могут включаться в предложениях SELECT, FROM, WHERE, HAVING, WITH
Если запрос возвращает несколько значений, его можно использовать в предикате IN, что позволит проверить значения на принадлежность множеству значений.

SELECT flight_no, departure_city, arrival_city
FROM routes
WHERE departure_city IN (
SELECT city
FROM airports
WHERE timezone ~ 'Krasnoyarsk'
)
AND arrival_city IN (
SELECT city
FROM airports
WHERE timezone ~ 'Krasnoyarsk'
);

Вывод: номер аэропорта, город отправитель, город получатель, где город отправитель и получатель, из временной зоны Красноярск
В подзапросах также можно использовать дополнительные условия, агрегатные функции
Можно сделать наоборот, используя NOT IN , т.е. выбрать те значения, которые не соответствуют списку из подзапроса
Факт наличия или отсутствия строк в конкретной таблице, значения атрибутов не имеют значения
Например, в какие города нет рейсов из Москвы

demo=# SELECT DISTINCT a.city
FROM airports a
WHERE NOT EXISTS (
SELECT * FROM routes r
WHERE r.departure_city = 'Москва'
AND r.arrival_city = a.city
)
AND a.city <> 'Москва'
ORDER BY city;

Запрос говорит выбрать в таблице аэропорты города, кроме Москвы, которых нет в подзапросе WHERE
Условие WHERE с помощью EXISTS(существует) отбирает из таблицы routes маршруты, где город отправления Москва и город прибытия = текущему городу из таблицы аэропорты
Города не повторяются
EXISTS устанавливает только сам факт наличия соответствующей строки в подзапросе

Подзапросы в SELECT

demo=# SELECT a.model, ( SELECT count( * ) FROM seats s WHERE s.aircraft_code = a.aircraft_code AND s.fare_conditions = 'Business' ) AS business FROM aircrafts a;
        model        | business 
---------------------+----------
 Боинг 777-300       |       30
 Боинг 767-300       |       30
 Сухой Суперджет-100 |       12
 Аэробус A320-200    |       20
 Аэробус A321-200    |       28
 Аэробус A319-100    |       20
 Боинг 737-300       |       12
 Сессна 208 Караван  |        0
 Бомбардье CRJ-200   |        0
(9 rows)

В данном случае, значения для второй колонки берутся из другой таблицы

Запросы в FROM

FROM ( SELECT a.model, s.fare_conditions, count( * ) AS num FROM aircrafts a  JOIN seats s ON a.aircraft_code = s.aircraft_code GROUP BY 1, 2 ORDER BY 1, 2 ) AS s2
GROUP BY s2.model
ORDER BY s2.model ;
        model        |                 string_agg                 
---------------------+--------------------------------------------
 Аэробус A319-100    | Business (20), Economy (96)
 Аэробус A320-200    | Business (20), Economy (120)
 Аэробус A321-200    | Business (28), Economy (142)
 Боинг 737-300       | Business (12), Economy (118)
 Боинг 767-300       | Business (30), Economy (192)
 Боинг 777-300       | Business (30), Comfort (48), Economy (324)
 Бомбардье CRJ-200   | Economy (50)
 Сессна 208 Караван  | Economy (12)
 Сухой Суперджет-100 | Business (12), Economy (85)
(9 rows)

Здесь подзапрос создает временные таблицы
string_agg - собирает все входные значения основанные на группировании строк, через разделитель заданный параметром delimiter

demo=# SELECT aa.city, aa.airport_code, aa.airport_name FROM ( SELECT city, count( * )
FROM airports
GROUP BY city
HAVING count( * ) > 1 ) AS a
JOIN airports AS aa ON a.city = aa.city
ORDER BY aa.city, aa.airport_name ;
   city    | airport_code |    airport_name     
-----------+--------------+---------------------
 Москва    | VKO          | Внуково
 Москва    | DME          | Домодедово
 Москва    | SVO          | Шереметьево
 Ульяновск | ULV          | Баратаевка
 Ульяновск | ULY          | Ульяновск-Восточный
(5 rows)

Здесь создается временная таблица a , которая соединяется с таблицей airports под алиасом aa