LeetCode-569、员工薪水中位数，571、给定数字的频率查询中位数

我就是我 2022-12-04 07:51 109阅读 0赞

> 来源：力扣（LeetCode）  
> 链接：https://leetcode-cn.com/problemset/database/

### 文章目录 ###

*  SQL结构：
 *   *  569. 员工薪水中位数（困难）
 *  SQL结构：
 *   *  571. 给定数字的频率查询中位数（困难）

# SQL结构： #

Create table If Not Exists Employee (Id int, Company varchar(255), Salary int)
    Truncate table Employee
    insert into Employee (Id, Company, Salary) values ('1', 'A', '2341')
    insert into Employee (Id, Company, Salary) values ('2', 'A', '341')
    insert into Employee (Id, Company, Salary) values ('3', 'A', '15')
    insert into Employee (Id, Company, Salary) values ('4', 'A', '15314')
    insert into Employee (Id, Company, Salary) values ('5', 'A', '451')
    insert into Employee (Id, Company, Salary) values ('6', 'A', '513')
    insert into Employee (Id, Company, Salary) values ('7', 'B', '15')
    insert into Employee (Id, Company, Salary) values ('8', 'B', '13')
    insert into Employee (Id, Company, Salary) values ('9', 'B', '1154')
    insert into Employee (Id, Company, Salary) values ('10', 'B', '1345')
    insert into Employee (Id, Company, Salary) values ('11', 'B', '1221')
    insert into Employee (Id, Company, Salary) values ('12', 'B', '234')
    insert into Employee (Id, Company, Salary) values ('13', 'C', '2345')
    insert into Employee (Id, Company, Salary) values ('14', 'C', '2645')
    insert into Employee (Id, Company, Salary) values ('15', 'C', '2645')
    insert into Employee (Id, Company, Salary) values ('16', 'C', '2652')
    insert into Employee (Id, Company, Salary) values ('17', 'C', '65')

Table: `Employee` 包含所有员工。Employee 表有三列：员工Id，公司名和薪水。

+-----+------------+--------+
    |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     |
    +-----+------------+--------+

## 569. 员工薪水中位数（困难） ##

需求：请编写SQL查询来查找每个公司的薪水中位数。

挑战点：你是否可以在不使用任何内置的SQL函数的情况下解决此问题。

查询结果格式如下：

+-----+------------+--------+
    |Id   | Company    | Salary |
    +-----+------------+--------+
    |5    | A          | 451    |
    |6    | A          | 513    |
    |12   | B          | 234    |
    |9    | B          | 1154   |
    |14   | C          | 2645   |
    +-----+------------+--------+

中位数的定义

总数为奇数：中位数，等于排序后，中间的那个数值，即大于这个数的数值个数 等于 小于这个数的数值个数。

总数为偶数：中位数，等于排序后，中间的那两个数的平均值。

总的来说，无论总数是奇还是偶，也不管元素是否唯一，中位数出现的频率一定大于等于大于它的数和小于它的数的绝对值之差，即

小于等于中位数的累计频数 要小于等于 总频数的一半 且  
大于等于中位数的累计频数 要小于等于 总频数的一半

中位数，主要分两种情况：
    1、若总数为奇数，则中位数就是排序后，中间的那个值；
    			   例如总数为5，中位数为第3个，即索引为2的那个。
    			   (5+1)/2向下取整为3，(5+2)/2向下取整还是3
    2、若总数为偶数，则中位数就是排序后，中间的那两个值；
    			   例如总数为6，中位数为第3、4个，即索引为2和3的那两个。
    			   (6+1)/2向下取整为3，(6+2)/2向下取整还是4
    -- 解法一：直接用窗口函数，按Company分区，求出总个数和行号，再子查询过滤出中位数
    SELECT
    	Id,
    	Company,
    	Salary
    FROM
    	(
    		SELECT
    			Id,
    			Company,
    			Salary,
    			ROW_NUMBER () over (PARTITION BY Company ORDER BY Salary) AS rowk,
    			COUNT(Id) over (PARTITION BY Company) AS cnt
    		FROM
    			Employee
    	) AS t1
    WHERE
    	rowk IN (FLOOR((cnt + 1) / 2), FLOOR((cnt + 2) / 2))

# SQL结构： #

Create table If Not Exists Numbers (Number int, Frequency int)
    Truncate table Numbers
    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')

Table: `Numbers` ，Numbers 表保存数字的值及其频率。

+----------+-------------+
    |  Number  |  Frequency  |
    +----------+-------------|
    |  0       |  7          |
    |  1       |  1          |
    |  2       |  3          |
    |  3       |  1          |
    +----------+-------------+
    在此表中，数字为 0, 0, 0, 0, 0, 0, 0, 1, 2, 2, 2, 3，所以中位数是 (0 + 0) / 2 = 0。

## 571. 给定数字的频率查询中位数（困难） ##

需求：请编写一个查询来查找所有数字的中位数并将结果命名为 median 。

查询结果格式如下：

+--------+
    | median |
    +--------|
    | 0.0000 |
    +--------+

求中位数：
    小于等于中位数的累计频数 要小于等于 总频数的一半 且
    大于等于中位数的累计频数 要小于等于 总频数的一半
    -- 解法一：先用窗口函数，求出正序和反序频数（上无边界到当前行），再根据上面的条件进行过滤
    SELECT
    	ROUND(AVG(number), 4) AS median
    FROM
    	(
    		SELECT
    			number,
    			SUM(frequency) over (ORDER BY number) AS asc_accumulate,
    			SUM(frequency) over (ORDER BY number DESC) AS desc_accumulate
    		FROM
    			Numbers
    	) AS t1,
    	(
    		SELECT
    			sum(frequency) total
    		FROM
    			Numbers
    	) AS t2
    WHERE
    	t1.asc_accumulate >= (t2.total / 2) AND t1.desc_accumulate >= (t2.total / 2)