力扣白嫖日记(sql)

前言

练习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

---

结果：

---

总结：

能运行就行。

---