присоединиться только к одной строке из многих возможных строк

мне нужна помощь с запросом.

Подробности: у меня есть 5 миллионов событий с ID и EventID и одно время начала [datetime].
ID-EventID не являются уникальными ключами и могут происходить несколько раз в день.
Для каждой строки у меня может не быть совпадений, одно совпадение или даже 10 000 совпадений [дата и время] окончания в течение следующих 5 дней.

Мне нужно только одно время окончания, которое ближе всего к времени начала.

Сам запрос довольно прост, но из-за того, что у меня есть несколько миллионов событий, каждое из которых может иметь 10 000 обращений, когда требуется только одно обращение. Он достигает миллиардов и больше не работает должным образом.

Я написал набор образцов данных, включая образец результата. (см. ниже)

Мне нужен запрос, который включает только одну совпадающую строку и оставляет остальные в покое.

CREATE TABLE #starts(
    [id] [smallint] NULL,
    [event_id] [nvarchar](50) NULL,
    [dt_start] [datetime] NULL
)

INSERT #starts ([id], [event_id], [dt_start]) VALUES (2, N'alpha', CAST(N'2015-05-01 23:06:22.000' AS DateTime))
INSERT #starts ([id], [event_id], [dt_start]) VALUES (2, N'alpha', CAST(N'2015-05-10 23:42:01.000' AS DateTime))
INSERT #starts ([id], [event_id], [dt_start]) VALUES (2, N'alpha', CAST(N'2015-05-28 02:36:44.000' AS DateTime))
INSERT #starts ([id], [event_id], [dt_start]) VALUES (2, N'alpha', CAST(N'2015-05-29 08:56:17.000' AS DateTime))

CREATE TABLE #ends(
    [id] [smallint] NULL,
    [event_id] [nvarchar](50) NULL,
    [dt_end] [datetime] NULL
)


INSERT #ends ([id], [event_id], [dt_end]) VALUES (2, N'alpha', CAST(N'2015-05-01 23:09:32.000' AS DateTime))
INSERT #ends ([id], [event_id], [dt_end]) VALUES (2, N'alpha', CAST(N'2015-05-28 02:40:14.000' AS DateTime))
INSERT #ends ([id], [event_id], [dt_end]) VALUES (2, N'alpha', CAST(N'2015-05-28 08:57:39.000' AS DateTime))
INSERT #ends ([id], [event_id], [dt_end]) VALUES (2, N'alpha', CAST(N'2015-05-28 14:09:39.000' AS DateTime))
INSERT #ends ([id], [event_id], [dt_end]) VALUES (2, N'alpha', CAST(N'2015-06-01 10:18:18.000' AS DateTime))
INSERT #ends ([id], [event_id], [dt_end]) VALUES (2, N'alpha', CAST(N'2015-06-01 14:42:04.000' AS DateTime))
GO

-- one extra step to clarify
select a.id, a.event_id,dt_start, dt_end 
,row_number() over (partition by a.id, a.event_id,dt_start order by dt_end) as rn
from #starts as a
left join #ends as b
on a.id=b.id
and a.event_id=b.event_id
AND a.dt_start<b.dt_end
and datediff(day,dt_start,dt_end) <=5

-- the result
select * from (
select a.id, a.event_id,dt_start, dt_end 
,row_number() over (partition by a.id, a.event_id,dt_start order by dt_end) as rn
from #starts as a
left join #ends as b
on a.id=b.id
and a.event_id=b.event_id
AND a.dt_start<b.dt_end
and datediff(day,dt_start,dt_end) <=5
) as dummy
where rn=1

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


sql-server tsql sql-server-2012
person Metanormal    schedule 12.08.2015    source источник
comment
Есть ли шанс, что вы могли бы взять образец массивной таблицы?   -  person Tim Biegeleisen    schedule 12.08.2015
comment
Вы имеете в виду вставку с 5000 совпадающими временами окончания? Если да, то да, я мог бы сгенерировать такой образец, но чем он поможет и можно ли добавить сюда несколько тысяч строк sql-кода?   -  person Metanormal    schedule 12.08.2015
comment
Если вы выполните дополнительный шаг, вы увидите нумерацию совпадающих строк. В реальной таблице эти числа легко превышают тысячи для большинства событий, даже если они не нужны. Мне нужен способ присоединиться только к номеру №1 и удалить остальные с самого начала.   -  person Metanormal    schedule 12.08.2015

Ответы (1)


arrow_upward

arrow_downward

person    schedule
comment
Привет, Фрисби, большое спасибо. Это так очевидно. Я видимо застрял в своем алгоритме. Это работает очень хорошо. Но поскольку иногда в течение одного часа происходит несколько тысяч событий конечного времени, мне пришлось перебирать дату. Сначала поиск от 1 минуты, потом 30 минут, потом 5 дней. Тем не мение. Теперь он работает менее чем за 10 минут вместо нескольких часов. Спасибо еще раз. - person Metanormal; 13.08.2015