体育馆的人流量

案例

表：

1
Stadium
1
2
3
4
5
6
7
8
9
10
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| visit_date    | date    |
| people        | int     |
+---------------+---------+
visit_date 是表的主键
每日人流量信息被记录在这三列信息中：序号 (id)、日期 (visit_date)、 人流量 (people)
每天只有一行记录，日期随着 id 的增加而增加

编写一个 SQL 查询以找出每行的人数大于或等于 100 且 id 连续的三行或更多行记录。

返回按 visit_date 升序排列 的结果表。

查询结果格式如下所示。

示例 1:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
输入：
Stadium 表:
+------+------------+-----------+
| id   | visit_date | people    |
+------+------------+-----------+
| 1    | 2017-01-01 | 10        |
| 2    | 2017-01-02 | 109       |
| 3    | 2017-01-03 | 150       |
| 4    | 2017-01-04 | 99        |
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-09 | 188       |
+------+------------+-----------+
输出：
+------+------------+-----------+
| id   | visit_date | people    |
+------+------------+-----------+
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-09 | 188       |
+------+------------+-----------+
解释：
id 为 5、6、7、8 的四行 id 连续，并且每行都有 >= 100 的人数记录。
请注意，即使第 7 行和第 8 行的 visit_date 不是连续的，输出也应当包含第 8 行，因为我们只需要考虑 id 连续的记录。
不输出 id 为 2 和 3 的行，因为至少需要三条 id 连续的记录。

解答(图解)

pPFe1H0.png

1
2
3
4
5
6
7
8
9
10
11
12
13
with tmp as (
    select
        *,
        id - row_number() over(order by id) as rk
    from Stadium where people >= 100
)
select
    id, visit_date, people
from tmp where rk in (
    select 
        rk
    from tmp group by rk having count(id) >= 3 
)

好友申请 II ：谁有最多的好友

案例

在 Facebook 或者 Twitter 这样的社交应用中，人们经常会发好友申请也会收到其他人的好友申请。

RequestAccepted 表：

1
2
3
4
5
6
7
8
9
+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| requester_id   | int     |
| accepter_id    | int     |
| accept_date    | date    |
+----------------+---------+
(requester_id, accepter_id) 是这张表的主键。
这张表包含发送好友请求的人的 ID ，接收好友请求的人的 ID ，以及好友请求通过的日期。

写一个查询语句，找出拥有最多的好友的人和他拥有的好友数目。

生成的测试用例保证拥有最多好友数目的只有 1 个人。

查询结果格式如下例所示。

示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
输入：
RequestAccepted 表：
+--------------+-------------+-------------+
| requester_id | accepter_id | accept_date |
+--------------+-------------+-------------+
| 1            | 2           | 2016/06/03  |
| 1            | 3           | 2016/06/08  |
| 2            | 3           | 2016/06/08  |
| 3            | 4           | 2016/06/09  |
+--------------+-------------+-------------+
输出：
+----+-----+
| id | num |
+----+-----+
| 3  | 3   |
+----+-----+
解释：
编号为 3 的人是编号为 1 ，2 和 4 的人的好友，所以他总共有 3 个好友，比其他人都多

解答

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
with tmp as (
    (select
    requester_id as id,
    count(*) as cnt
from RequestAccepted group by requester_id
)
union all
(
    select
        accepter_id as id,
        count(*) as cnt
    from RequestAccepted group by accepter_id
)
)
select
    id,
    sum(cnt) as num
from tmp group by id order by num desc limit 1

树节点

案例

给定一个表 tree，id 是树节点的编号， p_id 是它父节点的 id 。

1
2
3
4
5
6
7
8
9
+----+------+
| id | p_id |
+----+------+
| 1  | null |
| 2  | 1    |
| 3  | 1    |
| 4  | 2    |
| 5  | 2    |
+----+------+

树中每个节点属于以下三种类型之一：

  • 叶子：如果这个节点没有任何孩子节点。
  • 根：如果这个节点是整棵树的根，即没有父节点。
  • 内部节点：如果这个节点既不是叶子节点也不是根节点。

写一个查询语句，输出所有节点的编号和节点的类型，并将结果按照节点编号排序。上面样例的结果为：

1
2
3
4
5
6
7
8
9
+----+------+
| id | Type |
+----+------+
| 1  | Root |
| 2  | Inner|
| 3  | Leaf |
| 4  | Leaf |
| 5  | Leaf |
+----+------+

解释

  • 节点 ‘1’ 是根节点，因为它的父节点是 NULL ，同时它有孩子节点 ‘2’ 和 ‘3’ 。

  • 节点 ‘2’ 是内部节点，因为它有父节点 ‘1’ ，也有孩子节点 ‘4’ 和 ‘5’ 。

  • 节点 ‘3’, ‘4’ 和 ‘5’ 都是叶子节点，因为它们都有父节点同时没有孩子节点。

  • 样例中树的形态如下：

    1
    2
    3
    4
    5
      1
    /   \
                       2       3
                     /   \
                   4       5

解答

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select
    id,
    (
        case 
            when p_id is null then 'Root'
            when id not in (
                select
                    p_id
                from tree
                where p_id is not null
            )
            then 'Leaf'
            else 'Inner'
    end 
    )as Type
from tree