4，sql训练之：力扣1251，平均售价-爱代码爱编程
SQL
目录
一，原题力扣链接
二，题干
表：
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
三，建表语句
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 * from prices;
select * from UnitsSold;
四，分析
分析 求出每种产品的平均售价 一产品分组，AVG求价格 分析2个表 第一个表是 价格表 记录产品id 产品开始时间 结束时间 价格 第二个表是 产品数目表 记录 产品id 和产品销售日期 和总数 求平均售价 就是 不同产品 在 不同时间范围类 销售的总数 *单价 /总数思路一： 拼接价格表和产品数据表 以id作为 主链接条件
以不同时间的日期 between and 作为副链接条件
得到表如下：
比如要区间的总价格 这是单价*数量
要不同产品的总价格 就是不同日期的 单价*数量 加起来
所以可以选择开窗函数
也选择不开窗 在包一层：
在上表的基础上 拿到了 不同日期的总价格
继续以id分组 拿到不同产品的总价格 然后除以该产品的总数 在四舍五入 最后 ifnull判断一下
解法二：用窗口函数，第一步直接统计产品的总数，和该产品的总价格
最后外面包裹一下 然后 round四舍五入，保留2位数，最后在ifnull 判断一下有无空数量的
五，SQL解答
解法一：
#拿到 产品id 产品价格 产品数量
with t1 as (
select p1.product_id as p_id,p1.price as p_price,u1.units as u_units
from unitssold u1 right join prices p1
on u1.product_id=p1.product_id and u1.purchase_date between p1.start_date and p1.end_date
) ,
#在原来的基础 拿到 不同日期的销售总价 价格*数量
t2 as (
select t1.p_id as pid,t1.p_price as pprice,t1.u_units as 总数,
t1.p_price*t1.u_units as 总价格
from t1
),
#以id分组 求总数的平均值
t3 as (
select pid as product_id,ifnull(round(sum(总价格)/sum(总数),2),0) as average_price
from t2
group by pid
)
select * from t3;解法一优化：
-- 优化 缩短sql语句
with t1 as (
select p1.product_id as product_id,
p1.price as price,
u1.units as units,
p1.price*u1.units as 不同日期的总数
from prices p1
left join unitssold u1
on p1.product_id=u1.product_id
and u1.purchase_date between p1.start_date and p1.end_date
)
select product_id,ifnull(round(sum(不同日期的总数)/sum(units),2),0) as average_price
from t1 group by product_id;解法二 开窗
-- 优化 缩短sql语句
with t1 as (
select p1.product_id as product_id,
p1.price as price,
u1.units as units,
p1.price*u1.units as 不同日期的总数,
sum(p1.price*u1.units) over (partition by p1.product_id) ro1,
sum(u1.units) over (partition by p1.product_id) or2
from prices p1
left join unitssold u1
on p1.product_id=u1.product_id
and u1.purchase_date between p1.start_date and p1.end_date
)
select distinct product_id,ifnull(round(t1.ro1/t1.or2,2),0) average_price from t1;
六，验证
开窗验证：
解法一验证
七，知识点总结
- 四舍五入函数的运用 round
- ifnull函数的运用 判断里面的阐述时候是null，如果是null则返回为0 反之则返回原来结果；
- 字段的 乘法与除法运算
- 开窗函数 缩短计算过程
- 聚合函数 sum min max count avg 都可以开窗