hive中如何取到每个顾客最新交易

柔光的暖阳◎ 2022-10-06 04:56 15阅读 0赞

hive -e "
    use db; 
    select t.advertId,t.exposureNum from (
        select 
            advertId,
            exposureNum,
            ROW_NUMBER() OVER(PARTITION BY advertId ORDER BY addTime desc) AS rn 
        FROM tb_advert_flow_money 
        where 
        ftype = 2
    ) t where t.rn=1;" > exposureInfo.txt;

解释说明：  
ROW\_NUMBER() OVER(PARTITION BY advertId ORDER BY addTime desc) AS rn  
表示通过 advertId 进行分区，然后在每个分区中按照时间降序排列结果。  
ROW\_NUMBER() 生成的时候通OVER（通过）(PARTITION BY advertId ORDER BY addTime desc) 这里面的结果生成行号

其中：

select 
        advertId,
        exposureNum,
        ROW_NUMBER() OVER(PARTITION BY advertId ORDER BY addTime desc) AS rn 
    FROM tb_advert_flow_money where ftype = 2

通过上面SQL生成的结果类似：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RvdG8xMjk3NDg4NTA0_size_16_color_FFFFFF_t_70]

通过：

hive -e "
    use db; 
    select t.advertId,t.exposureNum from (
        select advertId,exposureNum,ROW_NUMBER() OVER(PARTITION BY advertId ORDER BY addTime desc) AS rn 
        FROM tb_advert_flow_money where ftype = 2
    ) t where t.rn=1;" > exposureInfo.txt;

生成的数据为：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RvdG8xMjk3NDg4NTA0_size_16_color_FFFFFF_t_70 1]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RvdG8xMjk3NDg4NTA0_size_16_color_FFFFFF_t_70]: /images/20221005/64ae9e71095c42f7ae175344ca5deab7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RvdG8xMjk3NDg4NTA0_size_16_color_FFFFFF_t_70 1]: /images/20221005/ff79556feb0d4c0ca3fef1008a1092c6.png