gifts2017

Вычисление нарастающего итога для N таблиц в рамках одного запроса

Опубликовал Юрий Строжевский (y-str) в раздел Программирование - Практика программирования

Данная часть статья является чисто теоретическим отступлением, объясняющим механизм, который будет использован в дальнейшем. В статье объясняется, как получить значения «нарастающего итога» для N различных таблиц в рамках одного запроса.

Для начала рассмотрим общий вид запроса, в котором присутствуют две (или более) таблицы. Первый вариант:

SELECT
 Таблица1.*,
 Таблица2.*
FROM Таблица1, Таблица2

Второй вариант (по итогу этот вариант аналогичен первому):

SELECT
 *
FROM Таблица1
LEFT JOIN Таблица2
ON Таблица1.ПолеСоединения = Таблица2.ПолеСоединения

Примем, что в качестве «Таблицы1» и «Таблицы2» используются таблицы со следующей структурой и значениями:

В наши цели входит одновременное вычисление «нарастающего итога» для этих двух таблиц одновременно, в рамках одного запроса. Напишем подобный запрос:

SELECT
 Таблица11.ПолеПодсчета1,
 SUM( Таблица12.ПолеСуммирования1 ) AS СуммаПредПолей1,
 Таблица21.ПолеПодсчета2,
 SUM( Таблица22.ПолеСуммирования2 ) AS СуммаПредПолей2
FROM Таблица1 AS Таблица11
LEFT JOIN Таблица1 AS Таблица12
ON Таблица11.ПолеПодсчета1 > Таблица12.ПолеПодсчета1,
Таблица2 AS Таблица21
LEFT JOIN Таблица2 AS Таблица22
ON Таблица21.ПолеПодсчета2 > Таблица22.ПолеПодсчета2
GROUP BY Таблица11.ПолеПодсчета1, Таблица21.ПолеПодсчета2

На самом деле в этом запросе отсутствует вычисление самого «нарастающего итога» и есть только вычисление суммы по всем предыдущим значениям суммируемой колонки. Однако, как известно, «нарастающий итог» может быть получен простым суммированием суммы по предыдущим значениям суммируемой колонки и текущим значением для этой колонки. Для простоты первоначально сосредоточимся на главной составляющей «нарастающего итога» - сумме по всем предыдущим значениям суммируемой колонки.

Итак, после применения приведенного выше запроса получаем следующую выходную таблицу:

Значения в «Таблице1» и «Таблице2» подобраны так, чтобы при вычислении сумм давать последовательные значения (2, 3, 4 и т.д.). Однако, как можно заметить из последнего рисунка, значения не всегда соответствуют ожидаемым (см. выделенные области на рисунке). Для пояснения причин подобных «ошибок суммирования» приведем результат запроса к «Таблице1» и «Таблице2» без группировки значений и подсчета сумм:

SELECT
Таблица1.*,
Таблица2.*
FROM Таблица1, Таблица2

Как можно заметить, в полученной таблице для каждой строки из «Таблицы1» присутствуют все строки из «Таблицы2». Следовательно строки как из «Таблицы1», так и из «Таблицы2» просто повторяются, что и приводит в свою очередь к «неправильному суммированию».

Для решения проблемы «неправильного суммирования» выполним следующий запрос:

SELECT
 Таблица11.ПолеПодсчета1,
 COUNT( DISTINCT Таблица12.ПолеПодсчета1 ) AS Счетчик1,
 SUM( Таблица12.ПолеСуммирования1 ) AS СуммаПредПолей1,
 Таблица21.ПолеПодсчета2,
 COUNT( DISTINCT Таблица22.ПолеПодсчета2 ) AS Счетчик2,
 SUM( Таблица22.ПолеСуммирования2 ) AS СуммаПредПолей2
FROM Таблица1 AS Таблица11
LEFT JOIN Таблица1 AS Таблица12
ON Таблица11.ПолеПодсчета1 > Таблица12.ПолеПодсчета1,
Таблица2 AS Таблица21
LEFT JOIN Таблица2 AS Таблица22
ON Таблица21.ПолеПодсчета2 > Таблица22.ПолеПодсчета2
GROUP BY Таблица11.ПолеПодсчета1, Таблица21.ПолеПодсчета2

В результате выполнения данного запроса получим следующий результирующий набор:

Как можно видеть из рисунка, имеется прямая связь между значениями счётчиков, и просуммированными значениями - суммированные значения превышают правильные данные ровно в то количество раз, которое и является значением соответствующего счётчика. Таким образом, правильное значение для сумм предыдущих значений суммируемой колонки можно получить, разделив первично вычисленное значение суммы на величину соответствующего счётчика. Один нюанс - счетчик может принимать значение «0», и в запросе следует предусмотреть этот вариант.

Запишем правильный запрос для вычисления полноценного «нарастающего итога» для двух различных таблиц:

SELECT
 Таблица11.ПолеПодсчета1,
 SUM( Таблица12.ПолеСуммирования1 ) 
 / 
 CASE
  WHEN
   COUNT( DISTINCT Таблица22.ПолеПодсчета2 ) = 0
  THEN
   1
 ELSE
  COUNT( DISTINCT Таблица22.ПолеПодсчета2 )
 END + MAX( Таблица11.ПолеСуммирования1 ) AS НарастающийИтог1,
 Таблица21.ПолеПодсчета2,
 SUM( Таблица22.ПолеСуммирования2 ) 
 / 
 CASE
  WHEN
   COUNT( DISTINCT Таблица12.ПолеПодсчета1 ) = 0
  THEN
   1
 ELSE
  COUNT( DISTINCT Таблица12.ПолеПодсчета1 )
 END + MAX( Таблица21.ПолеСуммирования2 ) AS НарастающийИтог2
FROM Таблица1 AS Таблица11
LEFT JOIN Таблица1 AS Таблица12
ON Таблица11.ПолеПодсчета1 > Таблица12.ПолеПодсчета1, 
Таблица2 AS Таблица21
LEFT JOIN Таблица2 AS Таблица22
ON Таблица21.ПолеПодсчета2 > Таблица22.ПолеПодсчета2
GROUP BY Таблица11.ПолеПодсчета1, Таблица21.ПолеПодсчета2

Теперь добавим в запрос третью таблицу со следующими значениями:

Опять выполним запрос, в котором будут объединены все три таблицы и будут присутствовать нужные нам значения счётчиков:

SELECT
 Таблица11.ПолеПодсчета1,
 COUNT( DISTINCT Таблица12.ПолеПодсчета1 ) AS Счетчик1,
 SUM( Таблица12.ПолеСуммирования1 ) AS СуммаПредпПолей1,
 Таблица21.ПолеПодсчета2,
 COUNT( DISTINCT Таблица22.ПолеПодсчета2 ) AS Счетчик2,
 SUM( Таблица22.ПолеСуммирования2 ) AS СммаПредпПолей2,
 Таблица31.ПолеПодсчета3,
 COUNT( DISTINCT Таблица32.ПолеПодсчета3 ) AS Счетчик3,
 SUM( Таблица32.ПолеСуммирования3 ) AS СуммаПредпПолей3
FROM Таблица1 AS Таблица11
LEFT JOIN Таблица1 AS Таблица12
ON Таблица11.ПолеПодсчета1 > Таблица12.ПолеПодсчета1, 
Таблица2 AS Таблица21
LEFT JOIN Таблица2 AS Таблица22
ON Таблица21.ПолеПодсчета2 > Таблица22.ПолеПодсчета2,
Таблица3 AS Таблица31
LEFT JOIN Таблица3 AS Таблица32
ON Таблица31.ПолеПодсчета3 > Таблица32.ПолеПодсчета3
GROUP BY Таблица11.ПолеПодсчета1, Таблица21.ПолеПодсчета2, Таблица31.ПолеПодсчета3

Как можно заметить, для получения правильных значений сумм необходимо деление не просто на значение для одного счётчика, а на их произведение.

Приведем запрос, позволяющий вычислить «нарастающие итоги» для трех таблиц одновременно:

SELECT
 Таблица11.ПолеПодсчета1,
 SUM( Таблица12.ПолеСуммирования1 ) 
 / 
 (
  CASE
   WHEN
    COUNT( DISTINCT Таблица22.ПолеПодсчета2 ) = 0
   THEN
    1
  ELSE
   COUNT( DISTINCT Таблица22.ПолеПодсчета2 )
  END 
  *
  CASE
   WHEN
    COUNT( DISTINCT Таблица32.ПолеПодсчета3 ) = 0
   THEN
    1
  ELSE
   COUNT( DISTINCT Таблица32.ПолеПодсчета3 )
  END 
 ) + MAX( Таблица11.ПолеСуммирования1 ) AS НарастающийИтог1,
 Таблица21.ПолеПодсчета2,
 SUM( Таблица22.ПолеСуммирования2 ) 
 / 
 (
  CASE
   WHEN
    COUNT( DISTINCT Таблица12.ПолеПодсчета1 ) = 0
   THEN
    1
  ELSE
   COUNT( DISTINCT Таблица12.ПолеПодсчета1 )
  END
  *
  CASE
   WHEN
    COUNT( DISTINCT Таблица32.ПолеПодсчета3 ) = 0
   THEN
    1
  ELSE
   COUNT( DISTINCT Таблица32.ПолеПодсчета3 )
  END 
 ) + MAX( Таблица21.ПолеСуммирования2 ) AS НарастающийИтог2,
 Таблица31.ПолеПодсчета3,
 SUM( Таблица32.ПолеСуммирования3 )
 /
 (
  CASE
   WHEN
    COUNT( DISTINCT Таблица12.ПолеПодсчета1 ) = 0
   THEN
    1
  ELSE
   COUNT( DISTINCT Таблица12.ПолеПодсчета1 )
  END
  *
  CASE
   WHEN
    COUNT( DISTINCT Таблица22.ПолеПодсчета2 ) = 0
   THEN
    1
  ELSE
   COUNT( DISTINCT Таблица22.ПолеПодсчета2 )
  END
 ) + MAX( Таблица31.ПолеСуммирования3 ) AS НарастающийИтог3
FROM Таблица1 AS Таблица11
LEFT JOIN Таблица1 AS Таблица12
ON Таблица11.ПолеПодсчета1 > Таблица12.ПолеПодсчета1, 
Таблица2 AS Таблица21
LEFT JOIN Таблица2 AS Таблица22
ON Таблица21.ПолеПодсчета2 > Таблица22.ПолеПодсчета2,
Таблица3 AS Таблица31
LEFT JOIN Таблица3 AS Таблица32
ON Таблица31.ПолеПодсчета3 > Таблица32.ПолеПодсчета3
GROUP BY Таблица11.ПолеПодсчета1, Таблица21.ПолеПодсчета2, Таблица31.ПолеПодсчета3

В общем случае, когда число соединяемых таблиц в одном запросе равно N, в качестве делителя для сумм следует использовать произведение счетчиков для всех других таблиц. В общем случае множитель для сумм таблицы с номером «i» будет выражаться формулой:

 

С уважением,

Юрий Строжевский

 

 

См. также

Подписаться Добавить вознаграждение

Комментарии

1. Сергей Рудаков (fishca) 03.10.11 09:24
Было бы очень неплохо сравнить с функцией СКД, или это не одно и то же?

ВЫЧИСЛИТЬВЫРАЖЕНИЕ()

Синтаксис:

ВычислитьВыражение(<Выражение>, <Группировка>, <ОбластьВычисления>, <Начало>, <Конец>, <Сортировка>, <ИерархическаяСортировка>, <ОбработкаОдинаковыхЗначенийПорядка>)

Описание:

Функция предназначена для вычисления выражения в контексте некоторой группировки.
2. Александр Медведев (anig99) 03.10.11 09:55
(1) немного разные вещи. Тут всё-таки чистый запрос и ВычислитьВыражение недавно появилось.
3. Сергей Рудаков (fishca) 03.10.11 10:26
(2) Понятно что немного разные. Интересно скорость обработки будет та же или нет?
4. Александр Медведев (anig99) 03.10.11 10:45
(3) на маленьких объемах данных скорее всего выиграет запрос - нет потерь на передачах данных. В СКД ВычислитьВыражение реализовано не на уровне SQL, а на уровне платформы.
На больших объемах неоптимизированный запрос затухнет в силу много раз проговоренного лавинообразно растущего объема данных.
5. Юрий Строжевский (y-str) 03.10.11 11:18
(4) Любая оценка скорости/требовательности к ресурсам нуждается в подтверждении экспериментальными данными.
6. Александр Медведев (anig99) 03.10.11 14:33
(5) мы их как-то проводили, просто на форуме где-то потерялись. Буду переделывать свой отчет по дебиторке, выложу все 3 сравнения. Код на сервере, неоптимизированный запрос, оптимизированный запрос, запрос на СКД с функцией ВычислитьВыражение.
7. Юрий Строжевский (y-str) 03.10.11 14:47
(6) Отлично, будет здорово посмотреть результаты!
8. Alex Tim (Qseft) 05.10.11 16:08
Примерно когда ожидать результатов сравнения? Очень интересно.
Для написания сообщения необходимо авторизоваться
Прикрепить файл
Дополнительные параметры ответа