题解 | #截至当月的练题情况#
截至当月的练题情况
https://www.nowcoder.com/practice/80ed62a097564fad880b967678e460fc
SELECT
device_id,
ym,
sum(cnt) over (PARTITION by device_id order by ym) sum_cnt,
cast(avg(cnt) over (PARTITION by device_id order by ym rows 2 preceding)as DECIMAL(10,2)) avg3_cnt,
sum(cnt) over (ORDER by ym) total_cnt
from(
SELECT distinct
device_id,
DATE_FORMAT( event_date, '%Y-%m' ) ym ,
count(question_id) cnt
FROM
question_practice_detail
GROUP BY device_id,ym
ORDER BY
device_id,
ym
)a
device_id,
ym,
sum(cnt) over (PARTITION by device_id order by ym) sum_cnt,
cast(avg(cnt) over (PARTITION by device_id order by ym rows 2 preceding)as DECIMAL(10,2)) avg3_cnt,
sum(cnt) over (ORDER by ym) total_cnt
from(
SELECT distinct
device_id,
DATE_FORMAT( event_date, '%Y-%m' ) ym ,
count(question_id) cnt
FROM
question_practice_detail
GROUP BY device_id,ym
ORDER BY
device_id,
ym
)a
order by device_id,ym
首先要计算出每人当月做题数量
拿到这个结果后， 再使用窗口函数（聚合）来计算截至当月的总做题数（sum_cnt），所有人截至当月的总做题数（total_cnt），
每人三个月的平均做题数（用滑动窗口来做 rows 2 preceding 意思是取前两行加到本行，平均用avg就好）
这道题需要注意的是：
1.去重， 由于我们实际关心device_id,ym,question_id， 不关心结果，那么同一个同月可能做了多次题目，存在重复。
2.avg 三月的计算，一开始我想要 avg(count())这样两层的搞，是不符合语法的，因此转化思路， 先求出cnt,再进行avg。
3.取两位小数，想要用round，失败，查询后用了cast,ok.
总结：此题花了我将近1h,希望面试不要遇到。
投递京东等公司10个岗位