需求:求公司中位分
思路:需要将数据进行排序,找出位于中间的数据,(查询出来的条数为奇数,中位数就是中间的这个得分,如果是偶数,中位数要等于中间两边的平均分)
ceil()函数向上取整,返回的是大于或等于函数参数,整数时,函数值不变,正数时,当前整数+1,负数时,取当前整数;
floor() 函数的作用就是返回小于等于括号内该值的最大整数,整数时,函数值不变,正数时,为当前整数,负数时,取当前整+1;
#中位分
SELECT #第三层开始 查询中位的得分(查询出来的条数为奇数,中位数就是中间的这个得分,如果是偶数,中位数要等于中间两边的平均分)
AVG( t3.score ) AS score
FROM
(
SELECT
t2.score #第三层开始 查询中位的得分情况(奇数返回一条,偶数返回两条)
FROM
(
SELECT #第二层开始 给查询出来的结果给定一个有序行号
t.score,
( @i := @i + 1 ) AS num
FROM
(
SELECT #第一层 开始 将得分情况进行一个排序
pd.newest_score AS score
FROM
peak_driver pd
LEFT JOIN peak_sys_user psu ON pd.user_id = psu.id
WHERE
pd.deleted = 0
AND psu.deleted = 0
AND psu.client_type LIKE '%APP%'
AND psu.company_id = '1'
ORDER BY
pd.newest_score DESC
) t #第一层结束,
( SELECT @i := 0 ) AS itable
) t2 #第二层结束 给定行号初始值
WHERE
floor( @i / 2+1 ) = num
OR ceil( @i / 2 ) = num #第三层结束
) t3 #第四层结束
第一层:将得分情况进行一个排序
SELECT
pd.newest_score AS score
FROM
peak_driver pd
LEFT JOIN peak_sys_user psu ON pd.user_id = psu.id
WHERE
pd.deleted = 0
AND psu.deleted = 0
AND psu.client_type LIKE '%APP%'
AND psu.company_id = '1'
ORDER BY
pd.newest_score DESC
结果:
第二层:给查询出来的结果给定一个有序行号
SELECT
t.score,
( @i := @i + 1 ) AS num
FROM
(
SELECT
pd.newest_score AS score
FROM
peak_driver pd
LEFT JOIN peak_sys_user psu ON pd.user_id = psu.id
WHERE
pd.deleted = 0
AND psu.deleted = 0
AND psu.client_type LIKE '%APP%'
#AND psu.company_id = '1'
ORDER BY
pd.newest_score DESC
) t,
( SELECT @i := 0 ) AS itable
结果:
第三层:查询中位的得分情况(奇数返回一条,偶数返回两条)
SELECT
t2.score,
t2.num
FROM
(
SELECT
t.score,
( @i := @i + 1 ) AS num
FROM
(
SELECT
pd.newest_score AS score
FROM
peak_driver pd
LEFT JOIN peak_sys_user psu ON pd.user_id = psu.id
WHERE
pd.deleted = 0
AND psu.deleted = 0
AND psu.client_type LIKE '%APP%'
#AND psu.company_id = '1'
ORDER BY
pd.newest_score DESC
) t,
( SELECT @i := 0 ) AS itable
) t2
WHERE
floor( @i / 2+1 ) = num
OR ceil( @i / 2 ) = num
结果:
奇数:(7条)
偶数:(8条)
第四层:查询出来的条数为奇数,中位数就是中间的这个得分,如果是偶数,中位数要等于中间两边的平均分