Поэтому, посмотрев на то, что мне задают часто задаваемый вопрос, и не имея возможности найти какое-либо решение, работающее для меня, я решил, что должен спросить себя.
У меня есть набор данных с двумя столбцами: session_start_time, uid
Я пытаюсь создать непрерывный 30-дневный подсчет уникальных сеансов.
Достаточно просто запросить количество уникальных uid в день:
SELECT
COUNT(DISTINCT(uid))
FROM segment_clean.users_sessions
WHERE session_start_time >= CURRENT_DATE - interval '30 days'
также относительно просто вычислить ежедневные уникальные идентификаторы uid за определенный период времени.
SELECT
DATE_TRUNC('day',session_start_time) AS "date"
,COUNT(DISTINCT uid) AS "count"
FROM segment_clean.users_sessions
WHERE session_start_time >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY date(session_start_time)
Затем я попробовал несколько способов сделать скользящий 30-дневный уникальный подсчет за определенный промежуток времени.
SELECT
DATE(session_start_time) AS "running30day"
,COUNT(distinct(
case when date(session_start_time) >= running30day - interval '30 days'
AND date(session_start_time) <= running30day
then uid
end)
) AS "unique_30day"
FROM segment_clean.users_sessions
WHERE session_start_time >= CURRENT_DATE - interval '3 months'
GROUP BY date(session_start_time)
Order BY running30day desc
Я действительно думал, что это сработает, но, глядя на результаты, кажется, что я получаю те же результаты, что и при выполнении ежедневного уникального, а не уникального в течение 30 дней.
Я пишу этот запрос из Metabase с помощью редактора SQL-запросов. нижележащие таблицы имеют красное смещение.
Если вы дочитали до этого места, спасибо, ваше время имеет ценность, и я ценю тот факт, что вы потратили часть его на то, чтобы прочитать мой вопрос.
РЕДАКТИРОВАТЬ: В соответствии с законной просьбой я добавил пример набора данных, с которым я работаю, и желаемого результата.
+-----+-------------------------------+
| UID | SESSION_START_TIME |
+-----+-------------------------------+
| | |
| 10 | 2020-01-13T01:46:07.000-05:00 |
| | |
| 5 | 2020-01-13T01:46:07.000-05:00 |
| | |
| 3 | 2020-01-18T02:49:23.000-05:00 |
| | |
| 9 | 2020-03-06T18:18:28.000-05:00 |
| | |
| 2 | 2020-03-06T18:18:28.000-05:00 |
| | |
| 8 | 2020-03-31T23:13:33.000-04:00 |
| | |
| 3 | 2020-08-28T18:23:15.000-04:00 |
| | |
| 2 | 2020-08-28T18:23:15.000-04:00 |
| | |
| 9 | 2020-08-28T18:23:15.000-04:00 |
| | |
| 3 | 2020-08-28T18:23:15.000-04:00 |
| | |
| 8 | 2020-09-15T16:40:29.000-04:00 |
| | |
| 3 | 2020-09-21T20:49:09.000-04:00 |
| | |
| 1 | 2020-11-05T21:31:48.000-05:00 |
| | |
| 6 | 2020-11-05T21:31:48.000-05:00 |
| | |
| 8 | 2020-12-12T04:42:00.000-05:00 |
| | |
| 8 | 2020-12-12T04:42:00.000-05:00 |
| | |
| 5 | 2020-12-12T04:42:00.000-05:00 |
+-----+-------------------------------+
Ниже показан результат, который я хотел бы получить:
+------------+---------------------+
| DATE | UNIQUE 30 DAY COUNT |
+------------+---------------------+
| | |
| 2020-01-13 | 3 |
| | |
| 2020-01-18 | 1 |
| | |
| 2020-03-06 | 3 |
| | |
| 2020-03-31 | 1 |
| | |
| 2020-08-28 | 4 |
| | |
| 2020-09-15 | 2 |
| | |
| 2020-09-21 | 1 |
| | |
| 2020-11-05 | 2 |
| | |
| 2020-12-12 | 2 |
+------------+---------------------+
Спасибо