Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Помогите написать запрос  [new]
rsolanov
Member

Откуда:
Сообщений: 930
Добрый день. Прошу помочь с запросом.
На вход поступают следующие данные:
select
	t.Vol,
	t.ElementID
from
	(
	values
		(1	,3292	,'001577889'),
		(1	,3293	,'005031244'),
		(1	,57212	,'004804318'),
		(1	,80905	,'001577889'),
		(1	,80905	,'004804318'),
		(1	,80905	,'005031244'),
		(1	,82408	,'001577889'),
		(1	,105704	,'005031244'),
		(1	,206718 ,'004804318'),
		(1	,398372	,'001577889'),
		(1	,398372	,'004804318'),
		(1	,398372	,'005031244'),
		(2	,20447	,'000566243'),
		(2	,20447	,'002111002'),
		(2	,20447	,'002397247'),
		(2	,20447	,'003753732'),
		(2	,20447	,'006443651'),
		(2	,20447	,'007907370'),
		(2	,20447	,'007928600'),
		(2	,20448	,'007928600'),
		(2	,20449	,'002111002'),
		(2	,22288	,'007907370'),
		(2	,22314	,'000566243'),
		(2	,22337	,'006443651'),
		(2	,24766	,'002397247'),
		(2	,257655	,'002397247'),
		(2	,257656	,'002397247'),
		(2	,266075	,'000566243'),
		(2	,309693	,'007928600'),
		(2	,316800	,'007907370'),
		(2	,329002	,'002111002'),
		(2	,355772	,'006443651'),
		(2	,355773	,'006443651'),
		(2	,380194	,'003753732')
	) as t (GroupID, Vol, ElementID)
order by NEWID()

Для каждой строки этого набора данных необходимо определить принадлежность к группе следующим образом, покажу на готовом тестовом результате, на примере формирования первой группы: (на рисунке)
Сначала группируем все данные поля «Vol», рассмотрим значение «3292», это значение одно, поэтому обвел в синий прямоугольник. Для него значение поля «ElementID» равно «1577889», для нас это первый элемент группы (поле GroupID). Далее ищем вхождение этого значения для «ElementID» у других групп, это справа синими линиями. Это значение для поля «ElementID» повторяется для групп со значениями поля «Vol»: «82408» и «398372». Далее, для значения «398372» поля «Vol» получаем новые элементы «4804318» и «5031244», которые также добавляются в текущую группу (поле «GroupID»). Далее все по такому же принципу. Мне видится решение данного запроса в виде рекурсии, но возможно есть решение и без нее.

К сообщению приложен файл. Размер - 94Kb
18 окт 18, 16:29    [21707977]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
_human
Member

Откуда:
Сообщений: 556
rsolanov,

очень похоже на пазл
18 окт 18, 16:36    [21707988]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
PizzaPizza
Member

Откуда:
Сообщений: 309
rsolanov,

Попробую понять.

Есть уникальные elementID, есть уникальные vol.
Нужно для каждого elementID взять все уникальные vol и ... Дальше мысль теряется. Чего с ними делать. Почему groupid определено в таблице, но описано иначе.
18 окт 18, 19:44    [21708205]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
PizzaPizza
Есть уникальные elementID, есть уникальные vol.
Нужно для каждого elementID взять все уникальные vol и ... Дальше мысль теряется. Чего с ними делать. Почему groupid определено в таблице, но описано иначе.

ничего подобного там не написано.
а то, что написано, совпадает с нарисованным.
попробую переформулировать, а ТС пусть подтверждает/опровергает:
Vol это типа аэропорт, перевалочный пункт.
ElementID это типа рейсы.
берем первый попавшийся аэропорт, берем все его рейсы, ищем, в какие аэропорты они летают.
далее из найденных аэропортов ищем другие, в которые можно попасть из найденных на данном шаге.
итого в одной группе оказываются все аэропорты,
соединенные какими-то рейсами (хотя бы и с тучей пересадок)
18 окт 18, 20:34    [21708239]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20190
Короче,обычный лес - ну то есть куча деревьев в одном флаконе одной таблице.
Единственная трудность - не "помечены" корни - но их несложно найти по отсутствию обратной ссылки.
В общем, тривиальный такой рекурсивный WITH.
Одно неясно - а в какой форме требуется результат всего этого шаманства?
18 окт 18, 21:15    [21708251]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
PizzaPizza
Member

Откуда:
Сообщений: 309
Yasha123,

Это тогда какой то обход графа. Но группы groupid тут я не понял все равно: такое впечатление, что они условны и как раз требуется их переопределить.
18 окт 18, 21:19    [21708255]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
rsolanov
Member

Откуда:
Сообщений: 930
Yasha123
PizzaPizza
Есть уникальные elementID, есть уникальные vol.
Нужно для каждого elementID взять все уникальные vol и ... Дальше мысль теряется. Чего с ними делать. Почему groupid определено в таблице, но описано иначе.

ничего подобного там не написано.
а то, что написано, совпадает с нарисованным.
попробую переформулировать, а ТС пусть подтверждает/опровергает:
Vol это типа аэропорт, перевалочный пункт.
ElementID это типа рейсы.
берем первый попавшийся аэропорт, берем все его рейсы, ищем, в какие аэропорты они летают.
далее из найденных аэропортов ищем другие, в которые можно попасть из найденных на данном шаге.
итого в одной группе оказываются все аэропорты,
соединенные какими-то рейсами (хотя бы и с тучей пересадок)
Да, вы совершенно правильно меня поняли! )
19 окт 18, 10:40    [21708608]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
rsolanov
Member

Откуда:
Сообщений: 930
PizzaPizza
Yasha123,

Это тогда какой то обход графа. Но группы groupid тут я не понял все равно: такое впечатление, что они условны и как раз требуется их переопределить.
На входе у нас только два поля:
select
	t.Vol,
	t.ElementID
Соответственно необходимо найти их объединения по принципу, указанному в задаче. Результатом этого объединения является поле "GroupID" как указано в примере чтобы лучше понять что требуется.
19 окт 18, 10:43    [21708620]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
_human
Member

Откуда:
Сообщений: 556
rsolanov,

21707988 решает вашу задачу - см. Solution 4: Unfolding connected nodes with deletion of processed edges
чуть подпилить нужно будет
разница в том что он берет за № группьі найменьший елемент в ней.
во внешнем цикле прикрутить нумерацию начиная с 1. на вьіходе получите все id в одном поле и с № группьі в другом.
аутпут джоините с исходньім набором по любому из полей - Vol, ElementID
19 окт 18, 11:44    [21708804]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
982183
Member

Откуда: VL
Сообщений: 3350
Была подобная задача, по группировке пользователей по IP.
Надо поискать
19 окт 18, 12:09    [21708857]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
rsolanov
Member

Откуда:
Сообщений: 930
получилось найти частное решение:
;with dat as
(
	select
		t.ElementID,
		t.Vol
	from
		(
		values
			(1	,3292	,'001577889'), 
			(1	,3293	,'005031244'),
			(1	,57212	,'004804318'),
			(1	,80905	,'001577889'),
			(1	,80905	,'004804318'),
			(1	,80905	,'005031244'),
			(1	,82408	,'001577889'),
			(1	,105704	,'005031244'),
			(1	,206718 ,'004804318'),
			(1	,398372	,'001577889'),
			(1	,398372	,'004804318'),
			(1	,398372	,'005031244'),
			(2	,20447	,'000566243'),
			(2	,20447	,'002111002'),
			(2	,20447	,'002397247'),
			(2	,20447	,'003753732'),
			(2	,20447	,'006443651'),
			(2	,20447	,'007907370'),
			(2	,20447	,'007928600'),
			(2	,20448	,'007928600'),
			(2	,20449	,'002111002'),
			(2	,22288	,'007907370'),
			(2	,22314	,'000566243'),
			(2	,22337	,'006443651'),
			(2	,24766	,'002397247'),
			(2	,257655	,'002397247'),
			(2	,257656	,'002397247'),
			(2	,266075	,'000566243'),
			(2	,309693	,'007928600'),
			(2	,316800	,'007907370'),
			(2	,329002	,'002111002'),
			(2	,355772	,'006443651'),
			(2	,355773	,'006443651'),
			(2	,380194	,'003753732')
		) as t (GroupID, Vol, ElementID)
)
, t1 as
(
	select
		*
		, min(ElementID) over (partition by Vol) as gr1
	from
		dat
)
, t2 as
(
	select
		*
		, min(gr1) over (partition by ElementID) as gr2
	from
		t1
)
select
	ElementID
	, Vol
	, DENSE_RANK() over (ORDER by gr2) as GroupID
from
	t2
Но если данных и групп будет намного больше, то чтобы их найти придется делать несколько раз t1 и затем t2.
Если найти условие выхода из "цикла"(кол-ва повторений t1 и t2) возможно получится реализовать это решение в виде циклов while или через рекурсию
19 окт 18, 12:27    [21708905]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
rsolanov
Member

Откуда:
Сообщений: 930
Получилось найти общее решение:
drop table if exists #dat
create table #dat
(
	ElementID nvarchar(20)
	, Vol nvarchar(150)
	, GroupID nvarchar(20)
	, LoopID int
)

create nonclustered index ix_dat_ElementID on #dat (ElementID, Vol, GroupID, LoopID)
create nonclustered index ix_dat_LoopID on #dat (LoopID) include (ElementID, Vol, GroupID)

declare @i int = 1

;with dat as
(
	select
		t.ElementID,
		t.Vol
	from
		(
		values
			(1	,3292	,'001577889'), 
			(1	,3293	,'005031244'),
			(1	,57212	,'004804318'),
			(1	,80905	,'001577889'),
			(1	,80905	,'004804318'),
			(1	,80905	,'005031244'),
			(1	,82408	,'001577889'),
			(1	,105704	,'005031244'),
			(1	,206718 ,'004804318'),
			(1	,398372	,'001577889'),
			(1	,398372	,'004804318'),
			(1	,398372	,'005031244'),
			(2	,20447	,'000566243'),
			(2	,20447	,'002111002'),
			(2	,20447	,'002397247'),
			(2	,20447	,'003753732'),
			(2	,20447	,'006443651'),
			(2	,20447	,'007907370'),
			(2	,20447	,'007928600'),
			(2	,20448	,'007928600'),
			(2	,20449	,'002111002'),
			(2	,22288	,'007907370'),
			(2	,22314	,'000566243'),
			(2	,22337	,'006443651'),
			(2	,24766	,'002397247'),
			(2	,257655	,'002397247'),
			(2	,257656	,'002397247'),
			(2	,266075	,'000566243'),
			(2	,309693	,'007928600'),
			(2	,316800	,'007907370'),
			(2	,329002	,'002111002'),
			(2	,355772	,'006443651'),
			(2	,355773	,'006443651'),
			(2	,380194	,'003753732')
		) as t (GroupID, Vol, ElementID)
)
insert into #dat
select
	ElementID
	, Vol
	, max(gr1) over (partition by ElementID) as GroupID
	, @i as LoopID
from
	(
		select
			*
			, max(ElementID) over (partition by Vol) as gr1
		from
			dat
	) as t1

while exists(
		select
			ElementID
			, Vol
			, GroupID
		from
			#dat
		where
			LoopID = @i
		except
		select
			ElementID
			, Vol
			, GroupID
		from
			#dat
		where
			LoopID = @i - 1
)
begin
	set @i = @i + 1

	insert into #dat
	select
		ElementID
		, Vol
		, max(gr1) over (partition by GroupID) as GroupID
		, @i as LoopID
	from
		(
			select
				*
				, max(GroupID) over (partition by Vol) as gr1
			from
				#dat
			where
				LoopID = @i - 1
		) as t1
end

select distinct
	DENSE_RANK() over (ORDER BY GroupID) as GroupID
	, ElementID
	, Vol
from
	#dat
where
	@i = 2
При количестве элементов около 500к штук выделяет около 150к групп за 3 минуты. На мой взгляд вполне приемлемый результат, позже можно будет разобраться в 4-м варианте запроса по статье которую предложил _human.
22 окт 18, 12:43    [21710947]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
GG-gabbiano
Member

Откуда:
Сообщений: 3
Добрый день. Прошу помочь с запросом.


после вот такого запроса:

select vh.*
from blank_blaisd_isdv_pbig as v
join blank_blaisd_isdvh_pbig as vh on vh.isd_vh_bnr = v.isd_v_bnr
where substr(v.isd_v_ueberschrift,1,2) = 'ÄH'
and v.isd_v_cflag = 'N'
and v.isd_v_reisedat between curdate() and curdate() +interval 28 day
and v.isd_v_reisedat >= '2018-11-01'


получаю вот такую таблицу:


Картинка с другого сайта.

когда в столбике isd_vh_agent есть 'PK' то в столбике isd_vh_hotel следующая строка не должна содержать 'LXR%'


Картинка с другого сайта.
25 окт 18, 12:01    [21714664]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
GG-gabbiano
Member

Откуда:
Сообщений: 3
извините не смог вставить картинки поэтому пишу заного


Kartinka Nr1

Kartinka Nr2
25 окт 18, 12:12    [21714675]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
iap
Member

Откуда: Москва
Сообщений: 46953
GG-gabbiano
between curdate() and curdate() +interval 28 day
Вам в какой-то другой форум надо
25 окт 18, 14:35    [21714908]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
GG-gabbiano
Member

Откуда:
Сообщений: 3
iap,

можно и без этой строчки , но намека про другой форум не понял , простите
25 окт 18, 15:45    [21714986]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36691
Модератор: GG-gabbiano, это не намек. Зачем вы задаете вопрос в ветке по MSSQL (да еще и в чужоv топике), если у вас какая-то другая СУБД?
25 окт 18, 15:47    [21714993]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
iap
Member

Откуда: Москва
Сообщений: 46953
GG-gabbiano
iap,

можно и без этой строчки , но намека про другой форум не понял , простите
В T-SQL нет функции curdate() и нет синтаксиса interval 28 day.
Отсутствует так же функция substr().
Несмотря на это, - "получаю вот такую таблицу".
Значит сервер-то у вас не MS SQL
25 окт 18, 15:51    [21715003]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить