每日刷力扣SQL题(一)

1934：确认率

主要考察AVG函数的使用 基本等同于SUM/COUNT

1. 使用AVG函数计算confirmed的平均值，如果不存在则为NULL
2. 使用IFNULL把NULL值转换为0
3. 最后使用ROUND精确到小数点后两位

# Write your MySQL query statement below
select s.user_id as user_id , 
Round(IFNULL(AVG(c.action='confirmed'),0),2) as confirmation_rate 
from Signups s 
left join Confirmations c on s.user_id = c.user_id
Group by s.user_id

620 有趣的电影

考察非和余数函数的运用

Select *
from cinema
where  description != "boring" AND mod( id , 2) = 1
Order by rating DESC

提交中的优化策略：

select * from cinema 
where id & 1 
and not description like '%boring%' order by rating desc;

1. 位运算符 (& 1) 和 MOD 函数

• id & 1: 这个表达式使用位运算符检查 id 的二进制表示的最后一位是否为1，即判断 id 是否是奇数。位运算通常比函数调用更快，因为它直接操作二进制位，无需调用函数。

• MOD(id, 2) = 1: 这个表达式使用 MOD 函数判断 id 是否是奇数。MOD 函数需要进行除法操作，然后取余数，相对位运算来说，通常会稍微慢一些，尽管差异可能非常微小。

2. 字符串比较 (!= vs LIKE)

• description != "boring": 这个表达式直接比较字符串是否等于 "boring"。这种比较通常非常快速，尤其是在索引列上进行比较时，性能会更好。

• description NOT LIKE '%boring%': 这个表达式使用 LIKE 进行模式匹配。由于 % 号在字符串的开头和结尾，这意味着数据库必须扫描整个 description 列来查找是否包含子字符串 "boring"。这通常比直接的字符串比较要慢，尤其是在没有索引的情况下。

3. 排序操作 (ORDER BY)

• 两个查询中的排序部分 (ORDER BY rating DESC) 是相同的，因此这部分对性能没有差异。

综合比较

• 第一种查询：id & 1 通常比 MOD(id, 2) 更快，但是 NOT LIKE '%boring%' 可能比 != "boring" 更慢。因此，查询整体性能可能取决于 description 列上的数据量和是否有相关索引。

• 第二种查询：MOD(id, 2) = 1 可能稍慢，但 description != "boring" 的性能可能更高。如果 description 列上有索引，那么这条查询的性能可能会比第一条更好。

实际性能测试

尽管有上述理论分析，实际性能差异可能需要通过具体的执行计划（使用 EXPLAIN）和实际运行时测量来确定，尤其是当数据库表中的数据量较大时。数据库的优化器可能会对查询进行优化，使得两者的性能差异变得不明显。

1251平均售价

左连接： 连接条件：对应的产品id 以及 售买时间需要在产品对应价格期间

Sum函数的运用：计算出产品每个价格的销售总额后，同样的使用 SUM 函数计算出产品所有时间的销售总额，然后除以总数量并使用 ROUND 函数保留两位小数即可。

# Write your MySQL query statement below
select p.product_id as product_id, Round(IFNULL(Sum(p.price*u.units)/Sum(u.units),0),2) as average_price
#select p.product_id as product_id, Sum(u.units) as average_price
from Prices p  
left join UnitsSold u 
on p.product_id=u.product_id 
and u.purchase_date >= p.start_date AND u.purchase_date <= p.end_date
group by p.product_id;

1075 项目员工I

# Write your MySQL query statement below
select p.project_id as project_id, 
Round( Sum(e.experience_years)/ Count(p.employee_id) ,2) as average_years
from Project p join Employee e 
on p.employee_id = e.employee_id
group by p.project_id

1633 各赛事的用户注册率

思路：

需要查询Users的用户的数量，所以我们需要写一个直接查询的方法，免得每次查询都要调用查询用户数量，原始是select(count(*)from Users)
改成(select count(user_id) as total from Users) as t,
这样就能直接调用。

Select r.contest_id , Round(count(r.user_id IS NOT NULL)/t.total * 100,2) as percentage 
from Register r,(select count(user_id) as total from Users) as t
group by r.contest_id
Order by percentage DESC,contest_id ASC;