Hive分析窗口函数(一)SUM,AVG,MIN,MAX

作者：喜劲瓶 | 来源：互联网 | 2018-05-27 03:08

Hive中提供了越来越多的分析函数，用于完成负责的统计分析。抽时间将所有的分析窗口函数理一遍，将陆续发布。今天先看几个基础的，SUM、AVG、MIN、MAX。p用于实现分组内所有和连续累积的统计。pCREATEEXTERNALTABLEyeshuai_test(cookieidstring,crea

Hive中提供了越来越多的分析函数，用于完成负责的统计分析。抽时间将所有的分析窗口函数理一遍，将陆续发布。今天先看几个基础的，SUM、AVG、MIN、MAX。p用于实现分组内所有和连续累积的统计。/p CREATE EXTERNAL TABLE yeshuai_test( COOKIEid string, crea

Hive中提供了越来越多的分析函数，用于完成负责的统计分析。抽时间将所有的分析窗口函数理一遍，将陆续发布。今天先看几个基础的，SUM、AVG、MIN、MAX。

用于实现分组内所有和连续累积的统计。

&＃160;

CREATE EXTERNAL TABLE yeshuai_test(
    COOKIEid string,
    createtime string, --day
    pv INT
    ) ROW FORMAT DELIMITED
    FIELDS TERMINATED BY &＃39;,&＃39;

为了测试方便，不用到hadoop上去上传文件进行测试

直接进入本地的一个文件夹进行编辑数据导入，这里我的文件夹路径为/home/work/yeshuai/data1.txt

用vim 编辑，复制进去数据

COOKIE1 2015-04-10 1

COOKIE1 2015-04-11 5

COOKIE1 2015-04-12 7

COOKIE1 2015-04-13 3

COOKIE1 2015-04-14 2

COOKIE1 2015-04-15 4

COOKIE1 2015-04-16 4

然后 hive>select * from yeshuai_test 进行测试，有数据的话，测试环境准备ok了

SUM — 注意,结果和ORDER BY相关,默认为升序

  SELECT COOKIEid,
    createtime,
    pv,
    SUM(pv) OVER(PARTITION BY COOKIEid ORDER BY createtime) AS pv1, -- 默认为从起点到当前行
    SUM(pv) OVER(PARTITION BY COOKIEid ORDER BY createtime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pv2, --从起点到当前行，结果同pv1
    SUM(pv) OVER(PARTITION BY COOKIEid) AS pv3,        --分组内所有行
    SUM(pv) OVER(PARTITION BY COOKIEid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv4, --当前行+往前3行
    SUM(pv) OVER(PARTITION BY COOKIEid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pv5, --当前行+往前3行+往后1行
    SUM(pv) OVER(PARTITION BY COOKIEid ORDER BY createtime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pv6 ---当前行+往后所有行
    FROM yeshuai_test;
     
    COOKIEid createtime pv pv1 pv2 pv3 pv4 pv5 pv6
    -----------------------------------------------------------------------------
    COOKIE1 2015-04-10 1 1 1 26 1 6 26
    COOKIE1 2015-04-11 5 6 6 26 6 13 25
    COOKIE1 2015-04-12 7 13 13 26 13 16 20
    COOKIE1 2015-04-13 3 16 16 26 16 18 13
    COOKIE1 2015-04-14 2 18 18 26 17 21 10
    COOKIE1 2015-04-15 4 22 22 26 16 20 8
    COOKIE1 2015-04-16 4 26 26 26 13 13 4

pv1: 分组内从起点到当前行的pv累积，如，11号的pv1=10号的pv&＃43;11号的pv, 12号=10号&＃43;11号&＃43;12号
pv2: 同pv1
pv3: 分组内(COOKIE1)所有的pv累加
pv4: 分组内当前行&＃43;往前3行，如，11号=10号&＃43;11号， 12号=10号&＃43;11号&＃43;12号， 13号=10号&＃43;11号&＃43;12号&＃43;13号， 14号=11号&＃43;12号&＃43;13号&＃43;14号
pv5: 分组内当前行&＃43;往前3行&＃43;往后1行，如，14号=11号&＃43;12号&＃43;13号&＃43;14号&＃43;15号=5&＃43;7&＃43;3&＃43;2&＃43;4=21
pv6: 分组内当前行&＃43;往后所有行，如，13号=13号&＃43;14号&＃43;15号&＃43;16号=3&＃43;2&＃43;4&＃43;4=13，14号=14号&＃43;15号&＃43;16号=2&＃43;4&＃43;4=10

如果不指定ROWS BETWEEN,默认为从起点到当前行;
如果不指定ORDER BY，则将分组内所有&＃20540;累加;
关键是理解ROWS BETWEEN含义,也叫做WINDOW子句：
PRECEDING：往前
FOLLOWING：往后
CURRENT ROW：当前行
UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING：表示到后面的终点

–其他AVG，MIN，MAX，和SUM用法一样。


&＃160; --AVG
&＃160; &＃160; SELECT COOKIEid,
&＃160; &＃160; createtime,
&＃160; &＃160; pv,
&＃160; &＃160; AVG(pv) OVER(PARTITION BY COOKIEid ORDER BY createtime) AS pv1, -- 默认为从起点到当前行
&＃160; &＃160; AVG(pv) OVER(PARTITION BY COOKIEid ORDER BY createtime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pv2, --从起点到当前行，结果同pv1
&＃160; &＃160; AVG(pv) OVER(PARTITION BY COOKIEid) AS pv3,&＃160; &＃160;&＃160; &＃160;&＃160;&＃160;--分组内所有行
&＃160; &＃160; AVG(pv) OVER(PARTITION BY COOKIEid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv4, --当前行+往前3行
&＃160; &＃160; AVG(pv) OVER(PARTITION BY COOKIEid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pv5, --当前行+往前3行+往后1行
&＃160; &＃160; AVG(pv) OVER(PARTITION BY COOKIEid ORDER BY createtime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pv6 ---当前行+往后所有行
&＃160; &＃160; FROM lxw1234;
&＃160; &＃160; COOKIEid createtime pv pv1 pv2 pv3 pv4 pv5 pv6
&＃160; &＃160; -----------------------------------------------------------------------------
&＃160; &＃160; COOKIE1 2015-04-10 1 1.0 1.0 3.7142857142857144 1.0 3.0 3.7142857142857144
&＃160; &＃160; COOKIE1 2015-04-11 5 3.0 3.0 3.7142857142857144 3.0 4.333333333333333 4.166666666666667
&＃160; &＃160; COOKIE1 2015-04-12 7 4.333333333333333 4.333333333333333 3.7142857142857144 4.333333333333333 4.0 4.0
&＃160; &＃160; COOKIE1 2015-04-13 3 4.0 4.0 3.7142857142857144 4.0 3.6 3.25
&＃160; &＃160; COOKIE1 2015-04-14 2 3.6 3.6 3.7142857142857144 4.25 4.2 3.3333333333333335
&＃160; &＃160; COOKIE1 2015-04-15 4 3.6666666666666665 3.6666666666666665 3.7142857142857144 4.0 4.0 4.0
&＃160; &＃160; COOKIE1 2015-04-16 4 3.7142857142857144 3.7142857142857144 3.7142857142857144 3.25 3.25 4.0

&＃160; &＃160; --MIN
&＃160; &＃160; SELECT COOKIEid,
&＃160; &＃160; createtime,
&＃160; &＃160; pv,
&＃160; &＃160; MIN(pv) OVER(PARTITION BY COOKIEid ORDER BY createtime) AS pv1, -- 默认为从起点到当前行
&＃160; &＃160; MIN(pv) OVER(PARTITION BY COOKIEid ORDER BY createtime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pv2, --从起点到当前行，结果同pv1
&＃160; &＃160; MIN(pv) OVER(PARTITION BY COOKIEid) AS pv3,&＃160; &＃160;&＃160; &＃160;&＃160;&＃160;--分组内所有行
&＃160; &＃160; MIN(pv) OVER(PARTITION BY COOKIEid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv4, --当前行+往前3行
&＃160; &＃160; MIN(pv) OVER(PARTITION BY COOKIEid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pv5, --当前行+往前3行+往后1行
&＃160; &＃160; MIN(pv) OVER(PARTITION BY COOKIEid ORDER BY createtime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pv6 ---当前行+往后所有行
&＃160; &＃160; FROM lxw1234;
&＃160; &＃160;&＃160;&＃160;
&＃160; &＃160; COOKIEid createtime pv pv1 pv2 pv3 pv4 pv5 pv6
&＃160; &＃160; -----------------------------------------------------------------------------
&＃160; &＃160; COOKIE1 2015-04-10 1 1 1 1 1 1 1
&＃160; &＃160; COOKIE1 2015-04-11 5 1 1 1 1 1 2
&＃160; &＃160; COOKIE1 2015-04-12 7 1 1 1 1 1 2
&＃160; &＃160; COOKIE1 2015-04-13 3 1 1 1 1 1 2
&＃160; &＃160; COOKIE1 2015-04-14 2 1 1 1 2 2 2
&＃160; &＃160; COOKIE1 2015-04-15 4 1 1 1 2 2 4
&＃160; &＃160; COOKIE1 2015-04-16 4 1 1 1 2 2 4

&＃160; &＃160; ----MAX
&＃160; &＃160; SELECT COOKIEid,
&＃160; &＃160; createtime,
&＃160; &＃160; pv,
&＃160; &＃160; MAX(pv) OVER(PARTITION BY COOKIEid ORDER BY createtime) AS pv1, -- 默认为从起点到当前行
&＃160; &＃160; MAX(pv) OVER(PARTITION BY COOKIEid ORDER BY createtime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pv2, --从起点到当前行，结果同pv1
&＃160; &＃160; MAX(pv) OVER(PARTITION BY COOKIEid) AS pv3,&＃160; &＃160;&＃160; &＃160;&＃160;&＃160;--分组内所有行
&＃160; &＃160; MAX(pv) OVER(PARTITION BY COOKIEid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv4, --当前行+往前3行
&＃160; &＃160; MAX(pv) OVER(PARTITION BY COOKIEid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pv5, --当前行+往前3行+往后1行
&＃160; &＃160; MAX(pv) OVER(PARTITION BY COOKIEid ORDER BY createtime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pv6 ---当前行+往后所有行
&＃160; &＃160; FROM lxw1234;
&＃160; &＃160;&＃160;&＃160;
&＃160; &＃160; COOKIEid createtime pv pv1 pv2 pv3 pv4 pv5 pv6
&＃160; &＃160; -----------------------------------------------------------------------------
&＃160; &＃160; COOKIE1 2015-04-10 1 1 1 7 1 5 7
&＃160; &＃160; COOKIE1 2015-04-11 5 5 5 7 5 7 7
&＃160; &＃160; COOKIE1 2015-04-12 7 7 7 7 7 7 7
&＃160; &＃160; COOKIE1 2015-04-13 3 7 7 7 7 7 4
&＃160; &＃160; COOKIE1 2015-04-14 2 7 7 7 7 7 4
&＃160; &＃160; COOKIE1 2015-04-15 4 7 7 7 7 7 4
&＃160; &＃160; COOKIE1 2015-04-16 4 7 7 7 4 4 4

推荐阅读

windows
解决Hive启动时权限被拒问题

本文详细分析了Hive在启动过程中遇到的权限拒绝错误，并提供了多种解决方案，包括调整文件权限、用户组设置以及环境变量配置等。 ... [详细]

蜡笔小新 2024-12-26 19:14:29
windows
Hadoop入门与核心组件详解

本文详细介绍了Hadoop的基础知识及其核心组件，包括HDFS、MapReduce和YARN。通过本文，读者可以全面了解Hadoop的生态系统及应用场景。 ... [详细]

蜡笔小新 2024-12-26 13:12:48
windows
Hadoop发行版本选择指南：技术解析与应用实践

本文详细介绍了Hadoop的不同发行版本及其特点，帮助读者根据实际需求选择最合适的Hadoop版本。内容涵盖Apache Hadoop、Cloudera CDH等主流版本的特性及应用场景。 ... [详细]

蜡笔小新 2024-12-22 20:38:12
vim
简化报表生成：EasyReport工具的全面解析

本文详细介绍了EasyReport，一个易于使用的开源Web报表工具。该工具支持Hadoop、HBase及多种关系型数据库，能够将SQL查询结果转换为HTML表格，并提供Excel导出、图表显示和表头冻结等功能。 ... [详细]

蜡笔小新 2024-12-22 11:11:28
vim
使用正则表达式去除字符串中单词间的空格

本文探讨了如何在Hive（基于Hadoop）环境中编写类似SQL的语句，以去除字段中的空格。特别是在处理邮政编码等数据时，去除特定位置的空格是常见的需求。 ... [详细]

蜡笔小新 2024-12-20 19:08:43
vim
Linux 文件权限与用户配置管理

本文详细介绍了 Linux 系统中用户、组和文件权限的设置方法，包括基本权限（读、写、执行）、特殊权限（SUID、SGID、Sticky Bit）以及相关配置文件的使用。 ... [详细]

蜡笔小新 2024-12-20 09:27:23
vim
Hive中Map任务数量的确定方法

本文探讨了Hive作业中Map任务数量的确定方式，主要涉及HiveInputFormat和CombineHiveInputFormat两种InputFormat的分片计算逻辑。通过调整相关参数，可以有效控制Map任务的数量，进而优化Hive作业的性能。 ... [详细]

蜡笔小新 2024-12-19 11:36:41
vim
请看|间隔时间_Postgresql 主从复制

请看|间隔时间_Postgresql 主从复制 ... [详细]

蜡笔小新 2024-12-19 03:24:54
git
构建基于BERT的中文NL2SQL模型：一个简明的基准

本文探讨了将自然语言转换为SQL语句（NL2SQL）的任务，这是人工智能领域中一项非常实用的研究方向。文章介绍了笔者在公司举办的首届中文NL2SQL挑战赛中的实践，该比赛提供了金融和通用领域的表格数据，并标注了对应的自然语言与SQL语句对，旨在训练准确的NL2SQL模型。 ... [详细]

蜡笔小新 2024-12-27 17:36:19
vim
Linux 文件输入输出操作

本文介绍了Linux系统中的文件IO操作，包括文件描述符、基本文件操作函数以及目录操作。详细解释了各个函数的参数和返回值，并提供了代码示例。 ... [详细]

蜡笔小新 2024-12-24 02:33:04
vim
MySQL 客户端执行 NOW() 函数时出现 8 小时时差问题及解决方案

本文介绍了一种在 MySQL 客户端执行 NOW() 函数时出现时间偏差的问题，并详细描述了如何通过配置文件调整时区设置来解决该问题。演示场景中，假设当前北京时间为2023年2月17日19:31:37，而查询结果显示的时间比实际时间晚8小时。 ... [详细]

蜡笔小新 2024-12-23 18:15:08
vim
Vim怎么批量处理文件将tab变为space？

Vim怎么批量处理文件将tab变为space？ ... [详细]

蜡笔小新 2024-12-23 16:36:55
diff
搭建高可用Etcd集群

本文详细介绍了如何规划和部署一个高可用的Etcd集群，包括主机配置、软件安装、防火墙设置及集群健康检查等内容。通过合理的硬件配置和网络规划，确保Etcd集群在生产环境中的稳定运行。 ... [详细]

蜡笔小新 2024-12-23 15:20:17
vim
CentOS 6.3 64位系统下搭建LAMP环境并安装PHP 5.5.7

在成功安装和测试MySQL及Apache之后，接下来的步骤是安装PHP。为了确保安全性和配置的一致性，建议在安装PHP前先停止MySQL和Apache服务，并将MySQL集成到PHP中。 ... [详细]

蜡笔小新 2024-12-23 13:08:12
ide
Nginx 反向代理与负载均衡实验

本实验旨在通过配置 Nginx 实现反向代理和负载均衡，确保从北京本地代理服务器访问上海的 Web 服务器时，能够依次显示红、黄、绿三种颜色页面以验证负载均衡效果。 ... [详细]

蜡笔小新 2024-12-22 15:15:48

喜劲瓶

这个家伙很懒，什么也没留下！

Tags | 热门标签

RankList | 热门文章