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

最新推荐文章于 2023-06-21 10:03:21 发布
最新推荐文章于 2023-06-21 10:03:21 发布
阅读量455 收藏 1
点赞数
分类专栏： HIVE

Hive中提供了越来越多的分析函数，用于完成负责的统计分析。
今天先看几个基础的，SUM、AVG、MIN、MAX。
用于实现分组内所有和连续累积的统计。

数据准备：

    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

 

X5-j
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
hive窗口函数.docx
07-08
1. 聚合类：avg()、sum()、max()、min() 等 2. 排名类：row_number()、rank()、dense_rank() 等 3. 其他类：lag()、lead()、ntile() 等 窗口函数应用场景 窗口函数常用于解决以下问题： * 数据排名和排序 * 数据...
Hive窗口函数，定义、demo
05-14
1. **聚合函数**：如`SUM()`, `AVG()`, `COUNT()`, `MIN()`, `MAX()`，它们在窗口内执行聚合操作。 2. **排名函数**：如`ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`，它们为窗口内的每一行分配一个唯一的排名。 3. **...
hive窗口函数
qq_34124060的博客
02-01 772
建立一张测试表 CREATE TABLE test (cookieid STRING, create_time STRING, pv INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','; 查看表数据 select * from test; +----------------+-------------------+----------+--+...
hive 最大值最小值max min greatest least详解
最新发布
weixin_45995752的博客
06-21 1135
2 min,max是求一列中的最大值最小值。所以传入的是一个列名。而greatest和least是求一行里面多列的最大值。所以传入参数是多列。但是只会对一行数据进行计算 ，因为他不是聚合函数！1 max和min是聚合函数，如果使用一般需要同步跟一个group by 而greatest 和least则不需要。如果greaat 和least 后面直接group by则会报错。首先max和greatest都是求最大值，min和least都是求最小值，区别在于2点。
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函数大全.doc
05-19
除此之外，还有除法（/）、模运算（%）、位运算（如BITWISE AND、BITWISE OR）等其他数学运算，以及各种聚合函数（如COUNT、SUM、AVG、MIN、MAX）和窗口函数（如LEAD、LAG、RANK、ROW_NUMBER等）。Hive还支持字符串...
【Hive】max函数和concat函数的结合使用，实现排序效果
一往无前
06-11 1409
需求：根据日期排序，取6月1号至6月10号这10天中不同id上报的最新的一条数据。 方法一：用开窗函数row_number。 select id ,info from ( select id ,info ,row_number() over(partition by id order by date desc) as rank from table1 w..
Hive 查询之分组
ITBOY_ITBOX博客
05-04 6441
Group By语句 GROUP BY语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。 案例实操： （1）计算emp表每个部门的平均工资 hive (default)> select t.deptno, avg(t.sal) avg_sal from emp t group by t.dep...
Hive：聚合函数、GROUP BY、ORDER BY、LIMIT、执行顺序和JOIN、函数
qq_43601664的博客
03-12 3197
HQL
hive中取最大值最小值的函数
热门推荐
qq_42456324的博客
09-18 1万+
max()和min()函数 select a,max(b) from t group by a select a,min(b) from t group by a max和min函数是取某一列中的最大或者最小值 greatest()和least()函数 select greatest(-1, 0, 5, 8) --8 select least(-1, 0, 5, 8) -- -1 greatest和least函数是取某一行中的最大或者最小值，但一定是比较相同类型的数据，如果数据类..
hive窗口函数实战(2)
bitcarmanlee的博客
04-08 1012
1.什么是窗口函数 在明白窗口函数的用途之前，我们先稍微提一下聚合函数，比如sum, count等常用的聚合函数，作用是将一列中多行的值合并为一行。与之对应的是，窗口函数完成的功能是本行内运算，从而多行的运算结果，即每一行的结果对应的多行的结果。一般窗口函数的语法为 function() over (partition by column1, column2 order by column3) ...
hive函数之~集合统计函数
weixin_42575593的博客
07-05 735
1、个数统计函数: count *** 语法: count(*), count(expr), count(DISTINCT expr[, expr_.]) 返回值: int 说明: count(*)统计检索出的行的个数，包括NULL值的行；count(expr)返回指定字段的非空值的个数；count(DISTINCT expr[, expr_.])返回指定字段的不同的非空值的个数 ...
hive中注意事项
p_max的专栏
10-08 2139
hive中不能直接访问非group by 的字段 注意： 如果出现 Expression not in GROUP BY key 可以使用 collect_set()返回array数组**。 任何时候,考虑是不是要用distinct 注意 : 在检索数据时,尤其是在JOIN,count(*)时,要多考虑要不要去重,有时如果忽略掉distinct会造成引发大量MR。 hive中NULL 和 “
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、付费专栏及课程。

余额充值