SQL之计算DNU、DAU、WAU以及MAU

-- 2. 再根据第一次访问日期分组, 然后去重, 就可以得到DNU了
select first_date, count(distinct unionid) DNU
from
( -- 1. 先找到每个用户第一次访问日期
  select unionid, min(to_date(createdate)) first_date
  from table a
  group by unionid
) b
group by first_date;

select to_date(partition_date) createdate, count(distinct unionid) DAU
from table a
group by to_date(partition_date);

select date_range, week, count(distinct unionid) WAU
FROM
(
    select to_date(createdate) createdate, weekofyear(to_date(createdate)) week, 
    concat_ws('__', 
        min(to_date(createdate)) over(partition by weekofyear(to_date(createdate))), 
        max(to_date(createdate)) over(partition by weekofyear(to_date(createdate)))
    ) date_range,
    unionid
    from table a
) b
group by week, date_range;

select DATE_FORMAT(to_date(partition_date),"yyyy-MM") ym_date, count(distinct unionid) MAU
FROM table
group by DATE_FORMAT(to_date(partition_date),"yyyy-MM");