Забава или рутина?
Построение всех возможных комбинаций(удовлетворяющих набору условий) это задача высосана из пальца или рутина? Давайте представим, что вы работаете в компании, которая предоставляет какие-то услуги своим клиентам, как правило компании продает не одну услугу, а целый набор. Например, покупая летом путевку в жаркую страну, вы наверняка приобретете следующее
- Авиабилет в страну отдых, то есть билет эконом-класса на %airlines%.
- Трансфер до отеля, то есть поездка на большом автобусе с еще 50 туристами.
- Проживание в отеле, то есть 14 ночей в %hotel% в номере с видом на сад.
- Трансфер до аэропорта, то есть еще одна поездка в автобусе.
- Авиабилет домой, возвращение обратным рейсом той же %airlines%.
Опишем эту же задачу с помощью кода.
--Классы услуг CREATE TABLE [Classes] ( [id] [int] NOT NULL PRIMARY KEY ,[name] varchar(50) NOT NULL ); GO INSERT INTO [Classes] VALUES (1,'Авиаперелет'); INSERT INTO [Classes] VALUES (2,'Трансфер'); INSERT INTO [Classes] VALUES (3,'Проживание'); --Список туров CREATE TABLE [Tours]( [id] [int] NOT NULL PRIMARY KEY ,[name] varchar(50) NOT NULL ); GO INSERT INTO [Tours] VALUES (1, 'Тур'); --Тур состоит набора услуг --В данном случае из тех же 5, что приведены в примере CREATE TABLE [TourDetails] ( [id] [int] NOT NULL PRIMARY KEY ,[tour] [int] NOT NULL ,[class] [int] NOT NULL ); GO INSERT INTO [TourDetails] VALUES (1,1,1) INSERT INTO [TourDetails] VALUES (2,1,2) INSERT INTO [TourDetails] VALUES (3,1,3) INSERT INTO [TourDetails] VALUES (4,1,2) INSERT INTO [TourDetails] VALUES (5,1,1) -- Справочник услуг. --Рассмотрим простую систему, где все услуги хранятся в одной табличке CREATE TABLE [Services]( [id] [int] NOT NULL PRIMARY KEY ,[name] varchar(50) ); GO INSERT INTO [Services] VALUES (1, 'Эконом класс'); INSERT INTO [Services] VALUES (2, 'Бизнес класс'); INSERT INTO [Services] VALUES (3, 'Групповой трансфер'); INSERT INTO [Services] VALUES (4, 'Номер с видом на бассейн'); --И последнее --Привязка услуги к классу услуг. CREATE TABLE [ServiceClasses]( [id] [int] NOT NULL PRIMARY KEY ,[class] [int] NOT NULL ,[service] [int] NOT NULL ); GO INSERT INTO [ServiceClasses] VALUES (1, 1, 1); INSERT INTO [ServiceClasses] VALUES (2, 1, 2); INSERT INTO [ServiceClasses] VALUES (3, 2, 3); INSERT INTO [ServiceClasses] VALUES (4, 3, 4);
А получить мы хотим все перестановки для данного тура(туров), то есть
id тура id комбинации id деталей тура id услуги ----------- ------------- --------------- ----------- 1 1 1 1 1 1 2 3 1 1 3 4 1 1 4 3 1 1 5 1 ----------------------------------------------------- 1 2 1 2 1 2 2 3 1 2 3 4 1 2 4 3 1 2 5 1 ----------------------------------------------------- 1 3 1 1 1 3 2 3 1 3 3 4 1 3 4 3 1 3 5 2 ----------------------------------------------------- 1 4 1 2 1 4 2 3 1 4 3 4 1 4 4 3 1 4 5 2
Посмотрим на результаты чуть внимательней. В первую колонку попадает id Тура([Tours].[id]), так как он у нас всего один, то значение всегда равно 1. Во второй колонке id комбинации, для каждой группы данных/набора оно свое. В третей колонке хранятся идентификаторы деталей тура, то есть [TourDetails].[id]. И в последней идентификаторы услуг. Таким образом, в первой комбинации оба перелета эконом класса, во второй и третей по одному перелету эконом класса, а в последней и туда и обратно турист полетит бизнес классом.
А теперь самое интересное, как сделать это с помощью T-SQL? Ответ и немного кода можно найти под катом.
Идея
Метод опирается на следующую идею, записанную с помощью утверждений.
- Все услуги(детали тура) упорядочиваются.
- Делим общее количество комбинаций на количество возможных вариантов для первой услуги и присваиваем соответствующие значения.
- Далее повторяем эту операцию для полученных отрезков и следующей услуги(детелей тура).
- Повторяем предыдущий шаг для всех услуг.
Решение в деталях.
Давайте посмотрим какие характеристики мы можем получить. Во первых мы можем узнать сколько есть вариантов для каждой услуги в туре. Исходя из имеющихся данных, есть два варианта для услуги авиаперелет и по одному для всех остальных.
select [Tours].[id] [tour] ,[TourDetails].[id] [details] ,count(*) [count] from [Tours] inner join [TourDetails] on [Tours].[id] = [TourDetails].[tour] inner join [Classes] on [TourDetails].[class] = [Classes].[id] inner join [ServiceClasses] on [Classes].[id] = [ServiceClasses].[class] group by [Tours].[id] ,[TourDetails].[id] ------------------------------------- -- tour details count -- ----------- ----------- ----------- -- 1 1 2 -- 1 2 1 -- 1 3 1 -- 1 4 1 -- 1 5 2
Имея эти данные, мы можем вычислить количество комбинаций для каждого тура. Чтобы сделать это, нам нужно перемножить количество вариантов для каждой услуги. Для этого используем прием, описанный здесь.
;with [Counts] as ( select [Tours].[id] [tour] ,[TourDetails].[id] [details] ,count(*) [count] from [Tours] inner join [TourDetails] on [Tours].[id] = [TourDetails].[tour] inner join [Classes] on [TourDetails].[class] = [Classes].[id] inner join [ServiceClasses] on [Classes].[id] = [ServiceClasses].[class] group by [Tours].[id] ,[TourDetails].[id] ) select [Counts].[tour] ,CAST(ROUND(EXP(SUM(LOG([Counts].[count]))),0) as int) [CombinationCount] from [Counts] group by [Counts].[tour] ------------------------------- -- tour CombinationCount -- ----------- ---------------- -- 1 4 -- 2*1*1*1*2 = 4
Теперь для каждой услуги нужно вычислить количество отрезков, с которым придется работать. Для первой услуги/детали тура это количество равно количеству вариантов для этой услуги. Чтобы получить результат для следующей услуги нужно предыдущий результат домножить на количество вариантов для соответствующей услуги. И так далее. А с помощью кода это можно записать так.
;with [Counts] as (
select
[Tours].[id] [tour]
,[TourDetails].[id] [details]
,count(*) [count]
from [Tours]
inner join [TourDetails]
on [Tours].[id] = [TourDetails].[tour]
inner join [Classes]
on [TourDetails].[class] = [Classes].[id]
inner join [ServiceClasses]
on [Classes].[id] = [ServiceClasses].[class]
group by
[Tours].[id]
,[TourDetails].[id]
)
,[AmountOfSegment] as (
select
[c1].[tour]
,[c1].[details]
,[c1].[count]
,CAST(ROUND(exp(SUM(LOG ([c2].[count]))),0) as int) [amount]
from [Counts] [c1]
inner join [Counts] [c2]
on [c1].[tour] = [c2].[tour]
and [c1].[details] >= [c2].[details]
group by
[c1].[tour]
,[c1].[details]
,[c1].[count]
)
select * from [AmountOfSegment]
--------------------------------------------------
-- tour details count amount
-- ----------- ----------- ----------- -----------
-- 1 1 2 2
-- 1 2 1 2
-- 1 3 1 2
-- 1 4 1 2
-- 1 5 2 4
Осталось только упорядочить услуги и присвоить каждой порядковый номер. И сформировать итоговый запрос.
;with [Counts] as ( select [Tours].[id] [tour] ,[TourDetails].[id] [details] ,count(*) [count] from [Tours] inner join [TourDetails] on [Tours].[id] = [TourDetails].[tour] inner join [Classes] on [TourDetails].[class] = [Classes].[id] inner join [ServiceClasses] on [Classes].[id] = [ServiceClasses].[class] group by [Tours].[id] ,[TourDetails].[id] ) , [CombinationCounts] as ( select [Counts].[tour] ,CAST(ROUND(EXP(SUM(LOG([Counts].[count]))),0) as int) [combinationCount] from [Counts] group by [Counts].[tour] ) ,[AmountOfSegment] as ( select [c1].[tour] ,[c1].[details] ,[c1].[count] ,CAST(ROUND(exp(SUM(LOG ([c2].[count]))),0) as int) [amount] from [Counts] [c1] inner join [Counts] [c2] on [c1].[tour] = [c2].[tour] and [c1].[details] >= [c2].[details] group by [c1].[tour] ,[c1].[details] ,[c1].[count] ) ,[OrderedService] as ( select [Tours].[id] [tour] ,[TourDetails].[id] [details] ,[Services].* ,ROW_NUMBER() OVER(PARTITION BY [Tours].[id],[TourDetails].[id] ORDER BY [Services].[id])-1 [order] from [Tours] inner join [TourDetails] on [Tours].[id] = [TourDetails].[tour] inner join [ServiceClasses] on [TourDetails].[class] = [ServiceClasses].[class] inner join [Services] on [Services].[id] = [ServiceClasses].[service] ) select [TourDetails].[tour] ,[Numbers].[number] ,[TourDetails].[id] ,[OrderedService].[id] from [master].[dbo].[spt_values] [Numbers]-- Можно использовать эту таблицу, или свою inner join [CombinationCounts] on [combinationCount] > [number] inner join [TourDetails] on [CombinationCounts].[tour] = [TourDetails].[tour] inner join [AmountOfSegment] on [AmountOfSegment].[tour] = [CombinationCounts].[tour] and [AmountOfSegment].[details] = [TourDetails].[id] inner join [OrderedService] [OrderedService] on [OrderedService].[tour] = [CombinationCounts].[tour] and [OrderedService].[details] = [TourDetails].[id] and (CEILING(([number]*1.0)/([CombinationCounts].[combinationCount]/[AmountOfSegment].[amount])) % [AmountOfSegment].[count]) = [order] where [type] = 'P' and [number] >= 0 order by [number] ,[TourDetails].[id] ,[OrderedService].[id]
Обратите внимание на выделенные строку. Первая выделенная строка отмечает ссылку на таблицу [master].[dbo].[spt_values], если нет доступа к базе [master], то можно создать свою таблицу с подряд идущими данными или генерировать ее на лету, используя CTE. Вторая группа строк выделяет выражение, с помощью которого подбирается подходящая услуга.
Вместо вывода
Наверняка многие решают подобную задачу разворачиванием столбца с деталями тура([TourDetails]) в строку и использованием большого количества inner join, но такой подход имеет один из двух недостатков
- Жестко привязывает нас к структуре тура - набору из [TourDetails].
- Дает некоторую свободу, но при этом добавляет массу ненужных left outer join.
Приведенное выше решение охватывает широкий класс задач, что заставляет возвращаться к этой идеи снова и снова. Но нужно понимать, что при по-настоящему больших объемах данных будет затрачено много ресурсов, поэтому его нужно использовать с осторожностью.
Комментариев нет:
Отправить комментарий