Hive -删除重复，保持最新的记录-所有的

Question

有一些这样的问题，没有答案，就像这里的这个。

我想我应该再发一张，希望能得到一张。

我有一个hive表的重复行。考虑下面的例子:

*ID             Date           value1         value2*
1001            20160101       alpha          beta
1001            20160201       delta          gamma
1001            20160115       rho            omega
1002            20160101       able           charlie
1002            20160101       able           charlie

完成后，我只想要两条记录。具体来说，是这两个:

*ID             Date           value1         value2*
1001            20160201       delta          gamma
1002            20160101       able           charlie

为什么是这两个?对于ID=1001，我想要最新的日期和该行中的数据。对于ID=1002，实际上是相同的答案，但具有该ID的两条记录是完全重复的，我只想要一条。

那么，关于如何做到这一点，有什么建议吗?使用ID和"max"日期的简单"group by"不起作用，因为这会忽略其他列。我不能把'max'放在上面，因为它会从所有记录中拉出最大列(会从旧记录中拉出'rho')，这是不好的。

我希望我的解释是清楚的，我感谢任何见解。

谢谢

Answer 1

试试这个:

WITH temp_cte AS (
        SELECT      mt.ID                                                           AS ID 
                    , mt.Date                                                       AS Date 
                    , mt.value1                                                     AS value1 
                    , mt.value2                                                     AS value2
                    , ROW_NUMBER() OVER (PARTITION BY mt.ID ORDER BY mt.Date DESC)  AS row_num
        FROM        my_table mt
    )
    SELECT      tc.ID           AS ID 
                , tc.Date       AS Date 
                , tc.value1     AS value1
                , tc.value2     AS value2
    FROM        temp_cte tc 
    WHERE       tc.row_num = 1 
    ;

或者您可以执行MAX()并将表连接到ID = ID和max_date = Date的表本身。HTH .

编辑2022年3月:由于ROW_NUMBER对每一行进行编号，而用户只关心最大日期的一行，因此我发现了一种更好的方法。

WITH temp_cte AS (
    SELECT      mt.ID                                                                               AS ID 
                , MAX(NAMED_STRUCT('Date', mt.Date, 'Value1', mt.value1, 'Value2', mt.Value2))      AS my_struct
    FROM        my_table mt
    GROUP BY    mt.ID 
)
SELECT      tt.ID                       AS ID 
            , tt.my_struct.Date         AS Date
            , tt.my_struct.Value1       AS Value1
            , tt.my_struct.Value2       AS Value2
FROM        temp_cte tt 
;