【每日SQL打卡】​​​​​​​​​​​​​​​DAY 4丨员工薪水中位数【难度困难】

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

请编写SQL查询来查找每个公司的薪水中位数。
挑战点：你是否可以在不使用任何内置的SQL函数的情况下解决此问题。

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

从中位数的计算方式入手

按公司分组 薪水排序，总数C 为奇数 ，序号rk=(C+1）/2 ,总数为偶数，序号rk为 C/2 和C/2+1，而 C/2 <(C+1）/2<C/2+1 可合一简化

通过窗口函数得出 C和rk即可

select id,company,salary
FROM
	(
	SELECT
		id,
		company,
		salary,
		row_number ( ) over ( PARTITION BY company ORDER BY salary ) AS rk,
		COUNT( * ) over ( PARTITION BY company ) AS cnt 
	FROM
		employee 
	) t 
WHERE
	rk BETWEEN ( cnt / 2 ) 	AND ( cnt  / 2 + 1 )