时间戳

首页 > 解决方案 > 如果按子句分组，如何在mysql中获取中位数？

mysql - 如果按子句分组，如何在mysql中获取中位数？

问题描述

我的 mysql 版本是 8.+。

表结构：

CREATE TABLE `loss` (
  `date` date DEFAULT NULL,
  `circle` varchar(100) DEFAULT NULL,
  `district` varchar(100) DEFAULT NULL,
  `kpi_1` int(11) DEFAULT NULL,
  `kpi_2` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

表数据loss：

insert  into `loss`(`date`,`circle`,`district`,`kpi_1`,`kpi_2`) values 
('2020-09-20','101','delhi',90,100),
('2020-09-20','102','Punjab',80,10),
('2020-09-20','104','delhi',90,90),
('2020-09-20','104','New Delhi',20,10),
('2020-09-20','104','Punjab',45,23),
('2020-09-20','104','New Delhi',4,13),
('2020-09-20','104','New Delhi',7,150),
('2020-09-20','104','New Delhi',80,40),
('2020-09-20','104','New Delhi',80,50),
('2020-09-20','104','New Delhi',NULL,NULL);

询问：

select date,circle,district,count(*) as total_rows,sum(kpi_1),sum(kpi_2) from loss
group by date,circle,distrcit

我可以得到所有的聚合，但是如何找到中位数呢？

标签： mysqlsqlcountwindow-functionsmedian

解决方案

不幸的是，MySQL 没有聚合中值函数或类似函数——甚至没有像 MariaDB 那样的窗口函数。

使用窗口函数的一种解决方法是：

select date, circle, district, count(*) cnt, sum(kpi_1) sum_kpi1, sum(kpi_2) sum_kpi2,
    avg(case when rn1 in (floor((cnt + 1)/2), floor((cnt + 2)/2)) then kpi1 end) media_kpi1,
    avg(case when rn2 in (floor((cnt + 1)/2), floor((cnt + 2)/2)) then kpi2 end) media_kpi2
from (
    select l.*, 
        row_number() over(partition by date, circle, district order by kpi1) rn1,
        row_number() over(partition by date, circle, district order by kpi2) rn2,
        count(*)     over(partition by date, circle, district) cnt
    from loss
) l
group by date, circle, district

这假设您要按日期、圈子和地区聚合记录，如原始查询中所示。如果您想要另一组列，那么您可以更改窗口函数的group by子句和partitions。

推荐阅读