Новость из категории: Информация

SQL Server: агрегатные вычисления с Distinct |Решение 3 и Решение 4

Содержание:
1. Задача и отсутствующий синтаксис;
2. Решение 1. Использование сгруппированных запросов;
3. Решение 2. Использование ROW_NUMBER;
4. Решение 3 и Решение 4 (Вы читаете данный раздел);
5. Тест производительности и пакетная обработка.
SQL Server: агрегатные вычисления с Distinct |Решение 3 и Решение 4

Решение 3. Использование MAX (DENSE.RANK)

В третьем решении (Решение 3) используется общая форма, аналогичная Решению 2, но вместо оконных функций ROW NUMBER и COUNT применяются оконные функции DENSE RANK и МАХ. Учтите, что максимальное значение плотности диапазона представляет собой просто количество различных объектов. В предыдущем решении в C1 вычисление ROW NUMBER заменяется вычислением DENSE RANK, основанным на тех же оконных характеристиках. В С2 вычисление COUNT заменяется на МАХ, применяемое к результатам плотности диапазона, полученным в С1. Остальное без изменений. В коде ниже приводится программный код полного решения.

SQL Server: агрегатные вычисления с Distinct |Решение 3 и Решение 4
Код Решения 3

План для Решения 3 показан на рисунке ниже.

SQL Server: агрегатные вычисления с Distinct |Решение 3 и Решение 4
План для Решения 3 в построчном режиме

Очевидно, этот план очень похож на план для Решения 2. Функция DENSE RAN К рассчитывается очень похоже на ROW NUMBER, а МАХ рассчитывается похоже на COUNT. Поэтому неудивительно, что это решение также довольно медленное. На моем компьютере оно выполнялось 24 секунды.

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

Решение 4. Использование DENSE_RANK asc + DENSE RANK desc — 1

Наиболее творческий подход применен в четвертом рассматриваемом решении (Решение 4). Оно появилось как ответ на вопрос на форуме stackoverflow.com по адресу: https://stackoverflow.com/questions/11202878/partition-function-count-over-possible-using-distinct.

SQL Server: агрегатные вычисления с Distinct |Решение 3 и Решение 4
Заполнение таблицы Т1 для Решения 4

Его идея заключается в том, что вычисляются два значения плотности диапазона — одно нарастающее (drkasc), другое убывающее (drkdesc). Сумма двух величин всегда на единицу больше количества уникальных объектов, поэтому, чтобы получить количество уникальных объектов, нужно сложить две величины и вычесть единицу. Для простоты метод будет продемонстрирован на примере упомянутой выше таблицы T1. Используйте программный код выше, чтобы создать и заполнить таблицу.

SQL Server: агрегатные вычисления с Distinct |Решение 3 и Решение 4
Вычисление количества величин

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

SQL Server: агрегатные вычисления с Distinct |Решение 3 и Решение 4
Результаты работы кода, представленного выше

SQL Server: агрегатные вычисления с Distinct |Решение 3 и Решение 4
Код Решения 4

Очевидно, что нарастающая плотность диапазона начинается с 1 и увеличивается на единицу на каждом шаге, а убывающая плотность диапазона начинается с количества уникальных объектов и уменьшается на единицу на каждом шаге. Поэтому их сумма во всех строках всегда на единицу больше количества уникальных объектов. Таким образом, чтобы получить количество уникальных объектов, сложите две величины и вычтите 1. Как применяется эта остроумная идея к поставленной в статье задаче в Решении 4, показано в коде выше. План для данного решения приведен на рисунке ниже.

SQL Server: агрегатные вычисления с Distinct |Решение 3 и Решение 4
План для Решения 4 в построчном режиме

Большое достоинство плана состоит в том, что базовые данные считываются и объединяются только один раз и буферизации не происходит. К сожалению, чтобы обеспечить вычисление плотности диапазона, приходится дважды сортировать большой объем данных. При использовании только операторов построчного режима для выполнения плана на моем компьютере потребовалось 15 секунд. Это лучше, чем в случае Решения 2 и Решения 3, но медленнее, чем в Решении 1.

Как мы видим. Решение 4 ограничено подсчетом уникальных объектов и не может использоваться для других типов агрегатных вычислений с ключевым словом DISTINCT.


Не как не можете вникнуть в суть использования представленных выше решений и все потому, что ваша голова забита мыслями о том, чтобы такого интересного подарить своей девушке на день рождения? В этом случае могу порекомендовать вам роза в колбе купить. Такой подарок определенно точно придется избраннице вашего сердца по вкусу и надолго ей запомнится.

Рейтинг статьи

Оценка
0/5
голосов: 0
Ваша оценка статье по пятибальной шкале:
 
 
   

Поделиться

Перевести статью:

Похожие новости

Комментарии

Информация

^ Наверх