如何将年薪从十五万到五十万系列之日期交叉问题

嗨，大家好，我是小萝卜算子。（微信公众号：数据仓库践行者。感谢关注）
目前正在带领小伙伴们阅读spark源码，有兴趣的小伙伴可以私信我，获取大数据进阶的相关视频哦。

1.简介

日期交叉去重问题，是一个经典sql，本文以一个电脑品牌促销的例子从不同的角度来看待解析这个问题，有更好方法的同学，欢迎私下交流…

2.表结构

CREATE TABLE `computer_promotion`(

  `brand` string COMMENT '用户主键', 

  `start_date` string COMMENT '开始日期', 

  `end_date` string COMMENT '结束日期')
  

3.表数据

brand	start_date	end_date

lenovo	2022-02-03	2022-02-07

lenovo	2022-02-10	2022-02-23

asus	2022-02-08	2022-02-24

asus	2022-02-13	2022-02-17

asus	2022-02-15	2022-02-28

dell	2022-02-04	2022-02-17

dell	2022-02-07	2022-02-21

hp	2022-02-06	2022-02-26

hp	2022-02-08	2022-02-19

hp	2022-02-15	2022-02-23

4.需求： 根据表数据求出每种电脑品牌促销的天数

解决方案1

根据开始和结束时间拆解促销日期（开始时间为正，结束时间为负）

其主要原理可以这样理解

1：根据开始可结束时间拆分，开始时间为正，结束时间为负，标记字段为flag
2：利用sum窗口函数，累加flag，sum（flag）=0 则打折日期结束或者与下一段打折日期断开，记为当前日期
3：根据步骤二的结果，利用窗口函数max，找出连续打折日期的分隔基准线
4：根据品牌和基准线分组，计算出每段的打折天数
5：根据品牌分组，计算出每个品牌总的打折天数

下面看一下实现过程

select 
    brand,
    sum(days) total_days
from    
    (SELECT
        brand,
        base_line,
        max(dt) max_dt,
        datediff(max(dt), base_line) + 1 days
    FROM
        (
            SELECT
                brand,
                dt,
                max(base_flag) over (PARTITION BY brand ORDER BY dt) base_line
            FROM
                (
                    SELECT
                        brand,
                        dt,
                        CASE
                    WHEN sum(flag) over (
                        PARTITION BY brand
                        ORDER BY
                            concat(dt, flag) DESC
                    ) = 0 THEN
                        dt
                    ELSE
                        "0"
                    END base_flag
                    FROM
                        (
                            SELECT
                                brand,
                                start_date dt,
                                1 AS flag
                            FROM
                                computer_promotion
                            UNION ALL
                                SELECT
                                    brand,
                                    end_date dt,
                                    - 1 AS flag
                                FROM
                                    computer_promotion
                        ) tmp
                ) tmp
        ) tmp
    GROUP BY
        brand,
        base_line) tmp
group by 
brand ;

解决方案2

根据促销开始时间排序，手工修改下次促销的开始时间

其主要原理可以这样理解

1：采用max窗口函数，根据开始日期正序，获得此次促销记录之前最大的促销结束日期
2：比较此次促销开始日期与步骤一获得的结束日期，如果开始日期比结束日期小或者相等，那么以步骤一获得的日期加一天作为此次促销的开始日期，反之，记当前记录的开始日期为本次促销的开始日期
3：过滤掉开始日期大于结束日期的数据，并且根据品牌分组，对每条记录的结束和开始日期求日期差+1，然后求sum， 获得最终结果

下面看一下实现过程

 SELECT
    brand,
    sum(
        datediff(end_date, start_date) + 1 
    ) total_days
FROM
    (
        SELECT
            brand,
            CASE
        WHEN start_date <= max_edd THEN
            date_add (max_edd, 1)
        ELSE
            start_date
        END 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) max_edd
            FROM
                computer_promotion
                
        ) tmp
    ) tmp
WHERE
    end_date > start_date
GROUP BY
    brand;

解决方案3

展开促销活动的每一天，然后去重

其主要原理可以这样理解

1：利用posexplode的序列，展开促销的开始和结束日期
2：根据品牌和日期去重
3：根据品牌分组，count获得最终的促销天数

下面看一下实现过程

SELECT
    brand,
    count(1) cnt
FROM
    (
        SELECT
            brand,
            curr_date
        FROM
            (
                SELECT
                    brand,
                    start_date,
                    end_date,
                    col_idx,
                    date_add (start_date, col_idx) curr_date
                FROM
                    computer_promotion lateral VIEW posexplode (
                        split (
                            space(
                                datediff(end_date, start_date) + 1
                            ),
                            " (?!$)"
                        )
                    ) tbl_idx AS col_idx,
                    col_val
            ) tmp
        GROUP BY
            brand,
            curr_date
    ) tmp
GROUP BY
    brand

5.拓展

1：本文从3个方向去解析交叉日期去重，以后碰到交叉问题，都可以迎刃而解

2：方法一是直接拆分开始结束日期，然后打上一个flag标记，获得连续的日期，想法比较新颖

3：方法二中， hive低版本不支持使用max窗口函数，求之前记录的最大值，同学们可以变换一下思维，即可实现

4：方法二比较巧妙的利用了窗口函数max，规避了日期交叉

5：对类似问题，如果数据量小的话，个人更倾向于方法三，简单明了，易于理解

6.想一想

方法一中第一次排序，为什么要concat(dt,flag)，而后面的排序仅仅使用dt就行了呢

方法二中，hive低版本可以先获得前一条记录的促销结束时间，然后用max窗口函数获得此字段结束时间的最大值，然后跟当前开始日期比较

方法三split的正则表达式为什么要写成" (?!$)"