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

最新推荐文章于 2022-12-19 16:55:19 发布
最新推荐文章于 2022-12-19 16:55:19 发布
阅读量411 收藏
点赞数
分类专栏： hive



转载留作备用，很好的文章. 地址：

http://lxw1234.com/archives/2015/04/176.htm

转载请注明：lxw的大数据田地 » Hive分析窗口函数(一) SUM,AVG,MIN,MAX

 

今天先看几个基础的，SUM、AVG、MIN、MAX。

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

Hive版本为 apache-hive-0.13.1

数据准备

    CREATE EXTERNAL TABLE lxw1234 (
    cookieid string,
    createtime string,   --day 
    pv INT
    ) ROW FORMAT DELIMITED 
    FIELDS TERMINATED BY ',' 
    stored as textfile location '/tmp/lxw11/';
     
    DESC lxw1234;
    cookieid                STRING 
    createtime              STRING 
    pv INT 
     
    hive> select * from lxw1234;
    OK
    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


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 lxw1234;
     
    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用法一样。

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

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

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


其他函数的介绍将陆续整理发布。。


zhoudetiankong
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
自连接，窗口函数（sum、avg、max、min）实战
MicoOu的博客
12-11 2249
采用自连接和窗口函数两种方法解题： 求每个用户截止到每月为止的当月访问次数、最大单月访问次数、累计到该月的总访问次数。 三个字段的意思： 用户名，月份，访问次数 表名visits 字段id, vmonth, pv 数据： A,2015-01,5 A,2015-01,15 B,2015-01,5 A,2015-01,8 B,2015-01,25 A,2015-01,5 A,2015-02,4 A,2...
SQL函数-窗口函数-聚合窗口函数
最新发布
shane700的博客
05-20 996
许多常见的聚合函数也可以作为窗口函数使用，包括AVG()，SUM()，COUNT()，MAX()以及MIN()等函数.
Hive学习之路 （十三）Hive分析窗口函数(一) SUM,AVG,MIN,MAX
mnasd的博客
07-29 1826
目录 数据准备 数据格式 创建数据库及表 玩一玩SUM 查询语句 查询结果 说明 玩一玩AVG 查询语句 查询结果 玩一玩MIN 查询语句 查询结果  玩一玩MAX 查询语句 查询结果   正文 回到顶部 数据准备 数据格式 cookie1,2015-04-10,1 cookie1,2015-04-11,5 co...
窗口函数详解
weixin_44100044的博客
03-01 2540
窗口函数可以进行排序、生成序列号等一般聚合函数无法实现的高级操作。其语法通常为 <窗口函数> over (partition by <列> order by <列> ) partiton by 分组 order by 排序 窗口函数大致分为两类 （1）能够作为窗口函数的聚合函数（sum、avg、count、max、min） （2）专用窗口函数 2.1 rank函数 ...
hive窗口函数.docx
07-08
1. 聚合类：avg()、sum()、max()、min() 等 2. 排名类：row_number()、rank()、dense_rank() 等 3. 其他类：lag()、lead()、ntile() 等 窗口函数应用场景 窗口函数常用于解决以下问题： * 数据排名和排序 * 数据...
hive开窗函数详细介绍
03-28
本文将详细探讨Hive的开窗函数，包括ROW_NUMBER、RANK、DENSE_RANK以及分析窗口函数SUM、AVG、MIN和MAX的用法。 一、窗口函数 1. ROW_NUMBER() ROW_NUMBER()函数为每个分区内的记录分配一个唯一的连续编号，根据...
电影票房数据分析-hive代码
05-22
Hive提供了多种数据分析函数，包括SUM、MAX、MIN、AVG等。在该资源中，我们使用MAX函数来查询2020年中当日综合总票房最多的10天。 知识点7：Hive数据排序 Hive提供了多种数据排序方式，包括ASC、DESC等。在该资源...
hive.ziphive数据迁移和数据分析
01-10
3. **统计分析**：内置的统计函数如AVG(), COUNT(), MAX(), MIN(), SUM()等可用于基本的统计计算。更高级的统计函数，如CORR()、VAR()、STDDEV()等，可进行相关性和方差分析。 4. **分区查询优化**：利用Hive的分区...
hive的函数大全.rar
01-04
5. **聚合函数**：用于处理一组值并返回单个值，例如`COUNT()`（计数）、`SUM()`（求和）、`AVG()`（平均值）、`MAX()`和`MIN()`（最大最小值）。 6. **分组和分桶函数**：`GROUP BY`和`DISTRIBUTE BY`用于数据分组...
Hive函数：SUM,AVG,MIN,MAX
weixin_33910385的博客
03-28 170
转自：http://lxw1234.com/archives/2015/04/176.htm，Hive分析窗口函数(一) SUM,AVG,MIN,MAX 之前看到大数据田地有关于max()over(partition by)的用法，今天恰好工作中用到了它，但是使用中遇到了一个问题：在max(rsrp)over(partition by buildingid,height) as max_rsrp...
hive --窗口函数（一）SUM,AVG,MIN,MAX
认真学代码的shell
09-28 843
参考链接： http://lxw1234.com/archives/2015/07/367.htm 转载自lxw的大数据田地 » Hive分析窗口函数(一) SUM,AVG,MIN,MAX：http://lxw1234.com/archives/2015/04/176.htm Hive分析窗口函数(一) SUM,AVG,MIN,MAX Hive版本为 apache-hive-0.13.1 0 数据准备 # 建表 hive>CREATE EXTERNAL TABLE lxw1234 ( cookieid
Mysql窗口函数 (知识点梳理+题目解析+面试实战）(四万字长文，一文读懂，建议收藏后食用)
weixin_45532984的博客
12-19 2830
窗口函数作为Mysql 8的新特性以及面试笔试的重点，掌握并且进行来练习是非常有必要的。本文章详细介绍窗口函数的方方面面，包含知识点梳理，简单案例，题目解析和面试实战，阅读需要约一定的时间，建议收藏后食用。其中在解题部分更是包含了题目分析和要求梳理，解体解答和答案总和，题目由易到难，更包含牛客会员类型题目，其中解体解答花费了很大心思。毫不夸张地说，弄懂本篇文章，可以应付基本所有的面试笔试，尤其是数据开发岗和数据分析岗。
【Hive】窗口函数（开窗函数部分）
康师傅没有眼泪
11-24 2878
我们都知道在sql中有一类函数叫做聚合函数,例如sum()、avg()、max()等等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后的行数是要少于聚集前的行数的.但是有时我们想要既显示聚集前的数据,又要显示聚集后的数据,这时我们便引入了窗口函数.目前，我用的多的就是 row_number() , sum(), 解决topN 问题，分区排序，累积计数。hive中的窗口函数和sql中的窗口函数相类似,都是用来做一些数据分析类的工作,一般用于OLAP分析（在线分析处理）。
HIVE分析窗口函数:SUM,AVG,MIN,MAX
Qi.zheng
10-19 3947
本文转载自:lxw的大数据田地 » Hive分析窗口函数(一) SUM,AVG,MIN,MAX Hive中提供了越来越多的分析函数，用于完成负责的统计分析。抽时间将所有的分析窗口函数理一遍，将陆续发布。 今天先看几个基础的，SUM、AVG、MIN、MAX。 用于实现分组内所有和连续累积的统计。 Hive版本为 apache-hive-0.13.1 数据准备 CR
窗口函数：SUM、AVG、COUNT、MAX、MIN
不管多累生活也要精致
04-26 2037
窗口函数：SUM、AVG、COUNT、MAX、MIN -》功能：用于实现数据分区后的聚合 -》语法：fun_name(col1) over (partition by col2 order by col3) 实现功能 over 按照什么分区，分区内部按照什么排序 -》示例：实现分区内的累加，其他的原理类似 -》区别： 聚合函数：分组 group by ：一组返回一条 开窗聚合：分区 partition by：将相同的数据放到一起 –创建文件：vim /export/datas/window.txt coo
hive中max函数无法区分字符串类型的数字
H_hanwen的博客
10-24 7016
问题描述：需要针对版本号app_version如：6.14.2，6.7.1，10.2.1进行取最大值   原先方法：max(app_version)   结论：失败 原因：hive中使用max将会默认对两个字符串从左至右挨个进行比较，即进行第三位比较时发现：“1&lt;7”,故6.14.2&lt;6.7.1   解决方法：首先针对hive本身的函数max无法直接解决，则可以将数据...
hive 窗口函数
05-30
Hive窗口函数是一种在查询结果集的特定行上执行计算和分析的函数。它们通常与OVER子句一起使用，OVER子句指定了窗口的范围和排序方式。通过使用窗口函数，可以在不使用子查询或自连接的情况下，对结果集中的数据进行计算和分析。 Hive支持多种窗口函数，包括聚合函数和排名函数等。常见的窗口函数包括：SUM、AVG、MIN、MAX、ROW_NUMBER、RANK、DENSE_RANK等。 下面是一个示例，演示如何使用Hive窗口函数计算每个部门的平均工资： ``` SELECT department, name, salary, AVG(salary) OVER (PARTITION BY department) as avg_salary FROM employees; ``` 在上面的查询中，使用了AVG函数来计算每个部门的平均工资，OVER子句指定了按照部门进行分组，并对每个分组中的行执行AVG函数计算。

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值