这道题卡了我很久，因为我对窗口函数和日期转换不熟，加上读题不清楚，所以一直排查，一直尝试，最终才做对。现在记录一下做题的过程。

一、题目要求

题目要求最后输出的字段除了device_id和练习年月两个好理解的外，还有个人练题总数、个人平均练题数和所有人总练题数。具体可以写为以下几点：

1. 截至当月的个人练题总数
2. 当月和最近三个月的个人月平均练题数
3. 截至当月所有人练题总数

可以看出，这里有些字段要求统计个人，有些要求统计所有人。有些要求统计最近四个月（这个月加上前三个月），有些要求统计截至目前（这个月以前的所有数据），这一点很容易绕晕人。

二、解题步骤

首先因为我们是按月和练习年月来统计数据，所以不妨建立一个临时表，统计每个人每个月的练习题数，再进一步完成题目的要求。

Step1:统计每人每月的练习题数

统计每人每月很简单，用group by设备号和日期就能解决。对于日期转换，通常我们用date_format()就能只保留日期里的年月，但要注意的是，在这个临时表里，我并没有把event_date直接转换为只含年月不含日的字段，而是将“x号”统一改为“1号”。至于为什么，我先留个坑。

处理好时间后，我们根据时间和device_id就能统计每人每月的题数，具体代码如下：

with t as (
    SELECT 
    	device_id,  
    	date_format(event_date,'%Y-%m-01') as ym2, #日期转换
    	count(question_id) as cnt  #统计做题数
    FROM question_practice_detail
    GROUP BY device_id, ym2
)

Step2：用窗口函数统计各字段

窗口函数语法可分为两个部分，一是具体窗口函数，二是排序、分组和窗口设置。通常滑动窗口要结合排序一起使用，否则可能会报错。语法代码如下所示：

<窗口函数> over(partition by 分组字段 
	order by 排序字段 
    ROWS|RANGE 滑动窗口)

（1）截至当月的个人练题总数：

从字段要求可以得到三个关键信息，“截至当月”、“个人”和“总数”，它们可以分别对应到窗口函数的滑动窗口、分组字段和聚合函数。以代码的形式可以写作：

截至当月——ROWS UNBOUNDED PRECEDING
个人——PARTITION BY device_id
总数——sum(cnt) 
#cnt是临时表中的个人每月做题数

这里的ROWS UNBOUNDED PRECEDING等同于ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，也就是窗口范围从第一行到当前这一行。整体结合起来，该字段可以写作：

sum(cnt) over(partition by device_id 
	order by ym2 
    ROWS UNBOUNDED PRECEDING)
#ym2是临时表中的日期（格式为"yyyy-mm-01")

这里有个小问题，order by ym2按日期排序是必要的吗？

答：这道题似乎不用添加，但平常最好写上，因为担心原表日期是混乱的，例如先是1月数据，接着3月的，而后又是2月的数据。

（2）当月和最近三个月的个人月平均练题数 ：

仍然是三个关键信息，可以用代码写作

当月和最近三个月——RANGE INTERVAL '3' MONTH PRECEDING
个人——ORDER BY device_id
平均——AVG(cnt)

因为用到了RANGE INTERVAL，所以必须要有排序order by。又因为题目要统计的是最近三个月，依据的是日期字段，所以排序依据的字段是临时表的，也就是date_format(event_date,'%Y-%m-01')。

这里我要把我的坑填上了，如果直接用order by ym2会报错，为什么？因为date_format是个日期转换为字符串的函数，此时的不是日期字段，是个属性为字符串的字段，而order by后面跟的应该是整数型、浮点型或日期字段，所以报错。

那应该怎么解决？这里我用的方法是用str_to_date(str,format)函数，是将字符串转换为日期的函数，和上面的函数功能相反。如果前面建临时表的时候，我对日期字段只保留了年月，那么在这里不管我将字段规范为年月日的形式，还是只用年月排序，它都会报错。（至于为什么，我也不知道，就是一次一次试出来的。如果有好的方法欢迎提出。）所以我把设置为“x年x月1日”的字符串字段，再在这里转换为日期并排序，就不会报错了。

具体代码如下：

avg(cnt) over(partition by device_id 
	order by str_to_date(ym2,'%Y-%m-%d') 
    Range INTERVAL '3' MONTH preceding)

（3） 截至当月所有人练题总数：

同上，将关键信息写成代码，如下所示：

截至当月——Range INTERVAL '3600' MONTH preceding
总数——sum(cnt)
所有人——不用分组了

Q：为什么这里要写成RANGE INTERVAL '3600' MONTH PRECEDING，而不像（1）一样写为ROW UNBOUNDED PRECEDING？

答：因为这里要统计的是所有人在截至当月的做题总数，如果用后者，它只能统计到这条数据行之前的所有做题数，如果这条数据行之后还有同一个月某个人的做题数，则不会计算在内。在这里设置一个很大的月范围数是为了把当月所有人做题数包括进来。

所以这个字段整体代码写为：

sum(cnt) over(order by str_to_date(ym2,'%Y-%m-%d') 
	Range INTERVAL '3600' MONTH preceding)

Step3：整合代码输出

最后按照题目要求，根据设备号和联系年月升序排序。整体代码可以写为：

with t as (
    select device_id,  date_format(event_date,'%Y-%m-01') as ym2, count(question_id) as cnt
    from question_practice_detail
    group by device_id, ym2
)

select device_id, date_format(ym2,'%Y-%m')  as ym,
    sum(cnt) over(partition by device_id ROWS unbounded preceding) as sum_cnt,
    round(avg(cnt) over(partition by device_id order by str_to_date(ym2,'%Y-%m-%d') Range INTERVAL '3' MONTH preceding),2) as avg3_cnt,
    sum(cnt) over(order by str_to_date(ym2,'%Y-%m-%d') Range INTERVAL '3600' MONTH preceding) as total_cnt
from t
order by device_id, ym