SQL题——中位数相关问题_sql求中位值价格时为什么单量为单数的价格都为零

1.中位数问题的统一思路

思路： 
中位数应满足：中位数的排名，正序排名和逆序排名都应该大于等于总数的一半
对应数前、后数的多少可分别通过正序排列和倒序排列求得
1
2
3

2.相关题目

569 员工薪水中位数

表: Employee

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| id           | int     |
| company      | varchar |
| salary       | int     |
+--------------+---------+
1
2
3
4
5
6
7

id 是该表的主键列(具有唯一值的列)。
该表的每一行表示公司和一名员工的工资。

编写解决方案，找出每个公司的工资中位数。

以 任意顺序 返回结果表。

查询结果格式如下所示。

示例 1:

输入:
Employee 表:

+----+---------+--------+
| id | company | salary |
+----+---------+--------+
| 1  | A       | 2341   |
| 2  | A       | 341    |
| 3  | A       | 15     |
| 4  | A       | 15314  |
| 5  | A       | 451    |
| 6  | A       | 513    |
| 7  | B       | 15     |
| 8  | B       | 13     |
| 9  | B       | 1154   |
| 10 | B       | 1345   |
| 11 | B       | 1221   |
| 12 | B       | 234    |
| 13 | C       | 2345   |
| 14 | C       | 2645   |
| 15 | C       | 2645   |
| 16 | C       | 2652   |
| 17 | C       | 65     |
+----+---------+--------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

输出:

+----+---------+--------+
| id | company | salary |
+----+---------+--------+
| 5  | A       | 451    |
| 6  | A       | 513    |
| 12 | B       | 234    |
| 9  | B       | 1154   |
| 14 | C       | 2645   |
+----+---------+--------+
 
1
2
3
4
5
6
7
8
9
10

答案：

select id, company, salary
from
    (select *
            ,count(id) over(partition by company) as emp_cnt ## 每个公司员工的数量
            ,row_number() over(partition by company order by salary, id) as rn1 ## 正序排序
            ,row_number() over(partition by company order by salary desc, id desc) as rn2  ## 倒序排序
    from employee) t0
where rn1 >= emp_cnt/2 and rn2 >= emp_cnt/2
order by company, salary
;

1
2
3
4
5
6
7
8
9
10
11

571 给定数字的频率查询中位数

Numbers 表：

+-------------+------+
| Column Name | Type |
+-------------+------+
| num         | int  |
| frequency   | int  |
+-------------+------+
1
2
3
4
5
6

num 是这张表的主键(具有唯一值的列)。
这张表的每一行表示某个数字在该数据库中的出现频率。

中位数 是将数据样本中半数较高值和半数较低值分隔开的值。
编写解决方案，解压 Numbers 表，报告数据库中所有数字的 中位数 。结果四舍五入至 一位小数 。

返回结果如下例所示。

示例 1：

输入：
Numbers 表：

+-----+-----------+
| num | frequency |
+-----+-----------+
| 0   | 7         |
| 1   | 1         |
| 2   | 3         |
| 3   | 1         |
+-----+-----------+
1
2
3
4
5
6
7
8

输出：

+--------+
| median |
+--------+
| 0.0    |
+--------+
1
2
3
4
5

解释：
如果解压这个 Numbers 表，可以得到 [0, 0, 0, 0, 0, 0, 0, 1, 2, 2, 2, 3] ，所以中位数是 (0 + 0) / 2 = 0 。

select round(avg(num), 1) as median
from
(select *
        ,sum(frequency) over(order by num) as rn1
        ,sum(frequency) over(order by num desc) as rn2
        ,sum(frequency) over() as s
from numbers) t
where rn1 >= s/2
and rn2 >= s/2;


1
2
3
4
5
6
7
8
9
10
11

1412 查询成绩处于中游的学生

表: Student

±--------------------±--------+
| Column Name | Type |
±--------------------±--------+
| student_id | int |
| student_name | varchar |
±--------------------±--------+
student_id 是该表主键(具有唯一值的列)。
student_name 学生名字。

表: Exam

±--------------±--------+
| Column Name | Type |
±--------------±--------+
| exam_id | int |
| student_id | int |
| score | int |
±--------------±--------+
(exam_id, student_id) 是该表主键(具有唯一值的列的组合)。
学生 student_id 在测验 exam_id 中得分为 score。

成绩处于中游的学生是指至少参加了一次测验, 且得分既不是最高分也不是最低分的学生。

编写解决方案，找出在 所有 测验中都处于中游的学生 (student_id, student_name)。不要返回从来没有参加过测验的学生。

返回结果表按照 student_id 排序。

返回结果格式如下。

示例 1：

输入：
Student 表：

+-------------+---------------+
| student_id  | student_name  |
+-------------+---------------+
| 1           | Daniel        |
| 2           | Jade          |
| 3           | Stella        |
| 4           | Jonathan      |
| 5           | Will          |
+-------------+---------------+
1
2
3
4
5
6
7
8
9

Exam 表：

+------------+--------------+-----------+
| exam_id    | student_id   | score     |
+------------+--------------+-----------+
| 10         |     1        |    70     |
| 10         |     2        |    80     |
| 10         |     3        |    90     |
| 20         |     1        |    80     |
| 30         |     1        |    70     |
| 30         |     3        |    80     |
| 30         |     4        |    90     |
| 40         |     1        |    60     |
| 40         |     2        |    70     |
| 40         |     4        |    80     |
+------------+--------------+-----------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14

输出：

+-------------+---------------+
| student_id  | student_name  |
+-------------+---------------+
| 2           | Jade          |
+-------------+---------------+
1
2
3
4
5

解释：
对于测验 1: 学生 1 和 3 分别获得了最低分和最高分。
对于测验 2: 学生 1 既获得了最高分, 也获得了最低分。
对于测验 3 和 4: 学生 1 和 4 分别获得了最低分和最高分。
学生 2 和 5 没有在任一场测验中获得了最高分或者最低分。
因为学生 5 从来没有参加过任何测验, 所以他被排除于结果表。
由此, 我们仅仅返回学生 2 的信息。

答案：

select b.*
from
(   
    select distinct student_id
    from Exam
    where student_id not in
    (   select distinct student_id
        from
            (select exam_id
                    ,student_id
                    ,score
                    ,max(score) over(partition by exam_id) as max_score
                    ,min(score) over(partition by exam_id) as min_score
            from exam) t
        where score = max_score OR score = min_score
    )
) a 
left join student b
on a.student_id = b.student_id
order by a.student_id
;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22