1251.平均售价
表: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。
编写解决方案:
1.以查找每种产品的平均售价。average_price 应该 四舍五入到小数点后两位。
2.如果产品没有任何售出,则假设其平均售价为 0。
返回结果表 无顺序要求 。
结果格式如下例所示。
示例 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
解题:
SELECT
P.product_id,
ROUND(ifnull(SUM(units * price) / SUM(units), 0),2) AS average_price
FROM
Prices P
LEFT JOIN
UnitsSold U
ON
P.product_id = U.product_id
where purchase_date BETWEEN start_date AND end_date
or u.product_id is null
GROUP BY
P.product_id
ORDER BY
P.product_id;
确保表中没有销售记录的产品也被包含在结果中
or u.product_id is null