leetcode刷题

3.1 力扣之1421-净现值查询

3.1.1 说明
表: NPV
±--------------±--------+
| Column Name | Type |
±--------------±--------+
| id | int |
| year | int |
| npv | int |
±--------------±--------+
(id, year) 是该表主键(具有唯一值的列的组合).
该表有每一笔存货的年份, id 和对应净现值的信息.
表: Queries
±--------------±--------+
| Column Name | Type |
±--------------±--------+
| id | int |
| year | int |
±--------------±--------+
(id, year) 是该表主键(具有唯一值的列的组合).
该表有每一次查询所对应存货的 id 和年份的信息.

编写解决方案,找到 Queries 表中每一次查询的净现值。
结果表 没有顺序要求 。
查询结果的格式如下所示:
示例 1:
输入:
NPV 表:
±-----±-------±-------+
| id | year | npv |
±-----±-------±-------+
| 1 | 2018 | 100 |
| 7 | 2020 | 30 |
| 13 | 2019 | 40 |
| 1 | 2019 | 113 |
| 2 | 2008 | 121 |
| 3 | 2009 | 12 |
| 11 | 2020 | 99 |
| 7 | 2019 | 0 |
±-----±-------±-------+
Queries 表:
±-----±-------+
| id | year |
±-----±-------+
| 1 | 2019 |
| 2 | 2008 |
| 3 | 2009 |
| 7 | 2018 |
| 7 | 2019 |
| 7 | 2020 |
| 13 | 2019 |
±-----±-------+
输出:
±-----±-------±-------+
| id | year | npv |
±-----±-------±-------+
| 1 | 2019 | 113 |
| 2 | 2008 | 121 |
| 3 | 2009 | 12 |
| 7 | 2018 | 0 |
| 7 | 2019 | 0 |
| 7 | 2020 | 30 |
| 13 | 2019 | 40 |
±-----±-------±-------+
解释:
(7, 2018)的净现值不在 NPV 表中, 我们把它看作是 0.
所有其它查询的净现值都能在 NPV 表中找到.
3.1.2 分析
在这里插入图片描述

3.1.3 实现
3.1.3.1 数据准备

Create Table If Not Exists NPV (id int, year int, npv int);
Create Table If Not Exists Queries (id int, year int);
Truncate table NPV;
insert into NPV (id, year, npv) values ('1', '2018', '100');
insert into NPV (id, year, npv) values ('7', '2020', '30');
insert into NPV (id, year, npv) values ('13', '2019', '40');
insert into NPV (id, year, npv) values ('1', '2019', '113');
insert into NPV (id, year, npv) values ('2', '2008', '121');
insert into NPV (id, year, npv) values ('3', '2009', '21');
insert into NPV (id, year, npv) values ('11', '2020', '99');
insert into NPV (id, year, npv) values ('7', '2019', '0');
Truncate table Queries;
insert into Queries (id, year) values ('1', '2019');
insert into Queries (id, year) values ('2', '2008');
insert into Queries (id, year) values ('3', '2009');
insert into Queries (id, year) values ('7', '2018');
insert into Queries (id, year) values ('7', '2019');
insert into Queries (id, year) values ('7', '2020');
insert into Queries (id, year) values ('13', '2019');

3.1.3.2 实现

select q.id id, q.year year,if(npv is null , 0 , npv) npv from queries q left join npv n ON  q.id = n.id and q.year = n.year;

3.1.4 小结
左连接

3.2 力扣之1435-制作会话柱状图

3.2.1 说明
表:Sessions
±--------------------±--------+
| Column Name | Type |
±--------------------±--------+
| session_id | int |
| duration | int |
±--------------------±--------+
session_id 是该表主键
duration 是用户访问应用的时间, 以秒为单位
你想知道用户在你的 app 上的访问时长情况。因此你决定统计访问时长区间分别为 “[0-5>”,“[5-10>”,“[10-15>” 和 “15 minutes or more” 的会话数量,并以此绘制柱状图。
写一个解决方案来报告 (bin, total) 。
返回结果 无顺序要求 。
结果格式如下所示。
示例 1:
输入:
Sessions 表:
±------------±--------------+
| session_id | duration |
±------------±--------------+
| 1 | 30 |
| 2 | 199 |
| 3 | 299 |
| 4 | 580 |
| 5 | 1000 |
±------------±--------------+
输出:
±-------------±-------------+
| bin | total |
±-------------±-------------+
| [0-5> | 3 |
| [5-10> | 1 |
| [10-15> | 0 |
| 15 or more | 1 |
±-------------±-------------+
解释:
对于 session_id 1,2 和 3 ,它们的访问时间大于等于 0 分钟且小于 5 分钟。
对于 session_id 4,它的访问时间大于等于 5 分钟且小于 10 分钟。
没有会话的访问时间大于等于 10 分钟且小于 15 分钟。
对于 session_id 5, 它的访问时间大于等于 15 分钟。
3.2.2 分析
暂时无法在飞书文档外展示此内容

3.2.3 实现
3.2.3.1 数据准备

Create table If Not Exists Sessions (session_id int, duration int);
Truncate table Sessions;
insert into Sessions (session_id, duration) values ('1', '30');
insert into Sessions (session_id, duration) values ('2', '199');
insert into Sessions (session_id, duration) values ('3', '299');
insert into Sessions (session_id, duration) values ('4', '580');
insert into Sessions (session_id, duration) values ('5', '1000');

3.2.3.2 实现
方法一

with t1 as (select *,
      CASE
            when duration/60>=0 and duration/60< 5  then '[0-5>'
            when duration/60>=5 and duration/60< 10  then '[5-10>'
            when duration/60>=10 and duration/60< 15  then '[10-15>'
        ELSE '15 or more'
    end as bin
from sessions),
    t2 as(
        select '[0-5>' as bin
        union
        select '[5-10>' as bin
        union
        select '[10-15>' as bin
        union
        select '15 or more' as bin
    )
select t2.bin bin ,count(session_id) total from t2 LEFT JOIN t1 on t2.bin=t1.bin group by t2.bin
;

方法二

select '[0-5>' as bin, sum(if(duration/60 < 5,1,0)) as total from Sessions
union all
select '[5-10>' as bin, sum(if(duration/60 >= 5 and duration/60 < 10,1,0)) as total from Sessions
union all
select '[10-15>' as bin, sum(if(duration/60 >= 10 and duration/60 < 15,1,0)) as total from Sessions
union all
select '15 or more' as bin, sum(if(duration/60 >= 15,1,0)) as total from Sessions

3.2.4 小结
Case when then
左连接
union

3.3 力扣之1440-计算布尔表达式的值

3.3.1 说明
表 Variables:
±--------------±--------+
| Column Name | Type |
±--------------±--------+
| name | varchar |
| value | int |
±--------------±--------+
在 SQL 中,name 是该表主键.
该表包含了存储的变量及其对应的值.

表 Expressions:
±--------------±--------+
| Column Name | Type |
±--------------±--------+
| left_operand | varchar |
| operator | enum |
| right_operand | varchar |
±--------------±--------+
在 SQL 中,(left_operand, operator, right_operand) 是该表主键.
该表包含了需要计算的布尔表达式.
operator 是枚举类型, 取值于(‘<’, ‘>’, ‘=’)
left_operand 和 right_operand 的值保证存在于 Variables 表单中.

计算表 Expressions 中的布尔表达式。
返回的结果表 无顺序要求 。
结果格式如下例所示。

示例 1:
输入:
Variables 表:
±-----±------+
| name | value |
±-----±------+
| x | 66 |
| y | 77 |
±-----±------+
Expressions 表:
±-------------±---------±--------------+
| left_operand | operator | right_operand |
±-------------±---------±--------------+
| x | > | y |
| x | < | y |
| x | = | y |
| y | > | x |
| y | < | x |
| x | = | x |
±-------------±---------±--------------+
输出:
±-------------±---------±--------------±------+
| left_operand | operator | right_operand | value |
±-------------±---------±--------------±------+
| x | > | y | false |
| x | < | y | true |
| x | = | y | false |
| y | > | x | true |
| y | < | x | false |
| x | = | x | true |
±-------------±---------±--------------±------+
解释:
如上所示, 你需要通过使用 Variables 表来找到 Expressions 表中的每一个布尔表达式的值.
3.3.2 分析
暂时无法在飞书文档外展示此内容

3.3.3 实现
3.3.3.1 数据准备

Create Table If Not Exists Variables (name varchar(3), value int);
Create Table If Not Exists Expressions (left_operand varchar(3), operator ENUM('>', '<', '='), right_operand varchar(3));
Truncate table Variables;
insert into Variables (name, value) values ('x', '66');
insert into Variables (name, value) values ('y', '77');
Truncate table Expressions;
insert into Expressions (left_operand, operator, right_operand) values ('x', '>', 'y');
insert into Expressions (left_operand, operator, right_operand) values ('x', '<', 'y');
insert into Expressions (left_operand, operator, right_operand) values ('x', '=', 'y');
insert into Expressions (left_operand, operator, right_operand) values ('y', '>', 'x');
insert into Expressions (left_operand, operator, right_operand) values ('y', '<', 'x');
insert into Expressions (left_operand, operator, right_operand) values ('x', '=', 'x');

3.3.3.2 实现

with t1 as (
select left_operand,operator,right_operand ,a.value-c.value as d
from variables a
    join expressions b on a.name =b.left_operand
  join variables c on c.name = b.right_operand)
select left_operand,operator,right_operand,
case
    when operator = '>' and d >0 then 'true'
    when operator = '<' and d <0 then 'true'
    when operator = '=' and d =0 then 'true'
else 'false'
end as value
from t1

3.3.4 小结
Case when then
表连接

3.4 力扣之1445-苹果和桔子

3.4.1 说明
表: Sales
±--------------±--------+
| Column Name | Type |
±--------------±--------+
| sale_date | date |
| fruit | enum |
| sold_num | int |
±--------------±--------+
(sale_date, fruit) 是该表主键(具有唯一值的列的组合)。
该表包含了每一天中"苹果" 和 "桔子"的销售情况。
编写解决方案报告每一天 苹果 和 桔子 销售的数目的差异.
返回的结果表, 按照格式为 (‘YYYY-MM-DD’) 的 sale_date 排序.
返回结果表如下例所示:
示例 1:
输入:
Sales 表:
±-----------±-----------±------------+
| sale_date | fruit | sold_num |
±-----------±-----------±------------+
| 2020-05-01 | apples | 10 |
| 2020-05-01 | oranges | 8 |
| 2020-05-02 | apples | 15 |
| 2020-05-02 | oranges | 15 |
| 2020-05-03 | apples | 20 |
| 2020-05-03 | oranges | 0 |
| 2020-05-04 | apples | 15 |
| 2020-05-04 | oranges | 16 |
±-----------±-----------±------------+
输出:
±-----------±-------------+
| sale_date | diff |
±-----------±-------------+
| 2020-05-01 | 2 |
| 2020-05-02 | 0 |
| 2020-05-03 | 20 |
| 2020-05-04 | -1 |
±-----------±-------------+
解释:
在 2020-05-01, 卖了 10 个苹果 和 8 个桔子 (差异为 10 - 8 = 2).
在 2020-05-02, 卖了 15 个苹果 和 15 个桔子 (差异为 15 - 15 = 0).
在 2020-05-03, 卖了 20 个苹果 和 0 个桔子 (差异为 20 - 0 = 20).
在 2020-05-04, 卖了 15 个苹果 和 16 个桔子 (差异为 15 - 16 = -1).
3.4.2 分析
暂时无法在飞书文档外展示此内容

3.4.3 实现
3.4.3.1 数据准备

drop table if EXISTS sales;
Create table If Not Exists Sales (sale_date date, fruit ENUM('apples', 'oranges'), sold_num int);
Truncate table Sales;
insert into Sales (sale_date, fruit, sold_num) values ('2020-05-01', 'apples', '10');
insert into Sales (sale_date, fruit, sold_num) values ('2020-05-01', 'oranges', '8');
insert into Sales (sale_date, fruit, sold_num) values ('2020-05-02', 'apples', '15');
insert into Sales (sale_date, fruit, sold_num) values ('2020-05-02', 'oranges', '15');
insert into Sales (sale_date, fruit, sold_num) values ('2020-05-03', 'apples', '20');
insert into Sales (sale_date, fruit, sold_num) values ('2020-05-03', 'oranges', '0');
insert into Sales (sale_date, fruit, sold_num) values ('2020-05-04', 'apples', '15');
insert into Sales (sale_date, fruit, sold_num) values ('2020-05-04', 'oranges', '16');

3.4.3.2 实现

WITH
    t1 AS
            (SELECT * FROM sales WHERE fruit = 'apples'),
    t2 AS (SELECT * FROM sales WHERE fruit = 'oranges')
SELECT
    t1.sale_date                sale_date,
    (t1.sold_num - t2.sold_num) diff
FROM
    t1
        JOIN t2
WHERE
    t1.sale_date = t2.sale_date
ORDER BY
    sale_date;
    ```
3.4.4 小结
表连接
## 3.5 力扣之1454-活跃用户
3.5.1 说明
表 Accounts:
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| name          | varchar |
+---------------+---------+
id 是该表主键(具有唯一值的列)
该表包含账户 id 和账户的用户名.
表 Logins:
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| login_date    | date    |
+---------------+---------+
该表可能包含重复项.
该表包含登录用户的账户 id 和登录日期. 用户也许一天内登录多次.
活跃用户 是指那些至少连续 5 天登录账户的用户。
编写解决方案,  找到 活跃用户 的 id 和 name。
返回的结果表按照 id 排序 。
结果表格式如下例所示。
示例 1:
输入:
Accounts 表:
+----+----------+
| id | name     |
+----+----------+
| 1  | Winston  |
| 7  | Jonathan |
+----+----------+
Logins 表:
+----+------------+
| id | login_date |
+----+------------+
| 7  | 2020-05-30 |
| 1  | 2020-05-30 |
| 7  | 2020-05-31 |
| 7  | 2020-06-01 |
| 7  | 2020-06-02 |
| 7  | 2020-06-02 |
| 7  | 2020-06-03 |
| 1  | 2020-06-07 |
| 7  | 2020-06-10 |
+----+------------+
输出:
+----+----------+
| id | name     |
+----+----------+
| 7  | Jonathan |
+----+----------+
解释:
id = 1 的用户 Winston 仅仅在不同的 2 天内登录了 2 次, 所以, Winston 不是活跃用户.
id = 7 的用户 Jonathon 在不同的 6 天内登录了 7 次, , 6 天中有 5 天是连续的, 所以, Jonathan 是活跃用户.
3.5.2 分析
![暂时无法在飞书文档外展示此内容](https://i-blog.csdnimg.cn/direct/ac78dd54423c407facce8dfcb8772c22.png)

3.5.3 实现
3.5.3.1 数据准备
```sql
Create table If Not Exists Accounts (id int, name varchar(10));
Create table If Not Exists Logins (id int, login_date date);
Truncate table Accounts;
insert into Accounts (id, name) values ('1', 'Winston');
insert into Accounts (id, name) values ('7', 'Jonathan');
Truncate table Logins;
insert into Logins (id, login_date) values ('7', '2020-05-30');
insert into Logins (id, login_date) values ('1', '2020-05-30');
insert into Logins (id, login_date) values ('7', '2020-05-31');
insert into Logins (id, login_date) values ('7', '2020-06-01');
insert into Logins (id, login_date) values ('7', '2020-06-02');
insert into Logins (id, login_date) values ('7', '2020-06-02');
insert into Logins (id, login_date) values ('7', '2020-06-03');
insert into Logins (id, login_date) values ('1', '2020-06-07');
insert into Logins (id, login_date) values ('7', '2020-06-10');
3.5.3.2 实现
with t1 as(
select DISTINCT a.id id ,name ,login_date from accounts a join logins l on a.id = l.id),
t2 as (select id,name,login_date, row_number()OVER (PARTITION BY id ORDER BY login_date) rn from t1),
t3 as (select id,name,login_date,rn,date_sub(login_date,INTERVAL rn DAY ) diff from t2),
t4 as (select id,name,count(diff)over(PARTITION BY name,diff ) cut from t3)
select distinct id ,name   from t4  where cut >=5 order by id
;

3.5.4 小结
窗口函数,date_sub函数

3.6 力扣之1459-矩形面积

3.6.1 说明
表: Points
±--------------±--------+
| Column Name | Type |
±--------------±--------+
| id | int |
| x_value | int |
| y_value | int |
±--------------±--------+
id 是该表中具有唯一值的列。
每个点都用二维坐标 (x_value, y_value) 表示。
编写解决方案,报告由表中任意两点可以形成的所有 边与坐标轴平行 且 面积不为零 的矩形。
结果表中的每一行包含三列 (p1, p2, area) 如下:

  • p1 和 p2 是矩形两个对角的 id
  • 矩形的面积由列 area 表示
    返回结果表请按照面积 area 大小 降序排列;如果面积相同的话, 则按照 p1 升序排序;若仍相同,则按 p2 升序排列。
    返回结果格式如下例所示:
    示例 1:
    [图片]
    输入:
    Points 表:
    ±---------±------------±------------+
    | id | x_value | y_value |
    ±---------±------------±------------+
    | 1 | 2 | 7 |
    | 2 | 4 | 8 |
    | 3 | 2 | 10 |
    ±---------±------------±------------+
    输出:
    ±---------±------------±------------+
    | p1 | p2 | area |
    ±---------±------------±------------+
    | 2 | 3 | 4 |
    | 1 | 2 | 2 |
    ±---------±------------±------------+
    解释:
    p1 = 2 且 p2 = 3 时, 面积等于 |4-2| * |8-10| = 4
    p1 = 1 且 p2 = 2 时, 面积等于 ||2-4| * |7-8| = 2
    p1 = 1 且 p2 = 3 时, 是不可能为矩形的, 面积等于 0
    3.6.2 分析
    暂时无法在飞书文档外展示此内容

3.6.3 实现
3.6.3.1 数据准备

CREATE TABLE IF NOT EXISTS Points
(
    id      INT,
    x_value INT,
    y_value INT
);
TRUNCATE TABLE Points;
INSERT INTO
    Points (id, x_value, y_value)
VALUES
    ('1', '2', '7');
INSERT INTO
    Points (id, x_value, y_value)
VALUES
    ('2', '4', '8');
INSERT INTO
    Points (id, x_value, y_value)
VALUES
    ('3', '2', '10');

3.6.3.2 实现

SELECT
    p1.id  p1,
    p2.id  p2,
    ABS((p1.x_value - p2.x_value) * (p1.y_value - p2.y_value)) area
FROM
    points p1
        JOIN points p2 ON p1.id < p2.id AND p1.x_value != p2.x_value AND p1.y_value != p2.y_value
ORDER BY
    area DESC, p1, p2 ASC;

3.6.4 小结
取绝对值函数,自连接,需要注意过滤条件和连接条件

  • 10
    点赞
  • 17
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值