SQL常用函数总结
1 关系运算
1.1 1、等值比较: =
1.2 2、不等值比较:<>
语法 : A <> B
操作类型 : 所有基本类型
描述 : 如果表达式A为NULL，或者表达式B为NULL，返回NULL；如果表达式A与表达式B不相等，则为TRUE；否则为FALSE
1.3 3、小于比较: <
1.4 4、小于等于比较: <=
1.5 5、大于比较: >
1.6 6、大于等于比较: >=
1.7 7、空值判断: IS NULL
1.8 8、非空判断: IS NOT NULL
1.9 9、LIKE比较: LIKE
2 逻辑运算：
2.1 1、逻辑与操作: AND
2.2 2、逻辑或操作: OR
2.3 3、逻辑非操作: NOT
3 数值计算：
3.1 1、取整函数: round
3.2 2、向下取整函数: floor
语法
: floor(double a)
返回值
: BIGINT
说明
: 返回等于或者小于该double变量的最大的整数
hive> select
floor(3.1415926) from
iteblog;
3
hive> select
floor(25) from
iteblog;
25
3.3 3、向上取整函数: ceil
语法
: ceil(double a)
返回值
: BIGINT
说明
: 返回等于或者大于该double变量的最小的整数
hive> select
ceil(3.1415926) from
iteblog;
4
hive> select
ceil(46) from
iteblog;
46
3.4 4、取随机数函数: rand
语法: rand(),rand(int seed)
返回值: double
说明: 返回一个0到1范围内的随机数。如果指定种子seed，则会等到一个稳定的随机数序列
5 日期函数
5.1 1、UNIX时间戳转日期函数: from_unixtime
语法
: from_unixtime(bigint unixtime[, string format])
返回值
: string
说明
: 转化UNIX时间戳（从1970-01-01 00:00:00 UTC到指定时间的秒数）到当前时区的时间格式
hive> select
from_unixtime(1323308943,'yyyyMMdd') from
iteblog;
20111208
5.2 2、获取当前UNIX时间戳函数: unix_timestamp
语法
: unix_timestamp()
返回值
: bigint
说明
: 获得当前时区的UNIX时间戳
hive> select
unix_timestamp() from
iteblog;
1323309615
5.3 3、日期转UNIX时间戳函数: unix_timestamp
语法
: unix_timestamp(string date)
返回值
: bigint
说明
: 转换格式为"yyyy-MM-dd HH:mm:ss"的日期到UNIX时间戳。如果转化失败，则返回0。
hive> select
unix_timestamp('2011-12-07 13:01:03') from
iteblog;
1323234063
5.4 4、指定格式日期转UNIX时间戳函数: unix_timestamp
语法: unix_timestamp(string date, string pattern)
返回值: bigint
说明: 转换pattern格式的日期到UNIX时间戳。如果转化失败，则返回0。
hive> select unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss') from iteblog;
1323234063
5.5 5、日期时间转日期函数: to_date
语法
: to_date(string timestamp)
返回值
: string
说明
: 返回日期时间字段中的日期部分。
hive> select
to_date('2011-12-08 10:03:01') from
iteblog;
2011-12-08
5.6 6、日期转年函数: year
5.7 7、日期转月函数: month
5.8 8、日期转天函数: day
5.9 9、日期转小时函数: hour
5.12 12、日期转周函数: weekofyear
5.13 13、日期比较函数: datediff
语法: datediff(string enddate, string startdate)
返回值: int
说明: 返回结束日期减去开始日期的天数。
hive> select datediff('2012-12-08','2012-05-09') from iteblog;
213
5.14 14、日期增加函数: date_add
语法
: date_add(string startdate, int days)
返回值
: string
说明
: 返回开始日期startdate增加days天后的日期。
hive> select
date_add('2012-12-08',10) from
iteblog;
2012-12-18
5.15 15、日期减少函数: date_sub
语法
: date_sub (string startdate, int days)
返回值
: string
说明
: 返回开始日期startdate减少days天后的日期。
hive> select
date_sub('2012-12-08',10) from
iteblog;
2012-11-28
6 条件函数
6.1 1、If函数: if
语法
: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
返回值
: T
说明
: 当条件testCondition为TRUE时，返回valueTrue；否则返回valueFalseOrNull
hive> select
if(1=2,100,200) from
iteblog;
200
hive> select
if(1=1,100,200) from
iteblog;
100
6.2 2、非空查找函数: COALESCE
语法
: COALESCE(T v1, T v2, …)
返回值
: T
说明
: 返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL
hive> select
COALESCE(null,'100','50′) from
iteblog;
100
6.3 3、条件判断函数：CASE
语法
: CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END
返回值
: T
说明
：如果a为TRUE,则返回b；如果c为TRUE，则返回d；否则返回e
hive> select
case
when
1=2 then
'tom'
when
2=2 then
'mary'
else
'tim'
end
from
iteblog;
mary
hive> select
case
when
1=1 then
'tom'
when
2=2 then
'mary'
else
'tim'
end
from
iteblog;
tom
7 字符串函数
7.1 1、字符串长度函数：length
语法
: length(string A)
返回值
: int
说明
：返回字符串A的长度
hive> select
length('abcedfg') from
iteblog;
7
7.2 2、字符串连接函数：concat
语法
: concat(string A, string B…)
返回值
: string
说明
：返回输入字符串连接后的结果，支持任意个输入字符串
hive> select
concat(‘abc’,'def’,'gh’) from
iteblog;
abcdefgh
7.3 3、字符串截取函数：substr,substring
语法
: substr(string A, int start, int len),substring(string A, int start, int len)
返回值
: string
说明
：返回字符串A从start位置开始，长度为len的字符串
hive> select
substr('abcde',3,2) from
iteblog;
cd
hive> select
substring('abcde',3,2) from
iteblog;
cd
hive>select
substring('abcde',-2,2) from
iteblog;
de
7.4 4、字符串转大写函数：upper,ucase
语法
: upper(string A) ucase(string A)
返回值
: string
说明
：返回字符串A的大写格式
hive> select
upper('abSEd') from
iteblog;
ABSED
hive> select
ucase('abSEd') from
iteblog;
ABSED
7.5 5、字符串转小写函数：lower,lcase
语法
: lower(string A) lcase(string A)
返回值
: string
说明
：返回字符串A的小写格式
hive> select
lower('abSEd') from
iteblog;
absed
hive> select
lcase('abSEd') from
iteblog;
absed
7.6 6、去空格函数：trim
语法
: trim(string A)
返回值
: string
说明
：去除字符串两边的空格
hive> select
trim(' abc ') from
iteblog;
abc
7.7 67、替换函数：replace
语法 : REPLACE( 对象字符串，替换前的字符串，替换后的字符串 )
8 集合统计函数