30-дневный скользящий подсчет различных идентификаторов

Поэтому, посмотрев на то, что мне задают часто задаваемый вопрос, и не имея возможности найти какое-либо решение, работающее для меня, я решил, что должен спросить себя.

У меня есть набор данных с двумя столбцами: 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                   |
+------------+---------------------+

Спасибо


sql amazon-redshift metabase
person Tobiah Adam    schedule 11.08.2020    source источник
comment
не могли бы вы также добавить, как выглядят данные / таблица, чтобы помочь воспроизвести проблему?   -  person Smurphy0000    schedule 11.08.2020
comment
вы все еще используете CURRENT_DATE в вашем where: WHERE session_start_time ›= CURRENT_DATE - interval '3 month' Можем ли мы значение, которое вы передаете на running30day и interval   -  person GregM    schedule 11.08.2020
comment
Приведите пример данных: ввод и ожидаемый результат.   -  person diziaq    schedule 11.08.2020
comment
Спасибо, я вернулся и добавил пример набора данных, с которым я работаю, и пример результата, который я ищу,   -  person Tobiah Adam    schedule 11.08.2020

Ответы (2)


arrow_upward
0
arrow_downward

Вы можете приблизиться к этому, ведя счетчик того, когда пользователи были подсчитаны, а затем не подсчитаны - 30 (или, возможно, 31) день спустя. Затем определите подсчитываемые острова и суммируйте их. Это включает в себя:

  • Отмена поворота данных для подсчета входов и оставления для каждого сеанса.
  • Накапливайте подсчет, чтобы каждый день для каждого пользователя вы знали, подсчитываются они или нет.
  • Это определяет острова счета. Определите, где начинаются и заканчиваются острова - избавившись от всего промежуточного мусора.
  • Теперь вы можете просто подсчитать совокупную сумму на каждую дату, чтобы определить 30-дневную сессию.

В SQL это выглядит так:

with t as (
      select uid, date_trunc('day', session_start_time) as s_day, 1 as inc
      from users_sessions
      union all
      select uid, date_trunc('day', session_start_time) + interval '31 day' as s_day, -1
      from users_sessions
     ),
     tt as (  -- increment the ins and outs to determine whether a uid is in or out on a given day
      select uid, s_day, sum(inc) as day_inc,
             sum(sum(inc)) over (partition by uid order by s_day rows between unbounded preceding and current row) as running_inc
      from t
      group by uid, s_day
     ),
     ttt as (  -- find the beginning and end of the islands
      select tt.uid, tt.s_day,
             (case when running_inc > 0 then 1 else -1 end) as in_island
      from (select tt.*,
                   lag(running_inc) over (partition by uid order by s_day) as prev_running_inc,
                   lead(running_inc) over (partition by uid order by s_day) as next_running_inc
            from tt
           ) tt
      where running_inc > 0 and (prev_running_inc = 0 or prev_running_inc is null) or
            running_inc = 0 and (next_running_inc > 0 or next_running_inc is null)
     )
select s_day,
       sum(sum(in_island)) over (order by s_day rows between unbounded preceding and current row) as active_30
from ttt
group by s_day;

Вот скрипка db ‹›.

person Gordon Linoff    schedule 11.08.2020
comment
Привет, Гордон, спасибо за предложенный ответ! к сожалению, я получаю следующую ошибку: [Amazon] (500310) Недопустимая операция: для агрегированных оконных функций с предложением ORDER BY требуется предложение кадра - person Tobiah Adam; 11.08.2020
comment
@TobiahAdam. . . Это легко исправить. Если вы добавите скрипт db ‹› с помощью Postgres, вам будет легче увидеть эту работу. - person Gordon Linoff; 11.08.2020
comment
Приношу свои извинения, так как я новичок в SQL и на 100% самоучка благодаря таким форумам. Я не знаю, что такое скрипт db ‹› и как я его добавляю. - person Tobiah Adam; 11.08.2020
comment
Я заметил, что вы обновили свой ответ, спасибо! к сожалению, теперь я получаю следующую ошибку: «[Amazon] (500310) Недопустимая операция: столбец t.s_day должен появиться в предложении GROUP BY или использоваться в агрегатной функции» - person Tobiah Adam; 11.08.2020
comment
@TobiahAdam. . . Я исправил опечатки в коде и добавил скрипку db ‹›. Честно говоря, у вас было всего два столбца в таблице, поэтому я должен был создать один изначально, чтобы избежать опечаток. - person Gordon Linoff; 12.08.2020

arrow_upward
0
arrow_downward

Я уверен, что более простой способ сделать это - использовать соединение. Это создает список всех отдельных пользователей, у которых был сеанс каждый день, и список всех отдельных дат в данных. Затем он по принципу «один ко многим» присоединяет список пользователей к списку дат и подсчитывает отдельных пользователей. Ключевым моментом здесь является расширенный критерий соединения, который сопоставляет диапазон дат с одной датой с помощью системы неравенств.

with users as 
    (select
    distinct uid,
    date_trunc('day',session_start_time) AS dt
    from <table>
    where session_start_time >= '2021-05-01'),
dates as 
    (select
    distinct date_trunc('day',session_start_time) AS dt
    from <table>
    where session_start_time >= '2021-05-01')
select 
    count(distinct uid), 
    dates.dt 
    from users
join
    dates
    on users.dt >= dates.dt - 29
    and users.dt <= dates.dt
    group by dates.dt
    order by dt desc
;
person user3130288    schedule 13.07.2021