HiveSQL 工作实战总结

记录一些工作中有意思的统计指标，做过一些简化方便大家阅读，记录如有错误，欢迎在评论区提问讨论~
问题类型

• 连续问题
  
  
  
  • 两种思路
    
  • 第一种：日期减去一列数字得出日期相同，主要是通过row_number窗口函数
    
  • 第二种：后一个日期减去前一个日期差值相等，用的较少，可以用lag/lead窗口函数解决
    
  
  
• 分组问题
  
  
  
  • 主要使用lag(col,1,0)分组将每行移到下一行，再按既定规则分组排序即可
    
  • 后面抽空试一下
    
  
  
• 间隔连续问题，比如每断一天也算连续
  
  
  
  • 两种思路：
    
  • 第一种：连续使用两次连续问题的求法即可，差了几次可以连续row_number几次，这种无限套娃不推荐使用
    
  • 第二种：连续差值小于要求数即可，比如断一天也可，只要每行间隔小于2天即可
    
  
  
• 打折日期交叉问题，两段活动期重复日期去除
  
• 同时在线问题
  

一、统计每个设备的累计告警次数

原始数据格式

deviceIdalarmDatealarmCount设备ID告警日期告警次数u012022/1/85u022022/1/87u032022/1/83u012022/1/122u022022/1/121u012022/1/149.........统计之后格式

设备ID告警月份告警次数小计告警次数累计u012022-021111u022022-031223............工作思路

• 先根据设备ID和告警日期分组
  
• 按月份统计可以用substr函数或者日期格式化函数
  
• 再统计即可得出小计告警次数
  
• 接着使用聚合窗口函数计算累计告警次数
  

工作语句

• 第一种方案，使用substr截取字符串函数
  

1. SELECT *,
2.            SUM(sumPart) OVER (PARTITION BY deviceId ORDER BY alarmMonth) AS sumAll
3. FROM
4. (SELECT deviceId,
5.            SUBSTR(alarmDate,1,7) AS alarmMonth,
6.            SUM(alarmCount) AS sumPart
7. FROM test_00
8. GROUP BY deviceId,
9.                  alarmMonth) t;

复制代码

• 第二种方案，使用日期格式化函数，使用date_format函数的字符串必须满足yyyy-MM-dd格式，所以必须先用regexp_replace替换/为-
  

1. SELECT *,
2.            SUM(sumPart) OVER (PARTITION BY deviceId ORDER BY alarmMonth) AS sumAll
3. FROM
4. (SELECT deviceId,
5.             DATE_FORMAT(REGEXP_REPLACE(alarmDate,'/','-'), 'yyyy-MM') AS alarmMonth,
6.             SUM(alarmCount) AS sumPart
7. FROM test_00
8. GROUP BY deviceId,
9.                  alarmMonth) t;

复制代码

开窗函数中的界限说明

• unbounded：无界限
  
• preceding：从分区第一行头开始，则为 unbounded N为：相对当前行向后的偏移量
  
• following ：与preceding相反,到该分区结束，则为 unbounded N为：相对当前行向后的偏移量
  
• current row：顾名思义，当前行，偏移量为0
  

二、统计环境设备每天的总污染告警次数，并输出每个设备告警次数排名前三的日期

原始数据格式

deviceIdalarmTime设备ID告警时间u012022/1/8/08/04/58u022022/1/8/12/05/38u032022/1/8/17/01/12u012022/1/12/12/04/53u022022/1/12/13/45/34u012022/1/14/02/12/51......统计之后格式

设备ID告警次数累计u013u022u031......工作思路

统计环境设备的总污染告警次数

• 由于有设备可能会有同一时间的告警记录，所以需要按告警时间去重后再统计
  
• 如果使用distinct去重，如果表数据过大，且设备ID差异化很大，那么会有性能压力
  
• 所以使用group by子查询代替
  
• mysql中的date_format格式化需要这样写：DATE_FORMAT(alarmTime, '%Y-%c-%d %T')
  

输出每个设备告警次数排名前三的日期

• 使用窗口函数ROW_NUMBER() OVER()进行分组排序即可，MySQL 替换 ROW_NUMBER() OVER (PARTITION ……) 函数
  
• 多个子句查询可以使用视图和WITH语句
  

工作语句

统计环境设备的总污染告警次数

1. SELECT deviceId,
2.                 COUNT(alarmTime) AS alarmCount
3. FROM
4. --- http://c.biancheng.net/mysql/date_format.html
5. (SELECT deviceId,
6.                 DATE_FORMAT(REGEXP_REPLACE(alarmTime,'/','-'), 'yyyy-MM-dd HH:mm:ss') AS alarmTime
7. FROM test_01
8. GROUP BY deviceId,alarmTime
9. ORDER BY alarmTime) t
10. GROUP BY deviceId;

复制代码

输出每个设备告警次数排名前三的日期

1. SELECT *
2. FROM
3. (SELECT deviceId,
4.                 alarmDate,
5.                 alarmCount,
6.                 ROW_NUMBER() OVER(PARTITION BY deviceId ORDER BY alarmCount DESC) AS alarmRank
7. FROM
8. (SELECT deviceId,
9.                 alarmDate,
10.                 COUNT(alarmDate) AS alarmCount
11. FROM
12. (SELECT deviceId,
13.                 DATE_FORMAT(alarmTime, 'yyyy-MM-dd') AS alarmDate,
14.                 DATE_FORMAT(alarmTime, 'yyyy-MM-dd HH:mm:ss') AS alarmTime
15. FROM test_01
16. GROUP BY deviceId,alarmTime
17. ORDER BY deviceId,alarmTime) t1
18. GROUP BY deviceId,alarmDate) t2) t3
19. WHERE alarmRank<=3;
21. -- 使用WITH语句优化一下
22. WITH t1 AS (
23. SELECT deviceId,
24.         DATE_FORMAT(alarmTime, 'yyyy-MM-dd') AS alarmDate,
25.         DATE_FORMAT(alarmTime, 'yyyy-MM-dd HH:mm:ss') AS alarmTime
26. FROM test_01
27. GROUP BY deviceId,alarmTime
28. ORDER BY deviceId,alarmTime),
29. t2 AS (
30.         SELECT deviceId,
31.         alarmDate,
32.         COUNT(alarmDate) AS alarmCount
33. FROM t1
34. GROUP BY deviceId,alarmDate),
35. t3 AS (
36. SELECT deviceId,
37.         alarmDate,
38.         alarmCount,
39.         ROW_NUMBER() OVER(PARTITION BY deviceId ORDER BY alarmCount DESC) AS alarmRank
40. FROM t2)
41. SELECT * FROM t3 WHERE alarmRank<=3;

复制代码

合并操作符union和union all之间的区别

• 相同之处
  
  
  
  • 都是用于合并两个或多个select语句的结果组合成单个结果集
    
  • 操作符内部的每个select语句必须拥有相同数量的，列也必须拥有相似的数据类型，同时每个select语句中的列的顺序必须相同
    
  
  
• 不同之处
  
  
  
  • 对重复结果的处理：union在进行表连接后会筛选掉重复的记录，union all不会去除重复记录
    
  • 对排序的处理：union将会按照字段的顺序进行排序，union all只是简单的将两个结果合并后就返回
    
  • 从效率上说，union all要比 union快很多，所以，如果可以确认合并的两个结果集中不包含重复数据且不需要排序时的话，那么就使用union all
    
  
  

Hive和MySQL中的日期函数

• MySQL Date 函数、MySQL 日期函数
  
• 【hive 日期函数】Hive常用日期函数整理
  
• 后期切记整理链接资料，若忘记请读者提醒！！！感谢！！！
  

七、统计2022年1月8日下午16点-17点，每个接口调用量top10的ip地址

原始数据格式

timeinterfaceip时间接口访问IP2021/1/8 15:01:28/api/user/login110.25.3.562021/1/8 15:21:12/api/device/alarm23.21.33.872021/1/8 15:51:34/api/device/record45.76.21.543......统计之后格式

接口访问IP访问次数排名/api/user/login110.25.3.56891/api/device/alarm23.21.33.871231/api/device/record45.76.21.543231............此题作为开放题供大家查阅，后面有空再继续写

附录资料

Hive和MySQL中部分函数的区别

• date_format()
  
  
  
  • Hive date_format(date date / timestamp time / string 'xxxx-xx-xx', format 'yyyy-MM-dd')，只能识别用-连接的日期字符串
    
  • MySQL date_format(date, format)，具体的format规则请查询参考资料
    
  
  
• date_sub()
  
  
  
  • Hive date_sub(date date / timestamp time, int days)
    
  • MySQL date_sub(date, interval 时间间隔 type)，具体的type规则请查询参考资料
    
  
  

Hive和MySQL常用日期函数

• date_add() 向日期添加指定的时间间隔
  
• date_sub() 从日期减去指定的时间间隔
  
• datediff() 返回两个日期之间的天数
  

Hive中order by/distribute by/sort by/group by/partition by之间的区别说明

• order by
  
  
  
  • order by会对数据进行全局排序，和oracle、mysql等数据库中的order by效果一样
    
  • 需要注意的是，hive执行过程中它只在一个reduce中进行，所以数据量特别大的时候效率非常低
    
  • group by分组之后是会组内聚合的，而distribute by和partition by仅仅是分组了，并未有聚合操作
    
  
  
• distribute by
  
  
  
  • distribute by是控制在map端如何拆分数据给reduce端的
    
  • hive会根据distribute by后面列，对应reduce的个数进行分发，默认是采用hash算法
    
  
  
• sort by
  
  
  
  • sort by为每个reduce产生一个排序文件
    
  • 在有些情况下，你需要控制某个特定行应该到哪个reducer，这通常是为了进行后续的聚集操作distribute by刚好可以做这件事
    
  • 因此，distribute by经常和sort by配合使用
    
  
  
• group by
  
  
  
  • 和distribute by类似 都是按key值划分数据 都使用reduce操作
    
  • 唯一不同的是，distribute by只是单纯的分散数据，distribute by col 是按照col列把数据分散到不同的reduce
    
  • 而group by把相同key值的数据聚集到一起，后续必须是聚合操作
    
  
  
• cluster by
  
  
  
  • 按列分桶建表使用
    
  • distribute by 和 sort by 合用就相当于cluster by，但是cluster by不能指定排序为asc(升序)或desc(倒序)的规则，只能是升序排列
    
  
  
• partition by
  
  
  
  • 按所分区名分区建表使用
    
  • 通常查询时会对整个数据库查询，而这带来了大量的开销，因此引入了partition的概念
    
  • 在建表的时候通过设置partition的字段，会根据该字段对数据分区存放，更具体的说是存放在不同的文件夹
    
  • 这样通过指定设置partition的字段条件查询时可以减少大量的开销
    
  • 区内排序用order by
    
  
  

MySQL多表查询时如何将NULL置为0

使用IFNULL("字段", 0)函数即可
Hive中如何处理NULL值和空字符串

• Hive表中默认将NULL存为\N，可查看表的源文件（hadoop fs -cat或者hadoop fs -text），文件中存储大量\N，这样造成浪费大量空间
  
• 但Hive的NULL有时候是必须的
  
  
  
  • Hive中insert语句必须列数匹配，不支持不写入，没有值的列必须使用NULL占位
    
  • Hive表的数据文件中按分隔符区分各个列，空列会保存NULL（\n）来保留列位置，
    但外部表加载某些数据时如果列不够，如表13列，文件数据只有2列，则在表查询时表中的末尾剩余列无数据对应，自动显示为NULL
    
  
  
• 所以，NULL转化为空字符串，可以节省磁盘空间
  
  
  
  • 建表时直接指定
    
  1. WITH t1 AS (
  2. SELECT *,
  3.                 DATE_FORMAT(alarmTime, 'yyyy-MM') AS alarmMonth
  4. FROM test_01
  5. ),
  6. t2 AS (
  7. SELECT deviceId,
  8.                 alarmTime,
  9.                 MIN(alarmMonth) AS firstAlarmMonth
  10. FROM t1
  11. GROUP BY deviceId
  12. )
  13. SELECT * FROM t2 WHERE firstAlarmMonth='2022-1';

  复制代码
  
  
  • 修改已存在的表
    
  1. -- 第一种方案
  2. WITH t1 AS(
  3. SELECT deviceId,
  4.                 COUNT(alarmTime) AS alarmCount
  5. FROM test_01
  6. GROUP BY deviceId
  7. ),
  8. t2 AS(
  9. SELECT deviceId,
  10.                 deviceDistrict,
  11.                 -- 如果地区编号是字符串可以先转换再比较，不然会触发隐式转换，导致全表扫描无法使用索引
  12.                 -- CONVERT(deviceDistrict, UNSIGNED)>=210000
  13.                 CASE WHEN deviceDistrict>=210000 AND deviceDistrict<210010 THEN '210000-210010'
  14.                          WHEN deviceDistrict>=210010 AND deviceDistrict<210020 THEN '210010-210020'
  15.                         WHEN deviceDistrict>=210020 AND deviceDistrict<210030 THEN '210020-210030'
  16.                          WHEN deviceDistrict>=210030 AND deviceDistrict<210040 THEN '210030-210040'
  17.                          WHEN deviceDistrict>=210040 AND deviceDistrict<210050 THEN '210040-210050'
  18.                          WHEN deviceDistrict>=210050 AND deviceDistrict<210060 THEN '210050-210060'
  19.                          WHEN deviceDistrict>=210060 AND deviceDistrict<210070 THEN '210060-210070'
  20.                 END deviceDistrictSection
  21. FROM test_02
  22. ),
  23. t3 AS (
  24.         SELECT t2.deviceDistrictSection AS deviceDistrictSection,
  25.                         SUM(t1.alarmCount) AS alarmCount
  26.         FROM t1 LEFT JOIN t2
  27.         ON t1.deviceId = t2.deviceId
  28.         GROUP BY deviceDistrictSection
  29.         ORDER BY deviceDistrictSection
  30. )
  31. SELECT * FROM t3;
  33. -- 第二种方案
  34. WITH t1 AS(
  35. SELECT deviceId,
  36.                 COUNT(alarmTime) AS alarmCount
  37. FROM test_01
  38. GROUP BY deviceId
  39. ),
  40. t2 AS(
  41. SELECT deviceId,
  42.                 deviceDistrict,
  43.                 CONCAT(FLOOR(deviceDistrict/10)*10, '-', (FLOOR(deviceDistrict/10)+1)*10) AS deviceDistrictSection
  44. FROM test_02
  45. ),
  46. t3 AS (
  47.         SELECT t2.deviceDistrictSection AS deviceDistrictSection,
  48.                         SUM(t1.alarmCount) AS alarmCount
  49.         FROM t1 LEFT JOIN t2
  50.         ON t1.deviceId = t2.deviceId
  51.         GROUP BY deviceDistrictSection
  52.         ORDER BY deviceDistrictSection
  53. )
  54. SELECT * FROM t3;
  56. -- 第二种方案的函数测试
  57. SELECT FLOOR(210015/10)*10 AS x;  -- 210015
  58. SELECT CEIL(210015/10)*10 AS y;  -- 210020
  59. SELECT CONCAT(FLOOR(210015/10)*10, '-', CEIL(210015/10)*10);  -- 210010-210020
  60. SELECT CONCAT(FLOOR(210020/10)*10, '-', CEIL(210020/10)*10);  -- 210020-210020
  61. SELECT CONCAT(FLOOR(210020/10)*10, '-', (FLOOR(210020/10)+1)*10);  -- 210020-210030

  复制代码
• 使用函数处理NULL值
  
  
  
  • NVL(expr1,expr2) 如果第一个参数为NULL那么显示第二个参数的值，如果第一个参数的值不为NULL，则显示第一个参数本来的值
    
  • Coalesce(expr1, expr2, expr3….. exprn) 返回表达式中第一个非空表达式，如果所有自变量均为NULL，则 COALESCE 返回NULL
    
  1. WITH
  2. -- 首先去除重复日期的重复数据，这里取最大值
  3. t1 AS(
  4. SELECT deviceId,
  5.                 alarmDate,
  6.                 MAX(alarmValueAvgDaily) AS alarmValueAvgDaily
  7. FROM test_03
  8. GROUP BY deviceId, alarmDate
  9. ),
  10. -- 去除重复设备数
  11. t2 AS(
  12. SELECT *
  13. FROM t1
  14. GROUP BY deviceId
  15. ),
  16. -- 查询设备总数
  17. t3 AS(
  18. SELECT '告警设备总数与均值' AS type,
  19.                 COUNT(deviceId) AS allDeviceCount
  20. FROM t2
  21. ),
  22. -- 查询总均值
  23. t4 AS(
  24. SELECT ROUND(AVG(alarmValueAvgDaily)) AS alarmValueAvgAll
  25. FROM t1
  26. ),
  27. -- 查询分组后的排序编号
  28. t5 AS(
  29. SELECT *,
  30.                 ROW_NUMBER() OVER(PARTITION BY deviceId ORDER BY alarmDate) AS alarmDateRank
  31. FROM t1
  32. ),
  33. -- 查询告警日期减去分组后排序编号之后的日期，如果有连续相同的说明是连续的天数
  34. t6 AS(
  35. SELECT *,
  36.                 DATE_SUB(alarmDate, INTERVAL alarmDateRank DAY) AS alarmDateSub
  37. FROM t5
  38. ),
  39. -- 查询连续天数大于3天的设备，以及这些活跃设备的平均值
  40. t7 AS(
  41. SELECT deviceId,
  42.                 ROUND(AVG(alarmValueAvgDaily))  AS alarmValueAvgActive,
  43.                 alarmDateSub,
  44.                 COUNT(*) AS  alarmDateSubCount
  45. FROM t6
  46. GROUP BY deviceId, alarmDateSub
  47. HAVING alarmDateSubCount>=3  
  48. ),
  49. t8 AS(
  50. SELECT '活跃告警设备总数与均值' AS type,
  51.                 COUNT(deviceId) AS allDeviceCount,
  52.                 ROUND(AVG(alarmValueAvgActive)) AS alarmValueAvgActiveAll
  53. FROM t7
  54. )
  55. -- 统计完成所有告警设备以及平均监测值
  56. SELECT * FROM t3 LEFT JOIN t4 ON t4.alarmValueAvgAll IS NOT NULL
  57. UNION ALL
  58. -- 统计完成活跃告警设备以及平均监测值
  59. SELECT * FROM t8;

  复制代码

我是 fx67ll.com，如果您发现本文有什么错误，欢迎在评论区讨论指正，感谢您的阅读！
如果您喜欢这篇文章，欢迎访问我的 本文github仓库地址，为我点一颗Star，Thanks~
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！