Группировать по многим столбцам
Я вижу много кода, который, кажется, автоматически генерируется с помощью какого-то инструмента перетаскивания, и я часто вижу этот шаблон:
select col1, col2, col3, col4, col5, col6, col7, col8, col9, sum(col10)
from Table
group by col1, col2, col3, col4, col5, col6, col7, col8, col9
Часто бывает и несколько соединений. Итак, мой вопрос: как вы думаете, что автор такого кода пытался сделать, и что такое код является симптомом? Поэтому я думаю переписать их (возможно, с помощью оконных функций или объединений), но просто хочу понять, в чем причина такого кода...
Больше ясности: если я покажу вам этот код, как вы думаете, в чем проблема? Результаты правильные (я думаю), проблема в производительности.
Изменить (дополнительно): я знаю, как работает группировка, поскольку некоторые упоминают такие вещи, как: вам нужно сгруппировать по всем столбцам в вашем выборе, которые не являются агрегатами.
Мой собственный анализ: я думаю, что многие из столбцов являются просто метками, и большинство из них можно определить по одному или двум основным столбцам. То есть col3 может быть таким же, как col4, просто представленным немного иначе. т.е. Я думаю, проблема в том, что данные денормализованы перед группировкой по, но, скажем, количество отдельных групп одинаковое, скажем, с двумя столбцами, тогда лучше выполнить эту группу по, а затем объединить оставшиеся столбцы.
@CetinBasoz, спасибо. Но результат правильный, так как код работает каждый день. Я предполагаю, что моя проблема в том, что я подозреваю комбинацию, например. двух столбцов достаточно для агрегатов, а остальные, возможно, лучше объединить, а не группировать по многим столбцам. Кроме того, в таблицах не определены ключи, поэтому трудно понять, с каким соединением я имею дело...
Это может быть для авторитета на основе столбцов? Просто догадка. Может быть функция фильтра, которая получает этот сценарий и возвращает только авторизованные столбцы. Некоторые пользователи могут просматривать только столбцы col1 и col2, но некоторые пользователи могут просматривать только столбцы col1, col2 и col3 и т. д.
Если это правильно, то с несколькими столбцами все в порядке, возможно, вы пропустили в моем комментарии «все неагрегированные столбцы должны быть включены в группу по».
@ Да, производительность очень плохая.
Ответы 1
Как уже упоминалось, sum(), предоставленный в этом конкретном примере, не имеет ничего общего с агрегированием и точно так же, как простой выбор этого столбца (в этом примере). Единственный способ, с помощью которого может работать сумма, - это предоставить какие-то совокупные данные. Но это не ваш вопрос.
Часто такого рода запросы разрабатываются для поддержки какого-либо отчета. Возможно, ежемесячный итоговый отчет или что-то подобное. Список возможных виновников довольно велик. Такие продукты, как Crystal Reports, например, позволяют разработчику отчета перетаскивать столбцы в конструктор отчетов, изменять его функцию отображения на SUM или COUNT, а затем программное обеспечение Crystal Reports автоматически генерирует запрос SQL под капотом, который будет снабжать этот отчет необходимые данные для создания этого отчета. Начните с расспросов, разрабатывает ли кто-нибудь отчеты и что они используют.
Производительность будет зависеть от сложности столбцов, включенных в выборку, размера таблицы (количества строк), общей длины всех столбцов в таблице и от того, включены ли все выбранные столбцы в индекс. Например, если один из столбцов является типом varchar(max), то независимо от фактической длины данных во всех возвращаемых строках это будет тяжелый запрос, который будет плохо выполняться.
Некоторые магазины могут улучшить производительность отчетов, сначала разработав какое-то фоновое или ночное задание, которое обрабатывает все числа, обрезает ненужные или большие столбцы и сбрасывает очищенные результаты в некоторые таблицы «отчетов». Затем разработчики отчетов могут ориентироваться на эти таблицы для создания своих отчетов вместо того, чтобы заставлять отчет выполнять эти длинные запросы по запросу каждый раз, когда пользователь нажимает кнопку отчета.
Возможно, это очевидно, но причина, по которой все эти поля упоминаются в предложении group by, заключается просто в том, что они перечислены в предложении select вместе с агрегатной функцией (sum) и, следовательно, должны быть перечислены в предложении group by. В противном случае SQL Server вернет ошибку, например
"Column 'Table.col1' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."
Спасибо, хорошая информация, но я чувствую, что моя проблема не решена. Вам не кажется, что длинный список в группе по — это просто ярлыки, возможно, для одного или двух столбцов в группе по? Я не знаю данных, но данные денормализованы перед агрегированием и, следовательно, длинным списком столбцов в списке выбора. Я бы сказал, может быть, нормализовать данные, создать простую группу, и они присоединятся к оставшимся столбцам.
То есть, если всегда можно определить значения col3-col10 на основе col2, например их, было бы лучше переписать запрос на соединение после «настоящей» группы по Это всего лишь моя гипотеза, пока мне нужно будет ее проверить ...
@ xhr489 Длинный список в группе точно соответствует списку полей в выборе, за исключением поля, которое уже является агрегатом. Это необходимо, и в противном случае возникнет ошибка, поскольку SQL Server должен физически сгруппировать строки вместе в определенном порядке полей, указанном в группе, в процессе вычисления SUM, поскольку поля упоминаются в выборе. Другими словами, эта сумма представляет собой сумму различных комбинаций выбранных вами столбцов, сгруппированных в определенном порядке полей, указанном в группе.
Во-первых, всякий раз, когда я вижу объединение и sum() вместе, я сначала ставлю под сомнение их правильность. Этот sum() будет работать только тогда и только тогда, когда это отношение 1 ко многим. Лучше сначала выполнить sum(), а затем присоединиться к результату. За исключением того, что наличие большого количества столбцов в группе по допустимо, в конце концов, все неагрегированные столбцы должны быть включены в группу по (хотя это уродливо и сомнительно, с объединением -1-ко-многим- это понятно).