Блог

У меня есть этот набор данных

 date         id
1/1/2020     1
1/1/2020     2
...
n            m
  

Я хочу иметь постоянное количество отдельных пользователей в месяц в AWS Quicksight или Athena. например

 date         MAU
1/1/2020     -
1/2/2020     -
1/30/2020    100
1/31/2020    102
  

100 на 1/30/2020 означает, что за последние 30 дней было 100 активных пользователей (с 1/1/2020 по 1/30/2020). 102 на 1/31/2020 означает, что за последние 30 дней было 102 активных пользователя (с 1/2/2020 по 1/30/2020)

Основная идея заключается в использовании подсчета окон с рамкой диапазона. Работает ли это в Amazon Athena, если мы преобразуем дату в эпоху и используем следующую рамку диапазона?

 select date, 
    sum(count(*)) over(
        order by to_unixtime(date)
        range between - 60 * 60 * 24 * 30 preceding and current row
    ) mau
from mytable
group by date
  

Альтернативой решению с оконной функцией может быть коррелированный подзапрос:

 select date, 
    count(*)   (
        select count(*) 
        from mytable t1
        where t1.date >= t.date - interval '30' day and t1.date < t.date
    ) mau
from mytable
group by date