LeetCode数据库SQL练习（十四）

• 练习题目来自：leetcode-cn.com/

每台机器的进程平均运行时间

表: Activity

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| machine_id     | int     |
| process_id     | int     |
| activity_type  | enum    |
| timestamp      | float   |
+----------------+---------+
该表展示了一家工厂网站的用户活动.
(machine_id, process_id, activity_type) 是当前表的主键.
machine_id 是一台机器的ID号.
process_id 是运行在各机器上的进程ID号.
activity_type 是枚举类型 ('start', 'end').
timestamp 是浮点类型,代表当前时间(以秒为单位).
'start' 代表该进程在这台机器上的开始运行时间戳 , 'end' 代表该进程在这台机器上的终止运行时间戳.
同一台机器，同一个进程都有一对开始时间戳和结束时间戳，而且开始时间戳永远在结束时间戳前面.

现在有一个工厂网站由几台机器运行，每台机器上运行着相同数量的进程. 请写出一条SQL计算每台机器各自完成一个进程任务的平均耗时.
完成一个进程任务的时间指进程的'end' 时间戳 减去'start' 时间戳. 平均耗时通过计算每台机器上所有进程任务的总耗费时间除以机器上的总进程数量获得.
结果表必须包含machine_id（机器ID） 和对应的average time（平均耗时） 别名processing_time, 且四舍五入保留3位小数.

具体参考例子如下:

Activity table:
+------------+------------+---------------+-----------+
| machine_id | process_id | activity_type | timestamp |
+------------+------------+---------------+-----------+
| 0          | 0          | start         | 0.712     |
| 0          | 0          | end           | 1.520     |
| 0          | 1          | start         | 3.140     |
| 0          | 1          | end           | 4.120     |
| 1          | 0          | start         | 0.550     |
| 1          | 0          | end           | 1.550     |
| 1          | 1          | start         | 0.430     |
| 1          | 1          | end           | 1.420     |
| 2          | 0          | start         | 4.100     |
| 2          | 0          | end           | 4.512     |
| 2          | 1          | start         | 2.500     |
| 2          | 1          | end           | 5.000     |
+------------+------------+---------------+-----------+

Result table:
+------------+-----------------+
| machine_id | processing_time |
+------------+-----------------+
| 0          | 0.894           |
| 1          | 0.995           |
| 2          | 1.456           |
+------------+-----------------+

一共有3台机器,每台机器运行着两个进程.
机器 0 的平均耗时: ((1.520 - 0.712) + (4.120 - 3.140)) / 2 = 0.894
机器 1 的平均耗时: ((1.550 - 0.550) + (1.420 - 0.430)) / 2 = 0.995
机器 2 的平均耗时: ((4.512 - 4.100) + (5.000 - 2.500)) / 2 = 1.456

• SQL1：

  • select
    machine_id, round(avg(times) * 2, 3) processing_time
    from
        (select
        machine_id, process_id,
        if(activity_type = 'start', round(-`timestamp`, 3), round(`timestamp`, 3)) times
        from
        Activity) t
    group by machine_id
    

• SQL2：

  • select
    a1.machine_id,
    round(avg(a2.timestamp - a1.timestamp) ,3) processing_time
    from
    Activity a1 join Activity a2
    using(machine_id, process_id)
    where a1.activity_type = 'start' and a2.activity_type = 'end'
    group by a1.machine_id
    

修复表中的名字

表： Users

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| user_id        | int     |
| name           | varchar |
+----------------+---------+
user_id 是该表的主键。
该表包含用户的 ID 和名字。名字仅由小写和大写字符组成。

编写一个 SQL 查询来修复名字，使得只有第一个字符是大写的，其余都是小写的。
返回按 user_id 排序的结果表。

查询结果格式示例如下：

Users table:
+---------+-------+
| user_id | name  |
+---------+-------+
| 1       | aLice |
| 2       | bOB   |
+---------+-------+

Result table:
+---------+-------+
| user_id | name  |
+---------+-------+
| 1       | Alice |
| 2       | Bob   |
+---------+-------+

• 字段截取：

  • 从左向右：
    • left(str, length)
  • 从右向左：
    • right(str, length)
  • 指定位置：
    • substring(str, pos)
    • substring(str, pos, len)
    • pos 可以是负值，但 len 不能取负值
  • 自定义：
    • substring_index(input, split, index)
    • input为要截取的字符，split为分隔符，Index为要截取第index个分隔符左（index为正）或右(index为负)的字符串。

• SQL：

  • select
    user_id,
    concat(upper(left(name, 1)), lower(substring(name, 2))) name
    from
    Users
    order by user_id
    

发票中的产品金额

Product 表：

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| product_id  | int     |
| name        | varchar |
+-------------+---------+
product_id 是这张表的主键
表中含有产品 id 、产品名称。产品名称都是小写的英文字母，产品名称都是唯一的

Invoice 表：

+-------------+------+
| Column Name | Type |
+-------------+------+
| invoice_id  | int  |
| product_id  | int  |
| rest        | int  |
| paid        | int  |
| canceled    | int  |
| refunded    | int  |
+-------------+------+
invoice_id 发票 id ，是这张表的主键
product_id 产品 id
rest 应缴款项
paid 已支付金额
canceled 已取消金额
refunded 已退款金额

要求写一个SQL查询，返回全部发票中每个产品的产品名称、总应缴款项、总已支付金额、总已取消金额、总已退款金额。
查询结果按 product_name排序。

示例：

Product 表：
+------------+-------+
| product_id | name  |
+------------+-------+
| 0          | ham   |
| 1          | bacon |
+------------+-------+

Invoice 表:
+------------+------------+------+------+----------+----------+
| invoice_id | product_id | rest | paid | canceled | refunded |
+------------+------------+------+------+----------+----------+
| 23         | 0          | 2    | 0    | 5        | 0        |
| 12         | 0          | 0    | 4    | 0        | 3        |
| 1          | 1          | 1    | 1    | 0        | 1        |
| 2          | 1          | 1    | 0    | 1        | 1        |
| 3          | 1          | 0    | 1    | 1        | 1        |
| 4          | 1          | 1    | 1    | 1        | 0        |
+------------+------------+------+------+----------+----------+

Result 表：
+-------+------+------+----------+----------+
| name  | rest | paid | canceled | refunded |
+-------+------+------+----------+----------+
| bacon | 3    | 3    | 3        | 3        |
| ham   | 2    | 4    | 5        | 3        |
+-------+------+------+----------+----------+

- bacon 的总应缴款项为 1 + 1 + 0 + 1 = 3
- bacon 的总已支付金额为 1 + 0 + 1 + 1 = 3
- bacon 的总已取消金额为 0 + 1 + 1 + 1 = 3
- bacon 的总已退款金额为 1 + 1 + 1 + 0 = 3
- ham 的总应缴款项为 2 + 0 = 2
- ham 的总已支付金额为 0 + 4 = 4
- ham 的总已取消金额为 5 + 0 = 5
- ham 的总已退款金额为 0 + 3 = 3

• Product表中的产品，不一定在Invoice表中

• SQL1：

  • select
    p.name, ifnull(t.rest, 0) rest, ifnull(t.paid, 0) paid, ifnull(t.canceled, 0) canceled, ifnull(t.refunded, 0) refunded
    from
    (select
    product_id, sum(rest) rest, sum(paid) paid, sum(canceled) canceled, sum(refunded) refunded
    from
    Invoice
    group by product_id) t
    right join
    Product p
    using(product_id)
    order by name
    

• SQL2：

  • select
    (select
    name
    from
    Product
    where product_id = i.product_id
    limit 1) name,
    sum(i.rest) rest, sum(i.paid ) paid, sum(i.canceled ) canceled, sum(i.refunded ) refunded
    from
    Invoice i
    group by i.product_id
    union all
    select
    name name,
    0 rest,
    0 paid,
    0 canceled,
    0 refunded
    from
    Product
    where product_id not in (select
                            product_id
                            from
                            Invoice)
    order by name
    

无效的推文

表：Tweets

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| tweet_id       | int     |
| content        | varchar |
+----------------+---------+
tweet_id 是这个表的主键。
这个表包含某社交媒体 App 中所有的推文。

写一条SQL 语句，查询所有无效推文的编号（ID）。当推文内容中的字符数严格大于 15 时，该推文是无效的。
以任意顺序返回结果表。

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

Tweets 表：
+----------+----------------------------------+
| tweet_id | content                          |
+----------+----------------------------------+
| 1        | Vote for Biden                   |
| 2        | Let us make America great again! |
+----------+----------------------------------+

结果表：
+----------+
| tweet_id |
+----------+
| 2        |
+----------+
推文 1 的长度 length = 14。该推文是有效的。
推文 2 的长度 length = 32。该推文是无效的。

• 统计长度：

  • char_length(str)
    • 计算单位：字符
    • 不管汉字还是数字或者是字母都算是一个字符
  • length(str)
    • 计算单位：字节
    • utf8编码：一个汉字三个字节，一个数字或字母一个字节。
    • gbk编码：一个汉字两个字节，一个数字或字母一个字节。

• SQL：

  • select
    tweet_id
    from
    Tweets
    where char_length(content) > 15
    

每天的领导和合伙人

表：DailySales

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| date_id     | date    |
| make_name   | varchar |
| lead_id     | int     |
| partner_id  | int     |
+-------------+---------+
该表没有主键。
该表包含日期、产品的名称，以及售给的领导和合伙人的编号。
名称只包含小写英文字母。

写一条 SQL 语句，使得对于每一个date_id和make_name，返回不同的lead_id以及不同的partner_id的数量。
按任意顺序返回结果表。

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

DailySales 表：
+-----------+-----------+---------+------------+
| date_id   | make_name | lead_id | partner_id |
+-----------+-----------+---------+------------+
| 2020-12-8 | toyota    | 0       | 1          |
| 2020-12-8 | toyota    | 1       | 0          |
| 2020-12-8 | toyota    | 1       | 2          |
| 2020-12-7 | toyota    | 0       | 2          |
| 2020-12-7 | toyota    | 0       | 1          |
| 2020-12-8 | honda     | 1       | 2          |
| 2020-12-8 | honda     | 2       | 1          |
| 2020-12-7 | honda     | 0       | 1          |
| 2020-12-7 | honda     | 1       | 2          |
| 2020-12-7 | honda     | 2       | 1          |
+-----------+-----------+---------+------------+

结果表：
+-----------+-----------+--------------+-----------------+
| date_id   | make_name | unique_leads | unique_partners |
+-----------+-----------+--------------+-----------------+
| 2020-12-8 | toyota    | 2            | 3               |
| 2020-12-7 | toyota    | 1            | 2               |
| 2020-12-8 | honda     | 2            | 2               |
| 2020-12-7 | honda     | 3            | 2               |
+-----------+-----------+--------------+-----------------+

在 2020-12-8，丰田（toyota）有领导者 = [0, 1] 和合伙人 = [0, 1, 2] ，同时本田（honda）有领导者 = [1, 2] 和合伙人 = [1, 2]。
在 2020-12-7，丰田（toyota）有领导者 = [0] 和合伙人 = [1, 2] ，同时本田（honda）有领导者 = [0, 1, 2] 和合伙人 = [1, 2]。

• 注意去重

• SQL：

  • select
    date_id, make_name, count(distinct lead_id) unique_leads, count(distinct partner_id) unique_partners
    from
    DailySales
    group by date_id, make_name
    

两人之间的通话次数

表：Calls

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| from_id     | int     |
| to_id       | int     |
| duration    | int     |
+-------------+---------+
该表没有主键，可能存在重复项。
该表包含 from_id 与 to_id 间的一次电话的时长。
from_id != to_id

编写 SQL 语句，查询每一对用户(person1, person2)之间的通话次数和通话总时长，其中person1 < person2。
以任意顺序返回结果表。

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

Calls 表：
+---------+-------+----------+
| from_id | to_id | duration |
+---------+-------+----------+
| 1       | 2     | 59       |
| 2       | 1     | 11       |
| 1       | 3     | 20       |
| 3       | 4     | 100      |
| 3       | 4     | 200      |
| 3       | 4     | 200      |
| 4       | 3     | 499      |
+---------+-------+----------+

结果表：
+---------+---------+------------+----------------+
| person1 | person2 | call_count | total_duration |
+---------+---------+------------+----------------+
| 1       | 2       | 2          | 70             |
| 1       | 3       | 1          | 20             |
| 3       | 4       | 4          | 999            |
+---------+---------+------------+----------------+

用户 1 和 2 打过 2 次电话，总时长为 70 (59 + 11)。
用户 1 和 3 打过 1 次电话，总时长为 20。
用户 3 和 4 打过 4 次电话，总时长为 999 (100 + 200 + 200 + 499)。

• greatest(value1,value2,...)：

  • 用于得到value1,value2,...中的最大值，value1,value2,...可以是数值也可以是时间

• least(value1,value2,...)：

  • 用于得到value1,value2,...中的最小值，value1,value2,...可以是数值也可以是时间

• SQL1：

  • 合并查询

  • select
    person1, person2, count(duration) call_count, sum(duration) total_duration
    from
    (select
    from_id person1, to_id person2, duration
    from
    Calls
    where from_id < to_id
    union all
    select
    to_id person1, from_id person2, duration
    from
    Calls
    where from_id > to_id) t
    group by person1, person2
    

• SQL2：

  • 子查询

  • select
    person1, person2, count(*) call_count, sum(duration) total_duration
    from
        (select
        if(from_id > to_id, to_id, from_id) person1,
        if(from_id > to_id, from_id, to_id) person2,
        duration
        from
        Calls) t
    group by person1, person2
    

• SQL3：

  • select
    from_id person1, to_id person2, count(*) call_count, sum(duration) total_duration
    from
    Calls
    group by least(from_id, to_id), greatest(from_id, to_id)
    

访问日期之间最大的空档期

表：UserVisits

+-------------+------+
| Column Name | Type |
+-------------+------+
| user_id     | int  |
| visit_date  | date |
+-------------+------+
该表没有主键。
该表包含用户访问某特定零售商的日期日志。

假设今天的日期是'2021-1-1'。
编写 SQL 语句，对于每个user_id，求出每次访问及其下一个访问（若该次访问是最后一次，则为今天）之间最大的空档期天数window。
返回结果表，按用户编号user_id排序。

查询格式如下示例所示：

UserVisits 表：
+---------+------------+
| user_id | visit_date |
+---------+------------+
| 1       | 2020-11-28 |
| 1       | 2020-10-20 |
| 1       | 2020-12-3  |
| 2       | 2020-10-5  |
| 2       | 2020-12-9  |
| 3       | 2020-11-11 |
+---------+------------+
结果表：
+---------+---------------+
| user_id | biggest_window|
+---------+---------------+
| 1       | 39            |
| 2       | 65            |
| 3       | 51            |
+---------+---------------+
对于第一个用户，问题中的空档期在以下日期之间：
    - 2020-10-20 至 2020-11-28 ，共计 39 天。
    - 2020-11-28 至 2020-12-3 ，共计 5 天。
    - 2020-12-3 至 2021-1-1 ，共计 29 天。
由此得出，最大的空档期为 39 天。
对于第二个用户，问题中的空档期在以下日期之间：
    - 2020-10-5 至 2020-12-9 ，共计 65 天。
    - 2020-12-9 至 2021-1-1 ，共计 23 天。
由此得出，最大的空档期为 65 天。
对于第三个用户，问题中的唯一空档期在 2020-11-11 至 2021-1-1 之间，共计 51 天。

• SQL1：

  • select
    user_id, max(datediff(last, visit_date)) biggest_window
    from
        (select
        user_id, visit_date, lead(visit_date, 1, '2021-1-1') over(partition by user_id order by visit_date) last
        from
        UserVisits) t
    group by user_id
    order by user_id
    

• SQL2：

  • with t as(
        select
        a.user_id, a.visit_date visit_date1, ifnull(b.visit_date, "2021-1-1") visit_date2, datediff(ifnull(b.visit_date, "2021-1-1"), a.visit_date) gap
        from
            ((select
            *
            from
            UserVisits
            order by user_id, visit_date) a
            left join
            (select
            *
            from
            UserVisits
            order by user_id, visit_date) b
            on a.user_id = b.user_id and a.visit_date < b.visit_date))
    select
    user_id, max(min_gap) biggest_window
    from
        (select
        user_id, visit_date1, min(gap) min_gap
        from
        t
        group by user_id, visit_date1) t1
    group by user_id
    

苹果和橘子的个数

表：Boxes

+--------------+------+
| Column Name  | Type |
+--------------+------+
| box_id       | int  |
| chest_id     | int  |
| apple_count  | int  |
| orange_count | int  |
+--------------+------+
box_id 是该表的主键。
chest_id 是 chests 表的外键。
该表包含大箱子 (box) 中包含的苹果和橘子的个数。每个大箱子中可能包含一个小盒子 (chest) ，小盒子中也包含若干苹果和橘子。

表：Chests

+--------------+------+
| Column Name  | Type |
+--------------+------+
| chest_id     | int  |
| apple_count  | int  |
| orange_count | int  |
+--------------+------+
chest_id 是该表的主键。
该表包含小盒子的信息，以及小盒子中包含的苹果和橘子的个数。

编写 SQL 语句，查询每个大箱子中苹果和橘子的个数。如果大箱子中包含小盒子，还应当包含小盒子中苹果和橘子的个数。
以任意顺序返回结果表。

查询结果的格式如下示例所示：

Boxes 表：
+--------+----------+-------------+--------------+
| box_id | chest_id | apple_count | orange_count |
+--------+----------+-------------+--------------+
| 2      | null     | 6           | 15           |
| 18     | 14       | 4           | 15           |
| 19     | 3        | 8           | 4            |
| 12     | 2        | 19          | 20           |
| 20     | 6        | 12          | 9            |
| 8      | 6        | 9           | 9            |
| 3      | 14       | 16          | 7            |
+--------+----------+-------------+--------------+

Chests 表：
+----------+-------------+--------------+
| chest_id | apple_count | orange_count |
+----------+-------------+--------------+
| 6        | 5           | 6            |
| 14       | 20          | 10           |
| 2        | 8           | 8            |
| 3        | 19          | 4            |
| 16       | 19          | 19           |
+----------+-------------+--------------+

结果表：
+-------------+--------------+
| apple_count | orange_count |
+-------------+--------------+
| 151         | 123          |
+-------------+--------------+

大箱子 2 中有 6 个苹果和 15 个橘子。
大箱子 18 中有 4 + 20 (在小盒子中) = 24 个苹果和 15 + 10 (在小盒子中) = 25 个橘子。
大箱子 19 中有 8 + 19 (在小盒子中) = 27 个苹果和 4 + 4 (在小盒子中) = 8 个橘子。
大箱子 12 中有 19 + 8 (在小盒子中) = 27 个苹果和 20 + 8 (在小盒子中) = 28 个橘子。
大箱子 20 中有 12 + 5 (在小盒子中) = 17 个苹果和 9 + 6 (在小盒子中) = 15 个橘子。
大箱子 8 中有 9 + 5 (在小盒子中) = 14 个苹果和 9 + 6 (在小盒子中) = 15 个橘子。
大箱子 3 中有 16 + 20 (在小盒子中) = 36 个苹果和 7 + 10 (在小盒子中) = 17 个橘子。
苹果的总个数 = 6 + 24 + 27 + 27 + 17 + 14 + 36 = 151
橘子的总个数 = 15 + 25 + 8 + 28 + 15 + 15 + 17 = 123

• SQL1：

  • select
    sum(a_cnt1 + a_cnt2) apple_count, sum(o_cnt1 + o_cnt2) orange_count
    from
        (select
        b.box_id, b.apple_count a_cnt1, b.orange_count o_cnt1, ifnull(c.apple_count, 0) a_cnt2, ifnull(c.orange_count, 0) o_cnt2
        from
        Boxes b left join Chests c
        using(chest_id)) t
    

• SQL2：

  • select
    sum(apple_count) apple_count, sum(orange_count) orange_count
    from
        ((select
        box_id, apple_count, orange_count
        from Boxes)
        union all
        (select b.box_id, c.apple_count, c.orange_count
        from Boxes b left join Chests c
        using(`chest_id`)
        where b.`box_id` is not null)) a
    

求关注者的数量

表：Followers

+-------------+------+
| Column Name | Type |
+-------------+------+
| user_id     | int  |
| follower_id | int  |
+-------------+------+
(user_id, follower_id) 是这个表的主键。
该表包含一个关注关系中关注者和用户的编号，其中关注者关注用户。

写出 SQL 语句，对于每一个用户，返回该用户的关注者数量。
按user_id的顺序返回结果表。

查询结果的格式如下示例所示：

Followers 表：
+---------+-------------+
| user_id | follower_id |
+---------+-------------+
| 0       | 1           |
| 1       | 0           |
| 2       | 0           |
| 2       | 1           |
+---------+-------------+

结果表：
+---------+----------------+
| user_id | followers_count|
+---------+----------------+
| 0       | 1              |
| 1       | 1              |
| 2       | 2              |
+---------+----------------+

0 的关注者有 {1}
1 的关注者有 {0}
2 的关注者有 {0,1}

• SQL：

  • select
    user_id, count(follower_id) followers_count
    from
    Followers
    group by user_id
    order by user_id
    

可回收且低脂的产品

表：Products

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| product_id  | int     |
| low_fats    | enum    |
| recyclable  | enum    |
+-------------+---------+
product_id 是这个表的主键。
low_fats 是枚举类型，取值为以下两种 ('Y', 'N')，其中 'Y' 表示该产品是低脂产品，'N' 表示不是低脂产品。
recyclable 是枚举类型，取值为以下两种 ('Y', 'N')，其中 'Y' 表示该产品可回收，而 'N' 表示不可回收。

写出 SQL 语句，查找既是低脂又是可回收的产品编号。
返回结果 无顺序要求 。

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

Products 表：
+-------------+----------+------------+
| product_id  | low_fats | recyclable |
+-------------+----------+------------+
| 0           | Y        | N          |
| 1           | Y        | Y          |
| 2           | N        | Y          |
| 3           | Y        | Y          |
| 4           | N        | N          |
+-------------+----------+------------+

Result 表：
+-------------+
| product_id  |
+-------------+
| 1           |
| 3           |
+-------------+

只有产品 id 为 1 和 3 的产品，既是低脂又是可回收的产品。

• SQL：

  • select
    product_id
    from
    Products
    where low_fats = 'Y' and recyclable = 'Y'
    

每家商店的产品价格

表：Products

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| product_id  | int     |
| store       | enum    |
| price       | int     |
+-------------+---------+
(product_id,store) 是这个表的主键。
store 字段是枚举类型，它的取值为以下三种 ('store1', 'store2', 'store3') 。
price 是该商品在这家商店中的价格。

写出一个 SQL 查询语句，查找每种产品在各个商店中的价格。
可以以 任何顺序 输出结果。

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

Products 表：
+-------------+--------+-------+
| product_id  | store  | price |
+-------------+--------+-------+
| 0           | store1 | 95    |
| 0           | store3 | 105   |
| 0           | store2 | 100   |
| 1           | store1 | 70    |
| 1           | store3 | 80    |
+-------------+--------+-------+

Result 表：
+-------------+--------+--------+--------+
| product_id  | store1 | store2 | store3 |
+-------------+--------+--------+--------+
| 0           | 95     | 100    | 105    |
| 1           | 70     | null   | 80     |
+-------------+--------+--------+--------+

产品 0 的价格在商店 1 为 95 ，商店 2 为 100 ，商店 3 为 105 。
产品 1 的价格在商店 1 为 70 ，商店 3 的产品 1 价格为 80 ，但在商店 2 中没有销售。

• SQL：

  • select
    product_id,
    max(if(store = 'store1', price, null)) store1,
    max(if(store = 'store2', price, null)) store2,
    max(if(store = 'store3', price, null)) store3
    from
    Products
    group by product_id