一、SQL（HIVE）
1、顺序
?
2、去重
-
distinct（对所有字段去重），会有时间损耗
-
group by（同样去重效果，大数据量效果比distinct好）
3、时间转换
时间戳：1627749129122，时间：2021-08-01 00:32:09。参考链接：时间转换
-
13位时间戳转化成时间（年月日时分秒）
select from_unixtime(cast('1627749129122'/1000 as bigint))
--from sys??运行结果：
?
-
时间转化成10位时间戳
select unix_timestamp('2021-08-01 00:32:09')运行结果：
?
?4、排序
select row_number() over (partition by a,b order by admin_time desc) as row_desc?根据a,b分组后，按照admin_time倒序排列，此时若查询row_desc=1,则为a,b分组后admin_time最近的一条数据
?
?
5、特殊处理
-
替换空格trim(value)
-
字符串连接concat(value1,value2,value3,...)
-
替换空值nvl(value,'')，和concat一起使用，避免连接的字段中，某个字段为空值时连接后的字符串输出为空值
注：nvl百度详解
例：
select nvl(sum(rulescore),0) from zwjc_graderule where rulecode='FWTD';select concat(nvl(a.`理由`,''),nvl(a.`违规时间点`,''),nvl(a.`违规内容`,'')) as `违规情况` from a
where dt='{{ds}}' and from_unixtime(cast(admin_time/1000 as bigint),'yyyy-MM-dd')='{{ds}}'-
替换字符串
select replace('我叫小花，我喜欢唱歌，你叫什么','叫','是') as content运行结果：
-
?查找字符所在位置
instr(str,substr) 返回substr在str第一次出现的位置（从1开始计数），如果substr在str中不存在则返回0
select instr(replace('我叫小花，我喜欢唱歌，你叫什么','叫','是'),'，') as content运行结果：
-
截取字符串
语法: substr(string A, int start, int len)
返回值: string
说明：返回字符串A从start位置开始，长度为len的字符串
参考链接：substr基础
select substr('我叫小花，我喜欢唱歌，你叫什么',0,instr(replace('我叫小花，我喜欢唱歌，你叫什么','叫','是'),'，')) as contentselect substr('我叫小花，我喜欢唱歌，你叫什么',0,5) as content两个语句返回结果一样，如下：
?
?
?6、条件
?rlike用法：
select name from a where name rlike '张|李|郝.*'参考链接：like和rlike
?
7、获取json存储数据
原始数据示例：
字段名称：submit_info，字段内容：如下
{"param":{},"action":15115,"imInfo":{"buyerId":0,"hitText":"08465879","hitTime":1628059737667,"sellerId":00000000,"buyerName":"未知","sellerName":"好藏","sellerText":"一八一零八四六五八七九","buyerIdMask":0,"conversation":[{"time":1628059719042,"content":"怎么付款","userName":"未知","imUserType":"BUYER","contentType":"TEXT"},{"time":1628059732311,"content":"181","userName":"好藏","imUserType":"SELLER","contentType":"TEXT"},{"time":1628059737667,"content":"66666679","userName":"好藏","imUserType":"SELLER","contentType":"TEXT"}],"conversationMask":[{"time":1628059719042,"content":"怎么付款","userName":"未知","imUserType":"BUYER","contentType":"TEXT"},"actionDesc":"命中违规","recallType":401,"auditHistory":{"label":"首次审核","level":"info"},"rejectReason":{"value":[{"label":"","value":"17","children":[{"label":"","value":"1701","children":[{"label":"","value":"170101"}]}]}],"reason":["私信内容有私单倾向"]},"subRecallType":0,"thirdDealInfo":null,"baseStaticInfo":{},"highLightWords":[],"recallTypeDesc":"私下交易","baseDynamicInfo":{},"subRecallTypeDesc":""}
- 获取两人对话
get_json_object(submit_info,'$.imInfo.conversation')- 获取对话中的内容
get_json_object(submit_info,'$.imInfo.conversation.contentType')- 获取对话中第一条对话
get_json_object(submit_info,'$.imInfo.conversation.contentType[0]')