- 学习：知识的初次邂逅
- 复习：知识的温故知新
- 练习：知识的实践应用
目录
一，原题力扣链接
二，题干
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');
select * from Product;
select * from Sales;四，分析
思路
表格大法
第一步:计算年份时间差 可不要写死2018 2019 2020哦 假如表中开始年份和结束年份不是这个呢?
第二步:根据最大年份时间差值的年 生成一个递归序列
第三步:递归表和销售表做内连接,条件 年时间差>= 递归的n 这样就可以把年份展开拉平了
第四步:用开始时间+年时间差 得到年份 也就是repoet_year
第五步: 分别开两列,计算开始年份的最终天数,和结束年份的开始天数
第六步:开始判断四种情况:
- 1,同年. 开始年份和结束年份相同 则用 同年的结束年份-开始的年份+1 [天差运算需要+1天才准确]
- 2,差值的第一年: 开始的年份等于时间差年份 且时间差年份<结束的年份 则:时间差年份的最后一天-开始执行日期+1
- 3,差值的中间年份,开始的年份<时间差年份 且时间差年份<结束的年份 则:时间差年份的最后一天-时间差的第一天+1 题目中表中只有一年 真实的业务中,这种可以能由多个年份 注意不要写365哦 可能有闰年平年之分哦
- 4,差值的最后一年,开始的年份<时间差年份,且时间差年份 =结束的年份 则:结束的时间 -结束的时间差年份第一天;
第七步:时间天数差*日平均销售额 等到该两次的总销售额
第八步:内连接产品表,拿到产品的名称, 第七步:指定输出对应的列,并排序,输出
具体看表格逻辑:
解题过程
mysql+pandas代码实现上述表格计算逻辑
第一步:计算年份时间差 可不要写死2018 2019 2020哦 假如表中开始年份和结束年份不是这个呢? 在mysql
第二步:根据最大年份时间差值的年 生成一个递归序列
在mysql
第三步:递归表和销售表做内连接,条件 年时间差>= 递归的n 这样就可以把年份展开拉平了
在mysql
第四步:用开始时间+年时间差 得到年份 也就是repoet_year
在mysql
第五步: 分别开两列,计算开始年份的最终天数,和结束年份的开始天数
在mysql
第六步:开始判断四种情况: 1,同年. 开始年份和结束年份相同 则用 同年的结束年份-开始的年份+1 [天差运算需要+1天才准确] 2,差值的第一年: 开始的年份等于时间差年份 且时间差年份<结束的年份 则:时间差年份的最后一天-开始执行日期+1 3,差值的中间年份,开始的年份<时间差年份 且时间差年份<结束的年份 则:时间差年份的最后一天-时间差的第一天+1 题目中表中只有一年 真实的业务中,这种可以能由多个年份 注意不要写365哦 可能有闰年平年之分哦 4,差值的最后一年,开始的年份<时间差年份,且时间差年份 =结束的年份 则:结束的时间 -结束的时间差年份第一天; 在mysql
第七步:时间天数差*日平均销售额 等到该两次的总销售额 在mysql
第八步:内连接产品表,拿到产品的名称, 在mysql
第七步:指定输出对应的列,并排序,输出
在mysql
在pands
五，SQL解答
with recursive t1 as (
select 0 as n
union all
select n+1 from t1 where n<(with t101 as (
select
cast(year(period_end) as char ) - cast(year(period_start) as char ) as diff_year
from sales) select max(diff_year) from t101)
)
# select * from t1;
,t2 as (
select product_id, period_start, period_end, average_daily_sales,
cast(year(period_end) as char ) - cast(year(period_start) as char ) as diff_year
from sales
)
# select * from t2;
,t3 as (
select product_id, period_start, period_end, average_daily_sales,diff_year,
concat( year(date_add(period_start,interval n year)),'-12-31') s_end,
concat( year(period_end),'-01-01') e_start
from t1 join t2 where diff_year>=n order by product_id,n
)
# select * from t3;
,t4 as (
select product_id, period_start, period_end, average_daily_sales,s_end,e_start,
case when year(period_start) =year(s_end) and year(s_end)=year(period_end) then datediff(period_end,period_start)+1
when year(period_start) = year(s_end) and year(s_end)<year(period_end) then datediff(s_end,period_start)+1
when year(period_start) < year(s_end) and year(s_end)<year(period_end) then datediff(s_end,concat(year(s_end),'-01-01'))+1
when year(period_start) < year(s_end) and year(s_end)=year(period_end) then datediff(period_end,e_start)+1
end as diff_day
from t3
)
# select * from t4;
select
t4.product_id,p.product_name,
substring(s_end,1,4) as report_year,
diff_day*average_daily_sales as total_amount
from t4 left join product p on t4.product_id=p.product_id order by product_id,report_year;
六，验证
七，知识点总结
- sql实现递归
- 时间函数的运算
- sql中强制类型转换 cast 便于计算
- sql中时间差的运算 datediff
- sql中字符串的拼接运用
- sql中casewhen的运用
- sql中时间的截取运用 substring
- 类连接的运用
- 左连接的运用
- 分组聚合的运用
标签：product,年份,period,力扣,01,2019,SQL,1384,id From： https://blog.csdn.net/qq_55006020/article/details/142981334
- 学习：知识的初次邂逅
- 复习：知识的温故知新
- 练习：知识的实践应用