当前位置： 首页 > article >正文

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

article 2024/9/23 5:39:41

目录

描述  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。

数据准备

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

代码

# 法一：
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;

总结

①加深对recursive循环的理解

②对于法二所展现的思想 需要积累

先求出最大的时间差 然后用开始时间相加 获取每一天的年份  省去了复杂的条件判断


http://www.kler.cn/news/315667.html

相关文章：

  • C# 委托与事件 观察者模式
  • Java项目实战II基于Java+Spring Boot+MySQL的植物健康系统(开发文档+源码+数据库)
  • 设计模式之复合模式
  • 高级java每日一道面试题-2024年9月16日-框架篇-Spring MVC和Struts的区别是什么?
  • Redis发布和订阅
  • 波分技术基础 -- Liquid OTN技术特性
  • 高效打造知识图谱，使用LlamaIndex Relik实现实体关联和关系抽取
  • 火车站高铁站站点时刻查询网站计算机毕设/动车站点时刻查询
  • WebRTC编译后替换libwebrtc.aar时提示找不到libjingle_peerconnection_so.so库
  • 基于单片机控制的程控开关电源研究
  • list(一)
  • 基于微信小程序的健身房管理系统
  • ROS第五梯：ROS+VSCode+C++单步调试
  • [Golang] Context
  • GNU链接器（LD）：设置入口点（ENTRY命令）的用法及实例解析
  • 科研绘图系列：R语言箱线图（boxplot）
  • error -- unsupported GNU version gcc later than 10 are not supported；（gcc、g++）
  • 计算机毕业设计 基于SpringBoot的小区运动中心预约管理系统的设计与实现 Java实战项目 附源码+文档+视频讲解
  • 【python】深度优先搜索文件夹并移动全部doc文件
  • 自闭症儿童寄宿学校：打造良好的学习和生活环境
  • 速盾：高防cdn除了快还有什么好处？
  • Maven国内镜像（四种）
  • 20240923 每日AI必读资讯
  • vue源码分析（九）—— 合并配置
  • ChromaDB教程_2024最新版（上）
  • 【sgCreateCallAPIFunction】自定义小工具：敏捷开发→调用接口方法代码生成工具
  • Java String trim()方法
  • 如何创建模板提示prompt
  • Linux移植之系统烧写
  • Redis 中 String 字符串类型详解