Язык манипуляции данными в SQL

Оператор SELECT

Рассмотрим еще несколько примеров.

Посчитать количество рейсов из Ростова в Москву, и количество рейсов, выполняемых в остальные города.

Здесь мы можем воспользоваться вычисляемым столбцом, по значениям которого будем выполнять группировку:

Консоль

Выполнить

SELECT flag, COUNT(*) qty FROM 
(SELECT CASE WHEN town_to ='Moscow' THEN 'Moscow' ELSE 'Other' END flag
FROM Trip
WHERE town_from='Rostov'
) X 
GROUP BY flag;

SELECT flag, COUNT(*) qty FROM (SELECT CASE WHEN town_to ='Moscow' THEN 'Moscow' ELSE 'Other' END flag FROM Trip WHERE town_from='Rostov' ) X GROUP BY flag;

    Схема

Нет схемы

Посчитать общее количество рейсов из Ростова и количество рейсов, пунктом назначения которых не является Москва.

В этой задаче тоже требуется выполнить агрегацию по двум выборкам, при этом одна из выборок является подмножеством второй. Поэтому здесь напрямую не подойдёт вычисляемый столбец, по которому можно выполнить группировку. Это годилось для решения предыдущей задачи, когда множество делилось на собственные непересекающиеся подмножества, по каждому из которых требовалось выполнить агрегацию.

Для решения данной задачи мы можем посчитать количество по всему множеству и использовать подзапрос для подсчета значений в подмножестве (второе обращение к таблице) или использовать CASE в сочетании с агрегатной функцией, чтобы избежать повторного чтения таблицы. Давайте посмотрим, как оценит оптимизатор эти варианты.

Использование подзапроса

Консоль

Выполнить

SELECT COUNT(*) total, 
(SELECT COUNT(*) FROM Trip 
WHERE town_from='Rostov' AND town_to <> 'Moscow') non_moscow
FROM Trip
WHERE town_from='Rostov';

SELECT COUNT(*) total, (SELECT COUNT(*) FROM Trip WHERE town_from='Rostov' AND town_to <> 'Moscow') non_moscow FROM Trip WHERE town_from='Rostov';

    Схема

Нет схемы

 Использование CASE с агрегатной функцией

Консоль

Выполнить

SELECT COUNT(*) total_qty,  
SUM(CASE WHEN town_to <>'Moscow' THEN 1 ELSE 0 END) non_moscow
FROM Trip
WHERE town_from='Rostov';

SELECT COUNT(*) total_qty,   SUM(CASE WHEN town_to <>'Moscow' THEN 1 ELSE 0 END) non_moscow FROM Trip WHERE town_from='Rostov';

    Схема

Нет схемы

Результат, естественно, будет одинаков:

а вот стоимость второго запроса, как и ожидалось, оказалась вдвое ниже.

Вы можете сравнить реальное время выполнения, если сгенерируете достаточный объём данных.

Второй вариант можно записать более компактно, если использовать функцию NULLIF - сокращенный вариант частного случая использования CASE:

Консоль

Выполнить

SELECT COUNT(*) total_qty,  
COUNT(NULLIF(town_to, 'Moscow')) non_moscow
FROM Trip
WHERE town_from='Rostov';

SELECT COUNT(*) total_qty,   COUNT(NULLIF(town_to, 'Moscow')) non_moscow FROM Trip WHERE town_from='Rostov';

    Схема

Нет схемы

Функция NULLIF возвращает NULL, если её аргументы равны, или первый аргумент в противном случае.

В решении используется тот факт, что агрегатные функции не учитывают NULL-значения,  которые появляются в аргументе функции COUNT тогда, когда город прибытия равен 'Moscow'.