Hive SQL：计算账户重叠日期区间的最小起始与最大结束日期

针对你提供的数据集，我们需要合并同一个账户下的重叠/连续日期区间，最终得到每个区间组的最小起始日期和最大结束日期。以下是具体的解决方案：

原始数据集 #

先把你的原始数据整理为更清晰的表格形式：

Hive SQL解决方案 #

我们可以通过窗口函数来实现重叠区间的分组和合并，具体代码如下（记得把your_table_name替换成你实际的表名）：

WITH transformed_data AS (
    -- 第一步：将字符串日期转换为Hive可识别的DATE类型，并按账户+起始日期排序
    SELECT 
        account_id,
        to_date(start_date, 'dd/MM/yyyy') AS start_dt,
        to_date(end_date, 'dd/MM/yyyy') AS end_dt
    FROM your_table_name
    ORDER BY account_id, start_dt
),
grouped_data AS (
    -- 第二步：生成重叠区间的分组标识
    SELECT 
        account_id,
        start_dt,
        end_dt,
        -- 判断当前区间是否与前一个区间重叠，生成分组ID
        SUM(CASE WHEN start_dt <= LAG(end_dt) OVER (PARTITION BY account_id ORDER BY start_dt) THEN 0 ELSE 1 END) 
            OVER (PARTITION BY account_id ORDER BY start_dt) AS group_id
    FROM transformed_data
)
-- 第三步：按账户和分组ID聚合，得到每个重叠组的最小起始和最大结束日期
SELECT 
    account_id,
    MIN(start_dt) AS min_start_date,
    MAX(end_dt) AS max_end_date
FROM grouped_data
GROUP BY account_id, group_id
ORDER BY account_id, min_start_date;

代码逻辑解释 #

1. transformed_data CTE：把原始的dd/MM/yyyy格式字符串转成Hive能处理的DATE类型，同时排序确保后续窗口函数能正确关联前一个区间。
2. grouped_data CTE：用LAG窗口函数获取当前账户的上一个区间结束日期，判断当前区间的起始日期是否小于等于上一个区间的结束日期（即是否重叠）。如果重叠则分组标识不变，不重叠则分组标识加1，以此把所有重叠/连续的区间归为同一组。
3. 最终聚合：按账户和分组ID分组，取每组的最小起始日期和最大结束日期，得到合并后的干净结果。

预期输出 #

执行上述SQL后，会得到如下合并后的结果（日期格式为Hive默认的yyyy-MM-dd）：

内容的提问来源于stack exchange，提问作者asad_rmd