Hsql每日一题

Hsql每日一题 | day03

前言

就一直向前走吧，沿途的花终将绽放~

题目：打折日期交叉问题

如下为平台商品促销数据：字段为品牌，打折开始日期，打折结束日期
brand stt        edt
oppo,2021-06-05,2021-06-09
oppo,2021-06-11,2021-06-21
vivo,2021-06-05,2021-06-15
vivo,2021-06-09,2021-06-21
redmi,2021-06-05,2021-06-21
redmi,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

计算每个品牌总的打折销售天数，注意其中的交叉日期，比如 vivo 品牌，
第一次活动时间为 2021-06-05 到 2021-06-15，第二次活动时间为 2021-06-09 到 2021-06-21 其中 9 号到 15号为重复天数，
只统计一次，即 vivo 总打折天数为 2021-06-05 到 2021-06-21 共计 17 天。

建表：

数据准备：

create table t18(
   brand             string,
   stt           string,
   edt           string
)row format delimited fields terminated by '\t';
load data local inpath '/opt/data/t18.txt' overwrite into table  t18;

需求实现：

select brand,sum(c2)
from(
    select brand,datediff(edt,if(stt>=c1,stt,date_add(c1,1))) c2
    from(
        select brand,stt,edt,
               nvl(max(edt) over (partition by brand order by stt rows between unbounded preceding
                   and 1 preceding),stt) c1
        from t18
        ) t1
    )t2 where c2 > 0 group by brand;

hsql语句分析：

最内层查询（子查询t1）:

从t18表中选择brand、stt和edt。
使用窗口函数MAX(edt) OVER (...)计算每个品牌下，按stt排序的每个行之前的最大edt值。这个窗口的范围是从所有之前的行（unbounded preceding）到当前行之前的那一行（1 preceding）。
如果当前行的stt大于或等于这个计算出的最大edt（即c1），那么使用stt作为c1的值，否则使用c1的次日（date_add(c1, 1)）。这是为了确保c1总是小于或等于当前行的stt，从而避免负的持续时间。
使用nvl函数（这通常是Oracle数据库中的函数，用于处理NULL值，但在其他数据库中可能是COALESCE或类似的函数）来处理可能的NULL值。如果窗口函数没有返回任何行（即对于每个品牌的第一行），则c1将默认为stt。

中间层查询（子查询t2）:

基于最内层查询的结果，计算每个事件或时间段的持续时间c2。这是通过计算edt和c1之间的日期差来实现的（datediff(edt, c1)）。
需要注意的是，由于在最内层查询中已经确保了c1总是小于或等于stt，所以这里计算出的c2应该总是非负的或零。

最外层查询:

从中间层查询中选择brand和持续时间c2的总和。
使用WHERE子句过滤掉持续时间为0的情况（虽然根据前面的逻辑，这种情况应该已经不存在了，但这里可能是为了额外的明确性）。
使用GROUP BY子句按brand分组，以便为每个品牌计算总的持续时间。