Я использую Amazon Redshift
.
У меня есть список IP-адресов. Может быть много записей с одного и того же IP-адреса, но с другим session_id
в течение определенного временного окна (скажем, 15 минут для аргумента). Я хотел бы считать эти сеансы для любого заданного IP-адреса в течение этого временного окна.
Другими словами, я хотел бы знать, сколько сеансов было зарегистрировано с определенного IP-адреса в течение 15-минутного окна времени.
Поэтому я придумал следующий запрос:
SELECT t1.client_ip,
COUNT(DISTINCT t2.session_id) AS sessions
FROM t AS t1
JOIN t AS t2
ON t1.client_ip = t2.client_ip
AND t2.created_at BETWEEN t1.created_at
AND dateadd (MINUTE,15,t1.created_at)
GROUP BY t1.client_ip
HAVING COUNT(DISTINCT t2.session_id) >= 5
ORDER BY t1.client_ip
К сожалению, запрос занимает слишком много времени и также возвращает неверные результаты. Должен быть лучший способ добиться этого. В таблице содержится около 18 миллионов различных IP-адресов, а сама таблица содержит около 400 записей Million+.
Вот некоторые примеры данных:
Client_ip Session_id created_at
1.0.0.0 abc <timestamp>
1.0.0.0 def <timestamp> + 5 minutes
1.0.0.0 ghi <timestamp> + 25 minutes
2.0.0.0 jkl <timestamp1>
2.0.0.0 mno <timestamp1> + 10 minutes
2.0.0.0 pqr <timestamp1> + 20 minutes
Требуемый результат:
Client_ip #Sessions
1.0.0.0 2 (sessions abc and def)
2.0.0.0 2 (sessions mno and pqr)
Любая помощь будет принята с благодарностью.
РЕДАКТИРОВАТЬ:
Возможно, вопрос был не таким ясным. Я прошу прощения за то.
Я не хочу иметь установленное временное окно, где я могу создавать интервалы времени в 15 минут друг от друга. Я хотел бы подсчитать количество сеансов в ЛЮБОЙ 15-минутном окне с определенного IP-адреса.
Например: В данном образце я разместил, сеансы mno
и pqr
должны учитываться (их IP - адрес), потому что они находятся в пределах 15 минут друг от друга. Аналогично, сеансы abc
и def
должны также учитываться для их соответствующего IP-адреса, поскольку они попадают в течение 15 минут друг от друга. Я не создал для него время внешнего запуска. Запрос должен идеально сравнивать каждую запись с каждой другой записью с тем же IP-адресом. Не должно быть необходимости создавать внешнее время начала.
Здесь вывод explain <query>
XN Subquery Scan derived_table1 (cost=6516525010733.39..6516525010733.41 rows=2 width=524)
-> XN Merge (cost=6516525010733.39..6516525010733.39 rows=2 width=1032)
Merge Key: t1.client_ip
-> XN Network (cost=6516525010733.39..6516525010733.39 rows=2 width=1032)
Send to leader
-> XN Sort (cost=6516525010733.39..6516525010733.39 rows=2 width=1032)
Sort Key: t1.client_ip
-> XN HashAggregate (cost=5516525010733.36..5516525010733.38 rows=2 width=1032)
Filter: (count(DISTINCT session_id) >= 10)
-> XN Hash Join DS_DIST_BOTH (cost=6284418.61..5516506756947.79 rows=2433838076 width=1032)
Outer Dist Key: t2.client_ip
Inner Dist Key: t1.client_ip
Hash Cond: (("outer".client_ip)::text = ("inner".client_ip)::text)
Join Filter: (("inner".created_at <= "outer".created_at) AND ("outer".created_at <= date_add('minute'::text, 15::bigint, "inner".created_at)))
-> XN Seq Scan on fbs_page_view_staging t2 (cost=0.00..6279185.96 rows=2093062 width=1040)
Filter: ((created_at <= '2017-09-30 00:00:00'::timestamp without time zone) AND (created_at >= '2017-09-01 00:00:00'::timestamp without time zone))
-> XN Hash (cost=6279185.96..6279185.96 rows=2093062 width=524)
-> XN Seq Scan on fbs_page_view_staging t1 (cost=0.00..6279185.96 rows=2093062 width=524)
Filter: ((created_at <= '2017-09-30 00:00:00'::timestamp without time zone) AND (created_at >= '2017-09-01 00:00:00'::timestamp without time zone))
----- Tables missing statistics: fbs_page_view_staging -----
----- Update statistics by running the ANALYZE command on these tables -----
SELECT t1.client_ip, t1.WindowStart, COUNT(DISTINCT t1.session_id) AS sessions
FROM (
SELECT DISTINCT client_ip,
created_at as WindowStart,
DATEADD(MINUTE,15,created_at) as WindowEnd
FROM t
-- Add a where clause in here if you want to reduce the number of rows that you're working with
-- e.g. WHERE created_at BETWEEN 'some_arbitrary_date' AND 'another_arbitrary_date'
) t1
INNER JOIN t as t2 ON t1.client_ip = t2.client_ip
AND t2.created_at BETWEEN t1.WindowStart AND t1.WindowEnd
GROUP BY t1.client_ip, t1.WindowStart
Это то, что я имею в виду.
SELECT t1.client_ip, t1.session_id, COUNT(DISTINCT t2.session_id)
FROM ( SELECT client_ip, session_id, MIN(created_at) created_at
FROM fbs_page
GROUP BY client_ip, session_id) AS t1
INNER JOIN (SELECT client_ip, session_id, MIN(created_at) created_at
FROM fbs_page
GROUP BY client_ip, session_id) AS t2
ON t1.client_ip = t2.client_ip
AND t1.session_id != t2.session_id
AND t1.created_at
BETWEEN DATEADD(MINUTE,-15,t2.created_at) AND t2.created_at
GROUP BY t1.client_ip, t1.session_id
ORDER BY 1, 2;
После некоторого обсуждения я думаю, что это может быть близко к тому, что вам нужно. Вы можете добавить предложение where, чтобы фильтровать результаты по мере необходимости для более короткого диапазона дат или других вещей, чтобы заставить его работать быстрее.