1934:确认率
主要考察AVG函数的使用 基本等同于SUM/COUNT
- 使用
AVG
函数计算confirmed
的平均值,如果不存在则为NULL - 使用
IFNULL
把NULL值转换为0 - 最后使用
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;