SQL计算中位数

（中位数：如果是偶数个元素，要把中间两位求和除2的）

查询一个字段的中位数

    <select id="selectMdnScore" parameterType="String" resultType="java.lang.Double">

    	select avg(${scoreKind}) as openglScore
        from
        ( select ${scoreKind} ,row_number() over(order by ${scoreKind}) as rn
        ,count(*) over() as n
           from gpu_info_result_suite
        )as t
        where rn in (floor(n/2)+1,if(mod(n,2) = 0,floor(n/2),floor(n/2)+1))

    </select>

查询多个字段的中位数(感觉有点笨，正在找其他方法)

        这里还增加了筛选条件inputCopies=1，如果不需要删除<where>标签即可

 <select id="selectV3MdnScore" parameterType="map" resultType="java.util.Map">

    select
        (select Round(avg(cpuScore),2)  from(select cpuScore ,row_number() over(order by cpuScore) as rn,count(cpuScore) over() as n from gcbsv3_info_result_suite
         <where>
            <if test="inputCopies != null">
                inputCopies=#{inputCopies}
            </if>
        </where>)as t where rn in (floor(n/2)+1,if(mod(n,2) = 0,floor(n/2),floor(n/2)+1))) As cpuScore,

        (select Round(avg(memoryScore),2)  from(select memoryScore ,row_number() over(order by memoryScore) as rn,count(memoryScore) over() as n from gcbsv3_info_result_suite
        <where>
            <if test="inputCopies != null">
                inputCopies=#{inputCopies}
            </if>
        </where>)as t where rn in (floor(n/2)+1,if(mod(n,2) = 0,floor(n/2),floor(n/2)+1))) As memoryScore

</select>

service层求中位数、平均数、最大值

    1：先把数据查出来到List集合中
    2：创建minMap 、mdnMap 、avgMap 、maxMap 
    3：创建HashMap<String, Map> resultMap，用来存放minMap 、mdnMap 、avgMap 、maxMap 
   

    4：创建HashMap<String,List<Double>> allScore，存放不同类型的所有数据
    5：遍历List数据集合，根据类型把元素添加到allScore的value中。
              遍历过程比较大小，如果最大就 maxMap 就取这个数
    6： 计算平均数和中位数使用CalculationUtils工具类的calAvgAndMedian(Listlist))方法;。结果是list。list.get（0）是平均数，list.get（0）是中位数
		CalculationUtils.calAvgAndMedian(allScore.get("cpuScore"));再把结果放在avgMap 、mdnMap 即可
              
   
public HashMap<String, Map> computedV3(EntireInfoVoSearch searchEntiry) {
        List<EntireInfoVo> entireInfoVoList = baseMapper.searchMoreInfoPage(searchEntiry);
        HashMap<String, Map> resultMap = new HashMap<>();
        if (entireInfoVoList.size()>0){
            //最大值、最小值、平均数、中位数
            HashMap<String,Double>  maxMap = new HashMap<>();
            maxMap.put("cpuScore",0d);
            maxMap.put("systemScore",0d);
            maxMap.put("score",0d);
            HashMap<String,Double>  minMap = new HashMap<>();

            HashMap<String,Double>  mdnMap = new HashMap<>();
            HashMap<String,Double>  avgMap = new HashMap<>();
            HashMap<String,List<Double>> allScore = new HashMap<>();

            allScore.put("cpuScore",new ArrayList<>());
            allScore.put("systemScore",new ArrayList<>());
            allScore.put("score",new ArrayList<>());
            //流处理将数据整合到一起
            entireInfoVoList.stream().forEach(entireInfoVo -> {
                if (entireInfoVo.getCpuScore() != null) {
                    allScore.get("cpuScore").add(entireInfoVo.getCpuScore());
                    if (entireInfoVo.getCpuScore()>maxMap.get("cpuScore")){
                        maxMap.put("cpuScore",entireInfoVo.getCpuScore());
                    }
                    if (!minMap.containsKey("cpuScore")||entireInfoVo.getCpuScore()<minMap.get("cpuScore")){
                        minMap.put("cpuScore",entireInfoVo.getCpuScore());
                    }
                }
                if (entireInfoVo.getSystemScore() != null) {
                    allScore.get("systemScore").add(entireInfoVo.getSystemScore());
                    if (entireInfoVo.getSystemScore()>maxMap.get("systemScore")){
                        maxMap.put("systemScore",entireInfoVo.getSystemScore());
                    }
                    if (!minMap.containsKey("systemScore")||entireInfoVo.getSystemScore()<minMap.get("systemScore")){
                        minMap.put("systemScore",entireInfoVo.getSystemScore());
                    }
                }
                if (entireInfoVo.getScore() != null) {
                    allScore.get("score").add(entireInfoVo.getScore());
                    if (entireInfoVo.getScore()>maxMap.get("score")){
                        maxMap.put("score",entireInfoVo.getScore());
                    }
                    if (!minMap.containsKey("score")||entireInfoVo.getScore()<minMap.get("score")){
                        minMap.put("score",entireInfoVo.getScore());
                    }
                }

            });
            //计算 平均数、中位数
            List<Double> cpuScoreAvgAndMdn = CalculationUtils.calAvgAndMedian(allScore.get("cpuScore"));
            List<Double> systemScoreAvgAndMdn = CalculationUtils.calAvgAndMedian(allScore.get("systemScore"));

            List<Double> scoreAvgAndMdn = CalculationUtils.calAvgAndMedian(allScore.get("score"));
            avgMap.put("cpuScore",cpuScoreAvgAndMdn.get(0));
            avgMap.put("systemScore",systemScoreAvgAndMdn.get(0));
            avgMap.put("score",scoreAvgAndMdn.get(0));

            mdnMap.put("cpuScore",cpuScoreAvgAndMdn.get(1));
            mdnMap.put("systemScore",systemScoreAvgAndMdn.get(1));
            mdnMap.put("score",scoreAvgAndMdn.get(1));

            resultMap.put("max",maxMap);
            resultMap.put("min",minMap);
            resultMap.put("mdn",mdnMap);
            resultMap.put("avg",avgMap);
            for (Map.Entry<String,List<Double>> data:allScore.entrySet()){
                System.out.println(data.getKey()+":");
                for (Double sor:data.getValue()){
                    System.out.print(sor+"、");
                }
            }
        }
        return resultMap;
    }