SQL学习笔记之常用技巧与函数合集

1.json解析

{} get_json_object(字段,'$.')
[] 数组 get_json_object(字段,'$.[X].')
(regexp_replace(tags,'(\\[|\\]|")','') --正则转换[]字符，结合下述lateral view食用
explode(split(regexp_replace(regexp_extract(jsontext,'^\\[(.+)\\]$',1),'\\}\\,\\{', '\\}\\|\\|\\{'),'\\|\\|'))
--regexp_extract(jsontext,'^\\[(.+)\\]$',1) 去掉方括号[]
--regexp_replace(string,'\\}\\,\\{', '\\}\\|\\|\\{') 用}||{代替},{
--split(string,'\\|\\|')根据双竖线分割数组为多个小的json，返回值是一个array数组
--explode 将一行的数据拆分成多行，它的参数必须为map或array
--涉及多列的情况，需要：
select id,name,orderdate,newcol
from table1
lateral view explode() as newcol 

链接：regexp_extract的用法总结
regexp_extract(string subject, string pattern, int index)
给定字符串subject，根据pattern的正则表达，按照index的规定返回
index：
0是显示与之匹配的整个字符串
1 是显示第一个括号里面的
2 是显示第二个括号里面的字段
…

#get_json_object一次解析一个值
select get_json_object(ziduan,'$.sessionid') as sessionid
,get_json_object(ziduan,'$.uid') as uid
from table1
;
#可以一次解析多个值，需要与lateral view一起使用
select a.*
from table1
lateral view json_tuple(ziduan,'sessionid','uid') a as  sessionid,uid

2.字段分行

select a.*,xx
from a lateral view explode(split(regexp_replace(tags,'(\\[|\\]|")',''),',')) as xx

select 
a.sid
,a.firstMessageTime
,a.closeTime
,from_unixtime(firstMessageTime_stamp/1000+pos,"yyyy-MM-dd HH:mm:ss") as key_tid
,pos,val 
,firstMessageTime_stamp
,closeTime_stamp
from sessiondata a
lateral view posexplode(split(space(round(closeTime_stamp/1000-firstMessageTime_stamp/1000,0)),' ')) tf as pos,val
order by a.empCode,firstMessageTime_stamp,pos

lateral view posexplode方法，产生两列：位置索引与具体的值，该方法借助分行索引&时间戳实现根据时间区间+1秒填充转换为列数据；其中注意时间戳的转换（/1000处理毫秒级时间戳）

3.排序

order by rand() --随机排序
ORDER BY NEWID() --随机排序
order by 列名 Collate Chinese_PRC_CS_AS_KS_WS --按拼音首字母排序
Order By 列名 Collate Chinese_PRC_Stroke_ci_as --按姓氏笔画排序

摘抄–注释：
首先，在这里的collate是一个子句，主要是定义排序规则，可应用于数据库定义或列定义；或应用于字符串表达式以应用排序规则投影。
语法是collate collation_name。参数collate_name是应用于表达式、列定义或数据库定义的排序规则的名称。
collation_name 可以只是指定的 Windows_collation_name 或 SQL_collation_name。
Windows_collation_name 是 Windows 排序规则的排序规则名称。参见 Windows 排序规则名称。
SQL_collation_name 是 SQL 排序规则的排序规则名称。参见 SQL 排序规则名称。

• SQL排序学习笔记.

4.数字&字母–正则

like '%[a-zA-Z]%'
like '[^F-M]'

其中
[ ]指定值的范围
^ 排除指定范围

5.逻辑操作符

• and 的优先级高于or

where id=1002 or id=1003 and price>=10
-- 表示id为1003且价格大于10的商品，或者id为1002的商品
-- 使用()消除歧义

• 指定值的情况，可以用 in 代替 or ，且 in 更快
• not操作符：
  在MySQL中仅支持对in、between、exists取反；其他DBMS支持对各类条件取反

6.通配符

• 使用关键字like
• % 表示任意字符出现任意次数，包括0次
• _ 表示一个字符
• 注意事项：
  其搜索所需时间更长，优先选择其他方式
  放在where条件的最后进行过滤，可提高效率
  

7.正则表达式

正则学习资料

SQL及MySQL支持的正则表达式仅为正则表达式语言的一个很小的子集
常用技巧：

• 使用关键字regexp 或者 rlike
  sql中的正则表达式匹配一般不区分大小写，若要区分 可以使用 regexp binary
  like匹配整个字符串，而regexp匹配字串，可以使用定位符^开始 $结束来达到和like一样的效果

select '是9.2' regexp '[0-9\\.]' -- 返回true
select '是9.2' regexp '^[0-9\\.]' -- 返回false

where name regexp '.000' -- .表示除换行符\n以外的所有字符

• 进行or匹配，多字符使用|，单字符使用[]
  [123]ton为[1|2|3]ton的缩写
• ^表示否定
• 匹配范围
  如： [0-9] [1-6] [a-z]
• 转义符\,其中MySQL使用\\作为转义符
• 匹配字符类——预定义的字符集,如下表1

_表1

类	说明
[:alnum:]	任意数字和字母（同[a-zA-Z0-9])
[:alpha:]	任意字母组成的字符串（同[a-zA-Z])
[:lower:]	同[a-z]
[:upper:]	同[A-Z]
[:digit:]	同[0-9]

8.解析url

用parse_url()
链接1
链接2

9.删除多余空格

trim()
Rtrim()
Ltrim()