118，SQL训练之，力扣，1113. 报告的记录

• 学习：知识的初次邂逅
• 复习：知识的温故知新
• 练习：知识的实践应用

目录

一，原题力扣链接

二，题干

三，建表语句

四，分析

五，SQL解答

六，验证

七，知识点总结

---

一，原题力扣链接

. - 力扣（LeetCode）

二，题干

动作表：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 包含关于 action 的可选信息，例如举报的原因或反馈的类型。
当 action 为 'report' 时 extra 不会为 NULL。

编写解决方案，针对每个举报原因统计昨天的举报帖子数量。假设今天是 2019-07-05 。

返回结果表 无顺序要求 。

结果格式如下示例所示。

示例 1：

输入：
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       | 4       | 2019-07-04  | view   | null   |
| 2       | 4       | 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-04  | view   | null   |
| 5       | 2       | 2019-07-04  | report | racism |
| 5       | 5       | 2019-07-04  | view   | null   |
| 5       | 5       | 2019-07-04  | report | racism |
+---------+---------+-------------+--------+--------+
输出：
+---------------+--------------+
| report_reason | report_count |
+---------------+--------------+
| spam          | 1            |
| racism        | 2            |
+---------------+--------------+ 
解释：注意，我们只关心举报帖数量非零的举报原因。

三，建表语句

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))
Truncate table Actions
insert into Actions (user_id, post_id, action_date, action, extra) values ('1', '1', '2019-07-01', 'view', NULL)
insert into Actions (user_id, post_id, action_date, action, extra) values ('1', '1', '2019-07-01', 'like', NULL)
insert into Actions (user_id, post_id, action_date, action, extra) values ('1', '1', '2019-07-01', 'share', NULL)
insert into Actions (user_id, post_id, action_date, action, extra) values ('2', '4', '2019-07-04', 'view', NULL)
insert into Actions (user_id, post_id, action_date, action, extra) values ('2', '4', '2019-07-04', 'report', 'spam')
insert into Actions (user_id, post_id, action_date, action, extra) values ('3', '4', '2019-07-04', 'view', NULL)
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', NULL)
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-04', 'view', NULL)
insert into Actions (user_id, post_id, action_date, action, extra) values ('5', '2', '2019-07-04', 'report', 'racism')
insert into Actions (user_id, post_id, action_date, action, extra) values ('5', '5', '2019-07-04', 'view', NULL)
insert into Actions (user_id, post_id, action_date, action, extra) values ('5', '5', '2019-07-04', 'report', 'racism')


select * from actions;

四，分析

表格大法：过滤后分组聚合

第一步：过滤掉非 2019-07-05前一天 也就是2019-07-04的帖子

第二步：过滤掉非举报的的帖子

第三步：过滤掉举报内容为null的帖子

第四步：去重

第五步：分组聚合 count求次数

第六步：映射指定的列 改名 输出

解题过程

第一步：过滤掉非 2019-07-05前一天 也就是2019-07-04的帖子

在mysql

第二步：过滤掉非举报的的帖子 和举报内容为空的帖子

在mysql

第三步：去重

在mysql

第四步：分组聚合 count求次数

在mysql

第五步：映射指定的列 改名 输出

五，SQL解答

with t1 as (
    select
        user_id, post_id, action_date, action, extra
    from actions where action_date = date_add('2019-07-05',interval -1 day )
)
#    select * from t1;
,t2 as (
    select
        user_id, post_id, action_date, action, extra
    from t1 where action='report' and extra is not null
)
# select * from t2;
, t3 as (
    select distinct post_id, extra from t2
)
# select * from t3;
select extra as report_reason ,count(post_id) as report_count  from t3 group by extra;

六，验证

七，知识点总结

• 时间函数的运用
• 条件过滤的运用
• 非空的判断
• 去重的运用 distinct
• 分组过滤的运用

• 学习：知识的初次邂逅
• 复习：知识的温故知新
• 练习：知识的实践应用