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~ ??
轉發請注明參考文章地址，非常感謝！！！

標籤：其他

上一篇：Mysql存盤引擎

下一篇：Hive 在作業中的調優總結