高頻用到的HIVE查詢相關(guān)SQL

1 HIVE中轨域，字段是String的時(shí)間戳轉(zhuǎn)換為日期格式
語句公式：
select from_unixtime(時(shí)間戳字段垂寥，要轉(zhuǎn)的日期格式) from someTable
eg： select from_unixtime(1234567899炉爆，'yyyy/MM/dd HH:mm:ss')

注意：如果跑出來發(fā)現(xiàn)日期不對(duì)朗涩，那先看一下自己時(shí)間戳的位數(shù)是不是10位，如果遇到的是13位的時(shí)間戳（13位毫秒時(shí)間戳），那么需要先讓它除以1000疫剃，注意除以之后會(huì)變成double格式，記得轉(zhuǎn)成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() 得到當(dāng)前時(shí)間戳
如果本身就符合日期格式y(tǒng)yyy-MM-dd HH:mm:ss硼讽，則可以直接select unix_timestamp(日期字段巢价，日期格式) from test_table 得到參數(shù)對(duì)應(yīng)的時(shí)間戳
如果參數(shù)date不滿足yyyy-MM-dd HH:mm:ss形式，則我們需要指定date的形式固阁，在進(jìn)行轉(zhuǎn)換
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 其它常用用得到的日期處理函數(shù)
獲取當(dāng)前時(shí)間下的日期：select to_date('2023-01-30 16:15:10')

image.png

獲取日期時(shí)間的年/月/周/日： year()
select year('2023-01-30 16:15:10')
month() 月
weekofyear() 一年的第幾周
day() 一年的第幾天

獲取日期時(shí)間的時(shí)/分/秒：hour()/minute()/second()
select hour('2023-01-30 16:15:10')

image.png

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

當(dāng)前日期減N天：date_sub(當(dāng)前日期,N)
select date_sub('2023-01-30 16:15:10',3)

日期相減：datediff(一個(gè)日期壤躲，另一個(gè)日期)
前一個(gè)日期小于后一個(gè)日期，是負(fù)數(shù)备燃。反之正數(shù)
select datediff('2023-01-30 16:15:10','2023-02-20 16:15:10')

image.png

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

1碉克、計(jì)算小時(shí)差：(hour(日期時(shí)間1) - hour(日期時(shí)間2) +(DATEDIFF(日期時(shí)間1, 日期時(shí)間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、在做“年-周”數(shù)據(jù)格式的字段時(shí)并齐，由于字符串排序問題經(jīng)常會(huì)遇到 “2021-10”排在“2021-5”的前面漏麦，所以需要在處理的時(shí)候加個(gè)"0"

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

5 計(jì)算字段中，某些字符串出現(xiàn)的次數(shù)
需求
現(xiàn)有一個(gè)字符串况褪，使用hivesql 統(tǒng)計(jì)指定字符出現(xiàn)的次數(shù)撕贞，比如一個(gè)字符串 “haifeng fox”,現(xiàn)在需要統(tǒng)計(jì)“f” 在字符串中出現(xiàn)的個(gè)數(shù)
select "haifeng fox" str;
實(shí)現(xiàn)方案
1、先將目標(biāo)字符串里的匹配字符串替換成空
select regexp_replace("haifeng fox","f","" ) str;
結(jié)果：haieng ox
2测垛、使用函數(shù)第一步的結(jié)果字符串的長(zhǎng)度捏膨，在計(jì)算原有字符串的長(zhǎng)度，兩者進(jìn)行求差值
select abs(length(regexp_replace("haifeng fox","f","")) - length("haifeng fox")) str
結(jié)果：2
3食侮、用差值/匹配字符串的長(zhǎng)度即可
select abs(length(regexp_replace("haifeng fox","f","")) - length("haifeng fox")) / length("f") str
結(jié)果：2
可見：匹配字符串出現(xiàn)了兩次号涯。

6 一個(gè)字段中的值包含了另外一個(gè)字段中的值
比如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

字符串拼接函數(shù)介紹
concat(str1,str2,str3,…)
連接參數(shù)的函數(shù)，返回結(jié)果為連接參數(shù)的字符串起胰。如果有一個(gè)參數(shù)為NULL久又，則返回的結(jié)果為NULL。
concat(‘a(chǎn)’,‘b’,‘c’) ---- ‘a(chǎn)bc’
concat(’‘a(chǎn),null,’'c)----null

7 GROUP和聚合函數(shù)：
https://www.gairuo.com/p/hive-sql-aggregate-functions

最大最小值 Max Min
max(col) 和 min(col) 可以取出結(jié)果中的最大值和最小值效五。

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