Mysql分析函数使用

1.查询 集合中的统计总和与平均值

SELECT asses_user, user_id, su, av,rn
       FROM (
           select asses_user,user_id,t.asses_all_data,
           sum(asses_all_data) OVER (PARTITION BY user_id) AS su,
           AVG(asses_all_data) OVER (PARTITION BY user_id) AS av,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY asses_all_data DESC) AS rn
           from (
               select asses_user,user_id,asses_all_data,pg_id from
               manage_evaluation_assess
               where create_time BETWEEN CAST(#{startDate}  as DATE) and CAST(#{endDate} as DATE)
               group  by   asses_user,user_id,asses_all_data,pg_id
           ) t
       ) AS temp
       WHERE rn = 1;