017-取中位数

各数据库取中位数

1）mysql 取中位数

参考：https://blog.csdn.net/weixin_37243717/article/details/79110730
mysql 取中位数：按部门分组，获取每个部门下的中位数

SELECT 
    median_group, AVG(salary) AS median
FROM
    (SELECT 
        @ROW_NUMBER:=CASE
                WHEN @median_group = dept_no THEN @ROW_NUMBER + 1
                ELSE 1
            END AS count_of_group,
            @median_group:=dept_no AS median_group,
            dept_no,
            salary,
            (SELECT 
                    COUNT(*)
                FROM
                    auser,(select @median_group:='',@ROW_NUMBER:=0) as tmp_tab
                WHERE
                    a.dept_no = dept_no) AS total_of_group
    FROM
        (SELECT 
        dept_no, salary
    FROM
        auser
    ORDER BY dept_no , salary) AS a) AS b
WHERE
    count_of_group BETWEEN total_of_group / 2.0 AND total_of_group / 2.0 + 1
GROUP BY median_group

2）oracle 取中位数

按部门分组，获取每个部门下的中位数

select dept_no,median(salary) median from auser group by dept_no;

3）postgresql 取中位数

参考：https://yq.aliyun.com/articles/150
按部门分组，获取每个部门下的中位数

select dept_no,percentile_cont(0.5) within group (order by salary) from auser group by dept_no;

4）sql server 取中位数

WITH OrdersRN AS  
( 
  SELECT 
	dept_no, 
	salary,  
    ROW_NUMBER() OVER(PARTITION BY dept_no ORDER BY salary) AS RowNum,  
    COUNT(*) OVER(PARTITION BY dept_no) AS Cnt  
  FROM tuser  
) 
select dept_no,avg(salary) salary from (  
	SELECT dept_no,salary,RowNum,Cnt  
	FROM OrdersRN o  
	WHERE RowNum IN((Cnt + 1) / 2,(Cnt + 2) / 2)   
)a  
group by dept_no