Эмуляция OLAP кубов. Hand Made. Или о том как на T-SQL реализовать опции <Все> и <ВошедшиеВЗапрос>

Начну с предыстории… Последнее время (и вообще в течение всего времени работы над библиотекой ToySQL) мне довольно часто задают вопрос как можно средствами ToySQL реализовать возможности предложения запроса 1С «Группировка». А именно опции «Все» и «ВсеВошедшиеВЗапрос».

До недавнего времени, а точнее до момента публикации этой статьи, мне приходилось отвечать, что к сожалению такой возможности в ToySQL не имеется и в ближайшее время не предвидится. А все потому, что не было времени серьезно подумать над этим вопросом.

Но к великом моему удовольствию решение пришло… Причем пришло довольно внезапно. По запросу одного из пользователей я решил таки выяснить эту проблему. Для начала я захотел посоветоваться со специалистами, для чего на форуме сайта http://ww.sql.ru разместил сообщение с описанием данного вопроса. К сожалению достойных ответов на вопрос не поступило, однако один ответ (точнее единственный) странным образом натолкнул меня на решение проблемы. Причем в самом ответе даже намека на решение не было. Просто в голове что-то щелкнуло, я крикнул про себя «Эврика!» и вот оно решение…

            Но не будем спешить. Сначала покопаемся в сути вопроса. Поставим, так сказать, задачу. Итак у нас имеется таблица фактов. Пусть это будут продажи по товарам и покупателям. Также у нас имеется справочник контрагентов и справочник товаров.

            Задача1 «Опция Все». Имея таблицу фактов получить следующую таблицу

Здесь N – количество контрагентов в справочнике контрагентов, M в справочнике товаров соответственно

В ячейках таблицы должны стоять суммы продаж по данному товару, по данному покупателю. Если данный товар покупатель не брал, сумма должна равняться 0.

Для решение задачки всего-то (оказывается) нужно хорошо знать теорию реляционных таблиц. Почему? Очень просто! – чтобы получить данную таблицу нам нужно получить множество размером N*M состоящее из всех комбинаций покупателей и товаров. Ну… догадались? Правильно! Для этого используется операция «декартово произведение». На языке SQL она выражается следующей конструкцией:

SELECT k.ID, t.ID FROM kontr k, tovar t

            После выполнения данного запроса мы имеем базовую таблицу, к которой затем можно присоединить таблицу фактов. Здесь используется тоже нетривиальное решение. Чтобы обойтись без подзапросов и получить выборку в одном запросе мы используем следующую конструкцию:

SELECT k.ID, t.ID, ISNULL(f.SUM,0) FROM kontr k, tovar t, fact f
   WHERE k.ID   *= f.k_id AND t.id *= f.t_id

Здесь операция сравнения «*=» аналогична конструкции LEFT JOIN. Однако с помощью операции JOIN в одном запросе сделать аналогичное не получится. Придется использовать подзапрос:

SELECT base.k_ID, base.t_ID, ISNULL(f.SUM,0) FROM (SELECT k.ID k_ID, t.ID  
  FROM t_IDkontr k, tovar t) base LEF JOIN fact f ON base.r_ID = f.k_id AND base.t_ID = f.t_d

Задач 2 «Опция ВсеВошедшиеВЗапрос». Требования аналогичны предыдущей,

кроме того, что количество измерений для базовой таблицы берется из таблицы фактов. Сразу приведу решение поскольку по-моему оно очевидно:

SELECT k.ID, t.ID, ISNULL(f.SUM,0) FROM 
  (SELECT DISTINCT k_ID ID FROM fact) k, 
  (SELECT DISTINCT t_ID ID FROM fact) t, 
  fact f 
  WHERE k.ID *= f.k_id AND t.id *= f.t_id

То есть мы выбираем из таблицы фактов нужные нам измерения, убирая с помощью опции DISTINCT дубликаты.

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

Задача 3 «Опция ВсеВошдшиеВЗапрос по одному из измерений». В 1С имеется возможность указывать данную опцию только для выбранных измерений. Используется это при количестве измерений больше двух. В этом случае пустыми записями дополняется только отмеченное данной опцией измерение. Например, в нашем случае мы можем использовать в качестве третьего измерения месяц продаж.

Для того, чтобы решить данную задачу, мы объединяем все измерения без опции в один подзапрос, а для всех измерений, которые отмечены данной опцией создаем отдельный подзапрос. Здесь я приведу уже пример на языке метазапросов ToySQL:

SELECT 
[Номенклатура] Номенклатура,[Покупатель] Покупатель,[ПериодДвижения] ПериодДвижения,
СуммаКол = [Сумма(Количество)],
СуммаПрод = [Сумма(ПродСтоимость)]
INTO #TempSelect
FROM [(Регистр.Продажи([@НачПериода()],[@КонПериода()],[@""Месяц""])
GROUP BY [Номенклатура],[Покупатель],[ПериодДвижения])]
ORDER BY [Номенклатура],[Покупатель],[ПериодДвижения]

SELECT 
[НомПок.Номенклатура],
[НомПок.Покупатель],
[Пер.ПериодДвижения],
(ISNULL([Факты.СуммаПрод],0)) СуммаПрод
FROM
(SELECT DISTINCT [Номенклатура],[Покупатель] FROM [ВремОбъекты.#TempSelect]) НомПок,
(SELECT DISTINCT [ПериодДвижения] FROM [ВремОбъекты.#TempSelect]) Пер,
[ВремОбъекты.#TempSelect] Факты
WHERE
[НомПок.Номенклатура] *= [Факты.Номенклатура] AND
[НомПок.Покупатель] *= [Факты.Покупатель] AND
[Пер.ПериодДвижения] *= [Факты.ПериодДвижения]
ORDER BY [НомПок.Номенклатура],[НомПок.Покупатель],[Пер.ПериодДвижения]

Сначала формируется по таблица фактов с помощью запроса по регистрам, затем по таблице фактов уже строится нужный нам запрос.

Для того чтобы запросы по созданию кубов работали максимально быстро необходим по крайней мере один индекс – по всем измерениям, которые будут участвовать в ORDER BY причем в том же порядке. Желательно (если максимально оптимизировать запрос) иметь индекс на каждое измерение, чтобы операция выборки значений измерения из таблицы фактов выполнялась эффективно.

Замечание. Приведенный выше запрос для ToySQL будет работать на версии 2.1.1.13 или выше.

Вот так. Как говорится, нет ничего невозможного в этом мире. Дерзайте!

 наверх