ОТЛИЧАЙТЕСЬ с РАЗДЕЛЕНИЕМ по сравнению с GROUPBY

Я нашел некоторые SQL-запросы в приложении, которое я рассматриваю следующим образом:

SELECT DISTINCT
Company, Warehouse, Item,
SUM(quantity) OVER (PARTITION BY Company, Warehouse, Item) AS stock

Я уверен, что это дает тот же результат, что и:

SELECT
Company, Warehouse, Item,
SUM(quantity) AS stock
GROUP BY Company, Warehouse, Item

Есть ли какая-либо польза (производительность, читаемость, дополнительная гибкость при написании запроса, ремонтопригодность и т.д.) с использованием первого подхода к более позднему?

Ответы

Ответ 1

Производительность:

Победитель: GROUP BY

Некоторые очень рудиментарные тесты на большой таблице с неиндексированными столбцами показали, что по крайней мере в моем случае два запроса породили совершенно другой план запроса. Единица для PARTITION BY была значительно медленнее.

План запроса GROUP BY включал только операцию сканирования и агрегирования таблицы, тогда как план PARTITION BY имел два вложенных цикла. PARTITION BY занял около 2800 мс во втором прогоне, GROUP BY заняло всего 500 мс.

Считываемость/ремонтопригодность:

Победитель: GROUP BY

Основываясь на мнениях комментаторов здесь, PARTITION BY менее читабельен для большинства разработчиков, поэтому его, вероятно, будет еще труднее поддерживать в будущем.

Гибкость

Победитель: PARTITION BY

PARTITION BY дает вам большую гибкость при выборе столбцов группировки. С GROUP BY вы можете иметь только один набор столбцов группировки для всех агрегированных столбцов. С помощью DISTINCT + PARTITION BY в каждом разделе может быть разный столбец. Также на некоторых СУБД вы можете выбрать из более агрегированных/аналитических функций в предложении OVER.

Ответ 2

Использование sum() в качестве аналитической функции с over partition by не требуется. Я не думаю, что есть большая разница между ними в любом смысле. В оракуле гораздо больше аналитической функции, чем функция агрегации. Я думаю, что ms-sql - это тот же случай. И, например, lag(), lead(), rank(), dense rank() и т.д. Гораздо сложнее реализовать только с group by. Конечно, этот аргумент не предназначен для защиты первой версии...

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

Ответ 3

Хотя оба запроса, похоже, вычисляют одно и то же, когда вы смотрите на столбцы, они фактически производят совершенно другой набор строк.

Первый, использующий аналитическую функцию, выдает ровно одну строку для каждой строки ввода. То есть для информации о запасах EACH, она вернет строку с общим количеством для ассоциированной компании/склада/предмета. (кстати, вычисление среднего для меня имеет смысл, но кто знает...)

Второй возвращает только одну строку для каждой комбинации компаний/складов/товаров.

Итак, да, в этом примере первый запрос кажется немного бесполезным... если вы не хотите вычислять статистику уровня запаса, например текущее соотношение акций по сравнению с общим количеством компании/складом/товаром (просто пример, t знать, имеет ли он какое-либо деловое значение!)

Аналитическая функция - очень мощный механизм в SQL, в некотором смысле более мощный, чем групповой. Но используйте его с осторожностью... Простое эмпирическое правило может быть: если вы можете вычислить его с помощью группы, не используйте аналитическую функцию;)