essay
hot50-平均售价
#mysql
hot50——平均售价
表：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 应该 四舍五入到小数点后两位。如果产品没有任何售出，则假设其平均售价为 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(p.price*u.units)/sum(u.units), 0), 2) as average_price from Prices as p
left join UnitsSold as u
on p.product_id = u.product_id and u.purchase_date between p.start_date and p.end_date
group by p.product_id解析：
- 必须用 LEFT JOIN
为了保留所有产品。即使某产品没卖出（UnitsSold 无记录），LEFT JOIN 也能保留它，配合IFNULL将其平均售价设为 0，防止被过滤掉。 - 连接需匹配日期 (BETWEEN)
价格是动态的。必须在 ON 中加上 purchase_dateBETWEENstart_dateANDend_date，确保销量匹配到对应时间段的正确价格。 - 加权平均与空值处理
公式为 总销售额 / 总销量。使用 SUM(price * units) / SUM(units) 计算，并用 IFNULL(..., 0) 将“无销量”导致的 NULL 结果转换为 0。