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

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

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

1. 第一种方案，使用截取字符串函数

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

开窗函数中的界限说明

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

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

1. 由于有设备可能会有同一时间的告警记录，所以需要按告警时间去重后再统计
2. 如果使用distinct去重，如果表数据过大，且设备ID差异化很大，那么会有性能压力
3. 所以使用group by子查询代替
4. mysql中的格式化需要这样写：

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

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

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

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

COUNT(1)和COUNT(*)的区别

1. 从执行结果来说
   • 和之间没有区别，因为和都不会去过滤空值
   • 但就有区别了，因为会去过滤空值
2. 从执行效率来说
   • 他们之间根据不同情况会有些许区别，MySQL会对做优化
   • 如果列为主键，效率优于
   • 如果列不为主键，效率优于
   • 如果表中存在主键，效率最优
   • 如果表中只有一列，则效率最优
   • 如果表有多列，且不存在主键，则效率优于

留给你思考

1. 先用格式化所有设备告警时间为的日期格式
2. 运用函数得出每个设备最早告警日期
3. 当最早告警日期是的时候即为我们所需要知道的设备记录

有一个5000万的设备信息表，一个2亿记录的告警记录表

1. 设备信息表

2. 告警记录表

1. 先根据设备ID分组统计报警次数
2. 再使用casewhen条件语句，或者使用函数动态划分，根据分段统计不同设备位于什么地区号段
3. 最后连接查询，并根据地区号段，使用函数统计总告警次数即可

拼接函数//的区别说明

1. • 将多个字符串连接成一个字符串
   • 返回结果为连接参数产生的字符串，如果有任何一个参数为null，则返回值为null
2. • 和concat()一样，将多个字符串连接成一个字符串，但是可以一次性指定分隔符
   • 第一个参数指定分隔符，
   • 返回结果为连接参数产生的字符串。需要注意的是分隔符不能为null，如果为null，则返回结果为null
3. • 将产生的同一个分组中的值连接起来，返回一个字符串结果
   • 说明：通过使用可以排除重复值；如果希望对结果中的值进行排序，可以使用子句；是一个字符串值，缺省为一个逗号

拼接函数//的区别说明

1. • 在英文中，是地面，地板的意思，有下面的意思；所以此函数是向下取整，它返回的是小于或等于函数参数，并且与之最接近的整数
   • 向下取整的时候，正数，则取其整数部位，抹除小数部位
   • 负数，则取其整数加一
   • 整数，则不变
2. • 在英文中，是天花板的意思，有向上的意思；所以此函数是向上取整，它返回的是大于或等于函数参数，并且与之最接近的整数
   • 向上取整的时候，正数，则直接将当前整数加一
   • 负数，则将整数后面的数据抹除
   • 整数，则不变
3. • 在英文中是有大约，环绕，在某某四周，附近的意思，所以，可以取其大约的意思，在函数中是四舍五入
   • 四舍五入的时候，正数，小数位大于5，则整数位加一，小数位小于5，则整数位不变，抹除小数位
   • 负数，小数位小于5，则整数位不变，抹除小数位，小数位大于5，则整数位加一
   • 整数，则不变

MySQL中保留两位小数

1. 四舍五入保留小数
   • 其实就是，也就是默认d为0，默认不保留小数，d为保留几位小数
   • d可以是负数，这时是指定小数点左边的d位整数位为0，同时小数位均为0，例如：
2. 函数返回被舍去至小数点后d位的数字x，和函数类似，但是没有四舍五入
3. 强制保留d位小数，整数部分超过三位的时候以逗号分割，并且返回的结果是类型的
4. 类型转换，相当于截取，例如：
   • convert(100.3465, decimal(10,2)) -> 100.35
   • convert(100, decimal(10,2)) -> 100
   • convert(100.4, decimal(10,2)) -> 100.4

Hive中保留两位小数

1. 四舍五入截取 这种方法慎用，有时候结果不是你想要的
2. 正则匹配截取，不做四舍五入，只是单纯的当作字符串截取
3. cast函数截取 推荐使用

活跃告警设备是指连续三天都有告警的设备

连续N天登录等类似题目的解题思路

1. 日期减去一列数字得到的日期相等
2. 后一个日期减去前一个日期的差值相等

1. 首先使用去除重复日期的重复数据，用函数取最大值
2. 然后使用去除重复设备数，分别查询设备总数和总平均值，再用左连接将查询结果拼接，保存结果查询
3. 接着处理统计活跃告警设备，先用函数查询分组编号，再使用函数用告警日期减去分组编号，得出一组临时告警日期用于判定是否是活跃告警设备
4. 如果有连续相同日期说明是活跃告警设备，所以接着使用函数和条件统计过滤有大于等于三天的连续相同日期的设备与告警日期，注意同时要计算均值
5. 左后统计活跃告警设备总数和平均值，并和第二步中的结果即可

合并操作符和之间的区别

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

Hive和MySQL中的日期函数

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

此题作为开放题供大家查阅，后面有空再继续写

1. date_format()
   • Hive ，只能识别用连接的日期字符串
   • MySQL ，具体的format规则请查询参考资料
2. date_sub()
   • Hive
   • MySQL ，具体的type规则请查询参考资料

1. 向日期添加指定的时间间隔
2. 从日期减去指定的时间间隔
3. 返回两个日期之间的天数

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

使用IFNULL函数即可

1. Hive表中默认将存为，可查看表的源文件（或者），文件中存储大量，这样造成浪费大量空间
2. 但Hive的有时候是必须的
   • Hive中语句必须列数匹配，不支持不写入，没有值的列必须使用占位
   • Hive表的数据文件中按分隔符区分各个列，空列会保存来保留列位置，
     但外部表加载某些数据时如果列不够，如表13列，文件数据只有2列，则在表查询时表中的末尾剩余列无数据对应，自动显示为
3. 所以，NULL转化为空字符串，可以节省磁盘空间
   • 建表时直接指定
   • 修改已存在的表

4. 使用函数处理NULL值
   • 如果第一个参数为那么显示第二个参数的值，如果第一个参数的值不为，则显示第一个参数本来的值
   • 返回表达式中第一个非空表达式，如果所有自变量均为，则 COALESCE 返回