目录
描述 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：
输入： Producttable: +------------+--------------+ | product_id | product_name | +------------+--------------+ | 1 | LC Phone | | 2 | LC T-Shirt | | 3 | LC Keychain | +------------+--------------+Salestable: +------------+--------------+-------------+---------------------+ | 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。
数据准备
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_endfrom Salesunion allselect product_id, report_year + 1, end, average_daily_sales, period_start, period_endfrom yearwhere 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,casewhen 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')) + 1end r1from year
③最后按照题目要求求出总销售额，连接产品名称 并且排序
select t2.product_id, product_name, report_year, r1 * average_daily_sales total_amount from t2join 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_diffunion allselect day_diff + 1from diffwhere day_diff < (select max(datediff(period_end, period_start)) from Sales)) select * from diff
② 用开始销售的日期加上日期差就是销售的年份 这里很巧妙的将diff循环表与Sales表连接起来 相当于标明了每一天的销售年份
with recursive diff as (select 0 as day_diffunion allselect day_diff + 1from diffwhere 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 diffjoin 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 diffjoin sales on datediff(period_end, period_start) >= day_diffjoin product on Sales.product_id = Product.product_id group by product_id,product_name,report_year order by product_id, report_year
代码
# 法一：
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_endfrom Salesunion allselect product_id, report_year + 1, end, average_daily_sales, period_start, period_endfrom yearwhere end > report_year), t2 as (select product_id,report_year,average_daily_sales,casewhen 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')) + 1end r1from year)
select t2.product_id, product_name, report_year, r1 * average_daily_sales total_amount
from t2join product on t2.product_id = product.product_id
order by product_id, report_year;
# 法二：
with recursive diff as (select 0 as day_diffunion allselect day_diff + 1from diffwhere 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 diffjoin sales on datediff(period_end, period_start) >= day_diffjoin product on Sales.product_id = Product.product_id
group by product_id, product_name, report_year
order by product_id, report_year;总结
①加深对recursive循环的理解
②对于法二所展现的思想 需要积累
先求出最大的时间差 然后用开始时间相加 获取每一天的年份 省去了复杂的条件判断
![[Redis][Set]详细讲解](https://i-blog.csdnimg.cn/direct/ce17d3baffe74718a5fc30b8504bf0b4.png)
