hive sql sum 列求和 hive sum函数

轻松入门大数据

文章目录

• 轻松入门大数据

• 第10章 入门到进阶：如何快速掌握Hive核心函数

• 10.1 Hive函数

• 10.1.19 【重要必掌握】窗口分析函数场景sumover的使用

第10章 入门到进阶：如何快速掌握Hive核心函数

10.1 Hive函数

1. 复杂数据类型
2. Hive内置函数
3. Hive UDF函数
4. 窗口分析函数
5. SQL场景题

10.1.19 【重要必掌握】窗口分析函数场景sumover的使用

【重要篇章】看到官网文档：

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select

进

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+WindowingAndAnalytics

→ 窗口分析函数，从0.11 版本开始对Hive QL 的增强。

在开始之前，先对这之前我们已经学到的东西进行一个总结回顾：

• 聚合函数：多进一出
  max min sum count avg 【出去一个】

在实际工作中，可能会遇到这样的需求：既要展示聚合前的数据，又要展示聚合后的数据

【窗口函数】 = 窗口 + 函数

官方描述：

1. Windowing functions 窗口函数
2. The OVER clause over 子句
3. Analytics functions 分析函数

OK， 实践一下

准备一份数据：window01.txt

pk,2019-04-10,1
pk,2019-04-11,5
pk,2019-04-12,7
pk,2019-04-13,3
pk,2019-04-14,2
pk,2019-04-15,4
pk,2019-04-16,4

解释[不要较真]：域名,访问时间,访问流量

创建表并加载数据：

create table win01(
domain string,
`time` string,
traffic int
)row format delimited fields terminated by ',';

load data local inpath '/home/hadoop/data/window/window01.txt' into table win01;

查一下

没问题。

直接先做个演示，看下效果先：

select domain,`time`,traffic,sum(traffic) over(partition by domain order by `time`) sum_traffic from win01;

这个东西会跑MR 作业的，但是笔者这里接收了一直不RUN 是几个意思

擦，解决一下，

我又试了一下运行一个样例

也是接收了不运行

OK， 谷歌到一个说法，让重新format 一下元数据信息【啊这，那不是以前的表都会没了吧】

用这个试试

<property>
    <name>yarn.nodemanager.resource.memory-mb</name>
    <value>20480</value>
</property>

再试试

OK， 还是没行，重启下虚拟机

寄了，直接进不去了…

那就个格式化吧

OK， 重装了一下，可以跑了

回到Hive

看来要把Hive 也格式化掉

先把元数据清除

直接把库删掉，再试一次

OK， 了

再试一次连接

可以用了，还是新建一个我们自己的库吧

OK了，就是之前的东西都没了，重来一下上面的操作

执行查询：

select domain,`time`,traffic,sum(traffic) over(partition by domain order by `time`) sum_traffic from win01;

开作业了

在运行了

可以，这样就出来了【就是beeline 又不好用了】

连上了，再跑一次

终于没问题了

可以看到这个结果代表什么呢

主要是最后那个字段，求和，按照域名进行分区，就pk 一个域名，所以都在一个组中，按照时间默认升序。可以看到是一个同分区中累加。

再看一个，

select
domain,`time`,traffic,
sum(traffic) over(partition by domain order by `time`) pv1,
sum(traffic) over(partition by domain order by `time` ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) pv2,
sum(traffic) over(partition by domain) pv3,
sum(traffic) over(partition by domain order by `time` ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) pv4,
sum(traffic) over(partition by domain order by `time` ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) pv5,
sum(traffic) over(partition by domain order by `time` ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) pv6
from win01;

我们通过执行结果进行分析

出来了6 个pv 跟在后面

算了，加个时间排序，

select
domain,`time`,traffic,
sum(traffic) over(partition by domain order by `time`) pv1,
sum(traffic) over(partition by domain order by `time` ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) pv2,
sum(traffic) over(partition by domain) pv3,
sum(traffic) over(partition by domain order by `time` ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) pv4,
sum(traffic) over(partition by domain order by `time` ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) pv5,
sum(traffic) over(partition by domain order by `time` ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) pv6
from win01 order by `time`;

又出现了，那种ACCEPTED 了但是不RUNNING 的情况

这次笔者看到了，活节点没有了…

重启一下yarn试试

看来还是资源的问题

那我就扩大一点嘛

在yarn-site.xml 配置文件中加入

<property>
     <name>yarn.nodemanager.disk-health-checker.min-healthy-disks</name>
     <value>0.0</value>
  </property>
  <property>
     <name>yarn.nodemanager.disk-health-checker.max-disk-utilization-per-disk-percentage</name>
     <value>100.0</value>
 </property>

资源不够真的都不容易学，后面还有好几个框架要跑

一顿操作之后，虚拟机烂掉了… 开不了机了，离谱

离谱，也没打快照，寄了【OK， 开启重装之路】

重新装一下Hadoop，Hive，ZK，IDEA…

这里笔者就不贴出来了【这次笔者直接搞个120G 的】

主要是扩容失败了，就想着直接重装吧【正好复习一下部署过程】

ZK 那些暂时用不到的笔者就暂时没装了，那个我们在hadoop 集群搭建的时候已经体会到了，这次暂时先Hadoop、Hive、JDK、IDEA也装上

OK，回来了，IDEA 暂时没装

再试一次

select
domain,`time`,traffic,
sum(traffic) over(partition by domain order by `time`) pv1,
sum(traffic) over(partition by domain order by `time` ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) pv2,
sum(traffic) over(partition by domain) pv3,
sum(traffic) over(partition by domain order by `time` ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) pv4,
sum(traffic) over(partition by domain order by `time` ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) pv5,
sum(traffic) over(partition by domain order by `time` ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) pv6
from win01 order by `time`;

终于又在跑了

一个语句开了几个作业

出来了，感动

分析一下结果

• pv1 我们已经看过了，从上到下累计求和
• pv2：我们写的意思是从第一行开始，到当前行，所以它的效果和pv1 是一样的【这说明这是默认的方式】
• pv3：按照域名进行了分区，以分区为单位求和，所以全是最终的26 这个结果【整个窗口中所有的数据加起来】
• pv4：我们写的意思是从前三条到当前行，所以从第四行开始，都是以前三条的累计求和结果13为基数，加上当前行，
• pv5：我们写的意思是从前三条开始，并且往下走一行，所以从第一行开始我们就可以发现，每次都累加上了下一行的数据
• pv6：我们写的意思是从当前行开始加到最后
• 【总之就是一个划分范围的概念】