mysql中位数

# 分位数

WITH tb AS 
(
SELECT 
sort_id,ddate,nfee,nfee_lastyear,lead(nfee,1,0)over(PARTITION BY sort_id ORDER BY ddate DESC) nfee_lastmonth 
FROM 
(
SELECT a.sort_id,DATE_FORMAT(a.ddate,'%Y-%m-01') AS ddate,SUM(nfee) AS nfee,SUM(nfee_lastyear) AS nfee_lastyear 
FROM performance_profit a
JOIN performance_item b 
    ON a.item_id=b.item_id AND b.csort1='费用'
AND a.ddate BETWEEN '2021-01-01' AND '2021-12-31' 
# and nfee_lastyear>0
AND  sort_id=2002191041440000111
GROUP BY a.sort_id,DATE_FORMAT(a.ddate,'%Y-%m-01')
# having SUM(nfee)>0 and SUM(nfee_lastyear)>0
) AS a 
)

 SELECT
     *,
     RANK() OVER w AS rankNo,
     PERCENT_RANK() OVER w AS percent_rankNo 
 FROM
     tb WINDOW w AS ( ORDER BY nfee DESC );
 

# 中位数

WITH tb_base AS ( # 基础数据
SELECT a.sort_id,DATE_FORMAT(a.ddate,'%Y-%m-01') AS ddate,person_id,SUM(nfee) AS nfee
FROM performance_profit a
JOIN performance_item b 
    ON a.item_id=b.item_id AND b.csort1='费用'
AND a.ddate BETWEEN '2021-01-01' AND '2021-12-31'  AND bcur=1
GROUP BY a.sort_id,DATE_FORMAT(a.ddate,'%Y-%m-01'),person_id
# HAVING DATE_FORMAT(ddate,'%Y-%m-01')='2021-02-01' AND sort_id IS NULL # 3220.80
# HAVING DATE_FORMAT(ddate,'%Y-%m-01')='2021-07-01' AND sort_id IS NULL # 897.315000
)
,tb_pr AS ( #分位数
SELECT
    *,
    ROUND(
       PERCENT_RANK() OVER (
          PARTITION BY sort_id,ddate
          ORDER BY nfee
       )
    ,2) percentile_rank
FROM
    tb_base
),
tb_mid AS (#中位数准备
    SELECT *,
        first_value(nfee) OVER (
        PARTITION BY sort_id,ddate,a1
        ORDER BY a2 ) firstvalue
        ,RANK() OVER (
        PARTITION BY sort_id,ddate,a1
        ORDER BY a2 ) AS rankNo    
    FROM (
        SELECT *,IF(aa>0,1,0) AS a1,ABS(aa) AS a2
        FROM (
            SELECT *,(percentile_rank-0.5) AS aa FROM tb_pr 
        ) AS a 
    ) AS a     
) 

# select * from tb_mid 
    
#取中位数
SELECT 
a.sort_id,a.ddate,AVG(b.firstvalue) AS median,MAX(a.inum) AS inum
FROM 
(
SELECT sort_id,ddate,MAX(rankNo) AS rankNo,COUNT(1) AS inum
FROM tb_mid
GROUP BY sort_id,ddate
) AS a 
JOIN tb_mid b 
    ON IFNULL(a.sort_id,0)=IFNULL(b.sort_id,0) AND a.ddate=b.ddate
    AND a.rankNo=b.rankNo
GROUP BY a.sort_id,a.ddate
;