LeetCode:Database 55.レポートのレコードII
要求:SQLを作成して検索します:ゴミ広告として報告された投稿の中で、削除された投稿の毎日の平均割合は、小数点以下2位に四捨五入されます.
Actionsテーブルの構造:
Removalsテーブルの構造:
Actionsテーブル:
Removalsテーブル:
Result Table:
SQL文:注意:onは左外の表と右外の表に対して無効で、whereフィルタリングを使用する必要があります
Actionsテーブルの構造:
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| user_id | int |
| post_id | int |
| action_date | date |
| action | enum |
| extra | varchar |
+---------------+---------+
, 。
Removalsテーブルの構造:
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| post_id | int |
| remove_date | date |
+---------------+---------+
post_id。
Actionsテーブル:
+---------+---------+-------------+--------+--------+
| 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テーブル:
+---------+-------------+
| 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%
, , 。
SQL文:注意:onは左外の表と右外の表に対して無効で、whereフィルタリングを使用する必要があります
#1. 1
SELECT ROUND(AVG(c1)*100,2) AS average_daily_percent
FROM(
SELECT COUNT(IFNULL(DISTINCT b.post_id,0))/COUNT(DISTINCT a.post_id) AS c1,a.action_date AS d1
FROM a
JOIN a b
ON a.action_date=b.action_date AND b.post_id IN(SELECT post_id FROM removals)
GROUP BY a.action_date)c
#2. 2
select round(avg(c1)*100,2) as average_daily_percent
from(
select count(distinct a2.post_id)/count(distinct a1.post_id) as c1
from actions as a1
left join actions as a2
on a1.action_date=a2.action_date
and a2.post_id in(select post_id from removals)
where a1.extra='spam'
group by a1.action_date)b;