Hive常用函数_hive取整数函数

1.书写顺序

-select
-from
-join
-on
-where
-group by
-having
-order by
-limit

2.执行顺序

- from  
- where  
- join   
- on  
- group by 
- select   
- having  
- distinct  
- order by 
- limit
- union / union on

3.Hive关系运算符[常用]

4.数学函数[常用]

4.1 取整函数: round

select round(11.214563);
返回 11 
--四舍五入取整数部分
 
2. 指定精度取整函数: round
round(double a, int b) 
示例: 
select round(11.256,2);  -- 11.26

4.2 向下取整函数: floor

SELECT floor(10.954) ;
返回 10

4.3 向上取整 ceil / ceiling

select ceil(12.006); / select ceiling(12.006);
返回 13

4.4 取随机数函数: rand

语法: rand(),rand(int seed)
 
返回值: double
 
说明:返回一个double型0到1范围内的随机数。如果指定种子seed，则会等到一个稳定的随机数序列
 
select rand();
返回: 0.7400880291262728
 
-- 指定种子 返回的结果一样
select rand(50);
0.7297136425657874
 
select rand(50);
0.7297136425657874

4.5 幂运算函数: pow

语法: pow(double a, double p), power(double a, double p)
 
返回值: double
 
说明:返回a的p次方
 
select pow(2,5);
返回 32.0

4.6 开平方函数: sqrt

语法: sqrt(double x)
 
返回值: double
 
说明:返回x的平方根
 
select sqrt(4);
返回 2.0

4.7  二进制函数: bin

语法: bin(BIGINT x)
 
返回值: string
 
说明:返回x的二进制字符串
 
select bin(4);
返回 100

4.8 绝对值函数: abs

语法: abs(double x),abs(int x)
 
返回值: double OR int
 
说明:返回数值x的绝对值
 
select abs(-2.0);
返回 2

4.9 判断正负函数:sign

语法: sign(double a)
 
返回值: double
 
说明:如果a是正数则返回1.0，是负数则返回-1.0，否则返回0.0
 
select sign(10);
返回  1.0
 
select sign(-10.0);
返回 -1.0
 
select sign(0);
返回 0.0

 4.10 相反数函数negative

语法: negative(int x), negative(double x)
 
返回值: int or double
 
说明:返回-x or x的相反数
 
select negative(-5);
返回  5
select negative(5);
返回 -5
 

4.11 数学pi函数:pi

语法: pi()
 
返回值: double
 
说明:数学常数π
 
select pi();
返回 3.141592653589793

4.12 求N个数最大值函数:greatest

语法:greatest(T v1, T v2, …) N个数的最大值
 
返回值:T
 
说明:求最大值
 
select greatest(1,2,3);
返回 3 

4.13 求N个数最小值函数:least

语法:least(T v1, T v2, …)
 
返回值:T
 
说明:求最小值
 
select least(1,2,3);
返回 1

4.14 银行家舍入法函数:bround

语法:bround(double a)
 
返回值:double
 
说明:银行家舍入法 四舍六入五成双
 
select bround(5.5)
返回 6
select bround(4.5)
返回 4

五.日期函数

5.1  UNIX时间戳转日期函数:from_unixtime

语法: from_unixtime(bigint unixtime, [string format])
 
返回值: string
说明: format格式可以是 “yyyy-MM-dd hh:mm:ss”,“yyyy-MM-dd hh”,“yyyy-MM-dd hh:mm”等
 
select from_unixtime(1634000000,'yyyy-MM-dd');
返回 2021-10-12
 
select from_unixtime(1634000000,'yyyy-MM-dd HH:mm:ss')
返回 2021-10-12 00:53:20

5.2  获取当前UNIX时间戳函数:unix_timestamp

语法: unix_timestamp()
 
返回值: bigint
 
说明:获得当前时区的UNIX时间戳
 
select unix_timestamp();
返回 1633679199
 
SELECT from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:dd');
返回 2021-10-08 15:48:10

5.3  日期转UNIX时间戳函数:unix_timestamp

语法: unix_timestamp(string date)
 
返回值: bigint
 
说明:只能将格式为'yyyy-MM-dd HH:mm:ss'的时间字符串转换成时间戳。如果转化失败，则返回0。
 
SELECT unix_timestamp('2021-10-08 15:50:00');
返回 1633708200
 
SELECT unix_timestamp('2021-10-08');
返回 0

5.4  指定日期格式 转UNIX时间戳函数:unix_timestamp

语法: unix_timestamp(string date, string pattern)
 
返回值: bigint
 
说明:将指定时间字符串格式字符串转换成Unix时间戳。如果转化失败，则返回0
 
select unix_timestamp('2021-10-08','yyyy-MM-dd');
返回 1633651200

5.5  日期时间转日期函数:to_date

语法: to_date(string timestamp)
 
返回值: string
 
说明:返回日期时间字段中的日期部分
 
select to_date('2021-10-08 15:00:00');
返回 2021-10-08

5.6  日期转年函数: year

语法: year(string date)
 
返回值: int
 
说明:返回日期时间字段中的年
 
select year('2021-10-08 15:00:00');
select year('2021-10-08');
返回 2021

5.7  日期转月函数: month

语法: month(string date)
 
返回值: int
 
说明:返回日期时间字段中的月份
 
select month('2021-10-08 15:00:00');
select month('2021-10-08');
返回 10

5.8  日期转天函数: day

语法: day(string date)
 
返回值: int
 
说明:返回日期时间字段中的日期
 
select day('2021-10-08 15:00:00');
select day('2021-10-08');
返回 8

5.9  日期转小时函数: hour

语法: hour(string date)
 
返回值: int
 
说明:返回日期时间字段中的日期,注意格式错误则返回0
 
select hour('2021-10-08 15:00:00');
 
返回 15
 
select hour('2021-10-08');
返回 0

5.10 日期转分钟函数: minute

10. 日期转分钟函数: minute
语法: minute (string date)
 
返回值: int
 
说明:返回日期中的分钟。
 
select minute('2021-10-08 15:10:00');
返回 10

5.11 日期转秒函数: second

语法: second (string date)
 
返回值: int
 
说明:返回日期中的秒。
 
select second(‘2011-12-08 10:03:01’);
返回 1

5.12 日期转周函数:weekofyear

语法: weekofyear (string date)
 
返回值: int
 
说明:返回时间字符串位于一年中的第几个周
 
select weekofyear('2021-10-08 15:00:00');
返回 40

5.13 日期比较函数: datediff

语法: datediff(string enddate, string startdate)
 
返回值: int
 
说明:返回结束日期减去开始日期的天数。
 
select datediff('2021-10-08','2021-10-01');
返回 7 

5.14 日期增加函数: date_add

语法: date_add(string startdate, int days)
 
返回值: string
 
说明:返回开始日期startdate增加days天后的日期。
 
select date_add(2021-10-01,7);
返回 2021-10-08

5.15 日期减少函数: date_sub

语法: date_sub(string startdate, int days)
 
返回值: string
 
说明: 返回开始日期startdate减少days天后的日期。
 
select date_sub('2021-10-08',8);
返回 2021-10-01

5.16 当前时间日期函数:current_date

语法:current_date()
 
返回值:date
 
说明:返回当前时间日期
 
select current_date();
 
返回 2021-10-08

5.17 当前时间日期函数:current_timestamp

语法:current_timestamp()
 
返回值:timestamp
 
说明:返回当前时间戳
 
select current_timestamp();
select last_day(current_timestamp());
 
返回 2021-10-08 12:25:22.339

5.18 月份增加函数:add_months

语法:add_months(string start_date, int num_months)
 
返回值:string
 
说明:返回当前时间下再增加num_months个月的日期
 
select add_months(‘2021-10-08’,2);
2021-12-08

5.19 最后一天的日期函数:last_day

语法:last_day(string date)
 
返回值:string
 
说明:返回这个月的最后一天的日期，忽略时分秒部分（HH:mm:ss）
 
select last_day(current_date());
select last_day(current_timestamp()); -- 忽略时分秒部分（HH:mm:ss）
 
返回 2021-10-31

5.20 下一个星期X所对应的日期函数:next_day

语法:next_day(string start_date, string day_of_week)
 
返回值:string
 
说明:返回当前时间的下一个星期X所对应的日期 如：next_day(‘2015-01-14’, ‘TU’) 
= 2015-01-20 以2015-01-14为开始时间，其下一个星期二所对应的日期为2015-01-20
 
select next_day(current_date(),'su');
2021-10-10

5.21 指定格式返回时间函数:date_format

语法:date_format(date/timestamp/string ts, string fmt)
 
返回值:string
 
说明:按指定格式返回时间date 如：date_format(“2016-06-22”,“MM-dd”)=06-22
 
select date_format(current_date(),'MM-dd');
 
返回 10-08

5.22 当前星期函数:dayofweek

语法:dayofweek(date)
 
返回值:int
 
说明:返回日期那天的周几
 
select dayofweek(current_date());
 
返回 6
 

 六、条件函数

6.1 If函数: if

语法: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
 
返回值: T
 
说明: 当条件testCondition为TRUE时，返回valueTrue；
否则返回valueFalseOrNull（valueTrue，valueFalseOrNull为泛型）
类似于三元表达式
 
select if(10>5,1,0);
返回 1
 
select if(10<5,1,0);
返回 0

6.2 空查找函数: nvl

语法: nvl(T value, T default_value)
返回值: T
 
说明:如果value值为NULL就返回default_value,否则返回value
 
select nvl(null,1);
返回 1
 
select nvl(2,1);
返回 2
 
注意: select nvl('',1); 返回 ''

6.3 非空查找函数: COALESCE

语法: COALESCE(T v1, T v2,…)
 
返回值: T
 
说明: 返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL
 
select coalesce(1,2,null,...); 
返回 1
select coalesce(null,null,2,...);
返回 2
 
select coalesce(null,null,null,...);
返回 null

6.4 条件判断函数：CASE

语法: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
 
返回值: T
 
说明：如果a等于b，那么返回c；如果a等于d，那么返回e；否则返回f
 
select CASE 4 WHEN 5 THEN 5 WHEN 4 THEN 4 ELSE 3 END;
返回 4

6.5 条件判断函数：CASE

语法: CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END
 
返回值: T
 
说明：如果a为TRUE,则返回b；如果c为TRUE，则返回d；否则返回e
 
select
case 
when a > 10  and a < 20 then '10-19岁'
when a >= 20 and a < 30 then '20-30岁'
else '30岁以上'
end as '年龄段'

6.6 空值判断函数：isnull

语法: isnull( a )
返回值:boolean
 
说明：如果a为null就返回true，否则返回false
 
select isnull(5);
返回 false
select isnull(null);
返回 true
 
select * from ads_class
where classid is null;
返回满足条件的结果

6.7 非空值判断函数：isnotnull

语法: isnotnull ( a )
 
返回值:boolean
 
说明：如果a为非null就返回true，否则返回false
 
select isnotnull(5);
返回 true
 
select * from ads_class
where classid is not null;
 
返回满足条件的结果

七、聚合函数 [一般会和分组函数或开窗函数使用]

不统计null值
7.1  个数统计函数: count

语法:count(*), count(expr), count(DISTINCT expr[, expr…])
 
返回值: BIGINT
 
说明: count(*)统计检索出的行的个数，包括NULL值的行；
count(expr)返回指定字段的非空值的个数；
count(DISTINCTexpr[, expr_.])统计提供非NULL且去重后的expr表达式值的行数
 
select count(1) from  tb_class;
通常和分组函数一起使用

7.2  总和统计函数: sum

2. 总和统计函数: sum
语法: sum(col), sum(DISTINCT col)
 
返回值: double
 
说明: sum(col)统计结果集中col的相加的结果；sum(DISTINCT col)统计结果中col不同值相加的结果
 
select sum(money) from order 

 7.3  平均值统计函数: avg

语法: avg(col), avg(DISTINCT col)
 
返回值: double
 
说明: avg(col)统计结果集中col的平均值；avg(DISTINCT col)统计结果中col不同值相加的平均值

7.4  最小值统计函数: min

语法: min(col)
 
返回值: double
 
说明:统计结果集中col字段的最小值

7.5  最大值统计函数: max

语法: maxcol)
 
返回值: double
 
说明:统计结果集中col字段的最大值

 

 八、分组函数

group by的意思是根据by对数据按照哪个字段进行分组，或者是哪几个字段进行分组。
语法: group by 后边跟分组字段,多个字段可以用逗号隔开
 
select   字段    from   表名   where    条件     group   by       字段
 
或者
 
select   字段    from   表名   group  by    字段    having    过滤条件
 
注意：对于过滤条件，可以先用where，再用group  by或者是先用group  by，再用having
group by 后不能出现where函数,因为 where 先制行

 8.1 举例:

单个字段进行分组
select class, count(1) as cnt 
from tb_class 
group by class       -- 注意select 后不能出现没有参与分组的字段
 
多个字段分组[每个班级相同人名的人数]
select class,name,count(1) as cnt 
from tb_class 
group by class,name

8.2 常和聚合函数一起使用 count() , sum() , avg() , max() , min()

1. count() --每个班级的人数
select class,count(1) as cnt from tb_class group by class;
注意: 如果count(distinct 字段) 效率较低,如果数据量很大时,不建议使用
[因为使用count(distinct)语法聚合时reduce会变成一个来完成聚合]
可以使用 group by 加 count 来代替完成
 
2.sum() -- 每个班级的工资总和
select class,sum(gongzi) as total_gongzi from tb_class group by class;
 
3.avg() --每个班级的平均年龄
select class, avg(age) from tb_class group by class;
 
4.max() --每个班级年龄的最大值
select class, max(age) from tb_class group by class;
 
5.min() --每个班级年龄的最小值
select class, min(age) from tb_class group by class;