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

作者：NewGuy | 来源：互联网 | 2017-05-12 16:09

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
WinPythonHadoop在Win10上安装教程

本文介绍了在Win10上安装WinPythonHadoop的详细步骤，包括安装Python环境、安装JDK8、安装pyspark、安装Hadoop和Spark、设置环境变量、下载winutils.exe等。同时提醒注意Hadoop版本与pyspark版本的一致性，并建议重启电脑以确保安装成功。 ... [详细]

蜡笔小新 2023-12-14 11:26:56
diff
HDFS2.x新特性

一、集群间数据拷贝scp实现两个远程主机之间的文件复制scp-rhello.txtroothadoop103:useratguiguhello.txt推pushscp-rr ... [详细]

蜡笔小新 2023-12-13 13:52:40
git
org.apache.hadoop.hive.ql.plan.ExprNodeColumnDesc.getTypeInfo()方法的使用及代码示例

本文整理了Java中org.apache.hadoop.hive.ql.plan.ExprNodeColumnDesc.getTypeInfo()方法的一些代码示例，展 ... [详细]

蜡笔小新 2023-10-17 21:32:56
git
Hadoop与大数据技术大会将于11月30日开幕

11月26日，由中国计算机协会（CCF）主办，CCF大数据专家委员会协办，CSDN承办的Hadoop与大数据技术大会（Hadoop&BigDataTechnology ... [详细]

蜡笔小新 2023-10-17 17:47:11
git
什么是大数据lambda架构

一、什么是Lambda架构Lambda架构由Storm的作者[NathanMarz]提出，根据维基百科的定义，Lambda架构的设计是为了在处理大规模数 ... [详细]

蜡笔小新 2023-10-17 16:06:09
git
Hadoop源码解析1Hadoop工程包架构解析

1 Hadoop中各工程包依赖简述 Google的核心竞争技术是它的计算平台。Google的大牛们用了下面5篇文章，介绍了它们的计算设施。 GoogleCluster：ht ... [详细]

蜡笔小新 2023-10-17 13:28:20
git
EPICS Archiver Appliance存储waveform记录的尝试及资源需求分析

本文介绍了EPICS Archiver Appliance存储waveform记录的尝试过程，并分析了其所需的资源容量。通过解决错误提示和调整内存大小，成功存储了波形数据。然后，讨论了储存环逐束团信号的意义，以及通过记录多圈的束团信号进行参数分析的可能性。波形数据的存储需求巨大，每天需要近250G，一年需要90T。然而，储存环逐束团信号具有重要意义，可以揭示出每个束团的纵向振荡频率和模式。 ... [详细]

蜡笔小新 2023-12-14 17:43:56
vim
在mac环境下使用nginx配置nodejs代理服务器的步骤

本文介绍了在mac环境下使用nginx配置nodejs代理服务器的步骤，包括安装nginx、创建目录和文件、配置代理的域名和日志记录等。 ... [详细]

蜡笔小新 2023-12-13 10:34:21
编辑器
Vim编辑器的三种模式及切换方法详解

本文详细介绍了Vim编辑器中的三种模式（命令模式、末行模式和编辑模式）以及它们之间的操作区别和切换方法。Vim编辑器凭借其多种命令快捷键和高效率的操作方式，得到了广大厂商和用户的认可。对于想要高效操作文本的用户来说，了解这些模式的使用方法是必不可少的。 ... [详细]

蜡笔小新 2023-12-12 15:59:51
vim
mac php错误日志配置方法及错误级别修改

本文介绍了在mac环境下配置php错误日志的方法，包括修改php.ini文件和httpd.conf文件的操作步骤。同时还介绍了如何修改错误级别，以及相应的错误级别参考链接。 ... [详细]

蜡笔小新 2023-12-12 11:59:08
macos
MacOS系统安装MySQL及设置MySQL服务开机启动和密码修改方法

本文介绍了在MacOS系统上安装MySQL的步骤，并详细说明了如何设置MySQL服务的开机启动和如何修改MySQL的密码。通过下载MySQL的macos版本并按照提示一步一步安装，在系统偏好设置中可以找到MySQL的图标进行设置。同时，还介绍了通过终端命令来修改MySQL的密码的具体操作步骤。 ... [详细]

蜡笔小新 2023-12-11 17:35:39
vim
CentOS 6.4更新源地址的方法

本文介绍了在CentOS 6.4系统中更新源地址的方法，包括备份现有源文件、下载163源、修改文件名、更新列表和系统，并提供了相应的命令。 ... [详细]

蜡笔小新 2023-12-11 16:09:40
ide
5个基本Linux命令行工具的现代化替代品

本文介绍了5个基本Linux命令行工具的现代化替代品，包括du、top和ncdu。这些替代品在功能上进行了改进，提高了可用性，并且适用于现代化系统。其中，ncdu是du的替代品，它提供了与du类似的结果，但在一个基于curses的交互式界面中，重点关注占用磁盘空间较多的目录。 ... [详细]

蜡笔小新 2023-12-09 20:30:48
vim
Annotation的大材小用

为什么80%的码农都做不了架构师？最近在开发一些通用的excel数据导入的功能，由于涉及到导入的模块很多，所以开发了一个比较通用的e ... [详细]

蜡笔小新 2023-10-17 19:43:18
vim
【转】腾讯分析系统架构解析

TA（TencentAnalytics，腾讯分析）是一款面向第三方站长的免费网站分析系统，在数据稳定性、及时性方面广受站长好评，其秒级的实时数据更新频率也获得业界的认可。本文将从实 ... [详细]

蜡笔小新 2023-10-16 19:05:20

NewGuy

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

Tags | 热门标签

RankList | 热门文章