常用SQL函数方法

tran
split

• • • • str_to_date(str,format)函数 　　日期与字符串的转换
      • add_months(date,num) 　　 日期操作
      • coalesce() 函数取值
      • unix_timestamp() 和 from_unixtime() 日期和时间戳的转换
      • RegExp 正则

适用于MYSQL

常用format:

1. %Y 　年
2. %m 　月
3. %d 　日
4. %H 　小时
5. %i 　分
6. %s 　秒

大全:

1. %a 　缩写星期名
2. %b 　缩写月名
3. %c 　月，数值
4. %D 　带有英文前缀的月中的天
5. %d 　月的天，数值(00-31)
6. %e 　月的天，数值(0-31)
7. %f 　微秒
8. %H 　小时 (00-23)
9. %h 　小时 (01-12)
10. %I　 小时 (01-12)
11. %i 　分钟，数值(00-59)
12. %j 　年的天 (001-366)
13. %k 　小时 (0-23)
14. %l 　小时 (1-12)
15. %M 　月名
16. %m 　月，数值(00-12)
17. %p 　AM 或 PM
18. %r 　时间，12-小时（hh:mm:ss AM 或 PM）
19. %S 　秒(00-59)
20. %s 　秒(00-59)
21. %T 　时间, 24-小时 (hh:mm:ss)
22. %U 　周 (00-53) 星期日是一周的第一天
23. %u 　周 (00-53) 星期一是一周的第一天
24. %V 　周 (01-53) 星期日是一周的第一天，与 %X 使用
25. %v 　周 (01-53) 星期一是一周的第一天，与 %x 使用
26. %W 　星期名
27. %w 　周的天 （0=星期日, 6=星期六）
28. %X 　年，其中的星期日是周的第一天，4 位，与 %V 使用
29. %x 　年，其中的星期一是周的第一天，4 位，与 %v 使用
30. %Y 　年，4 位
31. %y 　年，2 位

select str_to_date('2021-09-24 16:05:16','%Y-%m-%d %H:%i:%s');

spark sql

to_date(date,fromat)
mysql不支持,spark等大数据框架支持

select to_date('2021-09-24 21:15:37','yyyy-mm-dd hh:mi:ss') 

适用于spark
不适用于mysql

add_months 有两个参数，第一个参数是日期，第二个参数是对日期进行加减的数字(以月为单位的)

如：查询3个月以前的时间，可以用下列语句

select add_months(2021-09-24,-3);

得到结果是 2021-06-24

spark sql
mysql

用途:
1.将空值替换成其他值
2.返回第一个非空值

用法:
coalesce(expr1,expr2,…)
依次查看各表达式,遇到非NULL值既停止,并返回该值。
如果所有表达式都是空值，最终返回空值

ｅｇ：

select coalesce(null,null,'AAA') ;

得到值 AAA

FROM_UNIXTIME(unix_timestamp,format)
将时间戳转换为日期格式
mysql 和 spark 的函数用法不太相同
mysql:

SELECt FROM_UNIXTIME(1632477705,'%Y%m%d %H:%i:%s');

spark:

SELECt FROM_UNIXTIME(1632477705,'yyyyMMdd HH:mm:ss');

UNIX_TIMESTAMP()

将时间格式改为时间戳
mysql 和 spark 用法不同
mysql:
unix_timestamp(date)

SELECt UNIX_TIMESTAMP(NOW());

spark sql:
unix_timestamp(date,format)

select UNIX_TIMESTAMP(NOW(),'yyyyMMdd HHmmss');

SQL的查询语句中，有时会需要引进正则表达式为其复杂搜索指定模式。

-- 寻找到fruit列含有 'apple'字段的数据
select *
from tab1
where fruit regexp 'apple'