LeetCode--597. 好友申请 I：总体通过率

大黑牛
149 阅读5分钟

1 题目描述

表: FriendRequest

+----------------+---------+  
| Column Name    | Type    |  
+----------------+---------+  
| sender_id      | int     |  
| send_to_id     | int     |  
| request_date   | date    |  
+----------------+---------+

该表可能包含重复项 (换句话说, 在 SQL 中, 该表没有主键).
该表包含发送请求的用户的 ID, 接受请求的用户的 ID 以及请求的日期.

表: RequestAccepted

+----------------+---------+  
| Column Name    | Type    |  
+----------------+---------+  
| requester_id   | int     |  
| accepter_id    | int     |  
| accept_date    | date    |  
+----------------+---------+

该表可能包含重复项 (换句话说, 在 SQL 中, 该表没有主键)
该表包含发送请求的用户的 ID, 接受请求的用户的 ID 以及请求通过的日期
求出好友申请的通过率, 用 2 位小数表示. 通过率由接受好友申请的数目除以申请总数

提示:

  • 通过的好友申请不一定都在表 friend_request 中. 你只需要统计总的被通过的申请数 (不管它们在不在表 FriendRequest 中), 并将它除以申请总数, 得到通过率
  • 一个好友申请发送者有可能会给接受者发几条好友申请, 也有可能一个好友申请会被通过好几次. 这种情况下, 重复的好友申请只统计一次
  • 如果一个好友申请都没有, 你应该返回 accept_rate 为 0.00

2 测试用例

输入:
FriendRequest 表:

+-----------+------------+--------------+  
| sender_id | send_to_id | request_date |  
+-----------+------------+--------------+  
| 1         | 2          | 2016/06/01   |  
| 1         | 3          | 2016/06/01   |  
| 1         | 4          | 2016/06/01   |  
| 2         | 3          | 2016/06/02   |  
| 3         | 4          | 2016/06/09   |  
+-----------+------------+--------------+

RequestAccepted 表:

+--------------+-------------+-------------+  
| requester_id | accepter_id | accept_date |  
+--------------+-------------+-------------+  
| 1            | 2           | 2016/06/03  |  
| 1            | 3           | 2016/06/08  |  
| 2            | 3           | 2016/06/08  |  
| 3            | 4           | 2016/06/09  |  
| 3            | 4           | 2016/06/10  |  
+--------------+-------------+-------------+

输出:

+-------------+  
| accept_rate |  
+-------------+  
| 0.8         |  
+-------------+

解释:
总共有 5 个请求, 有 4 个不同的通过请求, 所以通过率是 0.80

3 解题思路

  1. 查询还有申请数量
select count(distinct sender_id, send_to_id) from FriendRequest

查询结果

+-------------------------------------+  
|count(distinct sender_id, send_to_id)|  
+-------------------------------------+  
|5                                    |  
+-------------------------------------+
  1. 查询好友申请通过量
select count(distinct requester_id, accepter_id) from RequestAccepted

查询结果

+-----------------------------------------+  
|count(distinct requester_id, accepter_id)|  
+-----------------------------------------+  
|4                                        |  
+-----------------------------------------+
  1. 计算通过率, 如果一个好友申请都没有, 需要返回 accept_rate 为 0.00
select round(ifnull(  
             (select count(distinct requester_id, accepter_id) from RequestAccepted) /  
             (select count(distinct sender_id, send_to_id) from FriendRequest),  
         0), 2) as accept_rate;

4 进阶问题

4.1 每个月的通过率

  1. 按月分别查询好友申请量和申请通过量
-- 好友申请量  
SELECT SUBSTRING(request_date, 1, 7) AS month, COUNT(DISTINCT sender_id, send_to_id) AS fr_count, 0 AS ra_count  
FROM FriendRequest  
GROUP BY month  
  
-- 申请通过量  
SELECT SUBSTRING(accept_date, 1, 7) AS month, 0 AS fr_count, COUNT(DISTINCT requester_id, accepter_id) AS ra_count  
FROM RequestAccepted  
GROUP BY month

查询结果

+-------+--------+--------+  
|month  |fr_count|ra_count|  
+-------+--------+--------+  
|2016-06|5       |0       |  
|2016-07|1       |0       |  
|2016-08|1       |0       |  
|2016-06|0       |4       |  
|2016-07|0       |1       |  
+-------+--------+--------+
  1. 按照月份分组统计, 好友申请量和通过量
SELECT sub_query.month, SUM(sub_query.ra_count), SUM(sub_query.fr_count)  
FROM (  
  SELECT SUBSTRING(request_date, 1, 7) AS month, COUNT(DISTINCT sender_id, send_to_id) AS fr_count, 0 AS ra_count  
  FROM FriendRequest  
  GROUP BY month  
  UNION  SELECT SUBSTRING(accept_date, 1, 7) AS month, 0 AS fr_count, COUNT(DISTINCT requester_id, accepter_id) AS ra_count  
  FROM RequestAccepted  
  GROUP BY month  
) AS sub_query  
GROUP BY sub_query.month  
ORDER BY sub_query.month;

查询结果

+-------+-----------------------+-----------------------+  
|month  |SUM(sub_query.ra_count)|SUM(sub_query.fr_count)|  
+-------+-----------------------+-----------------------+  
|2016-06|4                      |5                      |  
|2016-07|1                      |1                      |  
|2016-08|0                      |1                      |  
+-------+-----------------------+-----------------------+
  1. 计算每个月的通过率
SELECT sub_query.month, round(ifnull(SUM(sub_query.ra_count)/ SUM(sub_query.fr_count), 0), 2) AS accept_rate  
FROM (  
  SELECT SUBSTRING(request_date, 1, 7) AS month, COUNT(DISTINCT sender_id, send_to_id) AS fr_count, 0 AS ra_count  
  FROM FriendRequest  
  GROUP BY month  
  UNION  SELECT SUBSTRING(accept_date, 1, 7) AS month, 0 AS fr_count, COUNT(DISTINCT requester_id, accepter_id) AS ra_count  
  FROM RequestAccepted  
  GROUP BY month  
) AS sub_query  
GROUP BY sub_query.month  
ORDER BY sub_query.month;

查询结果

+-------+-----------+  
|month  |accept_rate|  
+-------+-----------+  
|2016-06|0.80       |  
|2016-07|1.00       |  
|2016-08|0.00       |  
+-------+-----------+

4.2 每一天的通过率

  1. 按照日期分别查询好友申请量和申请通过量
-- 好友申请量  
SELECT request_date as date_day, COUNT(DISTINCT sender_id, send_to_id) AS fr_count, 0 AS ra_count  
FROM FriendRequest  
GROUP BY date_day  
  
-- 申请通过量  
SELECT accept_date as date_day, 0 AS fr_count, COUNT(DISTINCT requester_id, accepter_id) AS ra_count  
FROM RequestAccepted  
GROUP BY date_day

查询结果

+----------+--------+--------+  
|date_day  |fr_count|ra_count|  
+----------+--------+--------+  
|2016-06-01|3       |0       |  
|2016-06-02|1       |0       |  
|2016-06-09|1       |0       |  
|2016-07-09|1       |0       |  
|2016-08-09|1       |0       |  
|2016-06-03|0       |1       |  
|2016-06-08|0       |2       |  
|2016-06-09|0       |1       |  
|2016-06-10|0       |1       |  
|2016-07-10|0       |1       |  
+----------+--------+--------+
  1. 按照日期分组统计, 好友申请量和通过量
SELECT sub_query.date_day, SUM(sub_query.ra_count), SUM(sub_query.fr_count)  
FROM (  
  SELECT request_date as date_day, COUNT(DISTINCT sender_id, send_to_id) AS fr_count, 0 AS ra_count  
  FROM FriendRequest  
  GROUP BY date_day  
  UNION  
  SELECT accept_date as date_day, 0 AS fr_count, COUNT(DISTINCT requester_id, accepter_id) AS ra_count  
  FROM RequestAccepted  
  GROUP BY date_day  
) AS sub_query  
GROUP BY sub_query.date_day  
ORDER BY sub_query.date_day;

查询结果

+----------+-----------------------+-----------------------+  
|date_day  |SUM(sub_query.ra_count)|SUM(sub_query.fr_count)|  
+----------+-----------------------+-----------------------+  
|2016-06-01|0                      |3                      |  
|2016-06-02|0                      |1                      |  
|2016-06-03|1                      |0                      |  
|2016-06-08|2                      |0                      |  
|2016-06-09|1                      |1                      |  
|2016-06-10|1                      |0                      |  
|2016-07-09|0                      |1                      |  
|2016-07-10|1                      |0                      |  
|2016-08-09|0                      |1                      |  
+----------+-----------------------+-----------------------+
  1. 计算每个月的通过率
SELECT sub_query.date_day, round(ifnull(SUM(sub_query.ra_count)/ SUM(sub_query.fr_count), 0), 2) AS accept_rate  
FROM (  
  SELECT request_date as date_day, COUNT(DISTINCT sender_id, send_to_id) AS fr_count, 0 AS ra_count  
  FROM FriendRequest  
  GROUP BY date_day  
  UNION  
  SELECT accept_date as date_day, 0 AS fr_count, COUNT(DISTINCT requester_id, accepter_id) AS ra_count  
  FROM RequestAccepted  
  GROUP BY date_day  
) AS sub_query  
GROUP BY sub_query.date_day  
ORDER BY sub_query.date_day;

查询结果

+----------+-----------+  
|date_day  |accept_rate|  
+----------+-----------+  
|2016-06-01|0.00       |  
|2016-06-02|0.00       |  
|2016-06-03|0.00       |  
|2016-06-08|0.00       |  
|2016-06-09|1.00       |  
|2016-06-10|0.00       |  
|2016-07-09|0.00       |  
|2016-07-10|0.00       |  
|2016-08-09|0.00       |  
+----------+-----------+
avatar
文章
阅读
粉丝