hive 开窗函数理解区别range和row以及默认省略语句

最新推荐文章于 2024-05-04 04:24:37 发布
最新推荐文章于 2024-05-04 04:24:37 发布
阅读量2.5k 收藏 5
点赞数 2
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/xinqiku/article/details/96634579
版权

数据准备：name，orderdate，cost


jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-01,29
jack,2017-01-01,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94

需求

  1. 查询在2017年4月份购买过的顾客及总人数
  2. 查询顾客的购买明细及月购买总额
  3. 上述的场景,要将cost按照日期进行累加
  4. 查询顾客上次的购买时间
  5. 查询前20%时间的订单信息

创建hive表并导入数据

create table business(

name string,

orderdate string,

cost int

) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

 

load data local inpath "/opt/module/datas/business.txt" into table business;

按需求查询数据

查询在2017年4月份购买过的顾客及总人数

select name,count(*) over ()

from business

where substring(orderdate,1,7) = '2017-04'

group by name;

查询每个月购买总额及顾客的购买明细

select name,orderdate,cost,sum(cost) over(partition by month(orderdate)) from business;

上述的场景,要将cost按照日期进行累加

 

select name,orderdate,cost,
sum(cost) over() as s1,
sum(cost) over(partition by name) as s2,
sum(cost) over(partition by name order by orderdate ) as s3,
sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row ) as s4
from business;

+-------+-------------+-------+------+------+------+------+
| name  |  orderdate  | cost  |  s1  |  s2  |  s3  |  s4  |
+-------+-------------+-------+------+------+------+------+
| jack  | 2017-01-01  | 10    | 661  | 176  | 56   | 10   |
| jack  | 2017-01-01  | 46    | 661  | 176  | 56   | 56   |
| jack  | 2017-01-08  | 55    | 661  | 176  | 111  | 111  |
| jack  | 2017-02-03  | 23    | 661  | 176  | 134  | 134  |
| jack  | 2017-04-06  | 42    | 661  | 176  | 176  | 176  |
| mart  | 2017-04-08  | 62    | 661  | 299  | 62   | 62   |
| mart  | 2017-04-09  | 68    | 661  | 299  | 130  | 130  |
| mart  | 2017-04-11  | 75    | 661  | 299  | 205  | 205  |
| mart  | 2017-04-13  | 94    | 661  | 299  | 299  | 299  |
| neil  | 2017-05-10  | 12    | 661  | 92   | 12   | 12   |
| neil  | 2017-06-12  | 80    | 661  | 92   | 92   | 92   |
| tony  | 2017-01-01  | 29    | 661  | 94   | 29   | 29   |
| tony  | 2017-01-02  | 15    | 661  | 94   | 44   | 44   |
| tony  | 2017-01-07  | 50    | 661  | 94   | 94   | 94   |
+-------+-------------+-------+------+------+------+------+
 

select name,orderdate,cost,
sum(cost) over(range between unbounded preceding and current row) as s1,
sum(cost) over(partition by name range between unbounded preceding and current row) as s2,
sum(cost) over(partition by name order by orderdate range between unbounded preceding and current row) as s3,
sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row ) as s4,

sum(cost) over(partition by name  rows between UNBOUNDED PRECEDING and current row ) as s5
from business;

+-------+-------------+-------+------+------+------+------+
| name  |  orderdate  | cost  |  s1  |  s2  |  s3  |  s4  |
+-------+-------------+-------+------+------+------+------+
| jack  | 2017-01-01  | 10    | 661  | 176  | 56   | 10   |
| jack  | 2017-01-01  | 46    | 661  | 176  | 56   | 56   |
| jack  | 2017-01-08  | 55    | 661  | 176  | 111  | 111  |
| jack  | 2017-02-03  | 23    | 661  | 176  | 134  | 134  |
| jack  | 2017-04-06  | 42    | 661  | 176  | 176  | 176  |
| mart  | 2017-04-08  | 62    | 661  | 299  | 62   | 62   |
| mart  | 2017-04-09  | 68    | 661  | 299  | 130  | 130  |
| mart  | 2017-04-11  | 75    | 661  | 299  | 205  | 205  |
| mart  | 2017-04-13  | 94    | 661  | 299  | 299  | 299  |
| neil  | 2017-05-10  | 12    | 661  | 92   | 12   | 12   |
| neil  | 2017-06-12  | 80    | 661  | 92   | 92   | 92   |
| tony  | 2017-01-01  | 29    | 661  | 94   | 29   | 29   |
| tony  | 2017-01-02  | 15    | 661  | 94   | 44   | 44   |
| tony  | 2017-01-07  | 50    | 661  | 94   | 94   | 94   |
+-------+-------------+-------+------+------+------+------+
 

两者运行结果一致, 说明省略的 即range between unbounded preceding and current row

官方给出了说省略 的是row between unbounded preceding and unbounded following

如果有order by 则是省略的range between unbounded preceding and unbounded following

个人解释:

s1  range没有partition 所以current row 没有界限 和unbounded following 一致 结果(此处不再显示演示结果) 

s2  unbounded preceding 即 同一个partition中的首行 不受order by 影响, current row 即当前行 ,当前行即name=jack , 因为此处为range开窗范围

range 代表name=jack的所有行, 所以结果为5个jack行之和, 关于range和row的区别请参照 https://blog.csdn.net/xinqiku/article/details/96362796

s3 unbounded preceding 即 同一个partition中的首行 不受order by 影响, current row 受order by orderdate影响 , 当前行即 name=jack, orderdate =2017-01-01 联合, 又因为是range开窗 所以结果为2行name=jack, orderdate =2017-01-01之和 (如果current row 改成unbounded following , unbounded following 则不受order by 影响 而直接指向partition的末行 结果为jack五行之和 ,此处不再显示演示结果)

s4 unbounded preceding 即 同一个partition中的首行 , current row 因为是row 开窗范围, 即当前行name=jack这一行的和, 如果当前行移到下一行,即为当前行之前的所有行之和

此为刚接触hive 总结规律所写, 不当之处请大佬留言赐教!

 

查询顾客上次的购买时间

select 
name, orderdate, cost,
lag(orderdate, 1 ,'1970-01-01') over(partition by name order by orderdate)
from
business

查询前20%时间的订单信息

SELECT *
FROM (
    SELECT *
        ,ntile(5) OVER (
            ORDER BY orderdate
            ) n
    FROM orders
    ) t
WHERE t.n = 1;

解释:

SELECT *
        ,ntile(5) OVER (
            ORDER BY orderdate
            ) n
    FROM orders

+----------------+---------------------+----------------+----+
| business.name  | business.orderdate  | business.cost  | n  |
+----------------+---------------------+----------------+----+
| jack           | 2017-01-01          | 10             | 1  |
| tony           | 2017-01-01          | 29             | 1  |
| jack           | 2017-01-01          | 46             | 1  |
| tony           | 2017-01-02          | 15             | 2  |
| tony           | 2017-01-07          | 50             | 2  |
| jack           | 2017-01-08          | 55             | 2  |
| jack           | 2017-02-03          | 23             | 3  |
| jack           | 2017-04-06          | 42             | 3  |
| mart           | 2017-04-08          | 62             | 3  |
| mart           | 2017-04-09          | 68             | 4  |
| mart           | 2017-04-11          | 75             | 4  |
| mart           | 2017-04-13          | 94             | 4  |
| neil           | 2017-05-10          | 12             | 5  |
| neil           | 2017-06-12          | 80             | 5  |
+----------------+---------------------+----------------+----+

输出为后续加了 n字段数字分为5桶 每桶为一样数字, 让后再查询此表 得出前20%数据

ntile 参考 https://blog.csdn.net/BabyFish13/article/details/78550601

 

新奇酷
关注
  • 2
    点赞
  • 踩
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
【xlwings api语言参考】Range.Row 属性
DataLab的博客
05-21 306
功能： 返回单元格区域中第一个区域第一行的行号。只读。 语法： rng.Row rng是一个表示 Range 对象的变量。 示例： 本例返回指定单元格区域第一行的行号。 import xlwings as xw app=xw.App() bk=app.books.active sht=bk.sheets.active rng=sht.api.Range('B2:D5') print(rng.Row) ...
hive开窗函数中range和rows的区别
weixin_42307036的博客
01-08 6052
rows是物理窗口，是哪一行就是哪一行，与当前行的值（order by key的key的值）无关，只与排序后的行号相关 range是逻辑窗口，与当前行的值有关（order by key的key的值） select id ,sum(id) over(order by id) default_sum ,sum(id) over(order by id range between unbounded preceding and current row) range_sum ,sum(id) over(orde.
大数据最全hive开窗函数总结
最新发布
2401_84160087的博客
05-04 353
窗口大小可以通过 rows between …and…
[20180917]关于分析函数的range与rows的区别.txt
09-17 194
[20180917]关于分析函数的range与rows的区别.txt --//这几天看文档<Oracle SQL Revealed.pdf>,主要想了解学习分析函数这方面内容. --//遇到一个问...
数据仓库-hive 窗口函数中的rows和range的区分
DolphinF的博客
07-13 563
datediff(buy_date,'2021-01-01’) 这个值找【datediff(buy_date,'2021-01-01’)-6，datediff(buy_date,‘2021-01-01’)】中间的key，然后根据这个范围内的所有key进行窗口函数计算，这个范围内的key结果都相同，rang限定的是key的窗口，突破了物理的行，不要在考虑行的问题，相同key一起处理。相同key的窗口函数的值是相同的，尽管不在同一行，已经不在考虑行，只考虑key的值。比如近几天，近几个等等。
分析函数用法及窗口子句 range/rows差别
落叶无声的专栏
05-05 4969
分析函数的语法结构一般是： 分析函数名(参数) OVER (PARTITION BY子句 ORDER BY子句 ROWS/RANGE子句)。 即由以下三部分组成： 分析函数名：如sum、max、min、count、avg等聚集函数以及lead、lag行比较函数等； over： 关键字，表示前面的函数是分析函数，不是普通的集合函数； 分析子句：over关键字后面挂号内的内容； 分析子句又由下
hive开窗函数知识点总结.txt
05-24
工作中用到了几个hive开窗函数,便想把hive开窗函数系统梳理一遍。 开窗函数 普通的聚合函数聚合的行集是组,开窗函数聚合的行集是窗口。因此,普通的聚合函数每组...开窗函数一般分为两类,聚合开窗函数和排序开窗函数。
hive开窗函数详细介绍
03-28
本文将详细探讨Hive的开窗函数，包括ROW_NUMBER、RANK、DENSE_RANK以及分析窗口函数SUM、AVG、MIN和MAX的用法。 一、窗口函数 1. ROW_NUMBER() ROW_NUMBER()函数为每个分区内的记录分配一个唯一的连续编号，根据...
hive开窗函数.docx
08-24
hive开窗函数
HIVE函数汇总-开窗函数篇
04-02
【Hive 开窗函数详解】 开窗函数是 Hive 数据处理中的一个重要工具，它允许用户在不使用 Group By 子句的情况下对数据进行聚合操作。这种功能使得数据分析更加灵活，可以在保持原始数据行的同时进行复杂的计算。 ...
Hive开窗函数测试-cube，rollup
04-19
为了更好地理解这些概念，你可以参考提供的文档“Hive开窗函数测试.docx”。这个文档应该包含了具体的示例和代码，展示了如何在Hive SQL查询中使用窗口函数、cube和rollup。通过实践这些示例，你可以更深入地了解...
hive开窗函数之rows与range区别
weixin_52953102的博客
05-09 611
hive rows与range区别
Hive函数之窗口（开窗）函数
dgqg1223
08-07 895
官方介绍 LanguageManual WindowingAndAnalytics 一句话解释开窗作用 使用开窗函数可以在一个select语句中且不使用子查询的情况下，显示同一指标不同维度的数据。（这么说虽然片面，但是便于理解，实际开窗函数的作用远远超出该描述，具体请看下面案例理解） 开窗函数格式 格式：over(partition by 要分组字段**,order by** 要排序的字段 window_clause) 其中 partition by ，order by ，window_clause 均为可
Hive中的炸裂、窗口函数及示例
qq_43147161的博客
12-24 6899
Hive中的炸裂函数和窗口函数的基本语法与示例
开窗函数的使用详解(窗口范围ROWS与RANGE图文详解)
Appreciate(欣赏)
02-20 5828
开窗函数，窗口范围ROWS与RANGE
MYSQL窗口函数（Rows & Range）——滑动窗口函数用法
WHYbeHERE的博客
11-17 8859
总结SQL开窗函数中range 和rows的用法，实现滑动窗口计算功能。
Hive中使用over()实现累积求和和滑动求和
Vulpes corsac
06-08 4333
1.累积求和 实现累积求和，使用sum()函数配合over()来实现，具体的实现语法如下： sum(需要求和的列) over(partition by 分组列 order by 排序列 asc/desc)
SQL窗口函数的使用
weixin_44133552的博客
04-22 1941
@SQLTOC 欢迎使用Markdown编辑器 你好！ 这是你第一次使用 Markdown编辑器 所展示的欢迎页。如果你想学习如何使用Markdown编辑器, 可以仔细阅读这篇文章，了解一下Markdown的基本语法知识。 新的改变 我们对Markdown编辑器进行了一些功能拓展与语法支持，除了标准的Markdown编辑器功能，我们增加了如下几点新功能，帮助你用它写博客： 全新的界面设计 ，将会带来全新的写作体验； 在创作中心设置你喜爱的代码高亮样式，Markdown 将代码片显示选择的高亮样式 进行展示
Hive sql 中 over 总结
leveretz的博客
06-20 1877
原文地址：https://blog.csdn.net/qq_28289405/article/details/81326767
hive开窗函数使用
07-28
Hive开窗函数主要分为排序开窗和聚合开窗两类。在排序开窗函数中，常用的函数有row_number()、rank()、dense_rank()和percent_rank()。其中，row_number()用于给每一行分配一个唯一的行号，rank()用于对组内的行进行排名，dense_rank()也是对组内的行进行排名，但排名是连续的，而percent_rank()用于计算给定行的百分比排名，可以用来计算超过了百分之多少的人。\[3\] 在使用Hive开窗函数时，可以通过rows/range between来控制窗口函数的范围。这个功能在满足业务需求时非常有用。通过使用rows/range between，可以根据自己的需求任意地控制窗口函数的范围。\[1\] 需要注意的是，在Hive中，开窗函数的使用是在2003年ISO SQL标准中引入的，它解决了许多用SQL语句难以解决的问题。在开窗函数出现之前，很多问题都需要通过复杂的相关子查询或存储过程来解决。而开窗函数的使用使得这些经典的难题可以轻松地解决。\[2\] #### 引用[.reference_title] - *1* *3* [大数据-Hive开窗函数](https://blog.csdn.net/MsSpark/article/details/122051503)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^control,239^v3^insert_chatgpt"}} ] [.reference_item] - *2* [Hive SQL主要开窗函数用法介绍](https://blog.csdn.net/weixin_43025027/article/details/124388152)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^control,239^v3^insert_chatgpt"}} ] [.reference_item] [ .reference_list ]

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值