力扣(LeetCode)数据库最新44题190526Oracle版(2)

排版好有颜色的文章在公众号“八哥的成长心路札记”上有，微信号是bager1912。

以下题目均来自力扣（LeetCode）官网和其他网站，仅用作数据库爱好者学习交流，严禁进行商业及任何非法用途。

 

570. 至少有5名直接下属的经理

Employee 表包含所有员工和他们的经理。每个员工都有一个 Id，并且还有一列是经理的 Id。

+------+----------+-----------+----------+
|Id    |Name 	  |Department |ManagerId |
+------+----------+-----------+----------+
|101   |John 	  |A 	      |null      |
|102   |Dan 	  |A 	      |101       |
|103   |James 	  |A 	      |101       |
|104   |Amy 	  |A 	      |101       |
|105   |Anne 	  |A 	      |101       |
|106   |Ron 	  |B 	      |101       |
+------+----------+-----------+----------+

给定 Employee 表，请编写一个SQL查询来查找至少有5名直接下属的经理。对于上表，您的SQL查询应该返回：

+-------+
| Name  |
+-------+
| John  |
+-------+

注意:
没有人是自己的下属。

select a.name "Name"
from employee a,employee b
where a.id = b.managerid
group by a.name
having count(*)>=5
;

 

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

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。

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

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

这道题参考了两个链接，大家也可以看看：

http://bookshadow.com/weblog/2017/05/01/leetcode-find-median-given-frequency-of-numbers/

https://blog.csdn.net/lv941002/article/details/84619808

 

八哥整了蛮久才算比较清楚的理解：

select avg(num) median
from (select num,frequency,accfreq,sumfreq
      from (select num,frequency,sum(frequency) over(order by num rows between unbounded preceding and current row) accfreq
            from numbers),(select sum(frequency) sumfreq
                           from numbers))
where accfreq between sumfreq/2 and sumfreq/2+frequency;

 

因为number是关键字，无法运行，所以替换成num。

 

第一个链接是这样描述思路的：

构造中间表t，包含列Number, Frequency, AccFreq（累积频率）, SumFreq（频率求和）

例如样例数据得到的中间表结果为

+----------+-------------+-----------+----------+
|  Number  |  Frequency  |  AccFreq  | SumFreq  |
+----------+-------------|-----------|----------|
|  0       |  7          |  7        |  12      |
|  1       |  1          |  8        |  12      |
|  2       |  3          |  11       |  12      |
|  3       |  1          |  12       |  12      |
+----------+-------------+-----------+----------+

AccFreq范围在[SumFreq / 2, SumFreq / 2 + Frequency]的Number均值即为答案。

AccFreq范围的推导过程如下：

AccFreq BETWEEN SumFreq / 2 AND SumFreq / 2 + 1 OR AccFreq - Frequency <= SumFreq / 2 AND AccFreq > SumFreq / 2 + 1

上式含义为：

AccFreq本身介于[SumFreq / 2, SumFreq / 2 + 1]之间

或者上一行的AccFreq <= SumFreq / 2 并且 当前行的AccFreq > SumFreq / 2 + 1

两种情况合并即可得到AccFreq的范围：

AccFreq BETWEEN SumFreq / 2 AND SumFreq / 2 + Frequency

我的理解是：中位数的分布在累积频率中有两种情况，一种是不跨段的，即总数为奇数时的中位数只有一个在一个累积频率中，总数为偶数时的中位数同时在一个累积频率中的情况，比如本题的两个中位数是第6,7位均在第一个累积频率7中；另一种是跨段的，就是总数为偶数时的中位数分布在两个累积频率中，比如中位数是第8,9位，那么分布在8和11两个累积频率中。

 

我用plsql developer实验了一把：

 

让我们换一个角度理解：中位数要么是单独一个，要么是连续的两个再除以2，整体思路就是通过建立累加值accfreq，锁定累加值的范围来定位中位数num。所以对于只有一个数的时候，它只在上图中的一行中， 满足这个就行：

AccFreq BETWEEN SumFreq / 2 AND SumFreq / 2 + 1

对于两个数的情况，在同一行时，也只需满足上述条件即可；而不在同一行，则必定在连续的两行，所以可能的情况是1,2行，2,3行，3,4行，但中位数只能在其中之一，于是对其进行筛选，以当前行为基准，

上一行的AccFreq <= SumFreq / 2 并且 当前行的AccFreq > SumFreq / 2 + 1

也即是

AccFreq - Frequency <= SumFreq / 2 AND AccFreq > SumFreq / 2 + 1

说实话这个即是整道题的算法精髓了，八哥也不知道原作者是怎么想到的，但是做得多了，练得多了，该有的算法也就出来了，需要熟练掌握很多相关知识，我们的路还长着呢。

有了这两个区间范围，再利用中学数学知识合并一下：

就有了最终范围：

AccFreq BETWEEN SumFreq / 2 AND SumFreq / 2 + Frequency

其实吧，到这里，八哥发现avg只在中位数是跨段的情况下有用，而在同一端的时候avg没啥用。比如在同一段，比如都是0，那么其实最后是只有一个num到最外层的select，求avg(0)，比如都是1，也是只有一个1到最外层的select，求avg(1)。 跨段的时候，会有两个数到最外层select，比如1,2，然后求avg。

 

第二个链接是用oracle中的分析函数over()来得到累加值。