leetcode 1384 按年度列出销售总额(postgresql)

需求

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 列存储销售期内该产品的日平均销售额。
销售日期范围为2018年到2020年。

编写一段 SQL 查询，查找出每个产品每年的总销售额，并包含 product_id , product_name , report_year 以及 total_amount 。

返回结果并按 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天。销售总额 35100 = 3500。
LC T-shirt 在 2018-12-01 至 2020-01-01 期间销售，该产品在2018年、2019年、2020年的销售时间分别是31天、365天、1天，2018年、2019年、2020年的销售总额分别是3110=310、36510=3650、110=10。
LC Keychain 在 2019-12-01 至 2020-01-31 期间销售，该产品在2019年、2020年的销售时间分别是：31天、31天，2019年、2020年的销售总额分别是311=31、311=31。

输入

输出

with t1 as (
-- 通过generate_series,生成从开始时间到结束时间内的每一天的时间序列
-- 通过extract提取出来 年,根据 年 和id进行分组,求出每个id在每年的销售额
select extract(year from generate_series(period_start, period_end, '1 day')) as year1,product_id,average_daily_sales
from sales
),t2 as (
select year1,product_id,sum(average_daily_sales) as total_amount
from t1
group by year1,product_id
)
select p.product_id,product_name,year1 as report_year,total_amount
from t2
left join product p
on t2.product_id=p.product_id
order by product_id,report_year