Два способа создания итогов в SQL
Пересказ статьи Steve Sohcot. Two Ways To Create Totals In SQL
При представлении табличных данных вам может потребоваться строка "итого" внизу списка.
Помнится лет 25! назад я применял подход создания многомерного массива для суммирования значений строка за строкой и столбец за столбцом. Как на стороне сервера, так и на JavaScript.
Создание итоговой строки на SQL (на стороне сервера) является более эффективным. Это также облегчает работу фронтенд разработчику.
Для начала давайте предположим, что у нас есть подобный пример запроса:
Результаты:
Первый подход, о котором я буду говорить, фактически создает копию вашего набора данных:
Теперь у нас есть строка "Итого", но она не является последней строкой набора данных:
"Total" - не последняя строка
Мы можем исправить это, изменив предложение ORDER BY с использованием предложения CASE:
Обратите внимание на оператор CASE в ORDER BY
Объяснение: Когда столбец содержит “Total”, то это дает значение 1, для всех остальных записей - значение 0. Следовательно, итоги всегда будут находиться в конце при сортировке по возрастанию. Вторичная сортировка использует другое поле (здесь “agent”).
Теперь мы имеем тоги в конце:
“Total” является последней строкой, как и должно быть
Вы можете просто добавить функцию ROLLUP сразу после GROUP BY.
Это работает, но вы увидите значение NULL:
Итоги обозначаются как NULL
Опять таки, чтобы облегчить жизнь фронтэтд-разработчику, мы можем заменить NULL на "Total".
Просто заменить будет недостаточно, необходимо использовать ранее упомянутый "сортировочный" трюк:
Обратите внимание, что я заменяю NULL на "Total" в нескольких местах.
Мне часто требуется иметь "Total" последней строкой. Иногда у меня будет значение "Other", которое я хотел бы иметь предпоследней строкой (вместо вывода в алфавитном порядке). И тут оператор CASE в предложении ORDER BY поможет решить эту задачу:
Ссылки по теме
1. Разница между Cube и Rollup
2. Получение итоговых данных с помощью оператора ROLLUP
SELECT agent, SUM(hours) AS totalHours
FROM myTable
GROUP BY agent
ORDER BY agentРезультаты:
Метод 1: создание итогов с помощью UNION ALL
Первый подход, о котором я буду говорить, фактически создает копию вашего набора данных:
- В этой копии отсутствует первый столбец, в котором выводятся различные значения.
- Жестко кодируется слово "Total", чтобы совпадало число столбцов в каждом наборе данных
- Объединяем два набора данных посредством UNION ALL.
- Оборачиваем оба набора в оператор SELECT.
Не забудьте удалить ORDER BY из подзапросов.
SELECT
agent, SUM(totalHours) AS totalHours
FROM (
-- Исходный запрос
SELECT agent, SUM(hours) AS totalHours
FROM myTable
GROUP BY agent
UNION ALL
-- Копируем, но не показываем (или GROUP BY) первый столбец
-- вместо этого жестко кодируем слово "Total"
SELECT 'Total', SUM(hours) AS totalHours
FROM myTable
) AS qryMain
GROUP BY
agent
ORDER BY
agentТеперь у нас есть строка "Итого", но она не является последней строкой набора данных:
"Total" - не последняя строка
Мы можем исправить это, изменив предложение ORDER BY с использованием предложения CASE:
SELECT
agent, SUM(totalHours) AS totalHours
FROM (
-- Исходный запрос
SELECT agent, SUM(hours) AS totalHours
FROM myTable
GROUP BY agent
UNION ALL
-- Копируем, но не показываем (или GROUP BY) первый столбец
-- вместо этого жестко кодируем слово "Total"
SELECT 'Total', SUM(hours) AS totalHours
FROM myTable
) AS qryMain
GROUP BY
agent
ORDER BY
CASE
WHEN agent = 'Total' THEN 1
ELSE 0
END,
agentОбратите внимание на оператор CASE в ORDER BY
Объяснение: Когда столбец содержит “Total”, то это дает значение 1, для всех остальных записей - значение 0. Следовательно, итоги всегда будут находиться в конце при сортировке по возрастанию. Вторичная сортировка использует другое поле (здесь “agent”).
Теперь мы имеем тоги в конце:
“Total” является последней строкой, как и должно быть
Метод 2: создание итогов при помощи ROLLUP
Этот метод может быть специфичен для используемой версии SQL. Это работает в MS SQL Server
Вы можете просто добавить функцию ROLLUP сразу после GROUP BY.
Не забудьте скобки
SELECT agent, SUM(hours) AS totalHours
FROM myTable
GROUP BY
ROLLUP (agent)Это работает, но вы увидите значение NULL:
Итоги обозначаются как NULL
Опять таки, чтобы облегчить жизнь фронтэтд-разработчику, мы можем заменить NULL на "Total".
Просто заменить будет недостаточно, необходимо использовать ранее упомянутый "сортировочный" трюк:
SELECT ISNULL(agent,'Total') AS agent, SUM(hours) AS totalHours
FROM myTable
GROUP BY
ROLLUP (agent)
ORDER BY
CASE
WHEN ISNULL(agent, 'Total') = 'Total' THEN 1
ELSE 0
END,
ISNULL(agent,'Total')Обратите внимание, что я заменяю NULL на "Total" в нескольких местах.
Последний трюк с итогами SQL
Мне часто требуется иметь "Total" последней строкой. Иногда у меня будет значение "Other", которое я хотел бы иметь предпоследней строкой (вместо вывода в алфавитном порядке). И тут оператор CASE в предложении ORDER BY поможет решить эту задачу:
SELECT ISNULL(agent,'Total') AS agent, SUM(hours) AS totalHours
FROM myTable
GROUP BY
ROLLUP (agent)
ORDER BY
CASE
WHEN ISNULL(agent, 'Total') = 'Other' THEN 1
WHEN ISNULL(agent, 'Total') = 'Total' THEN 9
ELSE 0
END,
ISNULL(agent,'Total')Ссылки по теме
1. Разница между Cube и Rollup
2. Получение итоговых данных с помощью оператора ROLLUP
Обратные ссылки
Нет обратных ссылок
Автор не разрешил комментировать эту запись
Комментарии
Показывать комментарии Как список | Древовидной структурой