关于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 sumAllFROM(SELECT deviceId,    SUBSTR(alarmDate,1,7) AS alarmMonth,    SUM(alarmCount) AS sumPartFROM test_00GROUP BY deviceId,      alarmMonth) t;

2. 第二种计划，应用日期格式化函数，应用date_format函数的字符串必须满足yyyy-MM-dd格局，所以必须先用regexp_replace替换/为-

   SELECT *,    SUM(sumPart) OVER (PARTITION BY deviceId ORDER BY alarmMonth) AS sumAllFROM(SELECT deviceId,     DATE_FORMAT(REGEXP_REPLACE(alarmDate,'/','-'), 'yyyy-MM') AS alarmMonth,     SUM(alarmCount) AS sumPartFROM test_00GROUP 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 alarmTimeFROM test_01GROUP BY deviceId,alarmTimeORDER BY alarmTime) tGROUP BY deviceId;

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

SELECT * FROM (SELECT deviceId,        alarmDate,        alarmCount,        ROW_NUMBER() OVER(PARTITION BY deviceId ORDER BY alarmCount DESC) AS alarmRankFROM (SELECT deviceId,        alarmDate,        COUNT(alarmDate) AS alarmCountFROM(SELECT deviceId,        DATE_FORMAT(alarmTime, 'yyyy-MM-dd') AS alarmDate,        DATE_FORMAT(alarmTime, 'yyyy-MM-dd HH:mm:ss') AS alarmTimeFROM test_01GROUP BY deviceId,alarmTimeORDER BY deviceId,alarmTime) t1GROUP BY deviceId,alarmDate) t2) t3WHERE 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 alarmTimeFROM test_01GROUP BY deviceId,alarmTimeORDER BY deviceId,alarmTime),t2 AS (    SELECT deviceId,    alarmDate,    COUNT(alarmDate) AS alarmCountFROM t1GROUP BY deviceId,alarmDate),t3 AS (SELECT deviceId,    alarmDate,    alarmCount,    ROW_NUMBER() OVER(PARTITION BY deviceId ORDER BY alarmCount DESC) AS alarmRankFROM 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 firstAlarmMonthFROM t1GROUP 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 alarmCountFROM test_01GROUP 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 deviceDistrictSectionFROM 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 alarmCountFROM test_01GROUP BY deviceId),t2 AS(SELECT deviceId,        deviceDistrict,        CONCAT(FLOOR(deviceDistrict/10)*10, '-', (FLOOR(deviceDistrict/10)+1)*10) AS deviceDistrictSectionFROM 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;  -- 210015SELECT CEIL(210015/10)*10 AS y;  -- 210020SELECT CONCAT(FLOOR(210015/10)*10, '-', CEIL(210015/10)*10);  -- 210010-210020SELECT CONCAT(FLOOR(210020/10)*10, '-', CEIL(210020/10)*10);  -- 210020-210020SELECT 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 alarmValueAvgDailyFROM test_03GROUP BY deviceId, alarmDate),-- 去除反复设施数t2 AS(SELECT *FROM t1GROUP BY deviceId),-- 查问设施总数t3 AS(SELECT '告警设施总数与均值' AS type,        COUNT(deviceId) AS allDeviceCountFROM t2),-- 查问总均值t4 AS(SELECT ROUND(AVG(alarmValueAvgDaily)) AS alarmValueAvgAllFROM t1),-- 查问分组后的排序编号t5 AS(SELECT *,        ROW_NUMBER() OVER(PARTITION BY deviceId ORDER BY alarmDate) AS alarmDateRankFROM t1),-- 查问告警日期减去分组后排序编号之后的日期，如果有间断雷同的阐明是间断的天数t6 AS(SELECT *,        DATE_SUB(alarmDate, INTERVAL alarmDateRank DAY) AS alarmDateSubFROM t5),-- 查问间断天数大于3天的设施，以及这些沉闷设施的平均值t7 AS(SELECT deviceId,        ROUND(AVG(alarmValueAvgDaily))  AS alarmValueAvgActive,        alarmDateSub,        COUNT(*) AS  alarmDateSubCountFROM t6GROUP BY deviceId, alarmDateSubHAVING alarmDateSubCount>=3  ),t8 AS(SELECT '沉闷告警设施总数与均值' AS type,        COUNT(deviceId) AS allDeviceCount,        ROUND(AVG(alarmValueAvgActive)) AS alarmValueAvgActiveAllFROM t7)-- 统计实现所有告警设施以及均匀监测值SELECT * FROM t3 LEFT JOIN t4 ON t4.alarmValueAvgAll IS NOT NULLUNION 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); 　　-- 后果为：3SELECT COALESCE(NULL,null,'',3,4,5);   -- 后果为：''SELECT COALESCE(NULL,null,null,NULL);  -- 后果为：null

我是 fx67ll.com，如果您发现本文有什么谬误，欢送在评论区探讨斧正，感谢您的浏览！
如果您喜爱这篇文章，欢送拜访我的 本文github仓库地址，为我点一颗Star，Thanks~ :)
转发请注明参考文章地址，非常感谢！！！