Hive SQL实现连续相同分组数据合并为单行

首先得明确你的核心需求：把连续且NAME、ID、RATE、LOC完全相同的记录合并成一行，显示它们的起始日期和结束日期。先对照下你的原始数据和期望输出：

原始表数据 #

NAME ID RATE LOC DAY
ABCD 123 -5 NYC 2017-01-01
ABCD 123 -5 NYC 2017-01-02
ABCD 123 -6 SFO 2017-01-03
ABCD 123 -6 DEN 2017-01-04
ABCD 345 -4 ATL 2017-01-05
ABCD 345 -4 WAS 2017-01-06
ABCD 123 -7 CLT 2017-01-07
ABCD 123 -7 CLT 2017-01-08

期望输出 #

NAME ID RATE LOC START DAY END DAY
ABCD 123 -5 NYC 2017-01-01 2017-01-02
ABCD 123 -6 SFO 2017-01-03 2017-01-03
ABCD 123 -6 DEN 2017-01-04 2017-01-04
ABCD 345 -4 ATL 2017-01-05 2017-01-05
ABCD 345 -4 WAS 2017-01-06 2017-01-06
ABCD 123 -7 CLT 2017-01-07 2017-01-08

你之前尝试的问题所在 #

你之前用partition by name,id的思路，是把所有同一个NAME+ID的记录都归为一组，但忽略了RATE和LOC的变化，而且没有区分“连续”的记录——比如ABCD 123后面出现了不同的RATE/LOC，哪怕是同一个NAME+ID，也需要拆成新组，这就是你没得到正确结果的原因。

正确的实现方法（Hive/SQL通用） #

这是个典型的连续相同分组问题，我们可以用“行号差”的技巧来标记连续的相同组，具体步骤如下：

1. 给每条记录按日期排序生成全局行号
2. 按NAME、ID、RATE、LOC分组，在组内按日期排序生成组内行号
3. 用全局行号减去组内行号，得到一个group_id——连续的相同组，这个ID会保持一致
4. 最后按group_id分组，取每组的最小/最大日期作为起止日期

直接上代码：

WITH ranked_data AS (
    SELECT 
        NAME,
        ID,
        RATE,
        LOC,
        DAY,
        -- 全局按日期排序的行号
        ROW_NUMBER() OVER (ORDER BY DAY) AS global_rn,
        -- 按NAME+ID+RATE+LOC分组，组内按日期排序的行号
        ROW_NUMBER() OVER (PARTITION BY NAME, ID, RATE, LOC ORDER BY DAY) AS group_rn
    FROM your_table_name -- 替换成你的表名
),
grouped_data AS (
    SELECT 
        NAME,
        ID,
        RATE,
        LOC,
        DAY,
        -- 计算分组标识：连续相同组的这个值会相同
        global_rn - group_rn AS group_id
    FROM ranked_data
)
SELECT 
    NAME,
    ID,
    RATE,
    LOC,
    MIN(DAY) AS `START DAY`,
    MAX(DAY) AS `END DAY`
FROM grouped_data
GROUP BY NAME, ID, RATE, LOC, group_id
ORDER BY `START DAY`;

原理说明 #

举个例子：前两条记录的NAME+ID+RATE+LOC完全相同，global_rn是1和2，group_rn是1和2，相减后都是0，所以属于同一个group_id；第三条记录的RATE变了，group_rn重新从1开始，global_rn是3，3-1=2，和前面的0不一样，就成了新组。这样就能精准把连续的相同记录归为一组，再聚合日期就得到你要的结果了。

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