leetcode 1809 没有广告的剧集

　

表：Playback

+-------------+------+
| Column Name | Type |
+-------------+------+
| session_id  | int  |
| customer_id | int  |
| start_time  | int  |
| end_time    | int  |
+-------------+------+
session_id 是该表中具有唯一值的列。
customer_id 是观看该剧集的客户的 id。
剧集播放时间包含start_time（开始时间） 及 end_time（结束时间）
可以保证的是，start_time（开始时间）<= end_time（结束时间），一个观众观看的两个剧集的时间不会出现重叠。

 

表：Ads

+-------------+------+
| Column Name | Type |
+-------------+------+
| ad_id       | int  |
| customer_id | int  |
| timestamp   | int  |
+-------------+------+
ad_id 是该表中具有唯一值的列。
customer_id 为 观看广告的用户 id
timestamp 表示广告出现的时间点

 

编写解决方案找出所有没有广告出现过的剧集。

返回结果 无顺序要求 。

返回结果格式如下例所示：

 

示例 1：

输入：
Playback table:
+------------+-------------+------------+----------+
| session_id | customer_id | start_time | end_time |
+------------+-------------+------------+----------+
| 1          | 1           | 1          | 5        |
| 2          | 1           | 15         | 23       |
| 3          | 2           | 10         | 12       |
| 4          | 2           | 17         | 28       |
| 5          | 2           | 2          | 8        |
+------------+-------------+------------+----------+
Ads table:
+-------+-------------+-----------+
| ad_id | customer_id | timestamp |
+-------+-------------+-----------+
| 1     | 1           | 5         |
| 2     | 2           | 17        |
| 3     | 2           | 20        |
+-------+-------------+-----------+
输出：
+------------+
| session_id |
+------------+
| 2          |
| 3          |
| 5          |
+------------+
解释：
广告1出现在了剧集1的时间段，被观众1看到了。
广告2出现在了剧集4的时间段，被观众2看到了。
广告3出现在了剧集4的时间段，被观众2看到了。
我们可以得出结论，剧集1 、4 内，起码有1处广告。 剧集2 、3 、5 没有广告。

方案1 

解题答案： 

# 解题答案1 .

select
    session_id
from
    Playback p left join Ads a
on
    p.customer_id = a.customer_id
group by
    1
having
    sum(case when a.timestamp between p.start_time and p.end_time then 0 else 1 end) = count(1);

 

验证答案.

SELECT session_id 
,SUM(CASE when timestamp between start_time AND end_time then 0 else 1 end) a 
,count(1)  b  --这个是重点. 
FROM Playback ASt1
LEFT JOIN ads AS t2
USING(customer_id)
GROUP BY 1
#HAVING SUM(CASE when timestamp between start_time AND end_time then 0 else 1 end) = count(1); 

如下是输出结果： 
| session_id | a | b |
| ---------- | - | - |
| 1          | 0 | 1 |
| 2          | 1 | 1 |
| 3          | 2 | 2 |
| 4          | 0 | 2 |
| 5          | 2 | 2 |    a 代表当 广告时间在 剧情时间中的话，返回0 ， 不在的话返回1 ， 不断累加. 如果所有seession_id都不在则 count（1）和sum里的值相等.

 

方案2 ： 

SELECT
DISTINCT session_id
FROM Playback AS t1
LEFT JOIN Ads AS t2 ON t1.customer_id = t2.customer_id 
AND t2.timestamp BETWEEN t1.start_time AND t1.end_time
WHERE t2.customer_id IS NULL;

SELECT
t1.*,t2.*
FROM Playback AS t1
LEFT JOIN Ads AS t2 ON t1.customer_id = t2.customer_id 
AND t2.timestamp BETWEEN t1.start_time AND t1.end_time
 #WHERE t2.customer_id IS NULL;

| session_id | customer_id | start_time | end_time | ad_id | customer_id | timestamp |
| ---------- | ----------- | ---------- | -------- | ----- | ----------- | --------- |
| 1          | 1           | 1          | 5        | 1     | 1           | 5         |
| 2          | 1           | 15         | 23       | null  | null        | null      |
| 3          | 2           | 10         | 12       | null  | null        | null      |
| 4          | 2           | 17         | 28       | 3     | 2           | 20        |
| 4          | 2           | 17         | 28       | 2     | 2           | 17        |
| 5          | 2           | 2          | 8        | null  | null        | null      |