MySQL-group by-子查询获取group by维度的平均值-计算平均通过率-对百分比进行再平均
示例1:
EXPLAIN
SELECT
e.enterprise_record_id enterpriseRecordId,
e.enterprise_name enterpriseName,
d.department_record_id departmentRecordId,
d.department_name departmentName,
ifnull(sum(r.score ),0) totalScore,
ifnull(ROUND(sum(r.score ) / (select count(1) from cred_user_info where container_id = u.container_id and department_record_id = u.department_record_id and is_delete = 0),1),0) as avgScore,
max(c.change_time)
FROM
cred_user_info u force INDEX (idx_d)
INNER JOIN cred_change c force INDEX (idx_c_u_d) ON c.container_id = u.container_id AND c.user_id = u.user_id and c.is_delete = u.is_delete
INNER JOIN cred_rule r ON r.container_id = c.container_id AND r.event_no = c.event_no and r.is_delete = c.is_delete
LEFT JOIN cred_enterprise e force INDEX (idx_c_e_i) ON e.container_id = u.container_id AND e.enterprise_record_id = u.enterprise_record_id and u.is_delete = e.is_delete
LEFT JOIN cred_department d force INDEX (idx_c_d_i) ON u.container_id = d.container_id AND u.department_record_id = d.department_record_id and u.is_delete = d.is_delete
WHERE
u.is_delete = 0
AND u.container_id = "AnQuanShengChanGong2"
GROUP BY
u.department_record_id
ORDER BY
totalScore DESC
1、Using temporary; Using filesort, 一般来自于on条件，不能是where，加 force INDEX (idx_c_d_i_2) 有效
2、如果有GROUP BY，就给这个字段单独设置一个字段索引，且该表上使用 force INDEX (idx_d)
3、实在不行加个子查询（似乎最多1个），
4、2个子查询的情况要代码分开了 关注下edu中的
"@GetMapping("/getUserLearningList")"
"ResultVo<EduUserLearningCourseRecordEveryDayVo> getUserLearningListNew1(UserLearningDto userLearningDto) "(《MySQL-记录join两个无关联的业务表数据-分组-聚合-排序-分拆sql-按排序join-分页参数》)
示例2:
计算平均通过率-对百分比进行再平均
不能通过累加各条数的除法的分子、分母的方式来求平均
而是先求各条数据的平均值，相加除以个数
累加分子/分母 4/51 计算百分比 7.84
数据1的分子/分母 2/26 7.69
数据2的分子/分母 1/21 4.76
数据3的分子/分母 1/4 25
上面3条数据的百分比求和后除以3 = 12.48 ，不等于7.84
SELECT DATE_FORMAT(train_start_time, '%c') as month,
count(1) as implementCount,
<!-- 不正确 -->
<!-- ROUND(
CASE
ifnull(sum(train_people_number), 0)
WHEN 0 THEN
0
ELSE ifnull(sum(pass_people_number), 0) * 100 / ifnull(sum(train_people_number), 0)
END, 2) AS trainAvgPassRate -->
<!-- 正确 -->
<!-- 内侧的ROUND( ... END, 4 ) 这个尽量保证数据精度，其实应该可以不要ROUND -->
ROUND(sum( ROUND(
CASE
ifnull(train_people_number, 0)
WHEN 0 THEN
0
ELSE ifnull(pass_people_number, 0) * 100 / ifnull(train_people_number, 0)
END, 4) ) / count(1) ,2) as trainAvgPassRate
from edu_train_implement
WHERE container_id = #{containerId}
and DATE_FORMAT(train_start_time, '%Y') = #{trainYear}
AND release_status = 1 AND train_status = 2
and is_delete = 0
GROUP BY month;
正文到此结束
热门推荐
相关文章
近期评论
-
来自： 运维-docker专题-创建镜像-操作容器-制作jdk17的docker镜像等
-
来自： 留言板
-
来自： 留言板
-
来自： js-处理网页图片宽度自适应屏幕宽度
-
来自： 留言板
