大数据第44天—Mysql练习题15-给定数字的频率查询中位数-杨大伟

需求：编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息： FirstName, LastName, City, State

展示效果：

median

0.0000

Create table If Not Exists Numbers (Number int, Frequency int);

insert into Numbers (Number, Frequency) values (0, 7);
insert into Numbers (Number, Frequency) values (1, 1);
insert into Numbers (Number, Frequency) values (2, 3);
insert into Numbers (Number, Frequency) values (3, 1);

最终SQL:

select
      avg(t.number) as median
from
      (select
             n1.number,
             n1.frequency,
             (select 
                   sum(frequency) 
              from 
                   numbers n2
              where 
                   n2.number<=n1.number
             ) as asc_frequency,
             (select
                   sum(frequency)
              from 
                   numbers n3 
              where 
                   n3.number>=n1.number
             ) as desc_frequency
      from 
             numbers n1
      ) t
where 
      t.asc_frequency>= (select sum(frequency) from numbers)/2
      and t.desc_frequency>= (select sum(frequency) from numbers)/2;