Hive之常用函数

最新推荐文章于 2022-09-05 11:53:05 发布
最新推荐文章于 2022-09-05 11:53:05 发布
阅读量613 收藏 4
点赞数
分类专栏： 大数据
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zhijunming/article/details/107921744
版权

Hive常用的内置函数

2.1空字段赋值

1)空字段赋值 nvl()

-- 给值为NULL的数据赋值，它的格式是NVL( value，default_value)。它的功能是如果value为NULL，则NVL函数返回default_value的值，
select nvl(null,'空值');

2)if(表达式,default,default)

-- if(表达式,value1,value2) 如果表达式为true 则取value1,否则取value2
select if('' is null,'空值','不是');

3)COALESCE(value1,value2,value3)

-- COALESCE(value1,value2,value3) 如果value1为null 则取value2,否则取value1.如果value2为null 则取value3,否则取vaue2
select COALESCE(null,"空值","不是空值");

2.2 CASE WHEN THEN ELSE END

1)格式
2 case 字段 when 判断 then 处理过程   else 不满足判断的处理  end 结束
2)使用场景
用于不同字段数据的转换
3)实例
--需求
求出不同部门男女各多少人。结果如下：
dept_Id    man    woman
A     		2       1
B     		1       2

select dept_Id,
sum(case sex when '男' then 1 else 0 end)  man,
sum(case sex when '女' then 1 else 0 end)  woman
from emp_sex
group by dept_Id

2.3日期函数

1)unix_timestamp:返回当前或指定时间的时间戳

select unix_timestamp();--当前的时间戳
select unix_timestamp('2020-08-01 08:08:08'); --指定的时间
select unix_timestamp('2020/08/01 08-08-08','yyyy/MM/dd HH-mm-SS')--指定格式的时间

2)from_unixtime:将时间戳转为日期格式

select from_unixtime(1596269280);-- 默认的格式 2020-08-01 08:08:00 
select from_unixtime(1596269280,'yyyy-MM-dd HH-mm-SS');-- 自定义格式

3)current_date:当前日期,年月日

select CURRENT_TIMESTAMP()

4)current_timestamp:当前的日期加时间

select CURRENT_TIMESTAMP()

5)to_date:抽取日期部分

select to_date('2018-08-08 08:08:08');

6)year:获取年

SELECT year('2018-08-08 08:08:08');

7)month:获取月

select month('2018-08-08 08:08:08');

8)day:获取日

select day('2018-08-08 08:08:08');

9)hour:获取时

select hour('2018-08-08 08:08:08');

10)minute:获取分钟

select minute('2018-08-08 08:08:08');

11)second:获取秒

select second('2018-08-08 08:09:06');

12)weekofyear:当前时间是一年中的第几个周

select weekofyear('2018-08-08 08:09:06');

13)dayofmonth:当前时间是一个月中的第几天

select dayofmonth('2018-08-08 08:09:06');

14)months_between:两个日期间的月份,一般大的日期放前面

SELECT months_between('2019-09-08 08:09:06','2019-08-08 08:09:06');

15)add_months:日期加减月

select add_months('2019-09-08 08:09:06',-2);
  1. datediff:两个日期相差天数
select datediff('2019-10-08 08:09:06','2019-09-08 08:09:06');

17)date_add 日期加天数

select date_add('2019-10-08 08:09:06',5);

18)date_sub 日期减天数

select date_sub('2019-10-08 08:09:06',5);

19)last_day 日期的当月的最后一天 返回的日期:2019-10-31

select last_day('2019-10-08 08:09:06');
  1. date_format:按指定格式返回日期
select date_format('2019-10-08 08:09:06','yyyy-MM');

2.4取整函数

1)round 四舍五入 round(数字,精度)

select round(1.23,1);

2)ceil 向上取整

select ceil(1.01);--2

3)floor 向下取取整

select floor(1.999);--1

2.5字符串函数

1)upper:转大写

select upper('aaSSdd');

2)lower:转小写

select lower('DDffDD');

3)length 长度

select length('aaddd d00');

4)trim:前后去空格

select trim(' dd dd dd ');

5)lpad:向左补齐,到指定长度 ,lpad(原字段,长度,补齐的字符)

select lpad('dd',9,'*');

6)rpad:向右补齐,到指定长度,rpad(原字段,长度,补齐的字符)

select rpad('gg',9,'*');

7)regexp_replace :使用正则表达式匹配目标字符串,匹配成功后替换 rergexp_replace(原字段,正则表达式,替换的内容)

select regexp_replace('100-900','\\d+','num');

2.6集合操作函数

(1)size:集合中元素的个数

select SIZE(array('q','f'));

(2)map_keys:返回map中的key

select map_keys(children) from test;

(3)map_values:返回map中的value

select map_values(children) from test;

(4)array_contains:判断array中是否包含某个元素array_contains(array字段,判断的字段)

select array_contains(array('a','v','o'),'o');

(5)sort_array:将array中的元素排序

select sort_array(array('1','6','4','2'));

2.7行转列函数

1)concat(string/col,拼接字段,string/col)返回输入字符串连接后的结果，支持任意个输入字符串;

select concat('a','|','b');

2)concat_ws(拼接字段,string/col,string/col) 或concat_ws(拼接字段,array)

它是一个特殊形式的 CONCAT()。第一个参数是剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;

–注意：CONCAT_WS must be “string or array”

select concat_ws('|','a','b');-- a|b

3)collect_set(col)函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段,要结合group by 一起使用

select collect_set(name) from woman -- array("jj","bb",...)

4)collect_list(col)函数只接受基本数据类型，它的主要作用是将某字段的值进行不去重汇总，产生array类型字段,要结合group by 一起使用

select collect_list(name) from woman; -- array("jj","bb","bb",...)

5)总结

collect_set(col)和collect_list(col)一般结合group by使用,返回值都是array类型

6)案例

[1]需求

把星座和血型一样的人归类到一起。结果如下：
射手座,A      大海|凤姐
白羊座,A      孙悟空|猪八戒
白羊座,B      宋宋|苍老师

[2]解答

-- ①先将星座和血型拼接 
select concat_ws(',',constellation,blood_typr) c_b,
name
from person_info
-- ② 分组
select t.c_b,collect_set(t.name)
from (select concat_ws(',',constellation,blood_typr) c_b,
name
from person_info)t
group by t.c_b;
-- ③ 处理格式
select t.c_b,concat_ws('|',collect_set(t.name))
from (select concat_ws(',',constellation,blood_typr) c_b,
name
from person_info)t
group by t.c_b;

2.8列转行

1)split(str,separator):分割函数,将字符串按照后面的分隔符切割，转换成字符array。str为要分割的字符,separator以什么为分割,返回array类型

select split(",",name) from women -- array("a","a","bb")

2)explode(col):炸裂函数,将hive一列中复杂的array或者map结构拆分成多行,处理的对象

3)lateral view:虚拟函数

用法:LATERAL VIEW udtf(expression) tableAlias AS columnAlias 

    总结:lateral view用于和split, explode等UDTF一起使用，它能够将一行数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合
lateral view首先为原始表的每行调用UDTF，UDTF会把一行拆分成一或者多行，lateral view再把结果组合，产生一个支持别名表的虚拟表

lateral view 后相当于一个只有一列的数据的虚拟表,然后原始表中与此对的字段逐一跟虚拟表的每一行做关联

4)案例

[1]需求

-- 原始数据
 movie	          category
《疑犯追踪》	   悬疑,动作,科幻,剧情
《Lie to me》	  悬疑,警匪,动作,心理,剧情
《战狼2》	       战争,动作,灾难

-- 处理后的数据
《疑犯追踪》      悬疑
《疑犯追踪》      动作
《疑犯追踪》      科幻
《疑犯追踪》      剧情
《Lie to me》    悬疑
《Lie to me》    警匪
《Lie to me》    动作
《Lie to me》    心理
《Lie to me》    剧情
《战狼2》        战争
《战狼2》        动作
《战狼2》        灾难

[2]解答

SELECT
 movie,
 category_name
 FROM 
 movie_info
 lateral view explode(split(category,',')) temp as category_name

2.9,窗口函数

1)概念
窗口函数=函数+窗口
解析:函数对窗口里面的数据进行函数的运算
窗口:限定函数计算的范围
窗口数据中的每一行数据都会执行一次窗口函数,即有多少行数据就执行多少次窗口函数
2)限定范围窗口函数over()

(1)over():指定分析函数工作的数据窗口大小(默认是全局的数据),这个数据窗口大小可能随着行的变化而变化

[1]over(partition by 字段...) 按照字段是否相同来划分分区窗口,将字段相同
的数据扔但同一个分区窗口里面
[2]over(order by 字段...) 当有order by 但是没有窗口子句的时候 默认窗口的范围是
rows between unbounded preceding and current row,
当有order by 和窗口子句都没有的时候,窗口默认的范围是rows between unbounded preceding and unbounded following
3)窗口子句
窗口子句 :当有只有over的时候窗口的范围是over指定的范围即原始表的大小,当有partition的时候,
窗口子句范围是:partition by 过后所有细窗口范围,他们之间相互独立,也即是先
over范围-->partition by分区的范围-->窗口子句-->order by(前无边界到当前行)

注意:特效的窗口函数:不需要窗口子句,默认的范围就是over范围或partition by的范围,ordre by的范围限制也不起作用

 rank(),nTile(),dense_rank(),row_number(),lead(),lag()
 因为这些函数,要有在范围比较大才有意义

1)current row:当前行,一般用于over()中的限定rows的范围

2)n preceding:往前n行数据,一般用于over()中限定rows往前的行数

3)n following 往后n行数据,一般用于over()中限定rows往后的行数

4)unbounded

[1]unbounded preceding 前无边界,表示从前面的起点

[2]unbounded following 后无边界,表示到后面的终点

4),取值的窗口函数

(1)lead(col,n,defualt_val):往后第n行 col是字段 n表示第几行,defualt_val如果没有往后第n

行则用默认值代替–lead(col) 默认取当前行下一行

(2)lag(col,n,defualt_val):往前第n行,col是字段,n代表第几行,defualt_val如果没有往前第n

行则用默认值代替–lag(col) 默认取当前行上一行

(3)first_value(col,flase/true):当前窗口下的第一个值,第二个参数为true,跳过空值

(4)last_value(col,flase/true):当前窗口下的最后一个值,第二个参数为true,跳过空值

5)排名和分析函数

(1)rank() 排序相同时会重复,总数不会变 比如1,2,2,4

(2)dense_rank() 排序相同时会重复,总数会减少 比如 1,2,2,3

(3)row_number() 会根据顺序计算 比如 1,2,3

(4)ntile(n):把有序窗口的行分发到指定数据的组中，各个组有编号，编号从1开始，

对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型,按n进行分组

不够n个数也可以排序编号,比如ntile(2) -->1,2,1,2,1

6)总结
rank(),dense_rank(),row_number(),ntile(),lead(),lag(),不用窗口子句,默认是over和partition by 的范围,order by的范围限制不起作用
7) 计算的窗口函数
count() 统计个数
sum()  求总和
min()  求最小值
max()  求最大值
avg()  求平均值
8)自定义函数

8.1编程步骤：

（1）继承Hive提供的类

​ org.apache.hadoop.hive.ql.udf.generic.GenericUDF

​ org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;

（2）实现类中的抽象方法

（3）在hive的命令行窗口创建函数

添加jar

add jar linux_jar_path

创建function

create [temporary] function [dbname.]function_name AS class_name;

（4）在hive的命令行窗口删除函数

drop [temporary] function [if exists] [dbname.]function_name;

8.2创建临时函数

（1）打成jar包上传到服务器/opt/module/hive/datas/myudf.jar

（2）将jar包添加到hive的classpath，临时生效

hive (default)> add jar /opt/module/hive/datas/myudf.jar;

（3）创建临时函数与开发好的java class关联

hive (default)> create temporary function my_len as "com.atguigu.hive.udf.MyUDF";

（4）即可在hql中使用自定义的临时函数

hive (default)> select ename,my_len(ename) ename_len from emp;

（5）删除临时函数

hive (default)> drop temporary function my_len;

注意：临时函数只跟会话有关系，跟库没有关系。只要创建临时函数的会话不断，在当前会话下，任意一个库都可以使用，其他会话全都不能使用。

5）创建永久函数

（1）创建永久函数

注意 因为add jar 本身也是临时生效,所以在创建永久函数的时候，需要制定路径(并且因为元数据的原因,这个路径还得是hdfs上的路径)

hive (default)> create function my_len2 as "com.atguigu.hive.udf.MyUDF" using jar "hdfs://hadoop102:9820/udf/myudf.jar";

（2）即可在hql中使用自定义的永久函数

hive (default)> select ename,my_len2(ename) ename_len from emp;

（3）删除永久函数

hive (default)> drop function my_len2;

注意：永久函数跟会话没有关系，创建函数的会话断了以后，其他会话也可以使用。

永久函数创建的时候，在函数名之前需要自己加上库名，如果不指定库名的话，会默认把当前库的库名给加上。

永久函数使用的时候，需要在指定的库里面操作,或者在其他库里面使用的话加上 库名.函数名

8.3案例

package com.atguigu.udf;

import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.exec.UDFArgumentLengthException;
import org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;

//给定基本数据类型，返回给定数据的长度
public class MyUDF extends GenericUDF {
    /**
     *initialize(ObjectInspector[] arguments)
     * 拿来判断数据类型是否正确  并抛出异常
     * 判断参数个数是否正确  并抛出异常
     * 约定返回值类型
     * @param arguments
     * @return
     * @throws UDFArgumentException
     */
    @Override
    public ObjectInspector initialize(ObjectInspector[] arguments) throws UDFArgumentException {
        //  判断参数的个数是否有问题
        if(arguments.length != 1){
            throw new UDFArgumentLengthException(" ARGS NUM ERROR please give me only one arg");
        }
        //判断参数的类型是否有问题
        if(!arguments[0].getCategory().equals(ObjectInspector.Category.PRIMITIVE)) {
            throw new UDFArgumentTypeException(1,"ARGS TYPE ERROR please give me PRIMITIVE");
        }
        // 约定返回值类型
        return PrimitiveObjectInspectorFactory.javaIntObjectInspector;
    }

    /**
     * evaluate(DeferredObject[] arguments) 写具体逻辑的方法
     * @param arguments
     * @return
     * @throws HiveException
     */
    @Override
    public Object evaluate(DeferredObject[] arguments) throws HiveException {
        Object o = arguments[0].get();
        if(o==null){
            return 0;
        }
        return o.toString().length();
    }

    /**
     * getDisplayString 获取解释的字符串 给你的hadooop看的
     * @param children
     * @return
     */
    @Override
    public String getDisplayString(String[] children) {
        return "";
    }
}
9)例子
--需求1  查询在2017年4月份购买过的顾客及总人数 总人次
SELECT 
name,
count(*)over() total
from business
where month(orderdata)=4
group by name

--需求1变种1  累加统计4月份有多少人消费

select 
name,
count(*)over(rows between unbounded preceding and current row ) total
from business 
where month(orderdata)=4
group by name

--需求1变种2  累加统计有多少人消费 
select 
name,
count(*) over(rows between unbounded preceding and current row) total
from business 
--需求1变种3  统计一共有多少人消费
select 
name,
count(*) over()
from business 

--用一般函数怎么做(求所有不同月份的总人数和人)
select 
date_format(orderdata,'yyyy-MM') m_d,
collect_set(name) l_m
from business 
group by date_format(orderdata,'yyyy-MM');


select 
t.m_d,
t.l_m,
size(t.l_m) total
from (select 
date_format(orderdata,'yyyy-MM') m_d,
collect_set(name) l_m
from business 
group by date_format(orderdata,'yyyy-MM'))t;

2017-01	["jack","tony"]	2
2017-02	["jack"]	    1
2017-04	["jack","mart"]	2
2017-05	["neil"]	    1
2017-06	["neil"]	    1

--8.5.2询顾客的购买明细及月购买总额
select 
name,
orderdata,
cost ,
sum(cost)over(partition by name,month(orderdata )) s_c
from business ;
jack	2017-01-01	10	111
jack	2017-01-05	46	111
jack	2017-01-08	55	111
jack	2017-02-03	23	23
jack	2017-04-06	42	42
mart	2017-04-13	94	299
mart	2017-04-08	62	299
mart	2017-04-09	68	299
mart	2017-04-11	75	299
neil	2017-05-10	12	12
neil	2017-06-12	80	80
tony	2017-01-04	29	94
tony	2017-01-07	50	94
tony	2017-01-02	15	94

--需求2的变种  统计每个人一共消费了多少和明细
select 
name,
cost ,
orderdata ,
sum(cost) over(partition by name) t_c
from business ;

jack	10	2017-01-01	176
jack	23	2017-02-03	176
jack	46	2017-01-05	176
jack	42	2017-04-06	176
jack	55	2017-01-08	176
mart	94	2017-04-13	299
mart	62	2017-04-08	299
mart	68	2017-04-09	299
mart	75	2017-04-11	299
neil	80	2017-06-12	92
neil	12	2017-05-10	92
tony	50	2017-01-07	94
tony	15	2017-01-02	94
tony	29	2017-01-04	94

--需求2的变种 累计每个人一共消费了多少和明细
SELECT 
name,
orderdata ,
cost,
sum(cost)over(PARTITION  by name order by orderdata ) t_c
from business ;
jack	2017-01-01	10	10
jack	2017-01-05	46	56
jack	2017-01-08	55	111
jack	2017-02-03	23	134
jack	2017-04-06	42	176
mart	2017-04-08	62	62
mart	2017-04-09	68	130
mart	2017-04-11	75	205
mart	2017-04-13	94	299
neil	2017-05-10	12	12
neil	2017-06-12	80	92
tony	2017-01-02	15	15
tony	2017-01-04	29	44
tony	2017-01-07	50	94

--上述的场景, 将每个顾客的cost按照日期进行累加

select 
name,
orderdata ,
cost ,
sum(cost )over(partition by name order by orderdata ) s_c
from business;
jack	2017-01-01	10	10
jack	2017-01-05	46	56
jack	2017-01-08	55	111
jack	2017-02-03	23	134
jack	2017-04-06	42	176
mart	2017-04-08	62	62
mart	2017-04-09	68	130
mart	2017-04-11	75	205
mart	2017-04-13	94	299
neil	2017-05-10	12	12
neil	2017-06-12	80	92
tony	2017-01-02	15	15
tony	2017-01-04	29	44
tony	2017-01-07	50	94

--需求变种1   统计当天消费和上一次消费的和

SELECT 
name,
orderdata,
cost,
sum(cost)over(partition by name order by 
orderdata rows between 1 preceding and current row )  s_c
from business ;
jack	2017-01-01	10	10
jack	2017-01-05	46	56
jack	2017-01-08	55	101
jack	2017-02-03	23	78
jack	2017-04-06	42	65
mart	2017-04-08	62	62
mart	2017-04-09	68	130
mart	2017-04-11	75	143
mart	2017-04-13	94	169
neil	2017-05-10	12	12
neil	2017-06-12	80	92
tony	2017-01-02	15	15
tony	2017-01-04	29	44
tony	2017-01-07	50	79

--需求变种2   统计当天消费和下一次消费的和
select 
name,
orderdata,
cost,
sum(cost)over(partition by name order by orderdata 
rows between current row  and 1 following  )s_c
from business 
jack	2017-01-01	10	56
jack	2017-01-05	46	101
jack	2017-01-08	55	78
jack	2017-02-03	23	65
jack	2017-04-06	42	42
mart	2017-04-08	62	130
mart	2017-04-09	68	143
mart	2017-04-11	75	169
mart	2017-04-13	94	94
neil	2017-05-10	12	92
neil	2017-06-12	80	80
tony	2017-01-02	15	44
tony	2017-01-04	29	79
tony	2017-01-07	50	50

--需求变种3   统计上一次消费到下一次消费的和
select 
name,
orderdata,
cost,
sum(cost)over(partition by name order by orderdata rows  between 1 preceding  
and 1 following) s_c
from business 
jack	2017-01-01	10	56
jack	2017-01-05	46	111
jack	2017-01-08	55	124
jack	2017-02-03	23	120
jack	2017-04-06	42	65
mart	2017-04-08	62	130
mart	2017-04-09	68	205
mart	2017-04-11	75	237
mart	2017-04-13	94	169
neil	2017-05-10	12	92
neil	2017-06-12	80	92
tony	2017-01-02	15	44
tony	2017-01-04	29	94
tony	2017-01-07	50	79
--需求变种4 直接按日期累加消费并且需要明细

select
name,
orderdata,
cost,
sum(cost)over(order by orderdata)s_c
from business 
tony	2017-01-02	15	25
tony	2017-01-04	29	54
jack	2017-01-05	46	100
tony	2017-01-07	50	150
jack	2017-01-08	55	205
jack	2017-02-03	23	228
jack	2017-04-06	42	270
mart	2017-04-08	62	332
mart	2017-04-09	68	400
mart	2017-04-11	75	475
mart	2017-04-13	94	569
neil	2017-05-10	12	581
neil	2017-06-12	80	661

--8.5.4查询顾客购买明细以及上次的购买时间和下次购买时间
select 
name,
orderdata,
cost,
lag(orderdata)over(partition by name order by orderdata) l_d,
lead(orderdata)over(partition by name order by orderdata) d_d
from business ;
jack	2017-01-01	10		2017-01-05
jack	2017-01-05	46	2017-01-01	2017-01-08
jack	2017-01-08	55	2017-01-05	2017-02-03
jack	2017-02-03	23	2017-01-08	2017-04-06
jack	2017-04-06	42	2017-02-03	
mart	2017-04-08	62		2017-04-09
mart	2017-04-09	68	2017-04-08	2017-04-11
mart	2017-04-11	75	2017-04-09	2017-04-13
mart	2017-04-13	94	2017-04-11	
neil	2017-05-10	12		2017-06-12
neil	2017-06-12	80	2017-05-10	
tony	2017-01-02	15		2017-01-04
tony	2017-01-04	29	2017-01-02	2017-01-07

SELECT 
name,
cost,
orderdata,
lag(orderdata,1,'0000-00-00')over(partition by name order by orderdata) l_d,
lead(orderdata,1,'9999-99-99') over(partition by name order by orderdata) d_d
from business ;

jack	10	2017-01-01	0000-00-00	2017-01-05
jack	46	2017-01-05	2017-01-01	2017-01-08
jack	55	2017-01-08	2017-01-05	2017-02-03
jack	23	2017-02-03	2017-01-08	2017-04-06
jack	42	2017-04-06	2017-02-03	9999-99-99
mart	62	2017-04-08	0000-00-00	2017-04-09
mart	68	2017-04-09	2017-04-08	2017-04-11
mart	75	2017-04-11	2017-04-09	2017-04-13
mart	94	2017-04-13	2017-04-11	9999-99-99
neil	12	2017-05-10	0000-00-00	2017-06-12
neil	80	2017-06-12	2017-05-10	9999-99-99
tony	15	2017-01-02	0000-00-00	2017-01-04
tony	29	2017-01-04	2017-01-02	2017-01-07

--8.5.5查询顾客每个月第一次的购买时间 和 每个月的最后一次购买时间
select 
name,
orderdata,
cost,
first_value(orderdata)over(partition by name,date_format(orderdata,'yyyy-MM') order by orderdata 
rows between UNBOUNDED preceding and unbounded following) f_d,
last_value(orderdata)over(partition by name,date_format(orderdata,'yyyy-MM') order by orderdata
rows between unbounded preceding  and unbounded following ) l_d
from business ;
jack	2017-01-01	10	2017-01-01	2017-01-08
jack	2017-01-05	46	2017-01-01	2017-01-08
jack	2017-01-08	55	2017-01-01	2017-01-08
jack	2017-02-03	23	2017-02-03	2017-02-03
jack	2017-04-06	42	2017-04-06	2017-04-06
mart	2017-04-08	62	2017-04-08	2017-04-13
mart	2017-04-09	68	2017-04-08	2017-04-13
mart	2017-04-11	75	2017-04-08	2017-04-13
mart	2017-04-13	94	2017-04-08	2017-04-13
neil	2017-05-10	12	2017-05-10	2017-05-10
neil	2017-06-12	80	2017-06-12	2017-06-12
tony	2017-01-02	15	2017-01-02	2017-01-07
tony	2017-01-04	29	2017-01-02	2017-01-07
tony	2017-01-07	50	2017-01-02	2017-01-07

--需求变种1 查询顾客第一次的购买时间 和 最后一次购买时间
select 
name,
cost,
orderdata,
first_value(orderdata) over(partition by name order by orderdata 
rows BETWEEN unbounded preceding and unbounded following) f_d,
last_value(orderdata)over(partition by name order by orderdata 
rows between unbounded preceding  and unbounded following )l_d
from business ;
jack	10	2017-01-01	2017-01-01	2017-04-06
jack	46	2017-01-05	2017-01-01	2017-04-06
jack	55	2017-01-08	2017-01-01	2017-04-06
jack	23	2017-02-03	2017-01-01	2017-04-06
jack	42	2017-04-06	2017-01-01	2017-04-06
mart	62	2017-04-08	2017-04-08	2017-04-13
mart	68	2017-04-09	2017-04-08	2017-04-13
mart	75	2017-04-11	2017-04-08	2017-04-13
mart	94	2017-04-13	2017-04-08	2017-04-13
neil	12	2017-05-10	2017-05-10	2017-06-12
neil	80	2017-06-12	2017-05-10	2017-06-12
tony	15	2017-01-02	2017-01-02	2017-01-07
tony	29	2017-01-04	2017-01-02	2017-01-07
tony	50	2017-01-07	2017-01-02	2017-01-07

-- 8.5.6查询前20%时间的订单信息
select 
name,
orderdata,
cost,
ntile(5)over(order by orderdata) n_m
from business ;

select 
t.name,
t.orderdata,
t.cost
from (select 
name,
orderdata,
cost,
ntile(5)over(order by orderdata) n_m
from business)t 
where 
t.n_m=1;
jack	2017-01-01	10
tony	2017-01-02	15
tony	2017-01-04	29

--RANK() 排序相同时会重复，总数不会变
--DENSE_RANK() 排序相同时会重复，总数会减少
--ROW_NUMBER() 会根据顺序计算

SELECT 
name ,
subject ,
score ,
rank() over(PARTITION  by subject ORDER  by score desc)  rk,
dense_rank()over(PARTITION  by subject ORDER  by score desc) drk,
row_number()over(PARTITION  by subject ORDER  by score desc) rrk
from score; 
孙悟空	数学	95	1	1	1
宋宋	数学	86	2	2	2
婷婷	数学	85	3	3	3
大海	数学	56	4	4	4
大海	英语	84	1	1	1
宋宋	英语	84	1	1	2
婷婷	英语	78	3	2	3
孙悟空	英语	68	4	3	4
大海	语文	94	1	1	1
孙悟空	语文	87	2	2	2
婷婷	语文	65	3	3	3
宋宋	语文	64	4	4	4
鸿儒之观
关注
  • 0
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Hive分区表新增字段值为NULL
Sin_Geek成长の迹
03-08 831
背景 对于HIVE分区表通过add column添加字段，向已存在分区中插入数据，结果新增字段的值全部为null。 已存在分区会产生该问题，新分区不会产生该问题。 原因 实际上数据是插入成功的。HDFS上的数据更新了，但是我们查询的时候仍然查询的是旧的元数据信息（即Mysql中的信息） 官方解释如下： Change Column Name/Type/Position/Comment ALTER T...
Hive的常用函数大全
weixin_44319333的博客
04-02 527
关系运算 等值比较：A=B 如果表达式A和表达式B相等，返回true，否则返回false – – 不等值比较：A<>B 如果表达式A和表达式B不相等，返回true，否则返回false（若A和B有一个为null，则返回null） – – 小于/大于比较：A<B / A>B 如果表达式A小于/大于表达式B，返回true，否则返回false（若...
【Hive函数大全】NVL : 空字段赋值
weixin_43958974的博客
09-27 811
函数说明： 给值为NULL的数据赋值，它的格式是NVL(str, replace_with)。 它的功能是如果str为NULL，则NVL函数返回replace_with的值，如果两个参数都为NULL，则返回NULL。 举例: NVL(id_code, 0) : 如果id_code为null，则返回0。 ...
Hive表新增字段值为NULL问题
a805814077的博客
09-05 2253
Hive中添加分区表字段，重新插入数据后，添加的字段仍为空问题探究
踩坑-HIVE double类型判空
qq_35757473的博客
02-28 2407
结论： hive中double类型数据 使用 <> '' （不等于空串），返回的是false，引以为戒。
(12)Hive的空字段赋值,case when,行列转换窗口函数,rank waiting
张不帅
10-29 2261
文章目录空字段赋值CASE WHEN行转列列转行窗口函数Rank 空字段赋值 函数说明: NVL：给值为NULL的数据赋值，它的格式是NVL( value，default_value)。它的功能是如果value为NULL，则NVL函数返回default_value的值，否则返回value的值，如果两个参数都为NULL ，则返回NULL。 查询：如果员工的comm为NULL，则用-1代替 hiv...
hive常用函数
RangeYan
07-22 1066
1、数据介绍 首先我们产生我们的数据,使用spark sql来产生吧： val data = Seq[(String,String)]( ("{\"userid\":\"1\",\"action\":\"0#222\"}","20180131"), ("{\"userid\":\"1\",\"action\":\"1#223\"}","20180131"), ("{\"userid\":\"1...
hive常用函数参数手册
10-21
hive常用的函数参考手册，仅供参考，可能版本不一样，函数方法不一致,请以进官方文档为准，https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF
Hive常用函数大全.pdf
04-14
hive常用函数大全，日期类别，函数类别等常用函数都可有；
hive和oracle常用函数对照表.xlsx
05-12
hive和oracle常用函数对照，包含常用的函数分类 字符函数 数值函数 日期函数 聚合函数 转换函数 其他 增加的hive函数对比，只需要2个积分喔
Hive函数大全.pdf
09-09
本篇文章将详细介绍Hive中的一些主要函数，包括数学函数、类型转换函数、条件函数、字符函数、聚合函数以及表生成函数。 一、关系运算 关系运算是查询语言的基础，用于比较和筛选数据。在Hive中，主要有以下几种...
hive常用运算和函数.docx
10-12
hive常用运算和函数.docx
Hive常用函数
04-07
下面将详细讨论 Hive 中的一些常用函数及其应用场景。 1. **函数分类**： - **简单函数**：这些函数处理单条记录，包括关系运算、数学运算、逻辑运算、数值计算、类型转换、日期函数、条件函数、字符串函数等。 -...
Hive常用的基础函数
最新发布
11-18
Hive常用的基础函数
Hive之函数
lslslslslss的博客
12-24 726
一、系统内置函数 日期类 unix_timestamp:返回指定时间的时间戳 select unix_timestamp('2021-12-23 12-51-25','yyyy-MM-dd HH-mm-ss'); from_unixtime：将时间戳转化为具体时间 select from_unixtime(1640263885); current_date：当前日期 select current_date; current_timestamp：当前的日期加时间带毫秒 to_date：抽取日期部分 sel
Hive 分区表增加字段并赋值
yutanbo123的博客
10-11 6998
Hive 分区表增加字段并赋值前言背景解决方案具体操作注意事项总结 前言 最近工作上需要用到大数据平台，之前没用过大数据相关的工具，所以踩了不少坑。今天就把在分区表中添加字段的坑分享出来，避免再次踩坑。 背景 今天接到一个需求，在原有的 hive 分区表中需要添加一个字段，并且原来的数据中这个字段还是需要赋值。后续这个值是由 ETL 任务去拉取的，所以只需要解决好原来的数据。 解决方案 为了数据不丢失，先把原表备份，备份表名叫 xxx_temp(备份表这里不给出语句)。 备份完了之后，开始把原表结算复制
Hive函数总结
学渣渣
07-24 568
显示当前会话有多少函数可用 SHOW FUNCTIONS; 如： hive&gt; show functions ; OK ! != %   显示函数的描述信息 DESC FUNCTION concat; 如： hive&gt; DESC FUNCTION concat; OK concat(str1, str2, ... strN) - returns the concatenati...
Hive中常用的函数
weixin_43553142的博客
12-06 349
6.1 系统内助函数 1）查看系统自带的函数 hive> show functions; 2）显示自带的函数的用法 hive> desc function upper; 3）详细显示自带的函数的用法 hive> desc function extended upper; 6.2 常用内置函数 6.2.1 空字段赋值 1）函数说明 NVL：给值为 NULL 的数据赋值，它的格式是 NVL( value，default_value)。它的功能是如果 value 为 NULL，则 NVL
hive的常用函数有哪些
03-31
Hive的常用函数包括： 1. 聚合函数：sum、avg、count、max、min等，用于对数据进行统计分析； 2. 数学函数：round、abs、floor、ceil、sqrt、log等，用于对数值进行运算； 3. 字符串函数：substring、length、trim...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值