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

最新推荐文章于 2022-05-05 11:04:18 发布
最新推荐文章于 2022-05-05 11:04:18 发布
阅读量3.1k 收藏 3
点赞数 1
分类专栏： 大数据 文章标签： hive 
Hive中提供了越来越多的分析函数，用于完成负责的统计分析。抽时间将所有的分析窗口函数理一遍，将陆续发布。
今天先看几个基础的，SUM、AVG、MIN、MAX。
<p>用于实现分组内所有和连续累积的统计。</p> 
CREATE EXTERNAL TABLE yeshuai_test(
    cookieid string,
    createtime string, --day
    pv INT
    ) ROW FORMAT DELIMITED
    FIELDS TERMINATED BY ','

为了测试方便，不用到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+11号的pv, 12号=10号+11号+12号
    pv2: 同pv1
    pv3: 分组内(cookie1)所有的pv累加
    pv4: 分组内当前行+往前3行，如，11号=10号+11号， 12号=10号+11号+12号， 13号=10号+11号+12号+13号， 14号=11号+12号+13号+14号
    pv5: 分组内当前行+往前3行+往后1行，如，14号=11号+12号+13号+14号+15号=5+7+3+2+4=21
    pv6: 分组内当前行+往后所有行，如，13号=13号+14号+15号+16号=3+2+4+4=13，14号=14号+15号+16号=2+4+4=10

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

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

    <div class="blockcode"><div id="code_cxN"><ol><li>  --AVG
</li><li>    SELECT cookieid,
</li><li>    createtime,
</li><li>    pv,
</li><li>    AVG(pv) OVER(PARTITION BY cookieid ORDER BY createtime) AS pv1, -- 默认为从起点到当前行
</li><li>    AVG(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pv2, --从起点到当前行，结果同pv1
</li><li>    AVG(pv) OVER(PARTITION BY cookieid) AS pv3,        --分组内所有行
</li><li>    AVG(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv4, --当前行+往前3行
</li><li>    AVG(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pv5, --当前行+往前3行+往后1行
</li><li>    AVG(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pv6 ---当前行+往后所有行
</li><li>    FROM lxw1234;
</li><li>    cookieid createtime pv pv1 pv2 pv3 pv4 pv5 pv6
</li><li>    -----------------------------------------------------------------------------
</li><li>    cookie1 2015-04-10 1 1.0 1.0 3.7142857142857144 1.0 3.0 3.7142857142857144
</li><li>    cookie1 2015-04-11 5 3.0 3.0 3.7142857142857144 3.0 4.333333333333333 4.166666666666667
</li><li>    cookie1 2015-04-12 7 4.333333333333333 4.333333333333333 3.7142857142857144 4.333333333333333 4.0 4.0
</li><li>    cookie1 2015-04-13 3 4.0 4.0 3.7142857142857144 4.0 3.6 3.25
</li><li>    cookie1 2015-04-14 2 3.6 3.6 3.7142857142857144 4.25 4.2 3.3333333333333335
</li><li>    cookie1 2015-04-15 4 3.6666666666666665 3.6666666666666665 3.7142857142857144 4.0 4.0 4.0
</li><li>    cookie1 2015-04-16 4 3.7142857142857144 3.7142857142857144 3.7142857142857144 3.25 3.25 4.0</li></ol></div>
</div><div class="blockcode"><div id="code_L6F"><ol><li>    --MIN
</li><li>    SELECT cookieid,
</li><li>    createtime,
</li><li>    pv,
</li><li>    MIN(pv) OVER(PARTITION BY cookieid ORDER BY createtime) AS pv1, -- 默认为从起点到当前行
</li><li>    MIN(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pv2, --从起点到当前行，结果同pv1
</li><li>    MIN(pv) OVER(PARTITION BY cookieid) AS pv3,        --分组内所有行
</li><li>    MIN(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv4, --当前行+往前3行
</li><li>    MIN(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pv5, --当前行+往前3行+往后1行
</li><li>    MIN(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pv6 ---当前行+往后所有行
</li><li>    FROM lxw1234;
</li><li>     
</li><li>    cookieid createtime pv pv1 pv2 pv3 pv4 pv5 pv6
</li><li>    -----------------------------------------------------------------------------
</li><li>    cookie1 2015-04-10 1 1 1 1 1 1 1
</li><li>    cookie1 2015-04-11 5 1 1 1 1 1 2
</li><li>    cookie1 2015-04-12 7 1 1 1 1 1 2
</li><li>    cookie1 2015-04-13 3 1 1 1 1 1 2
</li><li>    cookie1 2015-04-14 2 1 1 1 2 2 2
</li><li>    cookie1 2015-04-15 4 1 1 1 2 2 4
</li><li>    cookie1 2015-04-16 4 1 1 1 2 2 4</li></ol></div>
</div><div class="blockcode"><div id="code_f2D"><ol><li>    ----MAX
</li><li>    SELECT cookieid,
</li><li>    createtime,
</li><li>    pv,
</li><li>    MAX(pv) OVER(PARTITION BY cookieid ORDER BY createtime) AS pv1, -- 默认为从起点到当前行
</li><li>    MAX(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pv2, --从起点到当前行，结果同pv1
</li><li>    MAX(pv) OVER(PARTITION BY cookieid) AS pv3,        --分组内所有行
</li><li>    MAX(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv4, --当前行+往前3行
</li><li>    MAX(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pv5, --当前行+往前3行+往后1行
</li><li>    MAX(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pv6 ---当前行+往后所有行
</li><li>    FROM lxw1234;
</li><li>     
</li><li>    cookieid createtime pv pv1 pv2 pv3 pv4 pv5 pv6
</li><li>    -----------------------------------------------------------------------------
</li><li>    cookie1 2015-04-10 1 1 1 7 1 5 7
</li><li>    cookie1 2015-04-11 5 5 5 7 5 7 7
</li><li>    cookie1 2015-04-12 7 7 7 7 7 7 7
</li><li>    cookie1 2015-04-13 3 7 7 7 7 7 4
</li><li>    cookie1 2015-04-14 2 7 7 7 7 7 4
</li><li>    cookie1 2015-04-15 4 7 7 7 7 7 4
</li><li>    cookie1 2015-04-16 4 7 7 7 4 4 4</li></ol></div></div>


vipyeshuai
关注
  • 1
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Hive性能调优
fansy1990的专栏
07-01 1万+
软件环境：Hive1.2.1 ／Hadoop2.6.4 ；直接使用Hive Cli模式运行；1. 设置执行引擎set hive.execution.engine=mr; set hive.execution.engine=spark;如果设置执行引擎为mr，那么就会调用Hadoop的maprecude来运行需要执行job的程序；如果设置执行引擎为spark，那么就会调用spark来执行任务。有条件
Hive常用函数
weixin_41875135的博客
11-11 2223
1 关系运算 1.1 1、等值比较: = 1.2 2、不等值比较: 1.3 3、小于比较: < 1.4 4、小于等于比较: <= 1.5 5、大于比较: > 1.6 6、大于等于比较: >= 1.7 7、空值判断: IS NULL 1.8 8、非空判断: IS NOT NULL 1.9 9、LIKE比较: LIKE 1.10 10、JAVA的LIKE操作: RLIKE 1.11 11、REGEXP操作: REGEXP 2 数学运算..
Hive分析窗口函数之SUM，AVG，MIN和MAX
DataFlow范式
04-04 9438
Hive中提供了很多的分析函数，用于完成负责的统计分析。 本文先介绍SUM、AVG、MIN、MAX这四个函数。 环境信息： Hive版本为apache-hive-0.14.0-bin Hadoop版本为hadoop-2.6.0 Tez版本为tez-0.7.0 构造数据： P088888888888,2016-02-10,1 P088888888888,2016-02
hive数据库numeric_你知道Hive中的中位数吗
weixin_39693438的博客
12-20 413
关于求解中位数，我们知道在Python中直接有中位数处理函数(mean)，比如在Python中求解一个中位数，代码很简单。Python计算中位数import numpy as npnums = [1.1,2.2,3.3,4.4,5.5,6.6]#均值np.mean(nums)#中位数np.median(nums)在hive中没有直接提供相关的mean函数，但官方提供了两个UDAF，percenti...
Hive Sql中均值、方差、标准差、皮尔逊相关系数、偏度、峰度统计字段
别说话写代码的博客
08-14 2万+
SELECT mean(age) AS '均值', variance(age) AS '方差', stddev(age) AS '标准差', corr(age,yearsmarried) AS '两个指标的相关系数', skewness(age) AS 'skewness偏度', kurtosis(age) AS 'kurtosis峰度' FROM table
Hive：窗口函数
qq_24325581的博客
08-10 630
一、窗口函数是什么 二、窗口函数分类 一、聚合型窗口函数 二、分析型窗口函数 三、取值型窗口函数 三、参考文章 Hive 窗口函数
hive窗口函数
01-09
hive窗口系列函数
hive窗口函数.docx
07-08
数据蛙hive窗口函数 - 精心总结
Hive 窗口函数
01-07
over()：指定分析函数工作的数据窗口的大小，这个数据窗口大小会随着行的变而变化。over跟在聚合函数后面，只对聚合函数生效。 current row：当前行 n preceding：往前n行数据 n following：往后n行数据 unbounded：...
大数据hive中窗口函数的一些常用函数
最新发布
10-19
大数据hive中窗口函数的一些常用函数
HiveSQL窗口函数.pdf
12-18
HiveSQL窗口函数.pdf
Hive原理及查询优化
LW_ICE
05-21 1万+
Hive是构建在Hadoop上的数据仓库软件框架，支持使用SQL来读，写和管理大规模数据集合。Hive入门非常简单，功能非常强大，所以非常流行。 通常来说，Hive只支持数据查询和加载，但后面的版本也支持了插入，更新和删除以及流式api。Ｈive具有目前Hadoop上最丰富最全的SQL语法，也拥有最慢最稳定的执行。是目前Hadoop上几乎标准的ETL和数据仓库工具。 Hive这个特点与
Hive补充之窗口函数
Maynor996的博客
03-09 1455
我们在使用over()窗口函数时，over()函数中的这三个函数可组合使用也可以不使用。我们常使用的窗口范围是ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（表示从起点到当前行），常用该窗口来计算累加。ntile(n) 把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，ntile返回此行所属的组的编号。over()函数中如果不使用这三个函数，窗口大小是针对查询产生的所有数据，如果指定了分区，窗口大小是针对每个分区的数据。
Hive 开窗函数 —— over(partition by) 介绍
weixin_45417821的博客
08-01 8380
开窗函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。 –排序，即便值一样，也不会出现重复排序， select row_number() over(order by name) as 排序, * from t2_temp –排序，值一样，就重复排序,例如1,1,3,4 select rank() over(order by name)
Hive开窗函数总结
大数据
05-05 2419
一、介绍 分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。 开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化！到底什么是数据窗口？后面举例会详细讲到！ Window Function又称为窗口函数、分析函数。 窗口函数与聚合函数类似，但是每一行数据都生成一个结果。 聚合函数(比如sum、avg、max等)可以将多行数据按照规定聚合为一行，一般来讲聚集后的行数要少于聚集前的行数。但是有时我们想要既显示聚
hive的统计函数
热门推荐
haramshen的博客
09-26 5万+
hive函数—-集合统计函数 集合统计函数1. 个数统计函数: count 语法: count(*), count(expr), count(DISTINCT expr[, expr_.]) 返回值: int 说明: count(*)统计检索出的行的个数，包括NULL值的行；count(expr)返回指定字段的非空值的个数；c
Hive使用笔记
大鹏的专栏
04-01 942
Hive的存储是建立在Hadoop文件系统之上的。Hive本身没有专门的数据存储格式，也不能为数据建立索引，用户可以自由地组织Hive中的表，只需要在创建表的时候告诉Hive数据中的列分隔符和行分隔符就可以解析数据了。 Hive中主要包含四类数据模型：表（Table）、外部表（External Table）、分区（Partition）和桶（Bucket）。 Hive中每个分区都对应数据库中相应
hive 窗口函数
05-30
常见的窗口函数包括：SUM、AVG、MIN、MAX、ROW_NUMBER、RANK、DENSE_RANK等。 下面是一个示例，演示如何使用Hive窗口函数计算每个部门的平均工资： ``` SELECT department, name, salary, AVG(salary) OVER ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值