查询每个产品每年总销售额

题目

  已知有表如下，记录了每个产品id、产品名称、产品销售开始日期、产品销售结束日期以及产品日均销售金额，请计算出每个产品每年的销售金额

期望结果

说明

1. LC Phone 在 2019-01-25 至 2019-02-28 期间销售，该产品销售时间总计35天。销售总额 35*100 = 3500。
2. 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。
3. 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