LeetCode_sql_day31(1384.按年度列出销售总额)

描述 1384.按年度列出销售总额

数据准备

分析

法一

法二

代码

总结

描述 1384.按年度列出销售总额

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年。

编写解决方案，找出每个产品每年的总销售额，并包含 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天。销售总额 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。

数据准备

sql">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)
Truncate table Product
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')
Truncate table Sales
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')

分析

法一

①首先用recursive循环构造出这个report_year 我是用period_start来构造计数器是开始年份加一停止条件是结束年份不大于开始年份就停止
with recursive year as (select product_id,
                               cast(year(period_start) as char) report_year,
                               year(period_end)                 end,
                               average_daily_sales,
                               period_start,
                               period_end
                        from Sales
                        union all
                        select product_id, report_year + 1, end, average_daily_sales, period_start, period_end
                        from year
                        where end > report_year)
select * from year
②使用case when条件判断每个产品在该年的销售天数

如果report_year 与开始和结束的时间年份相同说明该产品只在当年销售用结束时间减去开始时间 + 1 即可

如果report_year与开始时间年份相同小于结束时间的年份说明该产品在下一年还在销售此时用concat(开始时间年份，‘-12-31’) 当年最后一天的时间减去开始销售的时间作为该年销售天数

如果report_year不等于开始时间年份也不等于结束时间年份说明该产品该年一整年都在销售返回365

如果report_year大于开始时间年份等于结束时间年份说明在该年销售结束用该年结束时间减去构造的concat(结束时间年份,'-01-01') +1 即该年销售天数
select product_id,
                report_year,
                average_daily_sales,
                case
                    when report_year = year(period_start) and report_year = year(period_end)
                        then datediff(period_end, period_start) + 1 -- 产品只在当年卖
                    when report_year = year(period_start) and report_year < year(period_end)
                        then datediff(concat(report_year, '-12-31'), period_start) + 1 -- 产品销售年份与产品开始销售年份相同时
                    when report_year > year(period_start) and report_year < year(period_end)
                        then 365 -- 产品销售年份 跨越了多个年份
                    when report_year > year(period_start) and report_year = year(period_end) -- 产品销售年份与产品结束销售年份相同时
                        then datediff(period_end, concat(report_year, '-01-01')) + 1
                    end r1
         from year
③最后按照题目要求求出总销售额，连接产品名称并且排序
select t2.product_id, product_name, report_year, r1 * average_daily_sales total_amount
from t2
         join product on t2.product_id = product.product_id
order by product_id, report_year
# 用cast强制转换report_year 年是因为题目最后要求使用字符串类型

法二

①利用recursive求出最大的Sales表中的时间差

with recursive diff as (select 0 as day_diff
                        union all
                        select day_diff + 1
                        from diff
                        where day_diff < (select max(datediff(period_end, period_start)) from Sales))
select * from diff

② 用开始销售的日期加上日期差就是销售的年份这里很巧妙的将diff循环表与Sales表连接起来相当于标明了每一天的销售年份

with recursive diff as (select 0 as day_diff
                        union all
                        select day_diff + 1
                        from diff
                        where day_diff < (select max(datediff(period_end, period_start)) from Sales))
select sales.product_id,
       cast(year(date_add(period_start, interval day_diff day)) as char) as report_year, 
day_diff
from diff
         join sales on datediff(period_end, period_start) >= day_diff

③此时就可以根据产品，年份计算该年总的销售额同时连接产品名称排序 cast强制转换year的类型为字符串型原因同上

select sales.product_id,
       product_name,
       cast(year(date_add(period_start, interval day_diff day)) as char) as report_year,
       sum(average_daily_sales)
       total_amount
#         day_diff
from diff
         join sales on datediff(period_end, period_start) >= day_diff
         join product on Sales.product_id = Product.product_id
group by product_id,
         product_name,
         report_year
order by product_id, report_year

代码

sql"># 法一：
with recursive year as (select product_id,
                               cast(year(period_start) as char) report_year,
                               year(period_end)                 end,
                               average_daily_sales,
                               period_start,
                               period_end
                        from Sales
                        union all
                        select product_id, report_year + 1, end, average_daily_sales, period_start, period_end
                        from year
                        where end > report_year)

   , t2 as (select product_id,
                   report_year,
                   average_daily_sales,
                   case
                       when report_year = year(period_start) and report_year = year(period_end)
                           then datediff(period_end, period_start) + 1 -- 产品只在当年卖
                       when report_year = year(period_start) and report_year < year(period_end)
                           then datediff(concat(report_year, '-12-31'), period_start) + 1 -- 产品销售年份与产品开始销售年份相同时
                       when report_year > year(period_start) and report_year < year(period_end)
                           then 365 -- 产品销售年份 跨越了多个年份
                       when report_year > year(period_start) and report_year = year(period_end) -- 产品销售年份与产品结束销售年份相同时
                           then datediff(period_end, concat(report_year, '-01-01')) + 1
                       end r1
            from year)
select t2.product_id, product_name, report_year, r1 * average_daily_sales total_amount
from t2
         join product on t2.product_id = product.product_id
order by product_id, report_year;
# 法二：
with recursive diff as (select 0 as day_diff
                        union all
                        select day_diff + 1
                        from diff
                        where day_diff < (select max(datediff(period_end, period_start)) from Sales))
select sales.product_id,
       product_name,
       cast(year(date_add(period_start, interval day_diff day)) as char) as report_year,
       sum(average_daily_sales)                                             total_amount
#        day_diff
from diff
         join sales on datediff(period_end, period_start) >= day_diff
         join product on Sales.product_id = Product.product_id
group by product_id, product_name, report_year
order by product_id, report_year;