SQL面试题挑战01：打折日期交叉问题

问题：

如下为某平台的商品促销数据，字段含义分别为品牌名称、打折开始日期、打折结束日期，现在要计算每个品牌的打折销售天数（注意其中的交叉日期）。比如vivo的打折销售天数就为17天。

brand   start_date  end_date
xiaomi  2021-06-05  2021-06-09
xiaomi  2021-06-11  2021-06-21
vivo    2021-06-05  2021-06-15
vivo    2021-06-09  2021-06-21 
honor   2021-06-05  2021-06-21 
honor   2021-06-09  2021-06-15
redmi   2021-06-17  2021-06-26
huawei  2021-06-05  2021-06-26
huawei  2021-06-09  2021-06-15
huawei  2021-06-17  2021-06-21

SQL解答：

第一种方式：

根据每个品牌的促销开始时间和结束时间可以得到品牌每天促销的明细数据，然后，按品牌分组，日期去重就可以得到每个品牌打折销售天数。但此种方式适合数据量不大的情况，因为该方法会让数据膨胀的很厉害。

with temp as (
        select 'xiaomi' as brand   ,'2021-06-05' as start_date,'2021-06-09' as end_date
        union all
        select 'xiaomi' as brand   ,'2021-06-11' as start_date,'2021-06-21' as end_date
        union all
        select 'vivo' as brand   ,'2021-06-05' as start_date,'2021-06-15' as end_date
        union all
        select 'vivo' as brand   ,'2021-06-09' as start_date,'2021-06-21' as end_date
        union all 
        select 'honor' as brand  ,'2021-06-05' as start_date,'2021-06-21' as end_date
        union all 
        select 'honor' as brand  ,'2021-06-09' as start_date,'2021-06-15' as end_date
        union all
        select 'honor' as brand  ,'2021-06-17' as start_date,'2021-06-26' as end_date
        union all
        select 'huawei' as brand ,'2021-06-05' as start_date,'2021-06-26' as end_date
        union all
        select 'huawei' as brand ,'2021-06-09' as start_date,'2021-06-15' as end_date
        union all
        select 'huawei' as brand ,'2021-06-17' as start_date,'2021-06-21' as end_date
)

select
brand
,count(distinct dt) as dts
from (
select
    brand
    ,start_date
    ,end_date
    ,date_add(start_date,tmp.col_idx) as dt
from temp
lateral VIEW posexplode(split(repeat("#,",datediff(date(end_date), date(start_date))),'#')) tmp AS col_idx,col_val
) tt 
group by brand
;

备注：补充repeat函数

select  repeat("#,",datediff('2023-12-18','2023-12-01'))	
#,#,#,#,#,#,#,#,#,#,#,#,#,#,#,#,#,

select  split(repeat("#,",datediff('2023-12-18','2023-12-01')),'#')
["",",",",",",",",",",",",",",",",",",",",",",",",",",",",",",",",",","]

第二种方式：

第二种方式规避数据膨胀的情况，经过适当的处理，消除日期交叉的情况

with temp as (
        select 'xiaomi' as brand   ,'2021-06-05' as start_date,'2021-06-09' as end_date
        union all
        select 'xiaomi' as brand   ,'2021-06-11' as start_date,'2021-06-21' as end_date
        union all
        select 'vivo' as brand   ,'2021-06-05' as start_date,'2021-06-15' as end_date
        union all
        select 'vivo' as brand   ,'2021-06-09' as start_date,'2021-06-21' as end_date
        union all 
        select 'honor' as brand  ,'2021-06-05' as start_date,'2021-06-21' as end_date
        union all 
        select 'honor' as brand  ,'2021-06-09' as start_date,'2021-06-15' as end_date
        union all
        select 'honor' as brand  ,'2021-06-17' as start_date,'2021-06-26' as end_date
        union all
        select 'huawei' as brand ,'2021-06-05' as start_date,'2021-06-26' as end_date
        union all
        select 'huawei' as brand ,'2021-06-09' as start_date,'2021-06-15' as end_date
        union all
        select 'huawei' as brand ,'2021-06-17' as start_date,'2021-06-21' as end_date
)

select
brand
,sum(datediff(date(end_date),date(start_date))+1)
from
(
select
    brand
    ,case
    when start_date<=max_date then date_add(date(max_date),1)
    else start_date end
    as start_date
    ,end_date
    from(
        select
        brand
        ,start_date
        ,end_date
        ,max(end_date) over(partition by brand order by start_date rows between UNBOUNDED PRECEDING and 1 PRECEDING ) as max_date  --获取同一品牌内按开始日期排序后，取第一行到前一行的最大结束时间
        from temp
    )t1
    )t1
where end_date>=start_date
group by brand
;

补充：rows 和range的区别
在 SQL 中，rows 和 range 是两种不同的窗口帧（window frame）类型，它们定义了窗口函数的计算范围。
rows 窗口帧是基于行的，它使用一组相对于当前行的行号来定义窗口函数的计算范围。rows 窗口帧可以指定 UNBOUNDED PRECEDING、n PRECEDING、CURRENT ROW、n FOLLOWING 和 UNBOUNDED FOLLOWING 五种窗口帧范围。
range 窗口帧是基于值的，它使用一组相对于当前行的数值范围来定义窗口函数的计算范围。range 窗口帧可以指定 UNBOUNDED PRECEDING、n PRECEDING、CURRENT ROW、n FOLLOWING 和 UNBOUNDED FOLLOWING 五种窗口帧范围。

注释：
PRECEDING：往前
FOLLOWING：往后
CURRENT ROW：当前行
UNBOUNDED：起点
UNBOUNDED PRECEDING 表示从前面的起点
UNBOUNDED FOLLOWING：表示到后面的终点

一般来说，rows 和 range 窗口帧都可以用于定义窗口函数的计算范围，但是它们有一些不同的特点：rows 窗口帧是基于行的，它使用一组相对于当前行的行号来定义窗口函数的计算范围。因此，rows 窗口帧适用于基于行号的计算，例如计算排名、移动平均等。range 窗口帧是基于值的，它使用一组相对于当前行的数值范围来定义窗口函数的计算范围。因此，range 窗口帧适用于基于数值范围的计算，例如计算累计和、百分比等。

一般情况下，rows 窗口帧比 range 窗口帧更常用，因为基于行号的计算更加常见。但是在某些特殊情况下，range 窗口帧也可以使用。
例如：当窗口函数的计算范围基于连续的数值范围时，可以使用 range 窗口帧。例如，计算累计和、计算百分比等。当窗口函数的计算范围包含重复的值时，可以使用 range 窗口帧来避免重复计算。例如，计算连续相同值的最大长度、计算某个值在窗口中的出现次数等。
需要注意的是，对于一些特殊的窗口函数，可能只能使用 rows 窗口帧，例如计算排名、计算移动平均等。因此，在使用 range 窗口帧时，需要根据具体的需求和窗口函数的特性选择合适的窗口帧类型。