Иногда в запросе из базы данных необходимо получить суммы значений с накоплением по строкам. Ниже рассмотрим несколько вариантов выполнения данной задачи, в среде MS SQL.
Задача
У нас есть таблица сделок: номер сделки, дата сделки, дилер заключивший сделку и сумма сделки.
Необходимо получить список сделок, с построчным накоплением суммы сделок (RunningTotal) в разрезе дилеров, упорядоченный по дилерам и дате заключению сделки.
Подготовка
Для написания запросов создаем таблицу сделок, над которой мы будем производить эксперименты:
Производим заполнение таблицы тестовыми данными:
Реализации
Основная идея реализации заключается, в том, что нужно упорядочить все строки по требуемым условиям и производить суммирование группы строк от текущей до первой в пределах группы.
Вариант 0
Написать хранимую процедуру, в которой использовать курсор и радостно по нему пробежаться.
Примерный состав процедуры, реализующей необходимую функциональность:
Вариант 1-n
Для всех остальных вариантов нам понадобиться получить упорядоченную исходную выборку данных, с пронумерованными строками. Для этого мы создаем временную таблицу, в которой добавляем порядковый номер строки, к первоначальной выборке.
Вариантов заполнения поля RowNumber существует несколько, но мы воспользуемся самым простым, а именно функцией ROW_NUMBER, которая позволяет нам сразу упорядочить данные в соответствии с требуемыми условиями.
Вариант 1
Подзапрос в select выражении:
Это самый простой вариант, первым приходящий на ум.
Вариант 2
Join таблицы, на себя, с условиями построения группы, и отбором строк относительно текущей строки:
Тут все выглядит хорошо, но группировка по большому количеству полей выглядит не очень красиво, поэтому у нас появляется Вариант 2.1
Вариант 2.1
Аналогичен Варианту 2, но мы уменьшаем количество группировок в результирующем запросе вынося подсчет running total в отдельный подзапрос имеющий минимальное количество группировок:
Вариант 3
Данный вариант могут использовать счастливцы, которые уже работают на MS SQL 2012. В этой версии Microsoft доработал window версию функции SUM:
Самый вкусный вариант, кратко и хорошо читается.
Все рассмотренные выше варианты дают один и тот же результат выполнения:
и имеют право на жизнь, но рассмотрим их скоростные характеристики.
Скорость выполнения
Для оценки скоростных характеристик внесем некоторые изменения в первоначальную подготовку, а именно:
Тестовый стенд:
Вариант 0
Время выполнения: 0:04 - не ожидал, думал будет медленнее
Вариант 1
Время выполнения: 1:47
План выполнения:
Вариант 2
Время выполнения: 0:47
План выполнения:
Вариант 2.1
Время выполнения: 0:38
План выполнения:
Вариант 3
Время выполнения: 0:01
План выполнения:
Выводы
При использовании SQL 2012, вывод однозначный - это вариант 3. Метод с использованием курсора быстр, но его сложнее сопровождать, размер процедуры вырастает, не всегда есть возможность использовать процедуру. Ну в общем каждый выбирает для себя.
P.S. Прогнал тесты с 1,000,000 сделок:
Вариант 0 - 00:44
Вариант 2.1 - не дождался, в общем медленно
Вариант 3 - 0:22, даже с учетом того, что в этот же запрос включил первоначальную нумерацию строк.
Задача
У нас есть таблица сделок: номер сделки, дата сделки, дилер заключивший сделку и сумма сделки.
Необходимо получить список сделок, с построчным накоплением суммы сделок (RunningTotal) в разрезе дилеров, упорядоченный по дилерам и дате заключению сделки.
Подготовка
Для написания запросов создаем таблицу сделок, над которой мы будем производить эксперименты:
Производим заполнение таблицы тестовыми данными:
Реализации
Основная идея реализации заключается, в том, что нужно упорядочить все строки по требуемым условиям и производить суммирование группы строк от текущей до первой в пределах группы.
Вариант 0
Написать хранимую процедуру, в которой использовать курсор и радостно по нему пробежаться.
Примерный состав процедуры, реализующей необходимую функциональность:
Вариант 1-n
Для всех остальных вариантов нам понадобиться получить упорядоченную исходную выборку данных, с пронумерованными строками. Для этого мы создаем временную таблицу, в которой добавляем порядковый номер строки, к первоначальной выборке.
Вариантов заполнения поля RowNumber существует несколько, но мы воспользуемся самым простым, а именно функцией ROW_NUMBER, которая позволяет нам сразу упорядочить данные в соответствии с требуемыми условиями.
Вариант 1
Подзапрос в select выражении:
Это самый простой вариант, первым приходящий на ум.
Вариант 2
Join таблицы, на себя, с условиями построения группы, и отбором строк относительно текущей строки:
Тут все выглядит хорошо, но группировка по большому количеству полей выглядит не очень красиво, поэтому у нас появляется Вариант 2.1
Вариант 2.1
Аналогичен Варианту 2, но мы уменьшаем количество группировок в результирующем запросе вынося подсчет running total в отдельный подзапрос имеющий минимальное количество группировок:
Вариант 3
Данный вариант могут использовать счастливцы, которые уже работают на MS SQL 2012. В этой версии Microsoft доработал window версию функции SUM:
Самый вкусный вариант, кратко и хорошо читается.
Все рассмотренные выше варианты дают один и тот же результат выполнения:
и имеют право на жизнь, но рассмотрим их скоростные характеристики.
Скорость выполнения
Для оценки скоростных характеристик внесем некоторые изменения в первоначальную подготовку, а именно:
- Создадим в таблице Deals 100,000 записей.
- Для ускорения тестирования создадим таблицу tmp_Deals взамен #Deals и единожды заполним ее.
- Заменим во всех запросах #Deals на tmp_Deals
- Каждый рассмотренный вариант будем запускать отдельно по 3 раза, вычисляя среднее значение продолжительности работы и получая актуальный план работы.
Тестовый стенд:
- Windows 8 Pro 64
- Intel i7 3770
- 16 GB RAM
- SSD OCZ Vertex 4 512GB
- MS SQL 2012 Developer Edition
Вариант 0
Время выполнения: 0:04 - не ожидал, думал будет медленнее
Вариант 1
Время выполнения: 1:47
План выполнения:
Вариант 2
Время выполнения: 0:47
План выполнения:
Вариант 2.1
Время выполнения: 0:38
План выполнения:
Вариант 3
Время выполнения: 0:01
План выполнения:
Выводы
При использовании SQL 2012, вывод однозначный - это вариант 3. Метод с использованием курсора быстр, но его сложнее сопровождать, размер процедуры вырастает, не всегда есть возможность использовать процедуру. Ну в общем каждый выбирает для себя.
P.S. Прогнал тесты с 1,000,000 сделок:
Вариант 0 - 00:44
Вариант 2.1 - не дождался, в общем медленно
Вариант 3 - 0:22, даже с учетом того, что в этот же запрос включил первоначальную нумерацию строк.
Комментариев нет :
Отправить комментарий