SQL Server: агрегатные вычисления с Distinct | Использование сгруппированных запросов
Содержание:
1. Задача и отсутствующий синтаксис;
2. Решение 1. Использование сгруппированных запросов (Вы читаете данный раздел);
3. Решение 2. Использование ROW_NUMBER;
4. Решение 3 и Решение 4;
5. Тест производительности и пакетная обработка.
Первое представленное решение (назовем его Решением 1) покажется многим самым очевидным. Ниже приводится полный программный код решения.
Код решения 1
Обобщенное табличное выражение Base основывается на запросе, который обрабатывает все применимые объединения, фильтры и другие предварительные шаги, и вы можете выполнить любые последующие вычисления над тем же базовым результатом. В нашем случае это означает объединение Orders и OrderDetails и фильтрацию заказов от января 2016 года. Обобщенное табличное выражение CustCounts вычисляет количество сгруппированных отдельных продуктов для каждого клиента из Base, а обобщенное табличное выражение AllCount вычисляет общее количество отдельных продуктов из Base.
Внешний запрос объединяет Base, CustCounts и AllCount, чтобы сопоставить каждой строке заказа количество отдельных продуктов соответствующего клиента и общее количество отдельных продуктов. Затем внешний запрос применяет логику скидок на основе перечисленных выше правил и, наконец, группирует данные по custid и агрегирует скидки по строкам, чтобы вычислить общую скидку клиента. План выполнения для Решения 1 показан на рисунке ниже. Этот план был применен на моем компьютере к большим таблицам с именами OrdersBig (приблизительно 1 млн строк) и OrderDetaiIsBig (приблизительно 3 млн строк). Тестовые данные для более объемных таблиц и пересмотренный код решения приведены далее в разделе о тестировании производительности.
План для Решения 1 в построчном режиме
В двух верхних ветвях плана вычисляются сгруппированные уникальные агрегаты. Каждая получает данные из двух входных таблиц, применяет объединение, а затем агрегирование. Нижняя ветвь получает подробные данные из двух входных таблиц и применяет окончательный сгруппированный агрегат. Сложные операции объединения и агрегирования в этом плане выполняются хеш-операторам и; буферизация не предусмотрена. Это решение было выполнено за 5 секунд при обработке большой таблицы на моем компьютере, и это при использовании только операторов построчного режима.
Естественно, это решение пригодно и для других агрегатных вычислений с ключевым словом DISTINCT, таких как SUM (DISTINCT ...).
Планируете постигать агрегатные вычисления с Distinct в SQL Server на мягком диване в уюте и комфорте? В этом случае вот вам 16 планшетов от 1200 грн. Эти недорогие, но чрезвычайно функциональные устройства, позволят вам изучить представленную выше информацию и даже отточить ее на практике в любом месте, где есть доступ к Интернету!
1. Задача и отсутствующий синтаксис;
2.
3. Решение 2. Использование ROW_NUMBER;
4. Решение 3 и Решение 4;
5. Тест производительности и пакетная обработка.
Первое представленное решение (назовем его Решением 1) покажется многим самым очевидным. Ниже приводится полный программный код решения.
Код решения 1
Обобщенное табличное выражение Base основывается на запросе, который обрабатывает все применимые объединения, фильтры и другие предварительные шаги, и вы можете выполнить любые последующие вычисления над тем же базовым результатом. В нашем случае это означает объединение Orders и OrderDetails и фильтрацию заказов от января 2016 года. Обобщенное табличное выражение CustCounts вычисляет количество сгруппированных отдельных продуктов для каждого клиента из Base, а обобщенное табличное выражение AllCount вычисляет общее количество отдельных продуктов из Base.
Внешний запрос объединяет Base, CustCounts и AllCount, чтобы сопоставить каждой строке заказа количество отдельных продуктов соответствующего клиента и общее количество отдельных продуктов. Затем внешний запрос применяет логику скидок на основе перечисленных выше правил и, наконец, группирует данные по custid и агрегирует скидки по строкам, чтобы вычислить общую скидку клиента. План выполнения для Решения 1 показан на рисунке ниже. Этот план был применен на моем компьютере к большим таблицам с именами OrdersBig (приблизительно 1 млн строк) и OrderDetaiIsBig (приблизительно 3 млн строк). Тестовые данные для более объемных таблиц и пересмотренный код решения приведены далее в разделе о тестировании производительности.
План для Решения 1 в построчном режиме
В двух верхних ветвях плана вычисляются сгруппированные уникальные агрегаты. Каждая получает данные из двух входных таблиц, применяет объединение, а затем агрегирование. Нижняя ветвь получает подробные данные из двух входных таблиц и применяет окончательный сгруппированный агрегат. Сложные операции объединения и агрегирования в этом плане выполняются хеш-операторам и; буферизация не предусмотрена. Это решение было выполнено за 5 секунд при обработке большой таблицы на моем компьютере, и это при использовании только операторов построчного режима.
Естественно, это решение пригодно и для других агрегатных вычислений с ключевым словом DISTINCT, таких как SUM (DISTINCT ...).
Планируете постигать агрегатные вычисления с Distinct в SQL Server на мягком диване в уюте и комфорте? В этом случае вот вам 16 планшетов от 1200 грн. Эти недорогие, но чрезвычайно функциональные устройства, позволят вам изучить представленную выше информацию и даже отточить ее на практике в любом месте, где есть доступ к Интернету!
