浅浅星空 2022-11-19 12:12 采纳率: 66.7%
浏览 21
已结题

mysql窗口函数一直报错

这段看了好几遍也不知道是哪里错了,输入也都是英文格式的呀,报错如下:
"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

  • 写回答

2条回答 默认 最新

  • curating 2022-11-19 12:20
    关注

    //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

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论 编辑记录
查看更多回答(1条)

报告相同问题?

问题事件

  • 系统已结题 12月5日
  • 已采纳回答 11月27日
  • 创建了问题 11月19日