MySQL中位数计算方法 [数据库教程]

Z时代
2024-01-10
分类：综合

database

在网上搜到的一种算法是利用自增长变量进行排序，然后再根据位置序号取。感觉有些复杂了，还是group_concat来的省事些

1. 按顺序聚合，逗号分隔，并计数

group_concat( number order by number asc)

2. 根据逗号拆分，判断奇偶数去截取中间位置的那个数

具体代码如下：

SELECT 
            doctor_name doctor,  -- 分组
	    count(1) patientNum,  -- 总数
	    group_concat(dnt order by dnt asc),
	    substring_index(SUBSTRING_INDEX(group_concat(dnt order by dnt asc),‘,‘,(count(1)+1) div 2),‘,‘,-1)  dnt,
	    case when count(1)%2=1 then substring_index(SUBSTRING_INDEX(group_concat(dnt order by dnt asc),‘,‘,(count(1)+1) div 2),‘,‘,-1) else (substring_index(SUBSTRING_INDEX(group_concat(dnt order by dnt asc),‘,‘,(count(1)+2) div 2),‘,‘,-1) + 
									    substring_index(SUBSTRING_INDEX(group_concat(dnt order by dnt asc),‘,‘,count(1) div 2),‘,‘,-1))/2
           end mid_dnt
FROM
(
                SELECT distinct doctor_name, record_id, dnt
                from rp_green_channel_patient_detaile
                where dnt is not null
                        AND visit_day >= ‘2020-03-30‘ 
                        AND visit_day <= ‘2020-06-27‘ 
) AS a 
group by doctor_name