查询一段时间内的DAU / MAU

假设您每天都有值，则可以使用子查询和获得总计数

range between

with dau as (      select date, count(userid) as dau      from dailysessions ds      group by date     )select date, dau,       sum(dau) over (order by date rows between -29 preceding and current row) as maufrom dau;

不幸的是，我认为您想要的是不同的用户，而不仅仅是用户数。这使问题变得更加困难，尤其是因为Postgres不支持

count(distinct)

我认为您必须为此进行某种形式的自我加入。这是一种方法：

with dau as (      select date, count(distinct userid) as dau      from dailysessions ds      group by date     )select date, dau,       (select count(distinct user_id)        from dailysessions ds        where ds.date between date - 29 * interval '1 day' and date       ) as maufrom dau;