Leetcode1384.按年度列出销售总额（困难）

作者: kaka22 | 来源:发表于2020-07-25 23:12 被阅读0次

Leetcode1384.按年度列出销售总额（困难）
Leetcode 1384. 按年度列出销售总额
面对困难列出清单
教育培训机构各项支出及详细分析
谷物饮料在饮料市场的发展是大势所趋
day02
我的年度十佳，2017院线电影
潜水艇销售法
postgresql 列出表名和字段名称类型
《行动力》读书笔记 05

这道题确实值得困难的难度
也很有意思
值得慢慢来做

问题
Table: Product

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| product_name  | varchar |
+---------------+---------+

product_id is the primary key for this table.
product_name is the name of the product.

Table: Sales

+---------------------+---------+
| Column Name         | Type    |
+---------------------+---------+
| product_id          | int     |
| period_start        | varchar |
| period_end          | date    |
| average_daily_sales | int     |
+---------------------+---------+

product_id is the primary key for this table.
period_start and period_end indicates the start and end date for sales period, both dates are inclusive.
The average_daily_sales column holds the average daily sales amount of the items for the period.

Write an SQL query to report the Total sales amount of each item for each year, with corresponding product name, product_id, product_name and report_year.

Dates of the sales years are between 2018 to 2020. Return the result table ordered by product_id and report_year.

The query result format is in the following example:

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                   |
+------------+--------------+-------------+---------------------+

Result table:

+------------+--------------+-------------+--------------+
| 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 was sold for the period of 2019-01-25 to 2019-02-28, and there are 35 days for this period. Total amount 35*100 = 3500.
LC T-shirt was sold for the period of 2018-12-01 to 2020-01-01, and there are 31, 365, 1 days for years 2018, 2019 and 2020 respectively.
LC Keychain was sold for the period of 2019-12-01 to 2020-01-31, and there are 31, 31 days for years 2019 and 2020 respectively.

生成数据

Create table If Not Exists Product (product_id int, product_name varchar(30))
Create table If Not Exists Sales (product_id varchar(30), 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 
  '2018' AS report_year,
  DATE('2018-01-01') AS start_date,
  DATE('2018-12-31') AS end_date 
FROM
  DUAL 
UNION
ALL 
SELECT 
  '2019' AS YEAR,
  DATE('2019-01-01') AS start_date,
  DATE('2019-12-31') AS end_date 
FROM
  DUAL 
UNION
ALL 
SELECT 
  '2020' AS YEAR,
  DATE('2020-01-01') AS start_date,
  DATE('2020-12-31') AS end_date 
FROM
  DUAL

from dual 是一个虚拟表纯粹是为了满足select … from…这一习惯问题，mysql会忽略对该表的引用。

有三种情况

销售区间包含自然年的前半段
这种情况下是period_end与start_date的差值加一就是所需天数
销售区间包含自然年的后半段
这种情况下 end_date与period_start的差值加一就是需要的天数
销售区间包含自然年的全部
这时period_end与period_start的差值+1即可

以上表与sales表按以上三种情况进行连接

SELECT *
FROM Sales1 AS S, (SELECT '2018'             AS report_year,
                DATE('2018-01-01') AS start_date,
                DATE('2018-12-31') AS end_date
         FROM DUAL
         UNION ALL
         SELECT '2019'             AS YEAR,
                DATE('2019-01-01') AS start_date,
                DATE('2019-12-31') AS end_date
         FROM DUAL
         UNION ALL
         SELECT '2020'             AS YEAR,
                DATE('2020-01-01') AS start_date,
                DATE('2020-12-31') AS end_date
         FROM DUAL) AS t
WHERE   # 销售区间包含自然年的后半段
    S.`period_start` BETWEEN t.start_date AND t.end_date 
    # 销售区间包含自然年的前半段
    OR  S.`period_end` BETWEEN t.start_date AND t.end_date 
    # 整个自然年都在销售区间之内
    OR  (S.`period_start`>= t.start_date AND S.`period_end` <= t.end_date);

我开始的想法是根据三种分类分别求出三种的天数差
但是是行不通的
首先忽略了这种情况比如20190401- 2019-0501 满足销售区间包含自然年的后半段也满足销售区间包含自然年的前半段
但是其计算方式只要period_end与period_start做时间差即可

其实发现上边是有"规律"的
1.都是end与start 求datediff
2.end取得是两个end中最小的 start则取得是两个start中的最大的
时间差记得加1

SELECT S.product_id, t.report_year,
(DATEDIFF(IF(S.`period_end`< t.end_date, S.`period_end`,t.end_date),
IF(S.`period_start` < t.start_date, t.start_date, S.`period_start`))+ 1) *S.`average_daily_sales` AS total_amount
FROM Sales1 AS S, (SELECT '2018'             AS report_year,
                DATE('2018-01-01') AS start_date,
                DATE('2018-12-31') AS end_date
         FROM DUAL
         UNION ALL
         SELECT '2019'             AS YEAR,
                DATE('2019-01-01') AS start_date,
                DATE('2019-12-31') AS end_date
         FROM DUAL
         UNION ALL
         SELECT '2020'             AS YEAR,
                DATE('2020-01-01') AS start_date,
                DATE('2020-12-31') AS end_date
         FROM DUAL) AS t
WHERE   # 销售区间包含自然年的后半段
    S.`period_start` BETWEEN t.start_date AND t.end_date 
    # 销售区间包含自然年的前半段
    OR  S.`period_end` BETWEEN t.start_date AND t.end_date 
    # 整个自然年都在销售区间之内
    OR  (t.start_date > S.`period_start` AND t.end_date < S.`period_end`);

最后当然是简单的两表连接啦

SELECT P.product_id, P.`product_name`, tmp.report_year, tmp.total_amount
FROM (
SELECT S.product_id, t.report_year,
(DATEDIFF(IF(S.`period_end`< t.end_date, S.`period_end`,t.end_date),
IF(S.`period_start` < t.start_date, t.start_date, S.`period_start`))+ 1) *S.`average_daily_sales` AS total_amount
FROM Sales1 AS S, (SELECT '2018'             AS report_year,
                DATE('2018-01-01') AS start_date,
                DATE('2018-12-31') AS end_date
         FROM DUAL
         UNION ALL
         SELECT '2019'             AS YEAR,
                DATE('2019-01-01') AS start_date,
                DATE('2019-12-31') AS end_date
         FROM DUAL
         UNION ALL
         SELECT '2020'             AS YEAR,
                DATE('2020-01-01') AS start_date,
                DATE('2020-12-31') AS end_date
         FROM DUAL) AS t
WHERE   # 销售区间包含自然年的后半段
    S.`period_start` BETWEEN t.start_date AND t.end_date 
    # 销售区间包含自然年的前半段
    OR  S.`period_end` BETWEEN t.start_date AND t.end_date 
    # 整个自然年都在销售区间之内
    OR  (t.start_date > S.`period_start` AND t.end_date < S.`period_end`)
) AS tmp
JOIN Product3 AS P
ON P.product_id = tmp.product_id
ORDER BY P.product_id, P.`product_name`;

Leetcode1384.按年度列出销售总额（困难）
这道题确实值得困难的难度也很有意思值得慢慢来做问题Table: Product product_id is th...
Leetcode 1384. 按年度列出销售总额
https://leetcode-cn.com/problems/total-sales-amount-by-ye...
面对困难列出清单
选择找工作，之前三年积累的资历也会随着离开而没有。有得有失，得到比失去多，就可以了。向上之路很难，大家都是一样，...
教育培训机构各项支出及详细分析
根据十几年的工作经验和总结，中小培训机构在年度市场计划中年度市场预算，一般情况下，市场营销费用为课程销售总额的8%...
谷物饮料在饮料市场的发展是大势所趋
据《中国饮料市场分析年度报告》，饮料行业的销售总额已突破1000亿元人民币。如此巨大的“市场蛋糕”确实令人垂涎，但...
day02
按导演名排重列出所有电影(只显示导演)，并按导演名正序排列列出按上映年份最新上线的4部电影按电影名字母序升序排...
我的年度十佳，2017院线电影
2017年，我上电影院看了79部电影，以真实感人为首要标准，从中选出个人年度十佳，按上映时间顺序列出，排名不分先后...
潜水艇销售法
一.按顺序列出买家系统的步骤？二.回顾自己的真实案例，陈述客户用买家系统对付你的案例？三.按顺序列出传统的卖家...
postgresql 列出表名和字段名称类型
列出表名排除postgresql 中的系统表按表名排序列出字段类型和名称 atttypid 字段类型对应id...
《行动力》读书笔记 05
列出梦想清单，分为：人生梦想清单和年度梦想清单。人生梦想清单是记录这辈子想实现的梦想，比较长远的梦想。年度梦...