按年度拆分销售总额：一个区间跨多年
LeetCode 1384 · 区间拆分、和年份表关联、区间重叠计算
面试怎么回答
我会构造一张年份表，用「区间重叠」条件（开始 <= 年末 且 结束 >= 年初）和销售区间关联，每一年的天数 = LEAST(结束, 年末) - GREATEST(开始, 年初) + 1，再乘日均额。区间按日历拆分在数仓里很常见，比如订阅收入按月分摊，都是这个套路。
先把这段讲顺；面试官追问时，再展开下面的原理和细节。
详细讲解
题意
销售表记录每个产品的一段销售期（起止日期都包含在内）和这段时间的日均销售额。销售期可能跨越 2018 到 2020 中的多个年份。按产品和年份输出每年的销售总额，按产品 ID 和年份排序。
表结构
- Product：product_id（主键）、product_name
- Sales：product_id（主键）、period_start、period_end、average_daily_sales
思路
把一个跨年的区间拆到各年：构造一张年份表（2018、2019、2020），让销售区间和每一年的区间做重叠判断，有重叠才关联。重叠天数 = MIN(两个结束日) - MAX(两个开始日) + 1，再乘以日均销售额。原解法为每一年写一段 UNION，逻辑相同但更冗长。
参考解法
(
select Sales.product_id, product_name, '2018' as 'report_year', if(period_start<'2019-01-01', (datediff(if(period_end<'2019-01-01', period_end, date('2018-12-31')), if(period_start>='2018-01-01', period_start, date('2018-01-01')))+1)*average_daily_sales, 0) as total_amount
from Sales
join Product on Sales.product_id = Product.product_id
having total_amount>0
)
union(
select Sales.product_id, product_name, '2019' as 'report_year', if( period_start<'2020-01-01', (datediff(if(period_end<'2020-01-01', period_end, date('2019-12-31')), if(period_start>='2019-01-01', period_start, date('2019-01-01')))+1)*average_daily_sales , 0) as total_amount
from Sales
join Product on (Sales.product_id = Product.product_id )
having total_amount>0
)
union(
select Sales.product_id, product_name, '2020' as 'report_year', (datediff(if(period_end<'2021-01-01', period_end, date('2020-12-31')), if(period_start>='2020-01-01', period_start, date('2020-01-01')))+1)*average_daily_sales as total_amount
from Sales
join Product on (Sales.product_id = Product.product_id)
having total_amount>0
)
order by product_id, report_year另一种写法：和年份表做区间重叠关联
SELECT s.product_id, p.product_name, y.yr AS report_year,
(DATEDIFF(LEAST(s.period_end, DATE(CONCAT(y.yr, '-12-31'))),
GREATEST(s.period_start, DATE(CONCAT(y.yr, '-01-01')))) + 1) * s.average_daily_sales AS total_amount
FROM Sales s
JOIN Product p ON p.product_id = s.product_id
JOIN (SELECT '2018' AS yr UNION ALL SELECT '2019' UNION ALL SELECT '2020') y
ON s.period_start <= DATE(CONCAT(y.yr, '-12-31')) AND s.period_end >= DATE(CONCAT(y.yr, '-01-01'))
ORDER BY s.product_id, report_year易错点
- 天数要 +1，因为起止日期都包含在内。
- 只和销售期有重叠的年份关联，否则会算出负数天数。