题目
已知有表如下,记录了每个产品id、产品名称、产品销售开始日期、产品销售结束日期以及产品日均销售金额,请计算出每个产品每年的销售金额
期望结果
说明
- 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年的销售总额分别是3110=310、36510=3650、1*10=10。
- LC Keychain 在 2019-12-01 至 2020-01-31 期间销售,该产品在2019年、2020年的销售时间分别是:31天、31天,2019年、2020年的销售总额分别是311=31、311=31。
分析:题目中给出的是每个产品的开始时间和结束时间,这里最关键的问题在于一个时间段可能跨年,并且不知道跨几年。所以我们先构建一个年份维表,利用年度维表去切割源每个产品的销售时间。
1构造年度维表
如下图所示,由于题目中只有3年,所以我们暂先构造3年的年度维表,包含由于题目中只有3年,所以我们暂先构造3年的年度维表。
2 将原始数据与维表关联
把原始数据与年份维表进行笛卡尔积,得到每年与原始数据的一个交叉值.
3 日期交叉的切割分析
上个步骤发生了笛卡尔积,每个产品都会与年度维表的每一年做关联,我们分别取每个产品与每个年度的交集,如果没有产生交集的年度就过滤,这样即可切割出每个产品的每年售卖的时间段
如图1所示,下面为产品为“LC Phone”在2019年与维度表关联的情况,因为产品为“LC Phone”只有在2019年有销量,所以与年度维表在时间上取交集后就是“LC Phone”的售卖时间端
图1
2018年“LC Phone”没有销售,我们来分析一下怎么将2018年的记录过滤掉
如图2所示,由于该产品在2018年没有被售卖,取period_end和year_end_day的较大值,所以这里取的是period_start的值为2019-01-25作为开始日期,取period_end和year_end_day的较小值,所以这里取的是period_end的值为2018-12-31作为结束日期,这里结束日期小于开始日期,这样就可以将行记录过滤掉,也符合实际没有交集的事实,该产品在2020年与年度维表的交集同样没有交集,也需要被过滤掉。
图2
接下来我们分析下’LC T-Shirt’产品的情况,如图3所以,2018年取到的交集是开始时间为2018-12-01,结束时间时2018-12-31
图3
同理,'LC T-Shirt’产品在2019年的交集如图4所示,取到的交集是开始时间为2019-01-01,结束时间时2019-12-31
图4
同理,'LC T-Shirt’产品在2019年的交集如图5所示,取到的交集是开始时间为2020-01-01,结束时间时2020-01-01
图5
以上分析了产品分别与年度维表的每年交集的所有情况,接下来,我们将结束日期小于开始日期的记录过滤掉,就可以得到每个产品每年的售卖时间天数
4 比较计算每年每个产品在售天数
with t_product_sales as (
select 1 as product_id, 'LC Phone' as product_name, '2019-01-25 00:00:00' as period_start, '2019-02-28 00:00:00' as period_end, 100 as average_daily_sales union all
select 2 as product_id, 'LC T-Shirt' as product_name, '2018-12-01 00:00:00' as period_start, '2020-01-01 00:00:00' as period_end, 10 as average_daily_sales union all
select 3 as product_id, 'LC Keychain' as product_name, '2019-12-01 00:00:00' as period_start, '2020-01-31 00:00:00' as period_end, 1 as average_daily_sales
),
dim_year as (
select '2018' as year, '2018-01-01' as year_first_day, '2018-12-31' as year_end_day union all
select '2019' as year, '2019-01-01' as year_first_day, '2019-12-31' as year_end_day union all
select '2020' as year, '2020-01-01' as year_first_day, '2020-12-31' as year_end_day
),
tmp as (
select product_id,
product_name,
period_start,
period_end,
average_daily_sales,
year,
year_first_day,
year_end_day,
datediff(
if(to_date(period_end) > to_date(year_end_day), to_date(year_end_day), to_date(period_end)),
if(to_date(period_start) > to_date(year_first_day), to_date(period_start),
to_date(year_first_day))) as date_diff
from t_product_sales
left join dim_year)
select product_id,
product_name,
year,
date_diff
from tmp
where date_diff >= 0
5 筛选符合条件数据,计算最终结果
with t_product_sales as (
select 1 as product_id, 'LC Phone' as product_name, '2019-01-25 00:00:00' as period_start, '2019-02-28 00:00:00' as period_end, 100 as average_daily_sales union all
select 2 as product_id, 'LC T-Shirt' as product_name, '2018-12-01 00:00:00' as period_start, '2020-01-01 00:00:00' as period_end, 10 as average_daily_sales union all
select 3 as product_id, 'LC Keychain' as product_name, '2019-12-01 00:00:00' as period_start, '2020-01-31 00:00:00' as period_end, 1 as average_daily_sales
),
dim_year as (
select '2018' as year, '2018-01-01' as year_first_day, '2018-12-31' as year_end_day union all
select '2019' as year, '2019-01-01' as year_first_day, '2019-12-31' as year_end_day union all
select '2020' as year, '2020-01-01' as year_first_day, '2020-12-31' as year_end_day
),
tmp as (
select product_id,
product_name,
period_start,
period_end,
average_daily_sales,
year,
year_first_day,
year_end_day,
datediff(
if(to_date(period_end) > to_date(year_end_day), to_date(year_end_day), to_date(period_end)),
if(to_date(period_start) > to_date(year_first_day), to_date(period_start),
to_date(year_first_day))) as date_diff
from t_product_sales
left join dim_year)
select product_id,
product_name,
year,
(date_diff + 1) * average_daily_sales as total_amount
from tmp
where date_diff >= 0