SQL函数语法及使⽤案例（持续更新）
函数名称导航--
1. MONTHS_BETWEEN函数
MONTHS_BETWEEN (x, y)⽤于计算x和y之间有⼏个⽉。如果x在⽇历中⽐y早，那么MONTHS_BETWEEN()就返回⼀个负数
SELECT MONTHS_BETWEEN('2008-05-05', '2008-04-05') FROM dual-----------------------------------------1
2. CASE WHEN THEN ELSE函数
Case具有两种格式。简单Case函数和Case搜索函数。
简单Case函数
--CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '⼥'
ELSE '其他' END
--Case搜索函数
CASE WHEN sex = '1' THEN '男'
WHEN sex = '2' THEN '⼥'
ELSE '其他' END
这两种⽅式，可以实现相同的功能。简单Case函数的写法相对⽐较简洁，但是和Case搜索函数相⽐，功能⽅⾯会有些限制，⽐如写判断式。
还有⼀个需要注意的问题，Case函数只返回第⼀个符合条件的值，剩下的Case部分将会被⾃动忽略。
3. CAST 函数
CAST函数语法规则是：Cast(字段名 as 转换的类型 )，其中类型可以为：
CHAR[(N)] 字符型
DATE ⽇期型
DATETIME ⽇期和时间型
DECIMAL float型
SIGNED int
TIME 时间型
4. NVL()函数
1.NVL函数的格式如下：NVL(expr1,expr2)
含义是：如果oracle第⼀个参数为空那么显⽰第⼆个参数的值，如果第⼀个参数的值不为空，则显⽰第⼀个参数本来的值。
2.NVL2函数的格式如下：NVL2(expr1,expr2, expr3)
含义是：如果该函数的第⼀个参数为空那么显⽰第⼆个参数的值，如果第⼀个参数的值不为空，则显⽰第三个参数的值。SQL> select ename,NVL2(comm,-1,1 ) from emp;
3.NULLIF(exp1,expr2)函数的作⽤是如果exp1和exp2相等则返回空(NULL)，否则返回第⼀个值。
4.Coalesce(expr1, expr2, expr3….. exprn)
表⽰可以指定多个表达式的占位符。所有表达式必须是相同类型，或者可以隐性转换为相同的类型。
返回表达式中第⼀个⾮空表达式，如有以下语句：  SELECT  COALESCE(NULL,NULL,3,4,5) FROM dual  　其返回结果为：3
如果所有⾃变量均为 NULL，则 COALESCE 返回 NULL 值。  COALESCE(expression1,...n) 与此 CASE 函数等价：
5. UNION函数
说明：union函数是⾏级连接，增加⾏数
例如：(不包括重复的)
select * from student_drb where bno=1
union
select * from student_drb where bno=4
此结果是将select * from student_drb where bno=4查出的结果合并到
select * from student_drb where bno=1结果下⾯
6. UNION ALL
insert overwrite table TABLE_NAME
select X from ...
union all
select X from ...;
-- 表⽰：将多个查询的结果合并，表中的数据都罗列出来（包括重复的）
-- 注："X"的位置必须相同
7. 时间戳转化
在sql查询时将⽇期转为时间戳
NOW()：当前⽇期时间
SELECT UNIX_TIMESTAMP(NOW());
将⽇期格式转成时间戳 1493016522
SELECT UNIX_TIMESTAMP(create_time);
在sql查询时将时间戳转为⽇期
SELECT FROM_UNIXTIME(1493016148);
将时间戳转成⽇期格式 2017-04-24 14:42:28
8. TIMESTAMP
1、current_date
-- 表⽰：当前⽇期,yyyy-MM-dd
2、current_time
-- 表⽰：当前时间,HH:mm:ss
3、current_timestamp
-- 表⽰：返回当前UTC时间(GMT+0)的时间戳，⼩于北京时间8⼩时，就是⽇期时间yyyy-MM-dd HH:mm:ss
4、unix_timestamp()
4.1、unix_timestamp()
-- 得到当前时间戳.
4.2、unix_timestamp(string date)
-- 如果参数date满⾜yyyy-MM-dd HH:mm:ss形式，则可以直接得到参数对应的时间戳.
-- 如果参数date不满⾜yyyy-MM-dd HH:mm:ss形式，则我们需要指定date的形式，再进⾏转换如：unix_timestamp(‘2009-03-20’, ‘yyyy-MM-dd’)=1237532400
5、from_unixtime(unix_timestamp,format)
-- 表⽰：返回表⽰ Unix 时间标记的⼀个字符串，根据format字符串格式化。
语法：from_unixtime(t1,’yyyy-MM-dd HH:mm:ss’)
其中t1是10位的时间戳值，即1970-1-1⾄今的秒，⽽13位的所谓毫秒的是不可以的。
对于13位时间戳，需要截取，然后转换成bigint类型，因为from_unixtime类第⼀个参数只接受bigint类型。例如：from_unixtime(cast(substring(tistmp,1,10) as bigint),’yyyy-MM-dd HH’)
9. IN函数
in 操作符：
SELECT * FROM table1 WHERE age1 IN(11,1);
查询来⾃表哥table1的数据，条件为age1 在(11,1)这两个数之中，其中in的意思就是说查询的数据在什么之中。那么我们这样使⽤in就可以查询age1为11和1的数据了。
注意:
(1）在使⽤IN 和 NOT IN 时要注意 IN范围中有NULL和空值的情况
（2）尽量不要⽤IN和NOT IN的⽅式⽽是转换为LEFT JOIN的形式
（3）在where语句中考虑NULL的同时要考虑空字符串的情况
10. SUBSTR和SUBSTRING区别
两者都是截取字符串。
1.相同点：如果只是写⼀个参数，两者的作⽤都⼀样：都是是截取字符串从当前下标以后直到字符串最后的字符串⽚段
var str = '123456789';
console.log(str.substr(2));    //  "3456789"
console.log(str.substring(2)) ;//  "3456789"
2.不同点：第⼆个参数
substr（startIndex,lenth）：第⼆个参数是截取字符串的长度（从起始点截取某个长度的字符串）；
substring（startIndex, endIndex）：第⼆个参数是截取字符串最终的下标（截取2个位置之间的字符串,‘含头不含尾’）。
例⼦1:
console.log("123456789".substr(2,5));    //  "34567"
console.log("123456789".substring(2,5)) ;//  "345"
例⼦2:
var a="abcdefghiklmnopqrstuvwxyz";
var b=a.substr(3,5);
var c=a.substring(3,5);
打印输出的结果是：
defgh
de
注意最后5下标是不会取到的意思是只能截取a字符串的3,4下标
截取的时候是不会截取到最后⼀个[3,5）
String.substr(startIndex,lenth) 这个是我们常⽤的从指定的位置(startIndex)截取指定长度(lenth)的字符串; String.substring(startIndex, endIndex) 这个是startIn dex,endIndex⾥出⼀个较⼩的值，然后从字符串的开始位置算起，截取较⼩值位置和较⼤值位置之间的字符串，截取出来的字符串的长度为较⼤值与较⼩值之间的差。
11. WITH 连接词
with TABLE_NAME AS (
SELECT ... FROM ... WHERE ...
)
-- ⾸个连接需要with，后续不要with：
TABLE_NAME AS (
SELECT ... FROM ... WHERE ...
12. ROW_NUMBER() over(partition by A order by B asc/desc)
row_number() over(partition by A order by B asc/desc)
-- 将查询结果按照A字段分组（partition），
-- 然后组内按照B字段排序，⾄于asc还是desc，可⾃⾏选择，
-- 然后为每⾏记录返回⼀个row_number⽤于标记顺序(编号)
特⾊功能：给已有hive表(dm.official_accounts_funscount_w) 添加⼀列序号(sample_key)，例：select
row_number() over(
partition by case when t.source is not null then 1 end
order by t.source asc,t.funCounts desc
) as sample_key,
t.source,
t.cityName,
t.weight,
t.strArea,
t.funCounts
from dm.official_accounts_funscount_w t;
13. Row number 和RANK 和DENSE区别
RANK() 排序相同时会重复，总数不会变
DENSE_RANK() 排序相同时会重复，总数会减少
ROW_NUMBER() 会根据顺序计算
14. CONCAT(a,b) 和concat_ws
-- 将a字符串与b字符串拼接 ==>('hello_world')
concat函数在连接字符串的时候，只要其中⼀个是NULL，那么将返回NULL
hive> select concat('a','b');
OK
ab
Time taken: 0.477 seconds, Fetched: 1 row(s)
hive> select concat('a','b',null);
OKsql left join 多表连接
NULL
Time taken: 0.181 seconds, Fetched: 1 row(s)
hive> select concat_ws('-','a','b');
OK
a-b
Time taken: 0.245 seconds, Fetched: 1 row(s)
hive> select concat_ws('-','a','b',null);
OK
a-b
Time taken: 0.177 seconds, Fetched: 1 row(s)
hive> select concat_ws('','a','b',null);
OK
ab
Time taken: 0.184 seconds, Fetched: 1 row(s)
15. Collect_set和Collect_list函数

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。