Блог

Специально ищу решения для SQL Server (или, в идеале, ANSI SQL). Я написал рекурсивное решение CTE для этого, но оно плохо работает удаленно для больших наборов данных.

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

Представьте, что у вас есть таблица фактов с двумя столбцами — person_id и event_time. Вы хотите разделить по идентификатору человека, а затем упорядочить по времени события в каждом разделе. Затем вы хотите начать с самого раннего времени и двигаться вперед, каждый раз выбрасывая что-либо в течение трех минут с самого раннего времени. Когда вы достигаете чего-то за пределами этого трехминутного окна, трехминутное окно сбрасывается само по себе.

Так, например:

 CREATE TABLE #res (person_id int, event_time datetime)
INSERT INTO #res VALUES
(2,'9/28/2021 10:00 am'),
(2,'9/28/2021 10:01 am'),
(2,'9/28/2021 10:02 am'),
(2,'9/28/2021 10:03 am'),
(2,'9/28/2021 10:05 am'),
(2,'9/28/2021 10:07 am'),
(2,'9/28/2021 10:08 am'),
(2,'9/28/2021 10:09 am'),
(2,'9/28/2021 10:45 am');
 

В этом примере мы начинаем с 10:00, но затем выбрасываем 10:01-10:03, потому что они все еще находятся в пределах трех минут. Затем мы сохраняем 10:05 и начинаем отсчет с этого момента. 10:07 и 10:08 находятся в пределах трех минут от 10:05, поэтому мы их выбрасываем. Затем мы сохраняем 10:09 и сохраняем 10:45. Мы остаемся с:

 (2,'9/28/2021 10:00 am'),
(2,'9/28/2021 10:05 am'),
(2,'9/28/2021 10:09 am'),
(2,'9/28/2021 10:45 am');
 

Я чувствую, что эта проблема принципиально требует некоторого уровня самореференции и, следовательно, требует рекурсивного CTE (или рекурсии в функции или процедуре), но я надеялся, что кто-нибудь сможет придумать другое решение, которого мне не хватает. Или даже просто советы по созданию чего-то подобного в таблице фактов приличного размера, содержащей от 100 до 1 млн строк.

Что я действительно хотел бы здесь сделать, так это перенести значение из строки в строку, на которое я могу ссылаться, а затем настроить — установить его как 10:00 в этом примере, а затем сбросить, как только мы перейдем к строке, которая находится за пределами 3-минутного окна (10:05). Я не думаю, что есть способ добиться такого рода самооценки за пределами тех вещей, о которых я упоминал в предыдущем абзаце, но мне бы очень хотелось ошибиться.