1384. 按年度列出销售总额（难度：困难）

题目描述

（通过次数4,032 | 提交次数6,887，通过率58.55%）

Product表：
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| product_name  | varchar |
+---------------+---------+
product_id 是这张表的主键。
product_name 是产品的名称。

Sales表：
+---------------------+---------+
| Column Name         | Type    |
+---------------------+---------+
| product_id          | int     |
| period_start        | date    |
| period_end          | date    |
| average_daily_sales | int     |
+---------------------+---------+
product_id 是这张表的主键。
period_start和 period_end是该产品销售期的起始日期和结束日期，且这两个日期包含在销售期内。
average_daily_sales 列存储销售期内该产品的日平均销售额。

编写一段 SQL 查询每个产品每年的总销售额，并包含 product_id, product_name 以及 report_year 等信息。
销售年份的日期介于 2018 年到 2020 年之间。你返回的结果需要按product_id 和 report_year 排序。
查询结果格式如下例所示。

示例 1：
输入：
Product table:
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 1          | LC Phone     |
| 2          | LC T-Shirt   |
| 3          | LC Keychain  |
+------------+--------------+
Sales table:
+------------+--------------+-------------+---------------------+
| product_id | period_start | period_end  | average_daily_sales |
+------------+--------------+-------------+---------------------+
| 1          | 2019-01-25   | 2019-02-28  | 100                 |
| 2          | 2018-12-01   | 2020-01-01  | 10                  |
| 3          | 2019-12-01   | 2020-01-31  | 1                   |
+------------+--------------+-------------+---------------------+
输出：
+------------+--------------+-------------+--------------+
| product_id | product_name | report_year | total_amount |
+------------+--------------+-------------+--------------+
| 1          | LC Phone     |    2019     | 3500         |
| 2          | LC T-Shirt   |    2018     | 310          |
| 2          | LC T-Shirt   |    2019     | 3650         |
| 2          | LC T-Shirt   |    2020     | 10           |
| 3          | LC Keychain  |    2019     | 31           |
| 3          | LC Keychain  |    2020     | 31           |
+------------+--------------+-------------+--------------+
解释：
LC Phone 在 2019-01-25 至 2019-02-28 期间销售，该产品销售时间总计35天。销售总额 35*100 = 3500。
LC T-shirt 在 2018-12-01至 2020-01-01 期间销售，该产品在2018年、2019年、2020年的销售时间分别是31天、365天、1天，2018年、2019年、2020年的销售总额分别是31*10=310、365*10=3650、1*10=10。
LC Keychain 在 2019-12-01至 2020-01-31 期间销售，该产品在2019年、2020年的销售时间分别是：31天、31天，2019年、2020年的销售总额分别是31*1=31、31*1=31。

来源：力扣（LeetCode）
链接：https://leetcode.cn/problems/total-sales-amount-by-year

//测试数据
Create table If Not Exists Product (product_id int, product_name varchar(30));
Create table If Not Exists Sales (product_id int, period_start date, period_end date, average_daily_sales int);

insert into Product (product_id, product_name) values ('1', 'LC Phone ');
insert into Product (product_id, product_name) values ('2', 'LC T-Shirt');
insert into Product (product_id, product_name) values ('3', 'LC Keychain');

insert into Sales (product_id, period_start, period_end, average_daily_sales) values ('1', '2019-01-25', '2019-02-28', '100');
insert into Sales (product_id, period_start, period_end, average_daily_sales) values ('2', '2018-12-01', '2020-01-01', '10');
insert into Sales (product_id, period_start, period_end, average_daily_sales) values ('3', '2019-12-01', '2020-01-31', '1');

解题思路

Sales源表记录了某一个时间区间内的平均销售额。这个时间区间可能是某一年内的，也可能是跨年的。

现题目要求，计算每一年的总销售额。

每一年的总销售额=这一年中每天的销售额的汇总

**每天的销售额按它所属的时间区间内的日平均销售额计算。**

这样，我们只要计算出每一天对应的时间区间，然后使用这个时间区间内的日平均销售额作为这一天的销售额。再按年汇总即可。

因为源表是个时间区间，而我们需要这个时间区间内的每一天。

所以，需要将时间区间按天展开。

在SQL语言中，递归可以很好的完成这样的计算。

with 
recursive t(product_id,period_date) as (
    select product_id,period_start as period_date 
    from Sales
    union all
    select product_id,date_add(period_date,interval 1 day) period_date 
    from t
    where exists(select 1 
                 from Sales s 
                 where t.product_id = s.product_id 
                 and t.period_date < s.period_end)
)
select * from t;

上面的SQL语句中，从product_id、区间起始日出发，按天依次递增，直到区间结束日。这样就完成了区间的展开。

接下来，将日期转化为年，然后与product_id一起，分组汇总统计即可。

参考SQL

未特别说明的情况下，参考SQL为基于MySQL8.0实现。

select
    round(sum(a.TIV_2016),2) as tiv_2016
from insurance a
where exists(select 1 from insurance b where a.TIV_2015 = b.TIV_2015 and a.PID<>b.PID)
and not exists(select 1 from insurance c where a.LAT = c.LAT and a.LON = c.LON and a.PID<>c.PID);