hive的一些常见内置函数

最新推荐文章于 2024-06-06 12:29:42 发布

给我一个苹果

最新推荐文章于 2024-06-06 12:29:42 发布

阅读量229

点赞数

本文链接：https://blog.csdn.net/weixin_43705952/article/details/105718080

版权

hive行转列

               select
                    t1.base,
                    concat_ws('|', collect_set(t1.name)) name
                from
                    (select
                        name,
                        concat(constellation, "," , blood_type) base
                    from
                        person_info) t1
                group by
                    t1.base;

hive列转行

select
movie,
category_name
from 
movie_info lateral view explode(category) table_tmp as category_name;

reflect调用Java自带函数

select reflect("java.lang.Math","max",col1,col2) from test_udf;
select reflect(class_name,method_name,col1,col2) from test_udf2;
select reflect("org.apache.commons.lang.math.NumberUtils","isNumber","123")

窗口函数

select 
user_id,
user_type,
sales,
--分组内所有行
sum(sales) over(partition by user_type) AS sales_1 ,
sum(sales) over(order  by user_type) AS sales_2 , 
--默认为从起点到当前行，如果sales相同，累加结果相同
sum(sales) over(partition by user_type order by sales asc) AS sales_3,
--从起点到当前行，结果与sales_3不同。 根据排序先后不同，可能结果累加不同
sum(sales) over(partition by user_type order by sales asc rows between unbounded preceding and current row) AS sales_4,
--当前行+往前3行 
sum(sales) over(partition by user_type order by sales asc rows between 3 preceding and current row) AS sales_5,
--当前行+往前3行+往后1行
sum(sales) over(partition by user_type order by sales asc rows between 3 preceding and 1 following) AS sales_6,
--当前行+往后所有行  
sum(sales) over(partition by user_type order by sales asc rows between current row and unbounded following) AS sales_7 
from 
order_detail 
order by 
    user_type,
    sales,
    user_id;

分析函数

select 
    user_id,user_type,sales,
    RANK() over (partition by user_type order by sales desc) as r,
    ROW_NUMBER() over (partition by user_type order by sales desc) as rn,
    DENSE_RANK() over (partition by user_type order by sales desc) as dr
from
    order_detail;

cube聚合

select
    user_type,
    sales,
    count(user_id) as pv,
    GROUPING__ID 
from 
    order_detail
group by 
    user_type,sales
WITH CUBE 
ORDER BY 
    GROUPING__ID;