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

Откуда:
Сообщений: 5
Есть две таблицы у которых id не пересекаются:
T1:   ID1  | value1    T2:   ID2  | value2
       1   |  20              a   |  19
       2   |  17              b   |  12
       3   |  10              c   |  14
                              d   |  8

Нужно к Т1 присоединить ID2 у которых минимальная разница между значениями ABS(value1-value2) причем только один ID2 который еще не джойнился, чтоб все присоединенные ID2 были уникальны.
В результате нужно получить такую таблицу:
ID1  | value1 | ID2
 1   |  20    |  a
 2   |  17    |  c
 3   |  10    |  b

Пока пришел к такому варианту:
select ID1, value1, ID2 from 
(
select ID1, value1, ID2, value2, row_number() over (partition by value1 order by diff asc) as lessdiff from 
(
select ID1, abs(value1 - value2) as  diff, ID2 , value1, value2 from  t1 join t2 on 1=1
) X1
) X2 where lessdiff = 1;

Проблема в том что ID2 повторяются, не знаю как пометить уже использовавшиеся ID2 чтоб их отфильтровать.
26 янв 17, 15:33    [20149811]     Ответить | Цитировать Сообщить модератору
 Re: join по ближайшему значению только уникальных записей  [new]
invm
Member

Откуда: Москва
Сообщений: 9824
declare @t1 table (id1 int, value1 int);
declare @t2 table (id2 varchar(10), value2 int);

insert into @t1
values
 (1, 20), (2, 17), (3, 10);

insert into @t2
values
 ('a', 19), ('b', 12), ('c', 14), ('d', 8);

with a as
(
 select id1, value1, row_number() over (order by id1) as rn from @t1
),
b as
(
 select
  t1.rn, t1.id1, t1.value1, t2.id2, cast(',' + t2.id2 + ',' as varchar(max))as Assigned_id2_list
 from
  a t1 cross apply
  (select top (1) id2 from @t2 order by abs(t1.value1 - value2)) t2
 where
  t1.rn = 1

 union all

 select
  t1.rn, t1.id1, t1.value1, t2.id2, b.Assigned_id2_list + t2.id2 + ','
 from
  b join
  a t1 on t1.rn = b.rn + 1 cross apply
  (select id2, row_number() over (order by abs(t1.value1 - value2)) as rn from @t2 where b.Assigned_id2_list not like '%,' + id2 + ',%') t2
 where
  t2.rn = 1
)
select id1, value1, id2 from b;


ИМХО курсор + временные таблицы тут будет эффективнее.
26 янв 17, 16:17    [20150085]     Ответить | Цитировать Сообщить модератору
 Re: join по ближайшему значению только уникальных записей  [new]
buven
Member

Откуда:
Сообщений: 792
А такого случая:
T1:   ID1  | value1    T2:   ID2  | value2
       1   |  20              a   |  19
       2   |  17              b   |  12
       3   |  10              c   |  14
                              d   |  8
                              e  |  21

как будет выглядеть результат?
что вы хотите видеть для T1.ID1=1? a|19 или e|21?
26 янв 17, 16:19    [20150098]     Ответить | Цитировать Сообщить модератору
 Re: join по ближайшему значению только уникальных записей  [new]
lacerated
Member

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

Без разницы любой из вариантов.
26 янв 17, 16:25    [20150145]     Ответить | Цитировать Сообщить модератору
 Re: join по ближайшему значению только уникальных записей  [new]
lacerated
Member

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

Спасибо большое!
Можно без курсоров обойтись? Пока небольшие таблицы на ms sql считаем, в дальнейшем планировл перенести подсчет в HIVE на кластере. А в HIVE курсоров нет (
26 янв 17, 16:27    [20150164]     Ответить | Цитировать Сообщить модератору
 Re: join по ближайшему значению только уникальных записей  [new]
invm
Member

Откуда: Москва
Сообщений: 9824
lacerated
Пока небольшие таблицы на ms sql считаем, в дальнейшем планировл перенести подсчет в HIVE на кластере
А рекурсивные CTE в этом HIVE есть?
26 янв 17, 16:31    [20150196]     Ответить | Цитировать Сообщить модератору
 Re: join по ближайшему значению только уникальных записей  [new]
lacerated
Member

Откуда:
Сообщений: 5
invm
lacerated
Пока небольшие таблицы на ms sql считаем, в дальнейшем планировл перенести подсчет в HIVE на кластере
А рекурсивные CTE в этом HIVE есть?

Тоже нет
https://cwiki.apache.org/confluence/display/Hive/Common Table Expression
Recursive Queries are not supported.
26 янв 17, 17:14    [20150410]     Ответить | Цитировать Сообщить модератору
 Re: join по ближайшему значению только уникальных записей  [new]
buven
Member

Откуда:
Сообщений: 792
lacerated
buven,

Без разницы любой из вариантов.

Интересно послушать условия задачи, в которой результат может быть неоднозначным и зависеть от настроения БД...
Вы же понимаете, что выборка никогда не будет одинаковой?
Если взять вырожденный случай - можно вообще случайное сочетание выводить. Или с другой стороны - всегда пустое множество.
26 янв 17, 18:09    [20150654]     Ответить | Цитировать Сообщить модератору
 Re: join по ближайшему значению только уникальных записей  [new]
lacerated
Member

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

Хорошо для постоянности выборки, если несколько ID2 находятся на одинаковом расстоянии от текущего значения ID1 то берем наименьший ID2
26 янв 17, 18:42    [20150803]     Ответить | Цитировать Сообщить модератору
 Re: join по ближайшему значению только уникальных записей  [new]
invm
Member

Откуда: Москва
Сообщений: 9824
lacerated
Тоже нет
Тогда зачем оценивать решения для MSSQL с точки зрения их переноса на HIVE?

+ Рекурсивное CTE vs курсор. Плюс оптимизация поиска
use tempdb;
set nocount on;
go

create table dbo.t1 (id1 int identity primary key, value1 int);
create table dbo.t2 (id2 int identity primary key, value2 int);
go

declare @c int = 1000;
insert into dbo.t1
select top (@c)
 rand(checksum(newid())) * 1000
from
 master.dbo.spt_values a cross join
 master.dbo.spt_values b;

insert into dbo.t2
select top (@c * 5)
 rand(checksum(newid())) * 1000
from
 master.dbo.spt_values a cross join
 master.dbo.spt_values b;

create index IX_t1__value1 on dbo.t1 (value1);
create index IX_t2__value2 on dbo.t2 (value2);

create table dbo.AssignedId2 (id2 int primary key);
go

create function dbo.fnGetNearestValue2Id_1
(
 @value1 int,
 @assignedids varchar(max)
)
returns table
as
return (
 select top (1)
  id2
 from
  (
   select top (1) id2, value2 from dbo.t2 where value2 >= @value1 and @assignedids not like '%,' + cast(id2 as varchar(10)) + ',%' order by value2
   union all
   select top (1) id2, value2 from dbo.t2 where value2 < @value1 and @assignedids not like '%,' + cast(id2 as varchar(10)) + ',%' order by value2 desc
  ) t
 order by
  abs(@value1 - value2), id2
)
go

create function dbo.fnGetNearestValue2Id_2
(
 @value1 int
)
returns table
as
return (
 select top (1)
  id2
 from
  (
   select top (1) id2, value2 from dbo.t2 where value2 >= @value1 and not exists(select 1 from dbo.AssignedId2 where id2 = t2.id2) order by value2
   union all
   select top (1) id2, value2 from dbo.t2 where value2 < @value1 and not exists(select 1 from dbo.AssignedId2 where id2 = t2.id2) order by value2 desc
  ) t
 order by
  abs(@value1 - value2), id2
)
go

create table #r ([description] varchar(100), elapsed_time_ms int);
go

declare @dt datetime = getdate(), @id1 int, @id2 int, @value1 int;
with a as
(
 select id1, value1, row_number() over (order by id1) as rn from dbo.t1
),
b as
(
 select
  t1.rn, t1.id1, t1.value1, t2.id2, cast(',' + cast(t2.id2 as varchar(10))+ ',' as varchar(max))as Assigned_id2_list
 from
  a t1 cross apply
  (select top (1) id2 from dbo.t2 order by abs(t1.value1 - value2)) t2
 where
  t1.rn = 1

 union all

 select
  t1.rn, t1.id1, t1.value1, t2.id2, b.Assigned_id2_list + cast(t2.id2 as varchar(10)) + ','
 from
  b join
  a t1 on t1.rn = b.rn + 1 cross apply
  dbo.fnGetNearestValue2Id_1(t1.value1, b.Assigned_id2_list) t2
)
select @id1 = id1, @value1 = value1, @id2 = id2 from b
option
 (maxrecursion 0);

insert into #r ([description], elapsed_time_ms) values ('Recursive CTE', datediff(ms, @dt, getdate()));
go

create table #t (id1 int, value1 int, id2 int);

declare t1 cursor local fast_forward for
 select id1, value1 from dbo.t1;

declare @dt datetime = getdate(), @id1 int, @value1 int, @id2 int;

open t1;
while 1 = 1
 begin
  fetch next from t1 into @id1, @value1;
  if @@fetch_status <> 0
   break;
  
  select @id2 = id2 from dbo.fnGetNearestValue2Id_2(@value1);
  insert into #t (id1, value1, id2) values (@id1, @value1, @id2);
  insert into dbo.AssignedId2 (id2) values (@id2);
 end;
close t1;
deallocate t1;

select @id1 = id1, @value1 = value1, @id2 = id2 from #t;
insert into #r ([description], elapsed_time_ms) values ('Cursor', datediff(ms, @dt, getdate()));
go

select * from #r;
go

drop function dbo.fnGetNearestValue2Id_1, dbo.fnGetNearestValue2Id_2;
drop table #r, #t, dbo.t1, dbo.t2, dbo.AssignedId2;
go

descriptionelapsed_time_ms
Recursive CTE956
Cursor403
26 янв 17, 23:24    [20151526]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить