关于sql: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;

   开窗函数中的界线阐明

3. unbounded：无界线
4. preceding：从分区第一行头开始，则为 unbounded N 为：绝对以后行向后的偏移量
5. following：与 preceding 相同, 到该分区完结，则为 unbounded N 为：绝对以后行向后的偏移量
6. current row：顾名思义，以后行，偏移量为 0

二、统计环境设备每天的总净化告警次数，并输入每个设施告警次数排名前三的日期

原始数据格式

统计之后格局

工作思路

统计环境设备的总净化告警次数

1. 因为有设施可能会有同一时间的告警记录，所以须要按告警工夫去重后再统计
2. 如果应用 distinct 去重，如果表数据过大，且设施 ID 差异化很大，那么会有性能压力
3. 所以应用 group by 子查问代替

4. mysql 中的 date_format 格式化须要这样写：DATE_FORMAT(alarmTime, '%Y-%c-%d %T')

   输入每个设施告警次数排名前三的日期

5. 应用窗口函数 ROW_NUMBER() OVER() 进行分组排序即可，MySQL 替换 ROW_NUMBER() OVER (PARTITION ……) 函数
6. 多个子句查问能够应用视图和 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 的区别阐明

4. floor

   • 在英文中，是高空，地板的意思，有上面的意思；所以此函数是向下取整，它返回的是小于或等于函数参数，并且与之最靠近的整数
   • 向下取整的时候，负数，则取其整数部位，抹除小数部位
   • 正数，则取其整数加一
   • 整数，则不变

5. ceil

   • 在英文中，是天花板的意思，有向上的意思；所以此函数是向上取整，它返回的是大于或等于函数参数，并且与之最靠近的整数
   • 向上取整的时候，负数，则间接将以后整数加一
   • 正数，则将整数前面的数据抹除
   • 整数，则不变

6. round

   • 在英文中是有大概，盘绕，在某某周围，左近的意思，所以，能够取其大概的意思，在函数中是四舍五入
   • 四舍五入的时候，负数，小数位大于 5，则整数位加一，小数位小于 5，则整数位不变，抹除小数位
   • 正数，小数位小于 5，则整数位不变，抹除小数位，小数位大于 5，则整数位加一
   • 整数，则不变

   MySQL 中保留两位小数

7. round(x,d) 四舍五入保留小数

   • round(x)其实就是round(x,0)，也就是默认 d 为 0，默认不保留小数，d 为保留几位小数
   • d 能够是正数，这时是指定小数点右边的 d 位整数位为 0，同时小数位均为 0，例如：round(114.6, -1) -> 110
8. truncate(x,d) 函数返回被舍去至小数点后 d 位的数字 x，和 round 函数相似，然而没有四舍五入
9. format(x,d) 强制保留 d 位小数，整数局部超过三位的时候以逗号宰割，并且返回的后果是 string 类型的

10. 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 中保留两位小数

11. round(column_name,2) 四舍五入截取 这种办法慎用，有时候后果不是你想要的
12. regexp_extract(column_name,'([0-9]*.[0-9][0-9])',1) 正则匹配截取，不做四舍五入，只是单纯的当作字符串截取
13. 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 中的日期函数

3. MySQL Date 函数、MySQL 日期函数
4. 【hive 日期函数】Hive 罕用日期函数整顿
5. 前期切记整顿链接材料，若遗记请读者揭示！！！感激！！！

七、统计 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~ :)
转发请注明参考文章地址，非常感谢！！！