sql题(简单)

sql题

196. 删除重复的电子邮箱

Truncate table Person
insert into Person (Id, Email) values ('1', 'john@example.com')
insert into Person (Id, Email) values ('2', 'bob@example.com')
insert into Person (Id, Email) values ('3', 'john@example.com')

编写一个 SQL 查询，来删除 Person 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个。

±—±-----------------+
| Id | Email |
±—±-----------------+
| 1 | john@example.com |
| 2 | bob@example.com |
| 3 | john@example.com |
±—±-----------------+
Id 是这个表的主键。
例如，在运行你的查询语句之后，上面的 Person 表应返回以下几行:

±—±-----------------+
| Id | Email |
±—±-----------------+
| 1 | john@example.com |
| 2 | bob@example.com |
±—±-----------------+

提示：
执行 SQL 之后，输出是整个 Person 表。
使用 delete 语句。

DELETE FROM <表名> WHERE ...;
delete p1 from Person p1 join 
            (select Email as Email,min(Id) as id
                from Person
            group by Email) as s1
        on p1.Email = s1.Email and p1.Id !=s1.id
        

1241. 每个帖子的评论数

Create table If Not Exists Submissions (sub_id int, parent_id int)
Truncate table Submissions
insert into Submissions (sub_id, parent_id) values ('1', 'None')
insert into Submissions (sub_id, parent_id) values ('2', 'None')
insert into Submissions (sub_id, parent_id) values ('1', 'None')
insert into Submissions (sub_id, parent_id) values ('12', 'None')
insert into Submissions (sub_id, parent_id) values ('3', '1')
insert into Submissions (sub_id, parent_id) values ('5', '2')
insert into Submissions (sub_id, parent_id) values ('3', '1')
insert into Submissions (sub_id, parent_id) values ('4', '1')
insert into Submissions (sub_id, parent_id) values ('9', '1')
insert into Submissions (sub_id, parent_id) values ('10', '2')
insert into Submissions (sub_id, parent_id) values ('6', '7')

±--------------±---------+
| 列名 | 类型 |
±--------------±---------+
| sub_id | int |
| parent_id | int |
±--------------±---------+
上表没有主键, 所以可能会出现重复的行。
每行可以是一个帖子或对该帖子的评论。
如果是帖子的话，parent_id 就是 null。
对于评论来说，parent_id 就是表中对应帖子的 sub_id。

编写 SQL 语句以查找每个帖子的评论数。

结果表应包含帖子的 post_id 和对应的评论数 number_of_comments 并且按 post_id 升序排列。

Submissions 可能包含重复的评论。您应该计算每个帖子的唯一评论数。

Submissions 可能包含重复的帖子。您应该将它们视为一个帖子。

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

Submissions table:
±--------±-----------+
| sub_id | parent_id |
±--------±-----------+
| 1 | Null |
| 2 | Null |
| 1 | Null |
| 12 | Null |
| 3 | 1 |
| 5 | 2 |
| 3 | 1 |
| 4 | 1 |
| 9 | 1 |
| 10 | 2 |
| 6 | 7 |
±--------±-----------+

结果表：
±--------±-------------------+
| post_id | number_of_comments |
±--------±-------------------+
| 1 | 3 |
| 2 | 2 |
| 12 | 0 |
±--------±-------------------+

表中 ID 为 1 的帖子有 ID 为 3、4 和 9 的三个评论。表中 ID 为 3 的评论重复出现了，所以我们只对它进行了一次计数。
表中 ID 为 2 的帖子有 ID 为 5 和 10 的两个评论。
ID 为 12 的帖子在表中没有评论。
表中 ID 为 6 的评论是对 ID 为 7 的已删除帖子的评论，因此我们将其忽略。

select t.post_id, nvl(count(distinct sub_id),0) number_of_comments
from submissions
right join
(select distinct sub_id post_id
from submissions
where parent_id is null) t
on parent_id = t.post_id
group by t.post_id

1485. 按日期分组销售产品

Create table If Not Exists Activities (sell_date date, product varchar(20))
Truncate table Activities
insert into Activities (sell_date, product) values ('2020-05-30', 'Headphone')
insert into Activities (sell_date, product) values ('2020-06-01', 'Pencil')
insert into Activities (sell_date, product) values ('2020-06-02', 'Mask')
insert into Activities (sell_date, product) values ('2020-05-30', 'Basketball')
insert into Activities (sell_date, product) values ('2020-06-01', 'Bible')
insert into Activities (sell_date, product) values ('2020-06-02', 'Mask')
insert into Activities (sell_date, product) values ('2020-05-30', 'T-Shirt')

编写一个 SQL 查询来查找每个日期、销售的不同产品的数量及其名称。
每个日期的销售产品名称应按词典序排列。
返回按 sell_date 排序的结果表。

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

Activities 表：
±-----------±------------+
| sell_date | product |
±-----------±------------+
| 2020-05-30 | Headphone |
| 2020-06-01 | Pencil |
| 2020-06-02 | Mask |
| 2020-05-30 | Basketball |
| 2020-06-01 | Bible |
| 2020-06-02 | Mask |
| 2020-05-30 | T-Shirt |
±-----------±------------+

Result 表：
±-----------±---------±-----------------------------+
| sell_date | num_sold | products |
±-----------±---------±-----------------------------+
| 2020-05-30 | 3 | Basketball,Headphone,T-shirt |
| 2020-06-01 | 2 | Bible,Pencil |
| 2020-06-02 | 1 | Mask |
±-----------±---------±-----------------------------+
对于2020-05-30，出售的物品是 (Headphone, Basketball, T-shirt)，按词典序排列，并用逗号 ‘,’ 分隔。
对于2020-06-01，出售的物品是 (Pencil, Bible)，按词典序排列，并用逗号分隔。
对于2020-06-02，出售的物品是 (Mask)，只需返回该物品名。

--listagg(字段, ',') within group(order by 字段)
select to_char(sell_date,'yyyy-mm-dd') sell_date,count(distinct product) num_sold,listagg(product, ',') within group(order by sell_date) as products 
    from (select distinct sell_date, product
    from Activities)
group by sell_date

1517. 查找拥有有效邮箱的用户

Create table If Not Exists Users (user_id int, name varchar(30), mail varchar(50))
Truncate table Users
insert into Users (user_id, name, mail) values ('1', 'Winston', 'winston@leetcode.com')
insert into Users (user_id, name, mail) values ('2', 'Jonathan', 'jonathanisgreat')
insert into Users (user_id, name, mail) values ('3', 'Annabelle', 'bella-@leetcode.com')
insert into Users (user_id, name, mail) values ('4', 'Sally', 'sally.come@leetcode.com')
insert into Users (user_id, name, mail) values ('5', 'Marwan', 'quarz#2020@leetcode.com')
insert into Users (user_id, name, mail) values ('6', 'David', 'david69@gmail.com')
insert into Users (user_id, name, mail) values ('7', 'Shapiro', '.shapo@leetcode.com')

用户表： Users

±--------------±--------+
| Column Name | Type |
±--------------±--------+
| user_id | int |
| name | varchar |
| mail | varchar |
±--------------±--------+
user_id （用户 ID）是该表的主键。
这个表包含用户在某网站上注册的信息。有些邮箱是无效的。

写一条 SQL 语句，查询拥有有效邮箱的用户。

有效的邮箱包含符合下列条件的前缀名和域名：

前缀名是包含字母（大写或小写）、数字、下划线 ‘_’、句点 ‘.’ 和/或横杠 ‘-’ 的字符串。前缀名必须以字母开头。
域名是 ‘@leetcode.com’ 。
按任意顺序返回结果表。

查询格式如下所示：

Users
±--------±----------±------------------------+
| user_id | name | mail |
±--------±----------±------------------------+
| 1 | Winston | winston@leetcode.com |
| 2 | Jonathan | jonathanisgreat |
| 3 | Annabelle | bella-@leetcode.com |
| 4 | Sally | sally.come@leetcode.com |
| 5 | Marwan | quarz#2020@leetcode.com |
| 6 | David | david69@gmail.com |
| 7 | Shapiro | .shapo@leetcode.com |
±--------±----------±------------------------+

结果表：
±--------±----------±------------------------+
| user_id | name | mail |
±--------±----------±------------------------+
| 1 | Winston | winston@leetcode.com |
| 3 | Annabelle | bella-@leetcode.com |
| 4 | Sally | sally.come@leetcode.com |
±--------±----------±------------------------+
2 号用户的邮箱没有域名。
5 号用户的邮箱包含非法字符 #。
6 号用户的邮箱的域名不是 leetcode。
7 号用户的邮箱以句点（.）开头。

/*
\d 匹配数字字符
\D 匹配非数字字符
\w 匹配单词字符
\W 匹配非单词字符
\s 匹配空白字符
\S 匹配非空白字符
\A 仅匹配字符串的开头
\Z 仅匹配字符串的结尾或者行结尾之前
\z 仅匹配字符串的结尾
*  匹配 0 次或更多次（非贪婪）
+  匹配 1 次或更多次（非贪婪）
?  匹配 0 次或 1 次（非贪婪）
{n}精确匹配 n 次（非贪婪）
{n,}至少匹配 n 次（非贪婪）
{n,m} 至少匹配 n 次，但不超过 m 次（贪婪）*/
select *
from users 
where mail regexp '^[a-zA-A]+[a-zA-Z0-9_\\./\\-]*@leetcode\\.com$'

177. 第N高的薪水
     编写一个 SQL 查询，获取 Employee 表中第 n 高的薪水（Salary）。

±—±-------+
| Id | Salary |
±—±-------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
±—±-------+
例如上述 Employee 表，n = 2 时，应返回第二高的薪水 200。如果不存在第 n 高的薪水，那么查询应返回 null。

±-----------------------+
| getNthHighestSalary(2) |
±-----------------------+
| 200 |
±-----------------------+

CREATE FUNCTION getNthHighestSalary(N IN NUMBER) RETURN NUMBER IS
result NUMBER;
BEGIN
    /* Write your PL/SQL query statement below */
    --NVL()函数可以解决salary不存在的情况
        select nvl(salary,null) into result --将查询出的结果插入结果集
            from
                (select salary ,rownum as num 
                    from 
                        (select distinct salary from employee order by salary desc) --去除重复值，倒序排列
                            WHERE ROWNUM<=N --缩小查询结果，增加性能
                ) --子查询的方式,给rownum定义一个别名num，记录下来查询结果
                    where num = N;
        RETURN result;
 END;

550. 游戏玩法分析 IV

Create table If Not Exists Activity (player_id int, device_id int, event_date date, games_played int)
Truncate table Activity
insert into Activity (player_id, device_id, event_date, games_played) values ('1', '2', '2016-03-01', '5')
insert into Activity (player_id, device_id, event_date, games_played) values ('1', '2', '2016-03-02', '6')
insert into Activity (player_id, device_id, event_date, games_played) values ('2', '3', '2017-06-25', '1')
insert into Activity (player_id, device_id, event_date, games_played) values ('3', '1', '2016-03-02', '0')
insert into Activity (player_id, device_id, event_date, games_played) values ('3', '4', '2018-07-03', '5')

±-------------±--------+
| Column Name | Type |
±-------------±--------+
| player_id | int |
| device_id | int |
| event_date | date |
| games_played | int |
±-------------±--------+
（player_id，event_date）是此表的主键。
这张表显示了某些游戏的玩家的活动情况。
每一行是一个玩家的记录，他在某一天使用某个设备注销之前登录并玩了很多游戏（可能是 0）。

编写一个 SQL 查询，报告在首次登录的第二天再次登录的玩家的比率，四舍五入到小数点后两位。换句话说，您需要计算从首次登录日期开始至少连续两天登录的玩家的数量，然后除以玩家总数。

查询结果格式如下所示：

Activity table:
±----------±----------±-----------±-------------+
| player_id | device_id | event_date | games_played |
±----------±----------±-----------±-------------+
| 1 | 2 | 2016-03-01 | 5 |
| 1 | 2 | 2016-03-02 | 6 |
| 2 | 3 | 2017-06-25 | 1 |
| 3 | 1 | 2016-03-02 | 0 |
| 3 | 4 | 2018-07-03 | 5 |
±----------±----------±-----------±-------------+

Result table:
±----------+
| fraction |
±----------+
| 0.33 |
±----------+
只有 ID 为 1 的玩家在第一天登录后才重新登录，所以答案是 1/3 = 0.33

--先求出数量,再除以总数量
SELECT round((
		SELECT COUNT(1)
		FROM (
			SELECT a.player_id, MIN(event_date) AS event_date
			FROM Activity a
			GROUP BY a.player_id
		) b
			LEFT JOIN Activity c ON b.player_id = c.player_id
		AND b.event_date + 1 = c.event_date 
		WHERE c.player_id IS NOT NULL
	) / (
		SELECT COUNT(DISTINCT player_id)
		FROM Activity
	), 2) AS fraction
FROM dual

626. 换座位

Create table If Not Exists seat(id int, student varchar(255))
Truncate table seat
insert into seat (id, student) values ('1', 'Abbot')
insert into seat (id, student) values ('2', 'Doris')
insert into seat (id, student) values ('3', 'Emerson')
insert into seat (id, student) values ('4', 'Green')
insert into seat (id, student) values ('5', 'Jeames')

小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。

其中纵列的 id 是连续递增的

小美想改变相邻俩学生的座位。

你能不能帮她写一个 SQL query 来输出小美想要的结果呢？

示例：

±--------±--------+
| id | student |
±--------±--------+
| 1 | Abbot |
| 2 | Doris |
| 3 | Emerson |
| 4 | Green |
| 5 | Jeames |
±--------±--------+
假如数据输入的是上表，则输出结果如下：

±--------±--------+
| id | student |
±--------±--------+
| 1 | Doris |
| 2 | Abbot |
| 3 | Green |
| 4 | Emerson |
| 5 | Jeames |
±--------±--------+
注意：

如果学生人数是奇数，则不需要改变最后一个同学的座位。

SELECT (CASE 
            WHEN MOD(id,2) = 1 AND id = (SELECT COUNT(*) FROM seat) THEN id --学生人数是奇数,且是最后一个
            WHEN MOD(id,2) = 1 THEN id+1
            ElSE id-1
        END) AS id, student
FROM seat
ORDER BY id;

1077. 项目员工 III

Create table If Not Exists Project (project_id int, employee_id int)
Create table If Not Exists Employee (employee_id int, name varchar(10), experience_years int)
Truncate table Project
insert into Project (project_id, employee_id) values ('1', '1')
insert into Project (project_id, employee_id) values ('1', '2')
insert into Project (project_id, employee_id) values ('1', '3')
insert into Project (project_id, employee_id) values ('2', '1')
insert into Project (project_id, employee_id) values ('2', '4')
Truncate table Employee
insert into Employee (employee_id, name, experience_years) values ('1', 'Khaled', '3')
insert into Employee (employee_id, name, experience_years) values ('2', 'Ali', '2')
insert into Employee (employee_id, name, experience_years) values ('3', 'John', '3')
insert into Employee (employee_id, name, experience_years) values ('4', 'Doe', '2')

项目表 Project：

±------------±--------+
| Column Name | Type |
±------------±--------+
| project_id | int |
| employee_id | int |
±------------±--------+
(project_id, employee_id) 是这个表的主键
employee_id 是员工表 Employee 的外键
员工表 Employee：

±-----------------±--------+
| Column Name | Type |
±-----------------±--------+
| employee_id | int |
| name | varchar |
| experience_years | int |
±-----------------±--------+
employee_id 是这个表的主键

写 一个 SQL 查询语句，报告在每一个项目中经验最丰富的雇员是谁。如果出现经验年数相同的情况，请报告所有具有最大经验年数的员工。

查询结果格式在以下示例中：

Project 表：
±------------±------------+
| project_id | employee_id |
±------------±------------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 4 |
±------------±------------+

Employee 表：
±------------±-------±-----------------+
| employee_id | name | experience_years |
±------------±-------±-----------------+
| 1 | Khaled | 3 |
| 2 | Ali | 2 |
| 3 | John | 3 |
| 4 | Doe | 2 |
±------------±-------±-----------------+

Result 表：
±------------±--------------+
| project_id | employee_id |
±------------±--------------+
| 1 | 1 |
| 1 | 3 |
| 2 | 1 |
±------------±--------------+
employee_id 为 1 和 3 的员工在 project_id 为 1 的项目中拥有最丰富的经验。在 project_id 为 2 的项目中，employee_id 为 1 的员工拥有最丰富的经验。

with cte as (
select p.project_id,p.employee_id,e.experience_years
    from Project p,Employee e 
    where p.employee_id = e.employee_id)
select project_id,employee_id //将两个表连接起来
from (
select project_id,employee_id,rank()over(partition by project_id order by experience_years desc) rk
from cte) where rk =1 //窗口函数,分组求最大值

1132. 报告的记录 II

Create table If Not Exists Actions (user_id int, post_id int, action_date date, action ENUM('view', 'like', 'reaction', 'comment', 'report', 'share'), extra varchar(10))
create table if not exists Removals (post_id int, remove_date date)
Truncate table Actions
insert into Actions (user_id, post_id, action_date, action, extra) values ('1', '1', '2019-07-01', 'view', 'None')
insert into Actions (user_id, post_id, action_date, action, extra) values ('1', '1', '2019-07-01', 'like', 'None')
insert into Actions (user_id, post_id, action_date, action, extra) values ('1', '1', '2019-07-01', 'share', 'None')
insert into Actions (user_id, post_id, action_date, action, extra) values ('2', '2', '2019-07-04', 'view', 'None')
insert into Actions (user_id, post_id, action_date, action, extra) values ('2', '2', '2019-07-04', 'report', 'spam')
insert into Actions (user_id, post_id, action_date, action, extra) values ('3', '4', '2019-07-04', 'view', 'None')
insert into Actions (user_id, post_id, action_date, action, extra) values ('3', '4', '2019-07-04', 'report', 'spam')
insert into Actions (user_id, post_id, action_date, action, extra) values ('4', '3', '2019-07-02', 'view', 'None')
insert into Actions (user_id, post_id, action_date, action, extra) values ('4', '3', '2019-07-02', 'report', 'spam')
insert into Actions (user_id, post_id, action_date, action, extra) values ('5', '2', '2019-07-03', 'view', 'None')
insert into Actions (user_id, post_id, action_date, action, extra) values ('5', '2', '2019-07-03', 'report', 'racism')
insert into Actions (user_id, post_id, action_date, action, extra) values ('5', '5', '2019-07-03', 'view', 'None')
insert into Actions (user_id, post_id, action_date, action, extra) values ('5', '5', '2019-07-03', 'report', 'racism')
Truncate table Removals
insert into Removals (post_id, remove_date) values ('2', '2019-07-20')

动作表： Actions

±--------------±--------+
| Column Name | Type |
±--------------±--------+
| user_id | int |
| post_id | int |
| action_date | date |
| action | enum |
| extra | varchar |
±--------------±--------+
这张表没有主键，并有可能存在重复的行。
action 列的类型是 ENUM，可能的值为 (‘view’, ‘like’, ‘reaction’, ‘comment’, ‘report’, ‘share’)。
extra 列拥有一些可选信息，例如：报告理由（a reason for report）或反应类型（a type of reaction）等。
移除表： Removals

±--------------±--------+
| Column Name | Type |
±--------------±--------+
| post_id | int |
| remove_date | date |
±--------------±--------+
这张表的主键是 post_id。
这张表的每一行表示一个被移除的帖子，原因可能是由于被举报或被管理员审查。

编写一段 SQL 来查找：在被报告为垃圾广告的帖子中，被移除的帖子的每日平均占比，四舍五入到小数点后 2 位。

查询结果的格式如下：

Actions table:
±--------±--------±------------±-------±-------+
| user_id | post_id | action_date | action | extra |
±--------±--------±------------±-------±-------+
| 1 | 1 | 2019-07-01 | view | null |
| 1 | 1 | 2019-07-01 | like | null |
| 1 | 1 | 2019-07-01 | share | null |
| 2 | 2 | 2019-07-04 | view | null |
| 2 | 2 | 2019-07-04 | report | spam |
| 3 | 4 | 2019-07-04 | view | null |
| 3 | 4 | 2019-07-04 | report | spam |
| 4 | 3 | 2019-07-02 | view | null |
| 4 | 3 | 2019-07-02 | report | spam |
| 5 | 2 | 2019-07-03 | view | null |
| 5 | 2 | 2019-07-03 | report | racism |
| 5 | 5 | 2019-07-03 | view | null |
| 5 | 5 | 2019-07-03 | report | racism |
±--------±--------±------------±-------±-------+

Removals table:
±--------±------------+
| post_id | remove_date |
±--------±------------+
| 2 | 2019-07-20 |
| 3 | 2019-07-18 |
±--------±------------+

Result table:
±----------------------+
| average_daily_percent |
±----------------------+
| 75.00 |
±----------------------+
2019-07-04 的垃圾广告移除率是 50%，因为有两张帖子被报告为垃圾广告，但只有一个得到移除。
2019-07-02 的垃圾广告移除率是 100%，因为有一张帖子被举报为垃圾广告并得到移除。
其余几天没有收到垃圾广告的举报，因此平均值为：(50 + 100) / 2 = 75%
注意，输出仅需要一个平均值即可，我们并不关注移除操作的日期。

SELECT
    ROUND(AVG(percent),2) average_daily_percent
FROM
    (
    SELECT
        action_date,
        //Removals 的 post_id / Actions 的 post_id
        COUNT(DISTINCT R.post_id) / COUNT(DISTINCT A.post_id) * 100 percent
    FROM 
        Actions A
    LEFT JOIN
        Removals R
    ON A.post_id = R.post_id
    WHERE
        extra='spam'
    GROUP BY
        action_date
    ) T

1149. 文章浏览 II

Create table If Not Exists Views (article_id int, author_id int, viewer_id int, view_date date)
Truncate table Views
insert into Views (article_id, author_id, viewer_id, view_date) values ('1', '3', '5', '2019-08-01')
insert into Views (article_id, author_id, viewer_id, view_date) values ('3', '4', '5', '2019-08-01')
insert into Views (article_id, author_id, viewer_id, view_date) values ('1', '3', '6', '2019-08-02')
insert into Views (article_id, author_id, viewer_id, view_date) values ('2', '7', '7', '2019-08-01')
insert into Views (article_id, author_id, viewer_id, view_date) values ('2', '7', '6', '2019-08-02')
insert into Views (article_id, author_id, viewer_id, view_date) values ('4', '7', '1', '2019-07-22')
insert into Views (article_id, author_id, viewer_id, view_date) values ('3', '4', '4', '2019-07-21')
insert into Views (article_id, author_id, viewer_id, view_date) values ('3', '4', '4', '2019-07-21')

Table: Views

±--------------±--------+
| Column Name | Type |
±--------------±--------+
| article_id | int |
| author_id | int |
| viewer_id | int |
| view_date | date |
±--------------±--------+
此表无主键，因此可能会存在重复行。此表的每一行都表示某人在某天浏览了某位作者的某篇文章。 请注意，同一人的 author_id 和 viewer_id 是相同的。

编写一条 SQL 查询来找出在同一天阅读至少两篇文章的人，结果按照 id 升序排序。

查询结果的格式如下：

Views table:
±-----------±----------±----------±-----------+
| article_id | author_id | viewer_id | view_date |
±-----------±----------±----------±-----------+
| 1 | 3 | 5 | 2019-08-01 |
| 3 | 4 | 5 | 2019-08-01 |
| 1 | 3 | 6 | 2019-08-02 |
| 2 | 7 | 7 | 2019-08-01 |
| 2 | 7 | 6 | 2019-08-02 |
| 4 | 7 | 1 | 2019-07-22 |
| 3 | 4 | 4 | 2019-07-21 |
| 3 | 4 | 4 | 2019-07-21 |
±-----------±----------±----------±-----------+

Result table:
±-----+
| id |
±-----+
| 5 |
| 6 |
±-----+

select  distinct viewer_id id
from views
group by viewer_id, view_date
having count(distinct article_id) > 1//在同一天阅读至少两篇文章
order by id

1285. 找到连续区间的开始和结束数字

Create table If Not Exists Logs (log_id int)
Truncate table Logs
insert into Logs (log_id) values ('1')
insert into Logs (log_id) values ('2')
insert into Logs (log_id) values ('3')
insert into Logs (log_id) values ('7')
insert into Logs (log_id) values ('8')
insert into Logs (log_id) values ('10')

表：Logs

±--------------±--------+
| Column Name | Type |
±--------------±--------+
| log_id | int |
±--------------±--------+
id 是上表的主键。
上表的每一行包含日志表中的一个 ID。

后来一些 ID 从 Logs 表中删除。编写一个 SQL 查询得到 Logs 表中的连续区间的开始数字和结束数字。

将查询表按照 start_id 排序。

查询结果格式如下面的例子：

Logs 表：
±-----------+
| log_id |
±-----------+
| 1 |
| 2 |
| 3 |
| 7 |
| 8 |
| 10 |
±-----------+

结果表：
±-----------±-------------+
| start_id | end_id |
±-----------±-------------+
| 1 | 3 |
| 7 | 8 |
| 10 | 10 |
±-----------±-------------+
结果表应包含 Logs 表中的所有区间。
从 1 到 3 在表中。
从 4 到 6 不在表中。
从 7 到 8 在表中。
9 不在表中。
10 在表中。

SELECT
    MIN(log_id) start_id,
    MAX(log_id) end_id
FROM
(SELECT
        -- 查找log_id 和log_id 与排序之间的差距
        log_id, log_id - row_number() OVER(ORDER BY log_id) as num
    FROM Logs)t
group by num --用这个差值确定哪几个在一组里,分别选取一组中的最大值和最小值作为区间的左端和右端
order by MIN(log_id)

1321. 餐馆营业额变化增长

Create table If Not Exists Customer (customer_id int, name varchar(20), visited_on date, amount int)
Truncate table Customer
insert into Customer (customer_id, name, visited_on, amount) values ('1', 'Jhon', '2019-01-01', '100')
insert into Customer (customer_id, name, visited_on, amount) values ('2', 'Daniel', '2019-01-02', '110')
insert into Customer (customer_id, name, visited_on, amount) values ('3', 'Jade', '2019-01-03', '120')
insert into Customer (customer_id, name, visited_on, amount) values ('4', 'Khaled', '2019-01-04', '130')
insert into Customer (customer_id, name, visited_on, amount) values ('5', 'Winston', '2019-01-05', '110')
insert into Customer (customer_id, name, visited_on, amount) values ('6', 'Elvis', '2019-01-06', '140')
insert into Customer (customer_id, name, visited_on, amount) values ('7', 'Anna', '2019-01-07', '150')
insert into Customer (customer_id, name, visited_on, amount) values ('8', 'Maria', '2019-01-08', '80')
insert into Customer (customer_id, name, visited_on, amount) values ('9', 'Jaze', '2019-01-09', '110')
insert into Customer (customer_id, name, visited_on, amount) values ('1', 'Jhon', '2019-01-10', '130')
insert into Customer (customer_id, name, visited_on, amount) values ('3', 'Jade', '2019-01-10', '150')

表: Customer

±--------------±--------+
| Column Name | Type |
±--------------±--------+
| customer_id | int |
| name | varchar |
| visited_on | date |
| amount | int |
±--------------±--------+
(customer_id, visited_on) 是该表的主键
该表包含一家餐馆的顾客交易数据
visited_on 表示 (customer_id) 的顾客在 visited_on 那天访问了餐馆
amount 是一个顾客某一天的消费总额

你是餐馆的老板，现在你想分析一下可能的营业额变化增长（每天至少有一位顾客）

写一条 SQL 查询计算以 7 天（某日期 + 该日期前的 6 天）为一个时间段的顾客消费平均值

查询结果格式的例子如下：

查询结果按 visited_on 排序
average_amount 要 保留两位小数，日期数据的格式为 (‘YYYY-MM-DD’)

Customer 表:
±------------±-------------±-------------±------------+
| customer_id | name | visited_on | amount |
±------------±-------------±-------------±------------+
| 1 | Jhon | 2019-01-01 | 100 |
| 2 | Daniel | 2019-01-02 | 110 |
| 3 | Jade | 2019-01-03 | 120 |
| 4 | Khaled | 2019-01-04 | 130 |
| 5 | Winston | 2019-01-05 | 110 |
| 6 | Elvis | 2019-01-06 | 140 |
| 7 | Anna | 2019-01-07 | 150 |
| 8 | Maria | 2019-01-08 | 80 |
| 9 | Jaze | 2019-01-09 | 110 |
| 1 | Jhon | 2019-01-10 | 130 |
| 3 | Jade | 2019-01-10 | 150 |
±------------±-------------±-------------±------------+

结果表:
±-------------±-------------±---------------+
| visited_on | amount | average_amount |
±-------------±-------------±---------------+
| 2019-01-07 | 860 | 122.86 |
| 2019-01-08 | 840 | 120 |
| 2019-01-09 | 840 | 120 |
| 2019-01-10 | 1000 | 142.86 |
±-------------±-------------±---------------+

第一个七天消费平均值从 2019-01-01 到 2019-01-07 是 (100 + 110 + 120 + 130 + 110 + 140 + 150)/7 = 122.86
第二个七天消费平均值从 2019-01-02 到 2019-01-08 是 (110 + 120 + 130 + 110 + 140 + 150 + 80)/7 = 120
第三个七天消费平均值从 2019-01-03 到 2019-01-09 是 (120 + 130 + 110 + 140 + 150 + 80 + 110)/7 = 120
第四个七天消费平均值从 2019-01-04 到 2019-01-10 是 (130 + 110 + 140 + 150 + 80 + 110 + 130 + 150)/7 = 142.86

select distinct to_char(visited_on,'yyyy-mm-dd') visited_on,sum_amount as amount,round(average_amount,2) as average_amount 
from
(
    select visited_on,
        sum(amount) over(order by visited_on rows 6 preceding) as sum_amount,--找出前6个和这个的总和
        avg(amount) over(order by visited_on rows 6 preceding) as average_amount--找出前6个和这个的平均值
    from(
         select visited_on,sum(amount) as amount 
         from Customer
         group by visited_on
         )t1
)t2 
--日期取最小日期后6天
where visited_on-(select min(visited_on)from Customer)>=6
order by visited_on 

1440. 计算布尔表达式的值

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')

表 Variables:

±--------------±--------+
| Column Name | Type |
±--------------±--------+
| name | varchar |
| value | int |
±--------------±--------+
name 是该表主键.
该表包含了存储的变量及其对应的值.

表 Expressions:

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

写一个 SQL 查询, 以计算表 Expressions 中的布尔表达式.

返回的结果表没有顺序要求.

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

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

Result 表:
±-------------±---------±--------------±------+
| 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 表中的每一个布尔表达式的值.

--只判断结果为true的
select e.*,
    case
        when operator = '=' and v1.value = v2.value then 'true'
        when operator = '>' and v1.value > v2.value then 'true'
        when operator = '<' and v1.value < v2.value then 'true'
        else 'false'
    end value
from Expressions e
--左边数据join Variables
left join Variables v1
on e.left_operand = v1.name
--右边数据join Variables
left join Variables v2
on e.right_operand = v2.name

1454. 活跃用户

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')

表 Accounts:

±--------------±--------+
| Column Name | Type |
±--------------±--------+
| id | int |
| name | varchar |
±--------------±--------+
id 是该表主键.
该表包含账户 id 和账户的用户名.

表 Logins:

±--------------±--------+
| Column Name | Type |
±--------------±--------+
| id | int |
| login_date | date |
±--------------±--------+
该表无主键, 可能包含重复项.
该表包含登录用户的账户 id 和登录日期. 用户也许一天内登录多次.

写一个 SQL 查询, 找到活跃用户的 id 和 name.

活跃用户是指那些至少连续 5 天登录账户的用户.

返回的结果表按照 id 排序.

结果表格式如下例所示:

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

Result 表:
±—±---------+
| id | name |
±—±---------+
| 7 | Jonathan |
±—±---------+
id = 1 的用户 Winston 仅仅在不同的 2 天内登录了 2 次, 所以, Winston 不是活跃用户.
id = 7 的用户 Jonathon 在不同的 6 天内登录了 7 次, , 6 天中有 5 天是连续的, 所以, Jonathan 是活跃用户.

select id,name 
from Accounts where id in(
select id
from (
SELECT id,reference_dt,COUNT(1) cnt 
from 
(--找出登陆日期,登陆日期-rank(结果为首次登陆日期)
    select distinct id,login_date,(login_date - dense_rank() over(partition by id order by login_date)) reference_dt  from Logins
    )
group by id,reference_dt)
where cnt>=5)
order by id