题目描述
(通过次数11,876 | 提交次数22,168,通过率53.57%)
动作表: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 字段是可选的信息(可能为 null),其中的信息例如有:1.报告理由(a reason for report) 2.反应类型(a type of reaction) 编写一条SQL,查询每种报告理由(report reason)在昨天的不同报告数量(post_id)。假设今天是2019-07-05。 查询及结果的格式示例: 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 | +---------+---------+-------------+--------+--------+ Result table: +---------------+--------------+ | report_reason | report_count | +---------------+--------------+ | spam | 1 | | racism | 2 | +---------------+--------------+ 注意,我们只关心报告数量非零的结果。 来源:力扣(LeetCode) 链接:https://leetcode.cn/problems/reported-posts
//测试数据 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)); 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', '4', '2019-07-04', 'view', 'None'); 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', '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-04', 'view', 'None'); 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', 'None'); insert into Actions (user_id, post_id, action_date, action, extra) values ('5', '5', '2019-07-04', 'report', 'racism');
解题思路
Actions表保存了所有动作以及动作补充信息。
题目要求:查询report动作中,所有不同报告原因的报告数。
首先,我们需要从Actions表取出所有动作类型为report的报告。
然后,再限定指定的日期。
最后,根据报告类型(extra字段)分组统计出不同的报告数。
参考SQL
未特别说明的情况下,参考SQL为基于MySQL8.0实现。
select extra report_reason, count(distinct post_id) report_count from Actions where action = 'report' and action_date = date_add('2019-07-05',interval -1 day) group by extra;
本站所有内容均为原创,本站保留所有权利。仅允许非商业用途的转载,但必须注明来源网站、作者、来源链接!否则,由此造成的一切后果,由转载方承担!
干货分享、技术提升、面试笔试、学习交流,欢迎关注公众号:xuesql。QQ学习交流群:209942678。