Как дополнить запрос, чтобы получить группировку по месяцам для запроса AVG(COUNT)?

Question

Есть таблица событий (одна строка = одно событие) с полями user_id и server_upload_time. Мне нужно получить таблицу с полями date и avg_dau, где date — неделя, а avg_dau среднее от count по дням за неделю. Иными словами нужен график изменения среднего dau по неделям.

У меня получилось найти среднее dau за месяц:

select avg(users) as "avg_dau"
from (select count(distinct user_id) as "users",
        server_upload_time::date
    from event
    where server_upload_time::date between '2020-01-01' and '2020-02-01'
    group by 2)

но это одна строка, как изменить запрос чтобы получить что-то подобное:

date | avg_dau

2020-01-01 | 140000

2020-01-08 | 138000

2020-01-15 | 142000

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

upd. Немного дописал, получилось вот так:

select date_trunc('Week', server_upload_time) as "date",
    count(distinct user_id) as "mau",

(select avg(users)
from (select count(distinct user_id) as "users",
        server_upload_time::date
    from event
    where server_upload_time::date between '2020-01-01' and '2020-02-01'
    group by 2)) as "avg_dau"
    
from event

where server_upload_time::date between '2020-01-01' and '2020-02-01'

group by 1

На выходе получается список недель, разные значения mau, но avg_dau из подзапроса не считается как отдельный результат на каждую неделю, а как среднее за весь срок, описанный в date. Да, это и логично, но мне надо не так :)

upd. http://sqlfiddle.com/#!17/5d0e5/1 попробовал что-то такое собрать. Postgre 9.6

В финальной таблице нужно получить вот такие значения average:

Answer 1

WITH cte AS ( select upload,
                     count(distinct user_id) as "count",
                     MIN(upload) OVER (PARTITION BY date_trunc('Week', upload)) min_upload
              from demo
              where upload between '2020-01-01' and '2020-05-01'
              group by upload )
SELECT "count",
       upload,
       CASE WHEN upload = min_upload 
            THEN AVG("count") OVER (PARTITION BY date_trunc('Week', upload))::CHAR(5)
            ELSE ''
            END "average(count)"
FROM cte
ORDER BY upload;

fiddle

БЛОГ НА HUSL

Как дополнить запрос, чтобы получить группировку по месяцам для запроса AVG(COUNT)?

Ответы (1 шт):