4，SQL训练之：力扣1251，平均售价

SQL

目录

一，原题力扣链接

二，题干

三，建表语句

四，分析

五，SQL解答

解法一：

解法一优化：

 解法二  开窗

六，验证

开窗验证：

解法一验证

七，知识点总结

---

一，原题力扣链接

. - 力扣（LeetCode）

二，题干

表：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 都可以开窗