hive sql日常技巧

@hive技巧

概要

hive 窗口函数 row_number()排序 对不规则字符的优先级进行排序
例如： T>>Y>>U>>I

row_number() over (partition by XX order by XX desc ) as rn --排序

直接上代码

    select 
         t2.id
        ,t2.status_code                              
        ,t2.status_code_mapping   
        ,t2.status_cnt  
        ,row_number() over (partition by t2.id order by t2.status_cnt desc ,t2.status_code_mapping desc ) as rn  --排序
    from (
        select 
             t1.id
            ,t1.status_code                               --状态
            ,case 
                when status_code= 'T'  then 4
                when status_code= 'Y'  then 3
                when status_code= 'U'  then 2
                when status_code= 'I'  then 1
                            end as status_code_mapping   
                --映射顺序,T>Y>U>I,desc排序数值越大优先级越大          
            ,t1.status_cnt  --status_code对应的数量
        from (
            select 1 as id ,'T' as status_code, 20  as status_cnt
            union all
            select 1 as id ,'Y' as status_code, 100 as status_cnt
            union all
            select 1 as id ,'U' as status_code, 100 as status_cnt
            union all
            select 1 as id ,'I' as status_code, 20  as status_cnt
    ) t1
    ) t2;

技术细节

将T>>Y>>U>>I 通过case when 映射成 4，3，2，1 之后再order by
这里就不多做解释了吧，懂得都懂
上结果

小结

提示：这里可以添加总结
来看重点：
1，case when 映射 方法
2，order by 多个字段得时候注意先后顺序，以及每个order 不用字段后面都要加 desc