Материал подготовлен в рамках нового потока курса «SQL для разработчиков и аналитиков».
Недавно я увидел пост про набор запросов для собеседований, и все они были посвящены ответам задачи из бизнес-практики, решаемые через БД.
Сегодня этот пост уже скрыт за какой-то плашкой «подпишитесь, чтобы читать дальше», так что я даже не буду на него ссылаться, но один вопрос оттуда привлек мое внимание. Поскольку я не могу просто скопировать и вставить текст, воспроизведу его по памяти.
Дана таблица sessions с полями
user_id,login_timeиcountry_id. Нужно вывести все случаи, когда один и тот же аккаунт входил в систему более чем из одной страны в пределах двухчасового окна.
Идея в том, что это может быть инструментом для поиска потенциально взломанных аккаунтов: резкая смена страны за два часа выглядит подозрительно. Хотя в реальности такой сигнал не всегда надёжен — например, из-за использования VPN.
В решении из того поста предлагалось соединить таблицу sessions саму с собой, используя условие с неравенством. Мне кажется, можно сделать лучше…
Для начала, конечно, создадим таблицу и положим туда немного данных. Поскольку сейчас меня не волнует размер данных, сделаем все небольшим — всего с парой хорошо определенных случаев:
=$ create table sessions ( user_id int8, login_time timestamptz, country_id int8 );user_id == 3 CREATE TABLE
В реальной жизни здесь были бы индексы, внешние ключи и первичный ключ, но для этого примера мне это просто не настолько важно.
Пример данных. Мне понадобится как минимум три случая:
пользователь, который никогда не менял страну — это будет
user_id == 1пользователь, который сменил страну, но между входами прошло больше двух часов — это будет
user_id == 2пользователь, который сменил страну и сделал это в пределах двух часов. Пусть это будет
user_id == 3
Тогда данные для вставки будут такими:
=$ insert into sessions (user_id, login_time, country_id) values (1, '2025-05-01 12:34:56', 100), (1, '2025-05-01 13:34:56', 100), (1, '2025-05-01 19:34:56', 100), (2, '2025-05-01 12:34:56', 100), (2, '2025-05-01 19:34:56', 200), (3, '2025-05-01 12:34:56', 100), (3, '2025-05-01 13:34:56', 200); INSERT 0 7
Теперь базовый подход с джойном (join, соединение) выглядел бы примерно так:
=$ select s_start.user_id, s_start.login_time, array_agg(distinct s_end.country_id) as countries from sessions as s_start join sessions s_end on s_start.user_id = s_end.user_id and s_end.login_time between s_start.login_time and s_start.login_time + '2 hours'::interval group by s_start.user_id, s_start.login_time having count(distinct s_end.country_id) > 1; user_id | login_time | countries ---------+------------------------+----------- 3 | 2025-05-01 12:34:56+02 | {100,200} (1 row)
Выглядит довольно просто. Но можно ли сделать это за один проход по таблице sessions?
Думаю, да. С помощью оконных функций, а точнее — нестандартных определений фрейма…
В частности, в определении окна можно использовать что-то вроде такого: order by login_time range between current row and '2 hours'::interval following. Это задает рамку, которая учитывает все строки, у которых login_time находится между временем текущей строки и моментом на два часа позже. Вот как это работает:
=$ SELECT s.*, array_agg(login_time) over (partition by user_id order by login_time range between current row and '2 hours'::interval following) FROM sessions s; user_id | login_time | country_id | array_agg ---------+------------------------+------------+----------------------------------------------------- 1 | 2025-05-01 12:34:56+02 | 100 | {"2025-05-01 12:34:56+02","2025-05-01 13:34:56+02"} 1 | 2025-05-01 13:34:56+02 | 100 | {"2025-05-01 13:34:56+02"} 1 | 2025-05-01 19:34:56+02 | 100 | {"2025-05-01 19:34:56+02"} 2 | 2025-05-01 12:34:56+02 | 100 | {"2025-05-01 12:34:56+02"} 2 | 2025-05-01 19:34:56+02 | 200 | {"2025-05-01 19:34:56+02"} 3 | 2025-05-01 12:34:56+02 | 100 | {"2025-05-01 12:34:56+02","2025-05-01 13:34:56+02"} 3 | 2025-05-01 13:34:56+02 | 200 | {"2025-05-01 13:34:56+02"} (7 rows)
Часть partition by user_id делает так, что для каждой строки учитываются только другие строки с тем же user_id.
В любом случае, имея это, мы можем изменить запрос так, чтобы он действительно показывал идентификаторы стран, а затем отфильтровать результаты так, чтобы остались только случаи с несколькими странами:
=$ SELECT s.user_id, s.login_time, array_agg(distinct s.country_id) over (partition by user_id order by login_time range between current row and '2 hours'::interval following) as countries FROM sessions s; ERROR: DISTINCT is not implemented for window functions LINE 4: array_agg(distinct country_id) over (partition by user_i... ^
Это немного ломает мой план. Но ничего, давайте сделаем это через array_agg, без distinct, а убрать дубликаты сможем позже:
=$ SELECT s.user_id, s.login_time, array_agg(s.country_id) over (partition by user_id order by login_time range between current row and '2 hours'::interval following) as countries FROM sessions s; user_id | login_time | countries ---------+------------------------+----------- 1 | 2025-05-01 12:34:56+02 | {100,100} 1 | 2025-05-01 13:34:56+02 | {100} 1 | 2025-05-01 19:34:56+02 | {100} 2 | 2025-05-01 12:34:56+02 | {100} 2 | 2025-05-01 19:34:56+02 | {200} 3 | 2025-05-01 12:34:56+02 | {100,200} 3 | 2025-05-01 13:34:56+02 | {200} (7 rows)
Сработало именно так, как и ожидалось, так что теперь давайте избавимся от повторяющихся стран. Сравнительно простой способ, по крайней мере для меня, — написать написать пользовательскую агрегатную функцию. Я понимаю, что это звучит страшновато, но на деле нужно всего лишь написать очень простую функцию и один оператор create aggregate:
=$ create function array_agg_unique( INOUT p_state int8[], IN p_newval int8 ) returns int8[] as $$ select case when p_newval = any(p_state) then p_state else p_state || p_newval end; $$ language sql; CREATE FUNCTION =$ create aggregate array_agg_unique( int8 ) ( sfunc = array_agg_unique, stype = int8[], initcond = '{}' ); CREATE AGGREGATE
Вот и всё. Теперь посмотрим, как работает новый агрегат с уникальными значениями:
=$ SELECT s.user_id, s.login_time, array_agg_unique(s.country_id) over (partition by user_id order by login_time range between current row and '2 hours'::interval following) as countries FROM sessions s; user_id | login_time | countries ---------+------------------------+----------- 1 | 2025-05-01 12:34:56+02 | {100} 1 | 2025-05-01 13:34:56+02 | {100} 1 | 2025-05-01 19:34:56+02 | {100} 2 | 2025-05-01 12:34:56+02 | {100} 2 | 2025-05-01 19:34:56+02 | {200} 3 | 2025-05-01 12:34:56+02 | {100,200} 3 | 2025-05-01 13:34:56+02 | {200} (7 rows)
А теперь фильтрация. Самый простой способ, на мой взгляд, — просто обернуть это в общее табличное выражение (CTE):
WITH base as ( SELECT s.user_id, s.login_time, array_agg_unique(s.country_id) over (partition by user_id order by login_time range between current row and '2 hours'::interval following) as countries FROM sessions s ) select * from base where array_upper(countries, 1) > 1; user_id | login_time | countries ---------+------------------------+----------- 3 | 2025-05-01 12:34:56+02 | {100,200} (1 row)
Теперь вопрос в другом: действительно ли это быстрее, чем подход с join?
Для начала давайте посмотрим на обычный explain analyze для обоих запросов. Сначала вариант с join:
=$ explain analyze select s_start.user_id, s_start.login_time, array_agg(distinct s_end.country_id) as countries from sessions as s_start join sessions s_end on s_start.user_id = s_end.user_id and s_end.login_time between s_start.login_time and s_start.login_time + '2 hours'::interval group by s_start.user_id, s_start.login_time having count(distinct s_end.country_id) > 1; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------- GroupAggregate (cost=194.32..209.42 rows=183 width=48) (actual time=0.147..0.163 rows=1.00 loops=1) Group Key: s_start.user_id, s_start.login_time Filter: (count(DISTINCT s_end.country_id) > 1) Rows Removed by Filter: 6 Buffers: shared hit=8 -> Sort (cost=194.32..195.69 rows=549 width=24) (actual time=0.120..0.139 rows=9.00 loops=1) Sort Key: s_start.user_id, s_start.login_time, s_end.country_id Sort Method: quicksort Memory: 25kB Buffers: shared hit=8 -> Hash Join (cost=45.33..169.34 rows=549 width=24) (actual time=0.039..0.074 rows=9.00 loops=1) Hash Cond: (s_start.user_id = s_end.user_id) Join Filter: ((s_end.login_time >= s_start.login_time) AND (s_end.login_time <= (s_start.login_time + '02:00:00'::interval))) Rows Removed by Join Filter: 8 Buffers: shared hit=2 -> Seq Scan on sessions s_start (cost=0.00..25.70 rows=1570 width=16) (actual time=0.004..0.013 rows=7.00 loops=1) Buffers: shared hit=1 -> Hash (cost=25.70..25.70 rows=1570 width=24) (actual time=0.026..0.030 rows=7.00 loops=1) Buckets: 2048 Batches: 1 Memory Usage: 17kB Buffers: shared hit=1 -> Seq Scan on sessions s_end (cost=0.00..25.70 rows=1570 width=24) (actual time=0.004..0.013 rows=7.00 loops=1) Buffers: shared hit=1 Planning: Buffers: shared hit=156 Planning Time: 0.435 ms Execution Time: 0.243 ms (25 rows)
Итак. Здесь было выполнено два отдельных последовательных сканирования (Seq Scan) таблицы sessions — на строках 31 и 36. Думаю, если бы данных было больше, одно из них превратилось бы в набор сканирований по индексу (Index Scan). Сейчас это и проверим.
С другой стороны, мой подход с оконной функцией, фреймом и агрегатной функцией даёт такой explain analyze:
=$ explain analyze WITH base as ( SELECT s.user_id, s.login_time, array_agg_unique(s.country_id) over (partition by user_id order by login_time range between current row and '2 hours'::interval following) as countries FROM sessions s ) select * from base where array_upper(countries, 1) > 1; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------- Subquery Scan on base (cost=109.08..163.99 rows=523 width=48) (actual time=0.279..0.296 rows=1.00 loops=1) Filter: (array_upper(base.countries, 1) > 1) Rows Removed by Filter: 6 Buffers: shared hit=50 -> WindowAgg (cost=109.08..140.44 rows=1570 width=48) (actual time=0.221..0.283 rows=7.00 loops=1) Window: w1 AS (PARTITION BY s.user_id ORDER BY s.login_time RANGE BETWEEN CURRENT ROW AND '02:00:00'::interval FOLLOWING) Storage: Memory Maximum Storage: 17kB Buffers: shared hit=50 -> Sort (cost=109.04..112.96 rows=1570 width=24) (actual time=0.044..0.056 rows=7.00 loops=1) Sort Key: s.user_id, s.login_time Sort Method: quicksort Memory: 25kB Buffers: shared hit=4 -> Seq Scan on sessions s (cost=0.00..25.70 rows=1570 width=24) (actual time=0.006..0.015 rows=7.00 loops=1) Buffers: shared hit=1 Planning: Buffers: shared hit=63 dirtied=1 Planning Time: 0.279 ms Execution Time: 0.385 ms (18 rows)
Одно последовательное сканирование и немного дополнительной обработки.
Отлично. А теперь давайте добавим побольше данных. Скажем, 100 000 строк со случайными пользователями, временем входа и странами:
=$ insert into sessions (user_id, login_time, country_id) select floor( 1 + random() * 50 ), now() - '3 months'::interval * random(), floor( 1 + random() * random() * random() * random() * random() * 5 ) from generate_series(1,100000); INSERT 0 100000
Выражение с несколькими random() для страны просто сделало распределение country_id гораздо менее равномерным:
=$ select country_id, count(*) from sessions group by 1 order by 1; country_id | count ------------+------- 1 | 97623 2 | 2119 3 | 235 4 | 23 100 | 5 200 | 2 (6 rows)
Чтобы всё было как следует проиндексировано, я добавлю индекс по (user_id, login_time):
=$ create index the_index on sessions (user_id, login_time); CREATE INDEX
Отлично. А теперь момент истины: я запущу оба запроса — вариант с join и вариант с окном — по три раза каждый, возьму самый быстрый результат для каждого типа и посмотрю, как они себя ведут.
Самый быстрый запуск варианта с JOIN:
QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- GroupAggregate (cost=17.24..2143404.69 rows=33298 width=48) (actual time=154.160..3097.721 rows=6316.00 loops=1) Group Key: s_start.user_id, s_start.login_time Filter: (count(DISTINCT s_end.country_id) > 1) Rows Removed by Filter: 93690 Buffers: shared hit=585882 -> Incremental Sort (cost=17.24..1919272.77 rows=22263351 width=24) (actual time=151.612..2689.938 rows=285069.00 loops=1) Sort Key: s_start.user_id, s_start.login_time, s_end.country_id Presorted Key: s_start.user_id, s_start.login_time Full-sort Groups: 8578 Sort Method: quicksort Average Memory: 26kB Peak Memory: 26kB Buffers: shared hit=585882 -> Nested Loop (cost=0.84..770705.97 rows=22263351 width=24) (actual time=151.285..1929.886 rows=285069.00 loops=1) Buffers: shared hit=585882 -> Index Only Scan using the_index on sessions s_start (cost=0.42..3052.52 rows=100007 width=16) (actual time=0.011..130.035 rows=100007.00 loops=1) Heap Fetches: 0 Index Searches: 1 Buffers: shared hit=387 -> Index Scan using the_index on sessions s_end (cost=0.42..5.46 rows=222 width=24) (actual time=0.003..0.007 rows=2.85 loops=100007) Index Cond: ((user_id = s_start.user_id) AND (login_time >= s_start.login_time) AND (login_time <= (s_start.login_time + '02:00:00'::interval))) Index Searches: 100007 Buffers: shared hit=585495 Planning Time: 0.145 ms JIT: Functions: 13 Options: Inlining true, Optimization true, Expressions true, Deforming true Timing: Generation 0.490 ms (Deform 0.123 ms), Inlining 8.884 ms, Optimization 123.118 ms, Emission 19.230 ms, Total 151.722 ms Execution Time: 3106.287 ms (26 rows)
А вот самый быстрый запуск запроса с пользовательским агрегатом и подходом на основе окна:
QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------- Subquery Scan on base (cost=49.51..8850.37 rows=33336 width=48) (actual time=0.890..957.652 rows=6316.00 loops=1) Filter: (array_upper(base.countries, 1) > 1) Rows Removed by Filter: 93691 Buffers: shared hit=100241 -> WindowAgg (cost=49.51..7350.26 rows=100007 width=48) (actual time=0.034..816.409 rows=100007.00 loops=1) Window: w1 AS (PARTITION BY s.user_id ORDER BY s.login_time RANGE BETWEEN CURRENT ROW AND '02:00:00'::interval FOLLOWING) Storage: Memory Maximum Storage: 17kB Buffers: shared hit=100241 -> Index Scan using the_index on sessions s (cost=0.42..5600.14 rows=100007 width=24) (actual time=0.011..169.658 rows=100007.00 loops=1) Index Searches: 1 Buffers: shared hit=100241 Planning Time: 0.068 ms Execution Time: 965.772 ms (13 rows)
Отлично. Похоже, этот вариант примерно в три раза быстрее.
Надеюсь, кому-нибудь это пригодится на каком-нибудь собеседовании. Или хотя бы покажет, что получить ответ на задачу можно разными способами…
Если вам интересен сам подход к работе с данными, обратите внимание на курс «SQL для разработчиков и аналитиков». На нём разбирают не только синтаксис запросов, но и оконные функции, соединения, индексы, оптимизацию и особенности работы разных СУБД. Чтобы узнать, подойдет ли вам программа курса, пройдите короткий тест.
Для знакомства с форматом обучения и экспертами приходите на бесплатные уроки:
31 марта в 20:00. «SQL: Оконные функции — когда GROUP BY уже не хватает». Записаться
21 апреля в 20:00. «Функции в SQL: вычисления и переиспользование кода». Записаться
