mysql窗口函数一直报错

Question

这段看了好几遍也不知道是哪里错了，输入也都是英文格式的呀，报错如下：
"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'precending)/count(ym) over(partition by device_id order by ym rows 2 precending)' at line 3"

select device_id,
ym,
sum(cnt) over (partition by device_id order by ym) as sum_cnt,
round(sum(cnt) over (partition by device_id order by ym rows 2 precending)/count(ym) over(partition by device_id order by ym rows 2 precending),2 ) as avg3_cnt,
sum(cnt) over (order by ym)as total_cnt from
(
    select device_id,substring(event_date,1,7 ) as ym,
    count(question_id) as cnt 
    from question_practice_detail 
    group by device_id,ym
    ) a
order by device_id,ym

Answer 1

//mysql 中over (partition by 的语法是8.0版以后才有的。
//mysql 5.7版只能用over格式。
把你的语句简化格式 为下面 ：可以直接看出缺group by a,b ，语法错误。
select a,b,sum(c) as sum_cnt,
round(sum(c)/count(b) as avg_cnt,
sum(cnt) as total_cnt
from (表）
order by a,b

Answer 2

• 关于该问题，我找了一篇非常好的博客，你可以看看是否有帮助，链接：MySQL利用窗口函数解决排名问题
• 你还可以看下mysql参考手册中的 MySQL 函数和运算符 窗口功能