前言
练习sql语句,所有题目来自于力扣(https://leetcode.cn/problemset/database/)的免费数据库练习题。
今日题目:
1251.平均售价
表:Prices
列名 | 类型 |
---|---|
product_id | int |
start_date | date |
end_date | date |
price | int |
(product_id,start_date,end_date) 是 prices 表的主键(具有唯一值的列的组合)。
prices 表的每一行表示的是某个产品在一段时期内的价格。每个产品的对应时间段是不会重叠的,这也意味着同一个产品的价格时段不会出现交叉。
表:UnitsSold
列名 | 类型 |
---|---|
product_id | int |
purchase_date | date |
units | int |
该表可能包含重复数据。该表的每一行表示的是每种产品的出售日期,单位和产品 id。
编写解决方案以查找每种产品的平均售价。average_price 应该 四舍五入到小数点后两位。
我那不值一提的想法:
我的思路:首先梳理表内容,题目给了两张表,一张价格表,其中记录了产品id,开始的日期,结束日期,以及价格,一张出售表,记录了产品id,购买日期,以及购买数量。
其中两张表的product_id是相同的。其次分析需求,需要查找每种商品的平均售价,那么肯定要对商品的id进行分组,以求平均值。我原本以为这样就结束了,但是没想到各个时间段的价格是不一样的,所以还需要对价格进行筛选,使其满足在start_date 和 end_date 区间内。
select p.product_id,round(sum(p.price*u.units)/sum(u.units),2) as average_price
from Prices p
left join UnitsSold u
on p.product_id = u.product_id
where u.purchase_date >=p.start_date and u.purchase_date <= p.end_date
group by p.product_id
最开始我的代码是这样,然后发现会有售卖不出去的情况,并且在结果表中也会显示0,那么我们就还需要average_price的结果前面加上ifnull(),判断是否空值,如果是空值则输出0.
select p.product_id,ifnull(round(sum(p.price*u.units)/sum(u.units),2),0) as average_price
from Prices p
left join UnitsSold u
on p.product_id = u.product_id
where u.purchase_date >=p.start_date and u.purchase_date <= p.end_date
group by p.product_id
但是我的代码好像是中无法显示0,我也不知道为啥,明明已经加了ifnull了,为什么还是把空值给筛选掉了。后来我查了相关资料,是因为在这种情况下,where 中的条件会在连接后才生效,这就意味着它会筛选掉不符合条件的行,也就是没有卖出去的产品,但如果通过将条件移到 ON 子句中,我们的条件就在连接过程中完成了,即使没有卖出产品,也会出现在表中,而不是直接被筛选掉。
select p.product_id,ifnull(round(sum(p.price*u.units)/sum(u.units),2),0) 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
结果:
总结:
能运行就行。