目录
题目
表:Prices
+---------------+---------+ | Column Name | Type | +---------------+---------+ | product_id | int | | start_date | date | | end_date | date | | price | int | +---------------+---------+ (product_id,start_date,end_date) 是 prices表的主键(具有唯一值的列的组合)。prices表的每一行表示的是某个产品在一段时期内的价格。 每个产品的对应时间段是不会重叠的,这也意味着同一个产品的价格时段不会出现交叉。
表:UnitsSold
+---------------+---------+ | Column Name | Type | +---------------+---------+ | product_id | int | | purchase_date | date | | units | int | +---------------+---------+ 该表可能包含重复数据。 该表的每一行表示的是每种产品的出售日期,数量和产品 id。
编写解决方案以查找每种产品的平均售价。average_price
应该 四舍五入到小数点后两位。
返回结果表 无顺序要求 。
结果格式如下例所示。
示例 1:
输入: Prices table: +------------+------------+------------+--------+ | product_id | start_date | end_date | price | +------------+------------+------------+--------+ | 1 | 2019-02-17 | 2019-02-28 | 5 | | 1 | 2019-03-01 | 2019-03-22 | 20 | | 2 | 2019-02-01 | 2019-02-20 | 15 | | 2 | 2019-02-21 | 2019-03-31 | 30 | +------------+------------+------------+--------+ UnitsSold table: +------------+---------------+-------+ | product_id | purchase_date | units | +------------+---------------+-------+ | 1 | 2019-02-25 | 100 | | 1 | 2019-03-01 | 15 | | 2 | 2019-02-10 | 200 | | 2 | 2019-03-22 | 30 | +------------+---------------+-------+ 输出: +------------+---------------+ | product_id | average_price | +------------+---------------+ | 1 | 6.96 | | 2 | 16.96 | +------------+---------------+ 解释: 平均售价 = 产品总价 / 销售的产品数量。 产品 1 的平均售价 = ((100 * 5)+(15 * 20) )/ 115 = 6.96 产品 2 的平均售价 = ((200 * 15)+(30 * 30) )/ 230 = 16.96
分析
产品价格表和出售时间表
左连接,from Prices p left join UnitsSold u on p.product_id = u.product_id
价格对应时间段,出售日期对应时间点
使用between找到对应价格,where u.purchase_date between p.start_date and p.end_date
考虑产品尚未出售
或关系,or u.product_id is null
查找每种产品的平均售价
按照产品分组,group by p.product_id
平均售价,sum(units*price)/sum(units
存在null情况,ifnull(,0)
average_price 应该 四舍五入到小数点后两位
round函数,round(ifnull(sum(units*price)/sum(units),0),2)
代码
select p.product_id, round(ifnull(sum(units*price)/sum(units),0),2) average_price
from Prices p left join UnitsSold u
on p.product_id = u.product_id
where u.purchase_date between p.start_date and p.end_date or u.product_id is null
group by p.product_id
标签:product,price,units,2019,SQL,date,平均,id,售价 From: https://blog.csdn.net/weixin_73404807/article/details/141127778