Leetcode SQL(二）

目录

1435. Create a Session Bar Chart????

196. 删除重复的电子邮箱

1179. 重新格式化部门表

 

---

1435. Create a Session Bar Chart????

https://leetcode-cn.com/problems/create-a-session-bar-chart/

题解

一：关注如何解决解决分组丢失问题。

bin

[0-5>

[5-10>

[10-15>

15 or more

 

 

 

 

 

 

select "[0-5>" bin union
select "[5-10>" bin union
select "[10-15>" bin union
select "15 or more" bin

其实表a中的select就可以Session表中的分好类，但是会存在分组丢失的情况，也即这四种情况中，若有未出现在Session表中的，则不会存在记录，用union解决一下。 

select b.bin, ifnull(total,0) total from 
(select case 
when 0 <= duration and duration < 300 then "[0-5>"
when 300 <= duration and duration < 600 then "[5-10>"
when 600 <= duration and duration < 900 then "[10-15>"
else "15 or more" end bin,
count(1) total
from Sessions 
group by bin
)a 
right join 
(select "[0-5>" bin union
select "[5-10>" bin union
select "[10-15>" bin union
select "15 or more" bin)b
on a.bin = b.bin

196. 删除重复的电子邮箱

https://leetcode-cn.com/problems/delete-duplicate-emails/

编写一个 SQL 查询，来删除 Person 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个。

提示：执行 SQL 之后，输出是整个 Person 表。使用 delete 语句。

题解

一：错解，如下，报错You can't specify target table for update in FROM clause含义:不能在同一表中查询的数据作为同一表的更新数据。

delete from Person where Id not in 
(select min(Id) Id from Person group by Email)

解决方法如下，建立一个临时表，也就是说将select出的结果再通过中间表select一遍。 

delete from Person where Id not in 
(select a.Id Id from (select min(Id) Id from Person group by Email)a)

二：将此表与它自身在电子邮箱列中连接起来,然后我们需要找到其他记录中具有相同电子邮件地址的更大 ID。所以我们可以像这样给 WHERE 子句添加一个新的条件。

select p1.* from Person p1
join Person p2
where p1.Email = p2.Email and p1.Id > p2.Id

因为我们已经得到了要删除的记录，所以我们最终可以将该语句更改为 DELETE。 

delete p1 from Person p1
join Person p2
where p1.Email = p2.Email and p1.Id > p2.Id

1179. 重新格式化部门表

https://leetcode-cn.com/problems/reformat-department-table/

(id, month) 是表的联合主键。这个表格有关于每个部门每月收入的信息。月份（month）可以取下列值 ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]。

编写一个 SQL 查询来重新格式化表，使得新的表中有一个部门 id 列和一些对应 每个月 的收入（revenue）列。查询结果格式如下面的示例所示：

题解

一：转自https://leetcode-cn.com/problems/reformat-department-table/solution/zhong-xin-ge-shi-hua-bu-men-biao-by-hu-tool/，

SELECT id,
CASE `month` WHEN 'Jan' THEN revenue END Jan_Revenue,
CASE `month` WHEN 'Feb' THEN revenue END Feb_Revenue,
CASE `month` WHEN 'Mar' THEN revenue END Mar_Revenue,
CASE `month` WHEN 'Apr' THEN revenue END Apr_Revenue,
CASE `month` WHEN 'May' THEN revenue END May_Revenue,
CASE `month` WHEN 'Jun' THEN revenue END Jun_Revenue,
CASE `month` WHEN 'Jul' THEN revenue END Jul_Revenue,
CASE `month` WHEN 'Aug' THEN revenue END Aug_Revenue,
CASE `month` WHEN 'Sep' THEN revenue END Sep_Revenue,
CASE `month` WHEN 'Oct' THEN revenue END Oct_Revenue,
CASE `month` WHEN 'Nov' THEN revenue END Nov_Revenue,
CASE `month` WHEN 'Dec' THEN revenue END Dec_Revenue
FROM Department;

SELECT id,
SUM(CASE `month` WHEN 'Jan' THEN revenue END) Jan_Revenue,
SUM(CASE `month` WHEN 'Feb' THEN revenue END) Feb_Revenue,
SUM(CASE `month` WHEN 'Mar' THEN revenue END) Mar_Revenue,
SUM(CASE `month` WHEN 'Apr' THEN revenue END) Apr_Revenue,
SUM(CASE `month` WHEN 'May' THEN revenue END) May_Revenue,
SUM(CASE `month` WHEN 'Jun' THEN revenue END) Jun_Revenue,
SUM(CASE `month` WHEN 'Jul' THEN revenue END) Jul_Revenue,
SUM(CASE `month` WHEN 'Aug' THEN revenue END) Aug_Revenue,
SUM(CASE `month` WHEN 'Sep' THEN revenue END) Sep_Revenue,
SUM(CASE `month` WHEN 'Oct' THEN revenue END) Oct_Revenue,
SUM(CASE `month` WHEN 'Nov' THEN revenue END) Nov_Revenue,
SUM(CASE `month` WHEN 'Dec' THEN revenue END) Dec_Revenue
FROM Department
group by id;

1322. 广告效果????

https://leetcode-cn.com/problems/ads-performance/

(ad_id, user_id) 是该表的主键，该表的每一行包含一条广告的 ID(ad_id)，用户的 ID(user_id) 和用户对广告采取的行为 (action)，action 列是一个枚举类型 ('Clicked', 'Viewed', 'Ignored') 。

一家公司正在运营这些广告并想计算每条广告的效果。广告效果用点击通过率（Click-Through Rate：CTR）来衡量，公式如下:

写一条SQL语句来查询每一条广告的 ctr ， ctr 要保留两位小数。结果需要按 ctr 降序、按 ad_id 升序 进行排序。

查询结果示例如下：

对于 ad_id = 1, ctr = (2/(2+1)) * 100 = 66.67
对于 ad_id = 2, ctr = (1/(1+2)) * 100 = 33.33
对于 ad_id = 3, ctr = (1/(1+1)) * 100 = 50.00
对于 ad_id = 5, ctr = 0.00, 注意 ad_id = 5 没有被点击 (Clicked) 或查看 (Viewed) 过
注意我们不关心 action 为 Ingnored 的广告
结果按 ctr（降序），ad_id（升序）排序

题解

带条件的sum和count函数。mark一下，带条件的sum和count函数的用法转自https://www.jianshu.com/p/db0f3d0307b2，

解释一下，当action="Clicked"时，返回1，否则返回0，故sum函数0对其不会有影响，相当于排除不等于"Clicked"，count是计数，0也会统计进去的，所以会统计所有的值包括不等于"Clicked"的，但是不会统计null值，所以我们在条件中加入了or null，当action不等于"Clicked"时，返回0，即False，看or后面的值，则返回null，而这种情况下count不会统计null值，当然count(*)会统计null值。

select ad_id, 
round(ifnull(sum(action="Clicked") / (sum(action="Clicked" or action="Viewed")), 0) * 100, 2) ctr
from Ads 
group by ad_id
order by ctr desc, ad_id asc

select ad_id, 
round(ifnull(count(action="Clicked" or null) / (count(action="Clicked" or action="Viewed" or null)), 0) * 100, 2) ctr
from Ads 
group by ad_id
order by ctr desc, ad_id asc