力扣高频SQL 50题（基础版）第十六题之1251. 平均售价

力扣高频SQL 50题（基础版）第十六题

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。

编写解决方案以查找每种产品的平均售价。average_price 应该 四舍五入到小数点后两位。

返回结果表 无顺序要求 。

思路分析

实现过程

准备数据

Create table If Not Exists Prices (product_id int, start_date date, end_date date, price int)
Create table If Not Exists UnitsSold (product_id int, purchase_date date, units int)
Truncate table Prices
insert into Prices (product_id, start_date, end_date, price) values ('1', '2019-02-17', '2019-02-28', '5')
insert into Prices (product_id, start_date, end_date, price) values ('1', '2019-03-01', '2019-03-22', '20')
insert into Prices (product_id, start_date, end_date, price) values ('2', '2019-02-01', '2019-02-20', '15')
insert into Prices (product_id, start_date, end_date, price) values ('2', '2019-02-21', '2019-03-31', '30')
Truncate table UnitsSold
insert into UnitsSold (product_id, purchase_date, units) values ('1', '2019-02-25', '100')
insert into UnitsSold (product_id, purchase_date, units) values ('1', '2019-03-01', '15')
insert into UnitsSold (product_id, purchase_date, units) values ('2', '2019-02-10', '200')
insert into UnitsSold (product_id, purchase_date, units) values ('2', '2019-03-22', '30')

实现方式

#要考虑到空值的情况
select p.product_id, ifnull(round(sum(price * units) / sum(units), 2), 0) as average_price
from Prices p 
left join UnitsSold u on u.product_id = p.product_id
and u.purchase_date between p.start_date and p.end_date
group by p.product_id

结果截图

总结

ifnull(round(sum(price * units) / sum(units), 2), 0)

这个表达式首先计算 price 和 units 的乘积的总和，然后除以 units 的总和，得到平均价格。

使用 round 函数将结果四舍五入到小数点后两位。

ifnull 函数检查 round 的结果。如果结果为 NULL（这在正常情况下不太可能发生，除非 sum(price * units) 和 sum(units) 都是 NULL），则返回 0。

if(round(sum(price * units) / sum(units) is null, 2), 0, round(sum(price * units) / sum(units)))

这个表达式看起来有语法错误。正确的 IF 函数语法应该是 IF(condition, true_value, false_value)。

假设表达式是 if(round(sum(price * units) / sum(units), 2) is null, 0, round(sum(price * units) / sum(units), 2))，那么它的意思是：首先计算平均价格，并四舍五入到小数点后两位。

然后检查这个四舍五入后的结果是否为 NULL。如果是 NULL，则返回 0；否则返回四舍五入后的结果。

its), 2))，那么它的意思是：首先计算平均价格，并四舍五入到小数点后两位。

然后检查这个四舍五入后的结果是否为 NULL。如果是 NULL，则返回 0；否则返回四舍五入后的结果。

但是，由于 round 函数的结果不可能是 NULL，除非 sum(price * units) 或 sum(units) 为 0 导致除以 0 的情况，这个 IF 条件实际上是多余的。