hive sql系列（总结）

hive sql系列（总结）介绍：

hive sql系列主打sql，通过案例，从实现到分析，帮助大家找到写sql的快乐

hive sql系列目录：

1. hive sql系列（一）：找出所有科目成绩都大于某一学科平均成绩的学生
2. hive sql系列（二）：统计每个用户每个月访问量和累计月访问量
3. hive sql系列（三）：求所有用户和活跃用户的总数及平均年龄
4. hive sql系列（四）：请用sql写出所有用户中在今年10月份第一次购买商品的金额
5. hive sql系列（五）：一个日志表中记录了某个商户费率变化状态的所有信息，现在有个需求,要取出按照时间轴顺序，发生了状态变化的数据行
6. hive sql系列（六）：每个用户连续登录最大天数
7. hive sql系列（七）：查询前20%时间的订单信息
8. hive sql系列（八）：根据聚合在一起的编码转换成聚合在一起的码值
9. hive sql系列（九）：有一张表，其中一个字段是由时间、接口、ip和其他字段组成的求11月9号下午14点（14-15点），访问/api/user/login接口的top10的ip地址
10. hive sql系列（十）：编写sql语句实现每班前三名，分数一样并列， 同时求出前三名按名次排序的分差

需求转化成sql思路：

1、排序相关三个函数：row_number()、rank()、dense_rank()

2、统计A下的B topn，需要基于A开窗根据不同的情况使用排序函数，得到临时表，然后基于临时表过滤数据。（重点：开窗、子查询、需要条件过滤的在子查询中先过滤）

3、hive sql系列（三）是一个级联求和的典型例子，意思是当月和累计在一起的意思，以此类推，相似的场景都可以用hive sql系列（三）的方式做，这就形成了一个类似鸡兔同笼方式的二元方程式的固定解法

4、写sql的步骤：分析需求（明确需要做什么） -> 拆解需求（大概如何实现） -> 列出实现步骤（具体实现方式） -> 合并步骤（可以在一步实现的合并，减少子查询）。（有点类似spark、flink算子链，算子合并的意思）

5、当遇到实现方式不能得到正确结果时，先核对逻辑，每一步的实现得到的结果是否如你所愿，如果还不能解决，每步一测，确保一进一出时符合的（划重点）

6、hql通常有两个场景：一是对业务数据处理，二是对日志数据处理。区别在于：业务数据处理的数据通常是关系型数据，数据比较规整和规矩，不必做过多转化；日志数据处理的数据通常是埋点数据，数据很长，有格式，需要先做转化得到符合处理要求时才能进一步统计分析，案例如hive sql（八）（网友的企业实战）（重点）和hive sql（九）

7、基于开窗排序之上还有取数，那就需要用到lag函数，甚至取数之后还要进行运算，无论多复杂的需求，都可以参考第5点，这让我想当《算法》里面说到的一句话——分而治之

8、写好sql，需要逻辑就可以了，想快点写好sql，需要多写sql就好，多次训练是题海战术，有题型有固定解法，想又快又好的写好sql，需要了解不同功能函数并灵活运用，比如日期类函数有多少种，功能是啥，返回值是啥；又比如排序函数三种的用法等等（重点：这部分的总结下次分享出来，总结中）

9、order by和开窗函数里面的order by重用之痛，可以参考hive sql系列（十）（重点：类似这种会慢慢总结分享出来，如果没有经验，会很浪费测试sql的时间）

10、这次就总结这些了，下次再见。。。欢迎小伙伴们参与进来，享受sql的快乐。

知识点：

1、over（）开窗范围：rows是行数的意思，后面跟的是函数的范围，between是行数在什么范围，unbounded是行数的起点,这里可以将unbounded替换成1，那就是相邻上一个月的意思，preceding是前面的意思，current row是当前行的意思2、cast(数据类型1 as 数据类型2)表示将数据类型1强转成数据类型23、decimal(10,2)是整数位长度为10，小数位长度为2，如果没有小数位，默认补0

4、row_number：添加序号，无论字段值是否相同

5、dense_rank()：基于over开窗函数的排序函数，如果值相同，则排序的序号相同，紧接的序号不跳过。举例123,1123,1223这样6、6、date_sub(日期，数值)，用日期-数值，即当前日期的前n天，返回值是日期字符串类型

7、ntile：把有序的数据集合平均分配到指定的数据量个桶中，将桶号分配给每一行。如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差1。

8、split(字符串，分割符)：使用分割符切割字符串，返回一个数组9、lateral view explode(数组)：将数组字段拆分成多行10、concat_ws(连接符,字符串，字符串)：连接多个字符串11、collect_list(分组键)：将分组中的某列聚合成一个数组，数组中元素与分组后的数据保持一致12、map：将多个排列好的k,v,k,v...变成一个map结构，这是初始化map结构的方式，取数据是map[key]13、row_number()：row_number是基于over()开窗函数的一个不重复的序号，如上结果所示，即便结果相同，也会顺延,序号自增14、substring(字符串，起始位置，步长)：根据起始位置和步长切割字符串

15、nvl(value1，value2)：如果value1是null，则返回value2，如果不是，则返回value1

16、lag(字段，n，默认值)：基于over开窗函数，根据排序规则取当前行前第n个数，如果不指定n，则默认取前一个，如果取不到，返回默认值，如果不指定默认值，取不到则返回Null