题解 | #统计自然周平均登录次数情况#

1. 关键知识点：DAYOFWEEK()，DATE_ADD()，count(distinct)，round()
2. 思路解析：用库函数DAYOFWEEK得到每个日期是星期几，然后使用DATE_ADD转换到当周周一的日期，再按周一日期分组，计算每个每组内用户登录总数（count(1)）和用户数（count(distinct uid)），总次数除以总人数就是平均登录次数。保留小数位数用round()

select
  week_begin,
  round(count(1) / count(distinct uid), 2) as avg_times
from
  (
    select
      uid,
      DATE_ADD(login_date, INTERVAL 1 - day_delta DAY) as week_begin
    from
      (
        select
          uid,
          login_date,
          IF(DAYOFWEEK(login_date) = 1, 8, DAYOFWEEK(login_date)) - 1 as day_delta
        from
          user_login_tb
      ) as t_day_delta
  ) as t_week_begin
group by
  week_begin
order by
  week_begin;

遇到问题记得私聊哦