前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >hive sql系列(总结)

hive sql系列(总结)

作者头像
大数据最后一公里
发布2021-08-05 10:26:08
1.8K0
发布2021-08-05 10:26:08
举报
文章被收录于专栏:大数据最后一公里
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

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2021-07-18,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 大数据最后一公里 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • hive sql系列(总结)介绍:
  • hive sql系列目录:
  • 需求转化成sql思路:
  • 知识点:
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档