как найти количество активных пользователей, скажем, за 1 день, 2 дня, 3 дня .....PostgreSQL

#sql #postgresql #windowing #partition-by

#sql #postgresql #оконный #раздел по

Вопрос:

Распределение активных дней в течение недели: я пытаюсь определить, сколько участников активно в течение 1 дня, 2 дней, 3 дней, … 7 дней в течение определенной недели 3/1-3/7.

Есть ли какой-либо способ использовать агрегатную функцию поверх раздела by? Если нет, то что можно использовать для достижения этой цели?

 select distinct memberID,count(date) over(partition by memberID) as no_of_days_active
from visitor
where date between '"2019-01-01 00:00:00"' and '"2019-01-07 00:00:00"'
order by no_of_days_active

результат должен выглядеть примерно так

 #Days Active    Count
1           20
2           32
3           678
4           34
5           3
6           678
7           2345

Ответ №1:

Я думаю, вам нужны два уровня агрегации для подсчета количества дней в течение недели:

 select num_days_active, count(*) as num_members
from (select memberID, count(distinct date::date) as num_days_active
      from visitor
      where date >= '2019-01-01'::date and 
            date < '2019-01-08'::date
      group by memberID
     ) v
group by num_days_active
order by num_days_active;

Обратите внимание, что я изменил сравнения дат. Если у вас есть компонент времени, то between не работает. И, поскольку вы включили время в константу, я добавил явное преобразование в дату для count(distinct) . Это может быть необязательно, если date это действительно дата без компонента времени.

1. Спасибо, Гордан. Добавлена группа по внутри подзапроса и корректно получены результаты. выберите num_days_active, count(*) как num_members из (выберите MemberID, count(distinct date::date) как num_days_active от посетителя, где date >= ‘2019-01-01’::date и date < ‘2019-01-08’::date группируйте по MemberID) v группируйте по num_days_active заказывайте по num_days_active

Ответ №2:

Основываясь на ответе @ Gordon, мне лично нравится использовать with оператор для подзапросов:

 with dat as (
    select distinct
        memberID,
        count(date) over(partition by memberID) as no_of_days_active
    from visitor
    where 1=1
        and date between '2019-01-01'::date and '2019-01-07'::date
    order by no_of_days_active
)

select 
    no_of_days_active, 
    count(no_of_days_active) no_of_days_active_cnt
from dat
group by no_of_days_active
order by no_of_days_active