HiveSQL 工作實戰總結

記錄一些工作中有意思的統計指標，做過一些簡化方便大家閱讀，記錄如有錯誤，歡迎在評論區提問討論~

問題類型

1. 連續問題
   • 兩種思路
   • 第一種：日期減去一列數字得出日期相同，主要是通過row_number視窗函數
   • 第二種：後一個日期減去前一個日期差值相等，用的較少，可以用lag/lead視窗函數解決
2. 分組問題
   • 主要使用lag(col,1,0)分組將每行移到下一行，再按既定規則分組排序即可
   • 後面抽空試一下
3. 間隔連續問題，比如每斷一天也算連續
   • 兩種思路：
   • 第一種：連續使用兩次連續問題的求法即可，差了幾次可以連續row_number幾次，這種無限套娃不推薦使用
   • 第二種：連續差值小於要求數即可，比如斷一天也可，只要每行間隔小於2天即可
4. 打折日期交叉問題，兩段活動期重覆日期去除
5. 同時線上問題

一、統計每個設備的累計告警次數

原始數據格式

統計之後格式

工作思路

1. 先根據設備ID和告警日期分組
2. 按月份統計可以用substr函數或者日期格式化函數
3. 再統計即可得出小計告警次數
4. 接著使用聚合視窗函數計算累計告警次數

工作語句

1. 第一種方案，使用substr截取字元串函數

SELECT *,
	   SUM(sumPart) OVER (PARTITION BY deviceId ORDER BY alarmMonth) AS sumAll
FROM
(SELECT deviceId,
	   SUBSTR(alarmDate,1,7) AS alarmMonth,
	   SUM(alarmCount) AS sumPart
FROM test_00
GROUP BY deviceId,
		 alarmMonth) t;

2. 第二種方案，使用日期格式化函數，使用date_format函數的字元串必須滿足yyyy-MM-dd格式，所以必須先用regexp_replace替換/為-

SELECT *,
	   SUM(sumPart) OVER (PARTITION BY deviceId ORDER BY alarmMonth) AS sumAll
FROM
(SELECT deviceId,
	    DATE_FORMAT(REGEXP_REPLACE(alarmDate,'/','-'), 'yyyy-MM') AS alarmMonth,
	    SUM(alarmCount) AS sumPart
FROM test_00
GROUP BY deviceId,
		 alarmMonth) t;

開窗函數中的界限說明

• unbounded：無界限
• preceding：從分區第一行頭開始，則為 unbounded N為：相對當前行向後的偏移量
• following ：與preceding相反,到該分區結束，則為 unbounded N為：相對當前行向後的偏移量
• current row：顧名思義，當前行，偏移量為0

二、統計環境設備每天的總污染告警次數，並輸出每個設備告警次數排名前三的日期

原始數據格式

統計之後格式

工作思路

統計環境設備的總污染告警次數

1. 由於有設備可能會有同一時間的告警記錄，所以需要按告警時間去重後再統計
2. 如果使用distinct去重，如果表數據過大，且設備ID差異化很大，那麼會有性能壓力
3. 所以使用group by子查詢代替
4. mysql中的date_format格式化需要這樣寫：DATE_FORMAT(alarmTime, '%Y-%c-%d %T')

輸出每個設備告警次數排名前三的日期

1. 使用視窗函數ROW_NUMBER() OVER()進行分組排序即可，MySQL 替換 ROW_NUMBER() OVER (PARTITION ……) 函數
2. 多個子句查詢可以使用視圖和WITH語句

工作語句

統計環境設備的總污染告警次數

SELECT deviceId,
		COUNT(alarmTime) AS alarmCount 
FROM
--- http://c.biancheng.net/mysql/date_format.html
(SELECT deviceId,
		DATE_FORMAT(REGEXP_REPLACE(alarmTime,'/','-'), 'yyyy-MM-dd HH:mm:ss') AS alarmTime
FROM test_01
GROUP BY deviceId,alarmTime
ORDER BY alarmTime) t
GROUP BY deviceId;

輸出每個設備告警次數排名前三的日期

SELECT * 
FROM 
(SELECT deviceId,
		alarmDate,
		alarmCount,
		ROW_NUMBER() OVER(PARTITION BY deviceId ORDER BY alarmCount DESC) AS alarmRank
FROM 
(SELECT deviceId,
		alarmDate,
		COUNT(alarmDate) AS alarmCount
FROM
(SELECT deviceId,
		DATE_FORMAT(alarmTime, 'yyyy-MM-dd') AS alarmDate,
		DATE_FORMAT(alarmTime, 'yyyy-MM-dd HH:mm:ss') AS alarmTime
FROM test_01
GROUP BY deviceId,alarmTime
ORDER BY deviceId,alarmTime) t1
GROUP BY deviceId,alarmDate) t2) t3
WHERE alarmRank<=3;

-- 使用WITH語句優化一下
WITH t1 AS (
SELECT deviceId,
	DATE_FORMAT(alarmTime, 'yyyy-MM-dd') AS alarmDate,
	DATE_FORMAT(alarmTime, 'yyyy-MM-dd HH:mm:ss') AS alarmTime
FROM test_01
GROUP BY deviceId,alarmTime
ORDER BY deviceId,alarmTime),
t2 AS (
	SELECT deviceId,
	alarmDate,
	COUNT(alarmDate) AS alarmCount
FROM t1
GROUP BY deviceId,alarmDate),
t3 AS (
SELECT deviceId,
	alarmDate,
	alarmCount,
	ROW_NUMBER() OVER(PARTITION BY deviceId ORDER BY alarmCount DESC) AS alarmRank
FROM t2)
SELECT * FROM t3 WHERE alarmRank<=3;

COUNT(1)和COUNT(*)的區別

1. 從執行結果來說
   • COUNT(1)和COUNT(*)之間沒有區別，因為COUNT(*)和COUNT(1)都不會去過濾空值
   • 但COUNT(列名)就有區別了，因為COUNT(列名)會去過濾空值
2. 從執行效率來說
   • 他們之間根據不同情況會有些許區別，MySQL會對COUNT(*)做優化
   • 如果列為主鍵，COUNT(列名)效率優於COUNT(1)
   • 如果列不為主鍵，COUNT(1)效率優於COUNT(列名)
   • 如果表中存在主鍵，COUNT(主鍵列名)效率最優
   • 如果表中只有一列，則COUNT(*)效率最優
   • 如果表有多列，且不存在主鍵，則COUNT(1)效率優於COUNT(*)

三、統計每個月的總告警次數，總告警設備數，以及能夠連續七天數值正常設備數量

留給你思考

四、統計出2022年1月首次出現告警的設備數量

原始數據格式

統計之後格式

工作思路

1. 先用date_format格式化所有設備告警時間為yyyy-MM的日期格式
2. 運用min函數得出每個設備最早告警日期
3. 當最早告警日期是2022年1月的時候即為我們所需要知道的設備記錄

工作語句

WITH t1 AS (
SELECT *,
		DATE_FORMAT(alarmTime, 'yyyy-MM') AS alarmMonth 
FROM test_01
),
t2 AS (
SELECT deviceId,
		alarmTime,
		MIN(alarmMonth) AS firstAlarmMonth
FROM t1
GROUP BY deviceId
)
SELECT * FROM t2 WHERE firstAlarmMonth='2022-1';

五、根據設備地區編號段對告警次數進行排序

有一個5000萬的設備信息表，一個2億記錄的告警記錄表

原始數據格式

1. 設備信息表

2. 告警記錄表

統計之後格式

工作思路

1. 先根據設備ID分組count統計報警次數
2. 再使用casewhen條件語句，或者使用concat/floor/ceil函數動態劃分，根據分段統計不同設備位於什麼地區號段
3. 最後連接查詢，並根據地區號段，使用sum函數統計總告警次數即可

工作語句

-- 第一種方案
WITH t1 AS(
SELECT deviceId,
		COUNT(alarmTime) AS alarmCount
FROM test_01
GROUP BY deviceId
),
t2 AS(
SELECT deviceId,
		deviceDistrict,
		-- 如果地區編號是字元串可以先轉換再比較，不然會觸發隱式轉換，導致全表掃描無法使用索引
		-- CONVERT(deviceDistrict, UNSIGNED)>=210000
		CASE WHEN deviceDistrict>=210000 AND deviceDistrict<210010 THEN '210000-210010'
		 	WHEN deviceDistrict>=210010 AND deviceDistrict<210020 THEN '210010-210020'
			WHEN deviceDistrict>=210020 AND deviceDistrict<210030 THEN '210020-210030'
		 	WHEN deviceDistrict>=210030 AND deviceDistrict<210040 THEN '210030-210040'
		 	WHEN deviceDistrict>=210040 AND deviceDistrict<210050 THEN '210040-210050'
		 	WHEN deviceDistrict>=210050 AND deviceDistrict<210060 THEN '210050-210060'
		 	WHEN deviceDistrict>=210060 AND deviceDistrict<210070 THEN '210060-210070'
		END deviceDistrictSection
FROM test_02
),
t3 AS (
	SELECT t2.deviceDistrictSection AS deviceDistrictSection,
			SUM(t1.alarmCount) AS alarmCount
	FROM t1 LEFT JOIN t2 
	ON t1.deviceId = t2.deviceId
	GROUP BY deviceDistrictSection
	ORDER BY deviceDistrictSection
)
SELECT * FROM t3;

-- 第二種方案
WITH t1 AS(
SELECT deviceId,
		COUNT(alarmTime) AS alarmCount
FROM test_01
GROUP BY deviceId
),
t2 AS(
SELECT deviceId,
		deviceDistrict,
		CONCAT(FLOOR(deviceDistrict/10)*10, '-', (FLOOR(deviceDistrict/10)+1)*10) AS deviceDistrictSection
FROM test_02
),
t3 AS (
	SELECT t2.deviceDistrictSection AS deviceDistrictSection,
			SUM(t1.alarmCount) AS alarmCount
	FROM t1 LEFT JOIN t2 
	ON t1.deviceId = t2.deviceId
	GROUP BY deviceDistrictSection
	ORDER BY deviceDistrictSection
)
SELECT * FROM t3;

-- 第二種方案的函數測試
SELECT FLOOR(210015/10)*10 AS x;  -- 210015
SELECT CEIL(210015/10)*10 AS y;  -- 210020
SELECT CONCAT(FLOOR(210015/10)*10, '-', CEIL(210015/10)*10);  -- 210010-210020
SELECT CONCAT(FLOOR(210020/10)*10, '-', CEIL(210020/10)*10);  -- 210020-210020
SELECT CONCAT(FLOOR(210020/10)*10, '-', (FLOOR(210020/10)+1)*10);  -- 210020-210030

拼接函數concat/concat_ws/group_concat的區別說明

1. concat
   • 將多個字元串連接成一個字元串
   • concat(str1, str2,...)
   • 返回結果為連接參數產生的字元串，如果有任何一個參數為null，則返回值為null
2. concat_ws
   • 和concat()一樣，將多個字元串連接成一個字元串，但是可以一次性指定分隔符
   • 第一個參數指定分隔符，concat_ws(separator, str1, str2, ...)
   • 返回結果為連接參數產生的字元串。需要註意的是分隔符不能為null，如果為null，則返回結果為null
3. group_concat
   • 將group by產生的同一個分組中的值連接起來，返回一個字元串結果
   • group_concat( [distinct] 要連接的欄位 [order by 排序欄位 asc/desc ] [separator '分隔符'] )
   • 說明：通過使用distinct可以排除重覆值；如果希望對結果中的值進行排序，可以使用order by子句；separator是一個字元串值，預設為一個逗號

拼接函數floor/ceil/round的區別說明

1. floor
   • 在英文中，是地面，地板的意思，有下麵的意思；所以此函數是向下取整，它返回的是小於或等於函數參數，並且與之最接近的整數
   • 向下取整的時候，正數，則取其整數部位，抹除小數部位
   • 負數，則取其整數加一
   • 整數，則不變
2. ceil
   • 在英文中，是天花板的意思，有向上的意思；所以此函數是向上取整，它返回的是大於或等於函數參數，並且與之最接近的整數
   • 向上取整的時候，正數，則直接將當前整數加一
   • 負數，則將整數後面的數據抹除
   • 整數，則不變
3. round
   • 在英文中是有大約，環繞，在某某四周，附近的意思，所以，可以取其大約的意思，在函數中是四捨五入
   • 四捨五入的時候，正數，小數位大於5，則整數位加一，小數位小於5，則整數位不變，抹除小數位
   • 負數，小數位小於5，則整數位不變，抹除小數位，小數位大於5，則整數位加一
   • 整數，則不變

MySQL中保留兩位小數

1. round(x,d) 四捨五入保留小數
   • round(x)其實就是round(x,0)，也就是預設d為0，預設不保留小數，d為保留幾位小數
   • d可以是負數，這時是指定小數點左邊的d位整數位為0，同時小數位均為0，例如：round(114.6, -1) -> 110
2. truncate(x,d) 函數返回被捨去至小數點後d位的數字x，和round函數類似，但是沒有四捨五入
3. format(x,d) 強制保留d位小數，整數部分超過三位的時候以逗號分割，並且返回的結果是string類型的
4. convert(value,type) 類型轉換，相當於截取，例如：
   • convert(100.3465, decimal(10,2)) -> 100.35
   • convert(100, decimal(10,2)) -> 100
   • convert(100.4, decimal(10,2)) -> 100.4

Hive中保留兩位小數

1. round(column_name,2) 四捨五入截取 這種方法慎用，有時候結果不是你想要的
2. regexp_extract(column_name,'([0-9]*.[0-9][0-9])',1) 正則匹配截取，不做四捨五入，只是單純的當作字元串截取
3. cast(column_name as decimal(10,2)) cast函數截取 推薦使用

六、統計所有告警設備和所有活躍告警設備（連續三天都有告警的設備）的總數，以及平均監測值

活躍告警設備是指連續三天都有告警的設備

連續N天登錄等類似題目的解題思路

1. 日期減去一列數字得到的日期相等
2. 後一個日期減去前一個日期的差值相等

原始數據格式

統計之後格式

工作思路

1. 首先使用group by去除重覆日期的重覆數據，用max函數取最大值
2. 然後使用group by去除重覆設備數，分別查詢設備總數和總平均值，再用左連接將查詢結果拼接，保存結果查詢
3. 接著處理統計活躍告警設備，先用row_number函數查詢分組編號，再使用date_sub函數用告警日期減去分組編號，得出一組臨時告警日期用於判定是否是活躍告警設備
4. 如果有連續相同日期說明是活躍告警設備，所以接著使用count函數和having條件統計過濾有大於等於三天的連續相同日期的設備與告警日期，註意同時要計算均值
5. 左後統計活躍告警設備總數和平均值，並和第二步中的結果union all即可

工作語句

WITH 
-- 首先去除重覆日期的重覆數據，這裡取最大值
t1 AS(
SELECT deviceId,
		alarmDate,
		MAX(alarmValueAvgDaily) AS alarmValueAvgDaily
FROM test_03
GROUP BY deviceId, alarmDate
),
-- 去除重覆設備數
t2 AS(
SELECT *
FROM t1
GROUP BY deviceId
),
-- 查詢設備總數
t3 AS(
SELECT '告警設備總數與均值' AS type,
		COUNT(deviceId) AS allDeviceCount
FROM t2
),
-- 查詢總均值
t4 AS(
SELECT ROUND(AVG(alarmValueAvgDaily)) AS alarmValueAvgAll
FROM t1
),
-- 查詢分組後的排序編號
t5 AS(
SELECT *,
		ROW_NUMBER() OVER(PARTITION BY deviceId ORDER BY alarmDate) AS alarmDateRank
FROM t1
),
-- 查詢告警日期減去分組後排序編號之後的日期，如果有連續相同的說明是連續的天數
t6 AS(
SELECT *,
		DATE_SUB(alarmDate, INTERVAL alarmDateRank DAY) AS alarmDateSub
FROM t5
),
-- 查詢連續天數大於3天的設備，以及這些活躍設備的平均值
t7 AS(
SELECT deviceId,
		ROUND(AVG(alarmValueAvgDaily))  AS alarmValueAvgActive,
		alarmDateSub,
		COUNT(*) AS  alarmDateSubCount
FROM t6
GROUP BY deviceId, alarmDateSub
HAVING alarmDateSubCount>=3  
),
t8 AS(
SELECT '活躍告警設備總數與均值' AS type,
		COUNT(deviceId) AS allDeviceCount,
		ROUND(AVG(alarmValueAvgActive)) AS alarmValueAvgActiveAll
FROM t7
)
-- 統計完成所有告警設備以及平均監測值
SELECT * FROM t3 LEFT JOIN t4 ON t4.alarmValueAvgAll IS NOT NULL
UNION ALL
-- 統計完成活躍告警設備以及平均監測值
SELECT * FROM t8;

合併操作符union和union all之間的區別

1. 相同之處
   • 都是用於合併兩個或多個select語句的結果組合成單個結果集
   • 操作符內部的每個select語句必須擁有相同數量的，列也必須擁有相似的數據類型，同時每個select語句中的列的順序必須相同
2. 不同之處
   • 對重覆結果的處理：union在進行表連接後會篩選掉重覆的記錄，union all不會去除重覆記錄
   • 對排序的處理：union將會按照欄位的順序進行排序，union all只是簡單的將兩個結果合併後就返回
   • 從效率上說，union all要比 union快很多，所以，如果可以確認合併的兩個結果集中不包含重覆數據且不需要排序時的話，那麼就使用union all

Hive和MySQL中的日期函數

1. MySQL Date 函數、MySQL 日期函數
2. 【hive 日期函數】Hive常用日期函數整理
3. 後期切記整理鏈接資料，若忘記請讀者提醒！！！感謝！！！

七、統計2022年1月8日下午16點-17點，每個介面調用量top10的ip地址

原始數據格式

統計之後格式

此題作為開放題供大家查閱，後面有空再繼續寫

附錄資料

Hive和MySQL中部分函數的區別

1. date_format()
   • Hive date_format(date date / timestamp time / string 'xxxx-xx-xx', format 'yyyy-MM-dd')，只能識別用-連接的日期字元串
   • MySQL date_format(date, format)，具體的format規則請查詢參考資料
2. date_sub()
   • Hive date_sub(date date / timestamp time, int days)
   • MySQL date_sub(date, interval 時間間隔 type)，具體的type規則請查詢參考資料

Hive和MySQL常用日期函數

1. date_add() 嚮日期添加指定的時間間隔
2. date_sub() 從日期減去指定的時間間隔
3. datediff() 返回兩個日期之間的天數

Hive中order by/distribute by/sort by/group by/partition by之間的區別說明

1. order by
   • order by會對數據進行全局排序，和oracle、mysql等資料庫中的order by效果一樣
   • 需要註意的是，hive執行過程中它只在一個reduce中進行，所以數據量特別大的時候效率非常低
   • group by分組之後是會組內聚合的，而distribute by和partition by僅僅是分組了，並未有聚合操作
2. distribute by
   • distribute by是控制在map端如何拆分數據給reduce端的
   • hive會根據distribute by後面列，對應reduce的個數進行分發，預設是採用hash演算法
3. sort by
   • sort by為每個reduce產生一個排序文件
   • 在有些情況下，你需要控制某個特定行應該到哪個reducer，這通常是為了進行後續的聚集操作distribute by剛好可以做這件事
   • 因此，distribute by經常和sort by配合使用
4. group by
   • 和distribute by類似 都是按key值劃分數據 都使用reduce操作
   • 唯一不同的是，distribute by只是單純的分散數據，distribute by col 是按照col列把數據分散到不同的reduce
   • 而group by把相同key值的數據聚集到一起，後續必須是聚合操作
5. cluster by
   • 按列分桶建表使用
   • distribute by 和 sort by 合用就相當於cluster by，但是cluster by不能指定排序為asc(升序)或desc(倒序)的規則，只能是升序排列
6. partition by
   • 按所分區名分區建表使用
   • 通常查詢時會對整個資料庫查詢，而這帶來了大量的開銷，因此引入了partition的概念
   • 在建表的時候通過設置partition的欄位，會根據該欄位對數據分區存放，更具體的說是存放在不同的文件夾
   • 這樣通過指定設置partition的欄位條件查詢時可以減少大量的開銷
   • 區內排序用order by

MySQL多表查詢時如何將NULL置為0

使用IFNULL("欄位", 0)函數即可

Hive中如何處理NULL值和空字元串

1. Hive表中預設將NULL存為\N，可查看表的源文件（hadoop fs -cat或者hadoop fs -text），文件中存儲大量\N，這樣造成浪費大量空間
2. 但Hive的NULL有時候是必須的
   • Hive中insert語句必須列數匹配，不支持不寫入，沒有值的列必須使用NULL占位
   • Hive表的數據文件中按分隔符區分各個列，空列會保存NULL（\n）來保留列位置，
     但外部表載入某些數據時如果列不夠，如表13列，文件數據只有2列，則在表查詢時表中的末尾剩餘列無數據對應，自動顯示為NULL
3. 所以，NULL轉化為空字元串，可以節省磁碟空間
   • 建表時直接指定

   # 第一種方式
   ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'  
   WITH SERDEPROPERTIES ('serialization.null.format' = '')  
   # 第二種方式
    ROW FORMAT DELIMITED NULL DEFINED AS ''  
   

   • 修改已存在的表

   ALTER TABLE hive_tb SET SERDEPROPERTIES('serialization.null.format' = '');
   

4. 使用函數處理NULL值
   • NVL(expr1,expr2) 如果第一個參數為NULL那麼顯示第二個參數的值，如果第一個參數的值不為NULL，則顯示第一個參數本來的值
   • Coalesce(expr1, expr2, expr3….. exprn) 返回表達式中第一個非空表達式，如果所有自變數均為NULL，則 COALESCE 返回NULL

   SELECT COALESCE(NULL,null,3,4,5); 　　-- 結果為：3
   SELECT COALESCE(NULL,null,'',3,4,5);   -- 結果為：''
   SELECT COALESCE(NULL,null,null,NULL);  -- 結果為：null
   

我是 fx67ll.com，如果您發現本文有什麼錯誤，歡迎在評論區討論指正，感謝您的閱讀！
如果您喜歡這篇文章，歡迎訪問我的 本文github倉庫地址，為我點一顆Star，Thanks~