今天也继续来练SQL了。今天先不练牛客了，去leetcode看看《高频SQL基础版》。leetcode的平均难度会比牛客要高一点，主要是leetcode的测试用例会多一点，牛客只有几个，所以leetcode提交的代码需要多注意一下隐藏的信息，不然很容易掉坑里。但是leetcode非常不好的一点就是它不提供测试用的sql代码，只提供数据库输出信息。。。。要么手动输入数据，要么用提交做测试（因为可以直接反馈出来最终的执行结果），然后你就会发现提交记录满满都是执行失败或者解答错误。。。QAQ 入正题了。
题目
题目链接：
平均售价
题干信息：
表：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 应该 四舍五入到小数点后两位。
返回结果表 无顺序要求 。
结果格式如下例所示。
输入：
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
解析
UnitsSold表记录的是某个商品在某天被购买的数量，对于同一个product_id会存在多行数据，这点需要注意。 Prices表的所有出现过的product_id都要在最终结果出现，即使该商品从未售出。
这题官方说考察的点是聚合函数，但是实际需要配合窗口函数使用。（难度偏大就体现在这里）
测试用例
这里提供两份测试用例数据。
drop table if exists Prices;
drop table if exists UnitsSold;
CREATE TABLE `Prices` (
`product_id` int DEFAULT NULL,
`start_date` date DEFAULT NULL,
`end_date` date DEFAULT NULL,
`price` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
CREATE TABLE `UnitsSold` (
`product_id` int DEFAULT NULL,
`purchase_date` date DEFAULT NULL,
`units` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
INSERT INTO `UnitsSold` (`product_id`, `purchase_date`, `units`) VALUES (1, '2019-01-17', 5);
INSERT INTO `UnitsSold` (`product_id`, `purchase_date`, `units`) VALUES (1, '2019-01-31', 4);
INSERT INTO `UnitsSold` (`product_id`, `purchase_date`, `units`) VALUES (2, '2019-01-29', 1);
INSERT INTO `UnitsSold` (`product_id`, `purchase_date`, `units`) VALUES (2, '2019-01-29', 2);
INSERT INTO `Prices` (`product_id`, `start_date`, `end_date`, `price`) VALUES (1, '2019-01-17', '2019-01-25', 18);
INSERT INTO `Prices` (`product_id`, `start_date`, `end_date`, `price`) VALUES (1, '2019-01-26', '2019-02-12', 5);
INSERT INTO `Prices` (`product_id`, `start_date`, `end_date`, `price`) VALUES (2, '2019-01-23', '2019-01-30', 3);
INSERT INTO `Prices` (`product_id`, `start_date`, `end_date`, `price`) VALUES (2, '2019-01-01', '2019-01-02', 18);
另一份测试数据
product_id purchase_date units
1 2019/1/15 1
1 2019/1/20 3
1 2019/1/19 3
2 2019/2/26 5
2 2019/2/4 4
2 2019/2/9 4
3 2019/1/5 5
4 2019/2/3 4
4 2019/1/10 4
4 2019/1/31 4
product_id start_date end_date price
1 2019/1/3 2019/1/7 14
1 2019/1/8 2019/1/13 10
1 2019/1/14 2019/1/27 8
2 2019/1/24 2019/2/11 16
2 2019/2/12 2019/2/22 19
2 2019/2/23 2019/3/9 14
3 2019/1/2 2019/1/8 9
4 2019/1/6 2019/1/16 13
4 2019/1/17 2019/2/1 16
4 2019/2/2 2019/2/6 10
作答
这里分步作答，先以UnitsSold表为准，将对应的售价都匹配上，查询出所有售出的商品的售价
select
tbb.product_id,
if(tba.units is null, 0, tba.units) as units,
if(tba.units * tbb.price is null, 0, tba.units * tbb.price) as value
from UnitsSold as tba right outer join Prices as tbb
on tba.product_id=tbb.product_id
and tba.purchase_date between tbb.start_date and tbb.end_date
;
结果如下：
发现部分商品售价未售出也会出现在这里，这部分数据不用。
可能跟前面说的话有点矛盾。这里解释一下，最终的结果需要prices表中的所有商品都出现，但是只要求解它的平均售价，那么该价格期间未售出的数据不需要统计，但是如果一个商品的所有定价均未售出，最终计算该商品平均售价为0。
还需要调整的一点就是，因为两份数据不平衡，prices表的一行数据可能对应UnitsSold表的多行数据，这个在后续的join后会导致笛卡尔积，这个是必须的，不然会对后续聚合结果产生影响，所以这个笛卡尔积的结果需要去重，这里如果是单纯按照units跟value两个字段去重，会发现将一部分数据也丢失了，所以这里引入UnitsSold.purchase_date用于辅助去重。
调整后的代码如下：
select
tbb.product_id,
tba.purchase_date,
if(tba.units is null, 0, tba.units) as units,
if(tba.units * tbb.price is null, 0, tba.units * tbb.price) as value
from UnitsSold as tba right outer join Prices as tbb
on tba.product_id=tbb.product_id
and tba.purchase_date between tbb.start_date and tbb.end_date HAVING units!=0
;
然后将上述结果与prices再次join，取得未售出的product_id。这时候未售出的商品的units跟value两个字段的值都是null，后续要根据这个来处理。（因为测试数据没有未售出的商品id，所以后面展示的结果都不会有这个数据，测试用例中会存在这个情况，或者可以生成个测试数据进行测试）
with tmp as (select
tbb.product_id,
tba.purchase_date,
if(tba.units is null, 0, tba.units) as units,
if(tba.units * tbb.price is null, 0, tba.units * tbb.price) as value
from UnitsSold as tba right outer join Prices as tbb
on tba.product_id=tbb.product_id
and tba.purchase_date between tbb.start_date and tbb.end_date HAVING units!=0)
select
distinct tb1.product_id,
tmp.purchase_date,
tmp.units,
tmp.value
from Prices as tb1 left outer join tmp
on tb1.product_id=tmp.product_id
;
然后就是聚合求解平均售价了
with tmp as (select
tbb.product_id,
tba.purchase_date,
if(tba.units is null, 0, tba.units) as units,
if(tba.units * tbb.price is null, 0, tba.units * tbb.price) as value
from UnitsSold as tba right outer join Prices as tbb
on tba.product_id=tbb.product_id
and tba.purchase_date between tbb.start_date and tbb.end_date HAVING units!=0)
SELECT
distinct t.product_id,
if(units is null, 0, round(sum(value) over(partition by product_id)/sum(units) over(partition by product_id), 2)) as average_price
from
(select
distinct tb1.product_id,
tmp.purchase_date,
tmp.units,
tmp.value
from Prices as tb1 left outer join tmp
on tb1.product_id=tmp.product_id) as t
;
