高频用到的HIVE查询相关SQL

1 HIVE中，字段是String的时间戳转换为日期格式
语句公式：
select from_unixtime(时间戳字段，要转的日期格式) from someTable
eg： select from_unixtime(1234567899，'yyyy/MM/dd HH:mm:ss')

注意：如果跑出来发现日期不对，那先看一下自己时间戳的位数是不是10位，如果遇到的是13位的时间戳（13位毫秒时间戳），那么需要先让它除以1000，注意除以之后会变成double格式，记得转成bigint

eg：select from_unixtime(cast(sort_time/1000 as bigint),'yyyy/MM/dd HH:mm:ss') from dm_fw.dm_equipment_data_dtl_di where data_type = 'WCS_CE' and inc_day >= '20230101' and inc_day <='20230107' and hub_code = "510000001" limit 10

image.png

2 unix_timestamp() 得到当前时间戳
如果本身就符合日期格式yyyy-MM-dd HH:mm:ss，则可以直接select unix_timestamp(日期字段，日期格式) from test_table 得到参数对应的时间戳
如果参数date不满足yyyy-MM-dd HH:mm:ss形式，则我们需要指定date的形式，在进行转换
SELECT unix_timestamp(inc_day,"yyyyMMdd") as time
from dm_fw.dm_equipment_data_dtl_di
WHERE data_type = "WCS_CE" and inc_day >='20230101' limit 10

image.png

3 其它常用用得到的日期处理函数
获取当前时间下的日期：select to_date('2023-01-30 16:15:10')

image.png

获取日期时间的年/月/周/日： year()
select year('2023-01-30 16:15:10')
month() 月
weekofyear() 一年的第几周
day() 一年的第几天

获取日期时间的时/分/秒：hour()/minute()/second()
select hour('2023-01-30 16:15:10')

image.png

当前日期加N天：date_add(当前日期,N)
select date_add('2023-01-30 16:15:10',3)

当前日期减N天：date_sub(当前日期,N)
select date_sub('2023-01-30 16:15:10',3)

日期相减：datediff(一个日期，另一个日期)
前一个日期小于后一个日期，是负数。反之正数
select datediff('2023-01-30 16:15:10','2023-02-20 16:15:10')

image.png

4 其它好玩的日期加工技巧

1、计算小时差：(hour(日期时间1) - hour(日期时间2) +(DATEDIFF(日期时间1, 日期时间2)) * 24)
select (hour('2023-01-30 16:15:10')-hour('2023-02-20 16:15:10')+(datediff('2023-01-30 16:15:10','2023-02-20 16:15:10'))*24)
2、在做“年-周”数据格式的字段时，由于字符串排序问题经常会遇到 “2021-10”排在“2021-5”的前面，所以需要在处理的时候加个"0"

eg：IF(CAST(WEEKOFYEAR(日期) AS DOUBLE) < 10, CONCAT(YEAR(日期), '-0', WEEKOFYEAR(日期)), CONCAT(YEAR(日期), '-', WEEKOFYEAR(日期)))

5 计算字段中，某些字符串出现的次数
需求
现有一个字符串，使用hivesql 统计指定字符出现的次数，比如一个字符串 “haifeng fox”,现在需要统计“f” 在字符串中出现的个数
select "haifeng fox" str;
实现方案
1、先将目标字符串里的匹配字符串替换成空
select regexp_replace("haifeng fox","f","" ) str;
结果：haieng ox
2、使用函数第一步的结果字符串的长度，在计算原有字符串的长度，两者进行求差值
select abs(length(regexp_replace("haifeng fox","f","")) - length("haifeng fox")) str
结果：2
3、用差值/匹配字符串的长度即可
select abs(length(regexp_replace("haifeng fox","f","")) - length("haifeng fox")) / length("f") str
结果：2
可见：匹配字符串出现了两次。

6 一个字段中的值包含了另外一个字段中的值
比如A字段有11093，11098，B字段有11093，则可以查出来
select planned_sort_port_codes, actual_sort_port_code from dm_fw.dm_equipment_data_dtl_di
where data_type = 'WCS_CE'
and inc_day >= '2023-01-01'
and planned_sort_port_codes like concat('%',actual_sort_port_code,'%')
LIMIT 10

image.png

字符串拼接函数介绍
concat(str1,str2,str3,…)
连接参数的函数，返回结果为连接参数的字符串。如果有一个参数为NULL，则返回的结果为NULL。
concat(‘a’,‘b’,‘c’) ---- ‘abc’
concat(’‘a,null,’'c)----null

7 GROUP和聚合函数：
https://www.gairuo.com/p/hive-sql-aggregate-functions

最大最小值 Max Min
max(col) 和 min(col) 可以取出结果中的最大值和最小值。

SELECT max(math) AS math
FROM Students
'''
math|
----+
88|
'''

SELECT class,
max(math) AS math
FROM Students
GROUP BY class;
'''
class|math|
-----+----+
1| 78|
2| 88|
3| 78|

SELECT sum(math) AS total
FROM Students;
'''
total|
-----+
661|
'''

SELECT class,
sum(math) AS total
FROM Students
GROUP BY class;
'''
class|total|
-----+-----+
1| 276|
2| 230|
3| 155|

https://zhuanlan.zhihu.com/p/377978302