题解 | #截至当月的练题情况#

1.首先找到每个设备id每个月的练题情况

select device_id, date_format(event_date, '%Y-%m') as ym, count(*) counts
            from question_practice_detail
            group by device_id, date_format(event_date, '%Y-%m')                

​ 2.使用窗口函数分别求出总练题数，三个月总共的练题数和月份数量，每个人的练题总数

select device_id,
             ym,
             sum(counts) over (partition by device_id order by ym)                   sum_cnt,
             sum(counts) over (order by ym)                                          total_cnt,
             sum(counts) over (partition by device_id order by ym ROWS 2 preceding ) avg3_,
             count(*) over (partition by device_id order by ym ROWS 2 preceding )    avg3_count


#        sum(counts) over (partition by device_id order by ym ),
      from (select device_id, date_format(event_date, '%Y-%m') as ym, count(*) counts
            from question_practice_detail
            group by device_id, date_format(event_date, '%Y-%m')) diyc

​ 3.在最外层计算最近三个练题月的月平均练题数

select device_id,
       ym,
       sum_cnt,
       avg3_ / avg3_count as avg3_cnt,
       total_cnt
from (select device_id,
             ym,
             sum(counts) over (partition by device_id order by ym)                   sum_cnt,
             sum(counts) over (order by ym)                                          total_cnt,
             sum(counts) over (partition by device_id order by ym ROWS 2 preceding ) avg3_,
             count(*) over (partition by device_id order by ym ROWS 2 preceding )    avg3_count


#        sum(counts) over (partition by device_id order by ym ),
      from (select device_id, date_format(event_date, '%Y-%m') as ym, count(*) counts
            from question_practice_detail
            group by device_id, date_format(event_date, '%Y-%m')) diyc) d;

​