1251.平均售价
表:Prices
(product_id,start_date,end_date) 是 prices 表的主键(具有唯一值的列的组合)。
prices 表的每一行表示的是某个产品在一段时期内的价格。
每个产品的对应时间段是不会重叠的,这也意味着同一个产品的价格时段不会出现交叉。
表:UnitsSold
该表可能包含重复数据。
该表的每一行表示的是每种产品的出售日期,单位和产品 id。
编写解决方案以查找每种产品的平均售价。average_price 应该 四舍五入到小数点后两位。
返回结果表 无顺序要求 。
结果格式如下例所示。
示例 1:
解释:
平均售价 = 产品总价 / 销售的产品数量。
产品 1 的平均售价 = ((100 * 5)+(15 * 20) )/ 115 = 6.96
产品 2 的平均售价 = ((200 * 15)+(30 * 30) )/ 230 = 16.96
分析:
要查询产品的平均售价,这里必然涉及到连表查询,需要以产品的Id作为连接条件进行连表查询,
我们先设计出初步的sql语句
select *
from prices p left join unitsSold u
on p.product_id = u.product_id
注意产品的价格是在不同时期有着不同的价格**.所以我们应该还需要加时间的判断加入进来.那我们是将时间的判断放在where当中呢**?还是在连表的过程中加入判断条件.我们来仔细分析看看
假设有这样一组数据
时间判断加入where条件中
select *
from prices p left join unitsSold u
on p.product_id = u.product_id
where purchase_date between start_date and end_date
查询出来的数据为
如果加入到连表查询的过程中
select *
from prices p left join unitsSold u
on p.product_id = u.product_id
and purchase_date between start_date and end_date
查出来的数据为
发现了没有,如果加入where条件后在id=3的商品过滤掉了,而我们的需求应该是没有售出商品商品的平均售价应该是为0而不是不存在这条数据.所以我们应该加时间的判断加入到连表查询当中.
接下来就简单了,我们需要计算平均售价即可,注意一个小问题,当没有销量时,平均售价应该是为0,而不是null,所以我们需要加入if判断.由于需要保留两个小数,所以最后的结果需要调用round函数.(记住还需要以商品id为分组哦,因为我们统计的是每一个商品的平均售价)
所以最终的答案为
select p.product_id,
if(round(sum(units*price)/sum(units),2) is null,
0,round(sum(units*price)/sum(units),2)) average_price
from prices p left join unitsSold u
on p.product_id = u.product_id and
purchase_date between start_date and end_date
group by p.product_id
当然可以使用ifnull简化一下
select p.product_id,ifnull(round(sum(units*price)/sum(units),2),0) average_price
from prices p left join unitsSold u
on p.product_id = u.product_id and
purchase_date between start_date and end_date
group by p.product_id