記錄一些工作中有意思的統(tǒng)計(jì)指標(biāo)，做過(guò)一些簡(jiǎn)化方便大家閱讀，記錄如有錯(cuò)誤，歡迎在評(píng)論區(qū)提問(wèn)討論~

問(wèn)題類(lèi)型

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

一、統(tǒng)計(jì)每個(gè)設(shè)備的累計(jì)告警次數(shù)

原始數(shù)據(jù)格式

統(tǒng)計(jì)之后格式

工作思路

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

工作語(yǔ)句

1. 第一種方案，使用substr截取字符串函數(shù)

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. 第二種方案，使用日期格式化函數(shù)，使用date_format函數(shù)的字符串必須滿足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;

開(kāi)窗函數(shù)中的界限說(shuō)明

• unbounded：無(wú)界限
• preceding：從分區(qū)第一行頭開(kāi)始，則為 unbounded N為：相對(duì)當(dāng)前行向后的偏移量
• following ：與preceding相反,到該分區(qū)結(jié)束，則為 unbounded N為：相對(duì)當(dāng)前行向后的偏移量
• current row：顧名思義，當(dāng)前行，偏移量為0

二、統(tǒng)計(jì)環(huán)境設(shè)備每天的總污染告警次數(shù)，并輸出每個(gè)設(shè)備告警次數(shù)排名前三的日期

原始數(shù)據(jù)格式

統(tǒng)計(jì)之后格式

工作思路

統(tǒng)計(jì)環(huán)境設(shè)備的總污染告警次數(shù)

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

輸出每個(gè)設(shè)備告警次數(shù)排名前三的日期

1. 使用窗口函數(shù)ROW_NUMBER() OVER()進(jìn)行分組排序即可，MySQL 替換 ROW_NUMBER() OVER (PARTITION ……) 函數(shù)
2. 多個(gè)子句查詢可以使用視圖和WITH語(yǔ)句

工作語(yǔ)句

統(tǒng)計(jì)環(huán)境設(shè)備的總污染告警次數(shù)

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;

輸出每個(gè)設(shè)備告警次數(shù)排名前三的日期

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語(yǔ)句優(yōu)化一下
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(*)的區(qū)別

1. 從執(zhí)行結(jié)果來(lái)說(shuō)
   • COUNT(1)和COUNT(*)之間沒(méi)有區(qū)別，因?yàn)?code>COUNT(*)和COUNT(1)都不會(huì)去過(guò)濾空值
   • 但COUNT(列名)就有區(qū)別了，因?yàn)?code>COUNT(列名)會(huì)去過(guò)濾空值
2. 從執(zhí)行效率來(lái)說(shuō)
   • 他們之間根據(jù)不同情況會(huì)有些許區(qū)別，MySQL會(huì)對(duì)COUNT(*)做優(yōu)化
   • 如果列為主鍵，COUNT(列名)效率優(yōu)于COUNT(1)
   • 如果列不為主鍵，COUNT(1)效率優(yōu)于COUNT(列名)
   • 如果表中存在主鍵，COUNT(主鍵列名)效率最優(yōu)
   • 如果表中只有一列，則COUNT(*)效率最優(yōu)
   • 如果表有多列，且不存在主鍵，則COUNT(1)效率優(yōu)于COUNT(*)

三、統(tǒng)計(jì)每個(gè)月的總告警次數(shù)，總告警設(shè)備數(shù)，以及能夠連續(xù)七天數(shù)值正常設(shè)備數(shù)量

留給你思考

四、統(tǒng)計(jì)出2022年1月首次出現(xiàn)告警的設(shè)備數(shù)量

原始數(shù)據(jù)格式

統(tǒng)計(jì)之后格式

工作思路

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

工作語(yǔ)句

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';

五、根據(jù)設(shè)備地區(qū)編號(hào)段對(duì)告警次數(shù)進(jìn)行排序

有一個(gè)5000萬(wàn)的設(shè)備信息表，一個(gè)2億記錄的告警記錄表

原始數(shù)據(jù)格式

1. 設(shè)備信息表

2. 告警記錄表

統(tǒng)計(jì)之后格式

工作思路

1. 先根據(jù)設(shè)備ID分組count統(tǒng)計(jì)報(bào)警次數(shù)
2. 再使用casewhen條件語(yǔ)句，或者使用concat/floor/ceil函數(shù)動(dòng)態(tài)劃分，根據(jù)分段統(tǒng)計(jì)不同設(shè)備位于什么地區(qū)號(hào)段
3. 最后連接查詢，并根據(jù)地區(qū)號(hào)段，使用sum函數(shù)統(tǒng)計(jì)總告警次數(shù)即可

工作語(yǔ)句

-- 第一種方案
WITH t1 AS(
SELECT deviceId,
        COUNT(alarmTime) AS alarmCount
FROM test_01
GROUP BY deviceId
),
t2 AS(
SELECT deviceId,
        deviceDistrict,
        -- 如果地區(qū)編號(hào)是字符串可以先轉(zhuǎn)換再比較，不然會(huì)觸發(fā)隱式轉(zhuǎn)換，導(dǎo)致全表掃描無(wú)法使用索引
        -- 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;

-- 第二種方案的函數(shù)測(cè)試
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

拼接函數(shù)concat/concat_ws/group_concat的區(qū)別說(shuō)明

1. concat
   • 將多個(gè)字符串連接成一個(gè)字符串
   • concat(str1, str2,...)
   • 返回結(jié)果為連接參數(shù)產(chǎn)生的字符串，如果有任何一個(gè)參數(shù)為null，則返回值為null
2. concat_ws
   • 和concat()一樣，將多個(gè)字符串連接成一個(gè)字符串，但是可以一次性指定分隔符
   • 第一個(gè)參數(shù)指定分隔符，concat_ws(separator, str1, str2, ...)
   • 返回結(jié)果為連接參數(shù)產(chǎn)生的字符串。需要注意的是分隔符不能為null，如果為null，則返回結(jié)果為null
3. group_concat
   • 將group by產(chǎn)生的同一個(gè)分組中的值連接起來(lái)，返回一個(gè)字符串結(jié)果
   • group_concat( [distinct] 要連接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'] )
   • 說(shuō)明：通過(guò)使用distinct可以排除重復(fù)值；如果希望對(duì)結(jié)果中的值進(jìn)行排序，可以使用order by子句；separator是一個(gè)字符串值，缺省為一個(gè)逗號(hào)

拼接函數(shù)floor/ceil/round的區(qū)別說(shuō)明

1. floor
   • 在英文中，是地面，地板的意思，有下面的意思；所以此函數(shù)是向下取整，它返回的是小于或等于函數(shù)參數(shù)，并且與之最接近的整數(shù)
   • 向下取整的時(shí)候，正數(shù)，則取其整數(shù)部位，抹除小數(shù)部位
   • 負(fù)數(shù)，則取其整數(shù)加一
   • 整數(shù)，則不變
2. ceil
   • 在英文中，是天花板的意思，有向上的意思；所以此函數(shù)是向上取整，它返回的是大于或等于函數(shù)參數(shù)，并且與之最接近的整數(shù)
   • 向上取整的時(shí)候，正數(shù)，則直接將當(dāng)前整數(shù)加一
   • 負(fù)數(shù)，則將整數(shù)后面的數(shù)據(jù)抹除
   • 整數(shù)，則不變
3. round
   • 在英文中是有大約，環(huán)繞，在某某四周，附近的意思，所以，可以取其大約的意思，在函數(shù)中是四舍五入
   • 四舍五入的時(shí)候，正數(shù)，小數(shù)位大于5，則整數(shù)位加一，小數(shù)位小于5，則整數(shù)位不變，抹除小數(shù)位
   • 負(fù)數(shù)，小數(shù)位小于5，則整數(shù)位不變，抹除小數(shù)位，小數(shù)位大于5，則整數(shù)位加一
   • 整數(shù)，則不變

MySQL中保留兩位小數(shù)

1. round(x,d) 四舍五入保留小數(shù)
   • round(x)其實(shí)就是round(x,0)，也就是默認(rèn)d為0，默認(rèn)不保留小數(shù)，d為保留幾位小數(shù)
   • d可以是負(fù)數(shù)，這時(shí)是指定小數(shù)點(diǎn)左邊的d位整數(shù)位為0，同時(shí)小數(shù)位均為0，例如：round(114.6, -1) -> 110
2. truncate(x,d) 函數(shù)返回被舍去至小數(shù)點(diǎn)后d位的數(shù)字x，和round函數(shù)類(lèi)似，但是沒(méi)有四舍五入
3. format(x,d) 強(qiáng)制保留d位小數(shù)，整數(shù)部分超過(guò)三位的時(shí)候以逗號(hào)分割，并且返回的結(jié)果是string類(lèi)型的
4. convert(value,type) 類(lèi)型轉(zhuǎn)換，相當(dāng)于截取，例如：
   • convert(100.3465, decimal(10,2)) -> 100.35
   • convert(100, decimal(10,2)) -> 100
   • convert(100.4, decimal(10,2)) -> 100.4

Hive中保留兩位小數(shù)

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

六、統(tǒng)計(jì)所有告警設(shè)備和所有活躍告警設(shè)備（連續(xù)三天都有告警的設(shè)備）的總數(shù)，以及平均監(jiān)測(cè)值

活躍告警設(shè)備是指連續(xù)三天都有告警的設(shè)備

連續(xù)N天登錄等類(lèi)似題目的解題思路

1. 日期減去一列數(shù)字得到的日期相等
2. 后一個(gè)日期減去前一個(gè)日期的差值相等

原始數(shù)據(jù)格式

統(tǒng)計(jì)之后格式

工作思路

1. 首先使用group by去除重復(fù)日期的重復(fù)數(shù)據(jù)，用max函數(shù)取最大值
2. 然后使用group by去除重復(fù)設(shè)備數(shù)，分別查詢?cè)O(shè)備總數(shù)和總平均值，再用左連接將查詢結(jié)果拼接，保存結(jié)果查詢
3. 接著處理統(tǒng)計(jì)活躍告警設(shè)備，先用row_number函數(shù)查詢分組編號(hào)，再使用date_sub函數(shù)用告警日期減去分組編號(hào)，得出一組臨時(shí)告警日期用于判定是否是活躍告警設(shè)備
4. 如果有連續(xù)相同日期說(shuō)明是活躍告警設(shè)備，所以接著使用count函數(shù)和having條件統(tǒng)計(jì)過(guò)濾有大于等于三天的連續(xù)相同日期的設(shè)備與告警日期，注意同時(shí)要計(jì)算均值
5. 左后統(tǒng)計(jì)活躍告警設(shè)備總數(shù)和平均值，并和第二步中的結(jié)果union all即可

工作語(yǔ)句

WITH 
-- 首先去除重復(fù)日期的重復(fù)數(shù)據(jù)，這里取最大值
t1 AS(
SELECT deviceId,
        alarmDate,
        MAX(alarmValueAvgDaily) AS alarmValueAvgDaily
FROM test_03
GROUP BY deviceId, alarmDate
),
-- 去除重復(fù)設(shè)備數(shù)
t2 AS(
SELECT *
FROM t1
GROUP BY deviceId
),
-- 查詢?cè)O(shè)備總數(shù)
t3 AS(
SELECT '告警設(shè)備總數(shù)與均值' AS type,
        COUNT(deviceId) AS allDeviceCount
FROM t2
),
-- 查詢總均值
t4 AS(
SELECT ROUND(AVG(alarmValueAvgDaily)) AS alarmValueAvgAll
FROM t1
),
-- 查詢分組后的排序編號(hào)
t5 AS(
SELECT *,
        ROW_NUMBER() OVER(PARTITION BY deviceId ORDER BY alarmDate) AS alarmDateRank
FROM t1
),
-- 查詢告警日期減去分組后排序編號(hào)之后的日期，如果有連續(xù)相同的說(shuō)明是連續(xù)的天數(shù)
t6 AS(
SELECT *,
        DATE_SUB(alarmDate, INTERVAL alarmDateRank DAY) AS alarmDateSub
FROM t5
),
-- 查詢連續(xù)天數(shù)大于3天的設(shè)備，以及這些活躍設(shè)備的平均值
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 '活躍告警設(shè)備總數(shù)與均值' AS type,
        COUNT(deviceId) AS allDeviceCount,
        ROUND(AVG(alarmValueAvgActive)) AS alarmValueAvgActiveAll
FROM t7
)
-- 統(tǒng)計(jì)完成所有告警設(shè)備以及平均監(jiān)測(cè)值
SELECT * FROM t3 LEFT JOIN t4 ON t4.alarmValueAvgAll IS NOT NULL
UNION ALL
-- 統(tǒng)計(jì)完成活躍告警設(shè)備以及平均監(jiān)測(cè)值
SELECT * FROM t8;

合并操作符union和union all之間的區(qū)別

1. 相同之處
   • 都是用于合并兩個(gè)或多個(gè)select語(yǔ)句的結(jié)果組合成單個(gè)結(jié)果集
   • 操作符內(nèi)部的每個(gè)select語(yǔ)句必須擁有相同數(shù)量的，列也必須擁有相似的數(shù)據(jù)類(lèi)型，同時(shí)每個(gè)select語(yǔ)句中的列的順序必須相同
2. 不同之處
   • 對(duì)重復(fù)結(jié)果的處理：union在進(jìn)行表連接后會(huì)篩選掉重復(fù)的記錄，union all不會(huì)去除重復(fù)記錄
   • 對(duì)排序的處理：union將會(huì)按照字段的順序進(jìn)行排序，union all只是簡(jiǎn)單的將兩個(gè)結(jié)果合并后就返回
   • 從效率上說(shuō)，union all要比 union快很多，所以，如果可以確認(rèn)合并的兩個(gè)結(jié)果集中不包含重復(fù)數(shù)據(jù)且不需要排序時(shí)的話，那么就使用union all

Hive和MySQL中的日期函數(shù)

1. MySQL Date 函數(shù)、MySQL 日期函數(shù)
2. 【hive 日期函數(shù)】Hive常用日期函數(shù)整理
3. 后期切記整理鏈接資料，若忘記請(qǐng)讀者提醒?。。「兄x?。?！

七、統(tǒng)計(jì)2022年1月8日下午16點(diǎn)-17點(diǎn)，每個(gè)接口調(diào)用量top10的ip地址

原始數(shù)據(jù)格式

統(tǒng)計(jì)之后格式

此題作為開(kāi)放題供大家查閱，后面有空再繼續(xù)寫(xiě)

附錄資料

Hive和MySQL中部分函數(shù)的區(qū)別

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

Hive和MySQL常用日期函數(shù)

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

Hive中order by/distribute by/sort by/group by/partition by之間的區(qū)別說(shuō)明

1. order by
   • order by會(huì)對(duì)數(shù)據(jù)進(jìn)行全局排序，和oracle、mysql等數(shù)據(jù)庫(kù)中的order by效果一樣
   • 需要注意的是，hive執(zhí)行過(guò)程中它只在一個(gè)reduce中進(jìn)行，所以數(shù)據(jù)量特別大的時(shí)候效率非常低
   • group by分組之后是會(huì)組內(nèi)聚合的，而distribute by和partition by僅僅是分組了，并未有聚合操作
2. distribute by
   • distribute by是控制在map端如何拆分?jǐn)?shù)據(jù)給reduce端的
   • hive會(huì)根據(jù)distribute by后面列，對(duì)應(yīng)reduce的個(gè)數(shù)進(jìn)行分發(fā)，默認(rèn)是采用hash算法
3. sort by
   • sort by為每個(gè)reduce產(chǎn)生一個(gè)排序文件
   • 在有些情況下，你需要控制某個(gè)特定行應(yīng)該到哪個(gè)reducer，這通常是為了進(jìn)行后續(xù)的聚集操作distribute by剛好可以做這件事
   • 因此，distribute by經(jīng)常和sort by配合使用
4. group by
   • 和distribute by類(lèi)似 都是按key值劃分?jǐn)?shù)據(jù) 都使用reduce操作
   • 唯一不同的是，distribute by只是單純的分散數(shù)據(jù)，distribute by col 是按照col列把數(shù)據(jù)分散到不同的reduce
   • 而group by把相同key值的數(shù)據(jù)聚集到一起，后續(xù)必須是聚合操作
5. cluster by
   • 按列分桶建表使用
   • distribute by 和 sort by 合用就相當(dāng)于cluster by，但是cluster by不能指定排序?yàn)?code>asc(升序)或desc(倒序)的規(guī)則，只能是升序排列
6. partition by
   • 按所分區(qū)名分區(qū)建表使用
   • 通常查詢時(shí)會(huì)對(duì)整個(gè)數(shù)據(jù)庫(kù)查詢，而這帶來(lái)了大量的開(kāi)銷(xiāo)，因此引入了partition的概念
   • 在建表的時(shí)候通過(guò)設(shè)置partition的字段，會(huì)根據(jù)該字段對(duì)數(shù)據(jù)分區(qū)存放，更具體的說(shuō)是存放在不同的文件夾
   • 這樣通過(guò)指定設(shè)置partition的字段條件查詢時(shí)可以減少大量的開(kāi)銷(xiāo)
   • 區(qū)內(nèi)排序用order by

MySQL多表查詢時(shí)如何將NULL置為0

使用IFNULL("字段", 0)函數(shù)即可

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

1. Hive表中默認(rèn)將NULL存為\N，可查看表的源文件（hadoop fs -cat或者hadoop fs -text），文件中存儲(chǔ)大量\N，這樣造成浪費(fèi)大量空間
2. 但Hive的NULL有時(shí)候是必須的
   • Hive中insert語(yǔ)句必須列數(shù)匹配，不支持不寫(xiě)入，沒(méi)有值的列必須使用NULL占位
   • Hive表的數(shù)據(jù)文件中按分隔符區(qū)分各個(gè)列，空列會(huì)保存NULL（\n）來(lái)保留列位置，
     但外部表加載某些數(shù)據(jù)時(shí)如果列不夠，如表13列，文件數(shù)據(jù)只有2列，則在表查詢時(shí)表中的末尾剩余列無(wú)數(shù)據(jù)對(duì)應(yīng)，自動(dòng)顯示為NULL
3. 所以，NULL轉(zhuǎn)化為空字符串，可以節(jié)省磁盤(pán)空間
   • 建表時(shí)直接指定

   # 第一種方式
   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. 使用函數(shù)處理NULL值
   • NVL(expr1,expr2) 如果第一個(gè)參數(shù)為NULL那么顯示第二個(gè)參數(shù)的值，如果第一個(gè)參數(shù)的值不為NULL，則顯示第一個(gè)參數(shù)本來(lái)的值
   • Coalesce(expr1, expr2, expr3….. exprn) 返回表達(dá)式中第一個(gè)非空表達(dá)式，如果所有自變量均為NULL，則 COALESCE 返回NULL

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

我是 fx67ll.com，如果您發(fā)現(xiàn)本文有什么錯(cuò)誤，歡迎在評(píng)論區(qū)討論指正，感謝您的閱讀！
如果您喜歡這篇文章，歡迎訪問(wèn)我的 本文github倉(cāng)庫(kù)地址，為我點(diǎn)一顆Star，Thanks~ :)
轉(zhuǎn)發(fā)請(qǐng)注明參考文章地址，非常感謝?。?！