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

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

展示效果：

median
0.0000 
1 Create table If Not Exists Numbers (Number int, Frequency int);
2 
3 insert into Numbers (Number, Frequency) values (0, 7);
4 insert into Numbers (Number, Frequency) values (1, 1);
5 insert into Numbers (Number, Frequency) values (2, 3);
6 insert into Numbers (Number, Frequency) values (3, 1);

最终SQL:

 1 select
 2       avg(t.number) as median
 3 from
 4       (select
 5              n1.number,
 6              n1.frequency,
 7              (select 
 8                    sum(frequency) 
 9               from 
10                    numbers n2
11               where 
12                    n2.number<=n1.number
13              ) as asc_frequency,
14              (select
15                    sum(frequency)
16               from 
17                    numbers n3 
18               where 
19                    n3.number>=n1.number
20              ) as desc_frequency
21       from 
22              numbers n1
23       ) t
24 where 
25       t.asc_frequency>= (select sum(frequency) from numbers)/2
26       and t.desc_frequency>= (select sum(frequency) from numbers)/2;

 

posted @ 2020-08-24 21:04  浪子逆行  阅读(206)  评论(0)    收藏  举报