开窗函数

最新推荐文章于 2024-06-03 14:11:42 发布
最新推荐文章于 2024-06-03 14:11:42 发布
阅读量918 收藏 3
点赞数 1
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_39128254/article/details/87910662
版权

MYSQl数据库


SELECT COUNT(*) FROM t_person;

结果

在这里插入图片描述

Hive

1001号 是01年级 成绩99 需求显示一下平均成绩 + 上原来的数据

在这里插入图片描述

Hive JDBC访问

启动hiveserver2服务

pp@hadoop102 hive]$ bin/hiveserver2

启动beeline

[pp@hadoop102 hive]$ bin/beeline
Beeline version 1.2.1 by Apache Hive
beeline>

连接hiveserver2

beeline> !connect jdbc:hive2://hadoop102:10000（回车）
Connecting to jdbc:hive2://hadoop102:10000
Enter username for jdbc:hive2://hadoop102:10000: atguigu（回车）
Enter password for jdbc:hive2://hadoop102:10000: （直接回车）
Connected to: Apache Hive (version 1.2.1)
Driver: Hive JDBC (version 1.2.1)
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:hive2://hadoop102:10000> show databases;
+----------------+--+
| database_name  |
+----------------+--+
| default        |
| hive_db2       |
+----------------+--+3

先做数据 数据准备：name，orderdate，cost

创建本地business.txt，导入数据

[pp@hadoop102 datas]$ vi business.txt

创建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;

[pp@hadoop101 datas]$ cat business.txt 
jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,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

0: jdbc:hive2://hadoop101:10000> use my_db;
No rows affected (0.036 seconds)
0: jdbc:hive2://hadoop101:10000> show tables;
+---------------+--+
|   tab_name    |
+---------------+--+
| business      |
| dept          |
| dept_par      |
| dept_par2     |
| emp           |
| emp_sex       |
| location      |
| movie_info    |
| person_info   |
| score         |
| stu_external  |
| student       |
+---------------+--+

查询business表 jack 哪个日期,购买数据的总额

0: jdbc:hive2://hadoop101:10000> select * from business;
+----------------+---------------------+----------------+--+
| business.name  | business.orderdate  | business.cost  |
+----------------+---------------------+----------------+--+
| jack           | 2017-01-01          | 10             |
| tony           | 2017-01-02          | 15             |
| jack           | 2017-02-03          | 23             |
| tony           | 2017-01-04          | 29             |
| jack           | 2017-01-05          | 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月份购买过的顾客及总人数

最终的结果

jack 2
mart 2

如何实现呢? 第一步 hive查询走 MR

select name
from business
where orderdate like '2017-04%'
group by name;

结果

+-------+--+       在这个的窗口下为聚合的窗口,
| name  |
+-------+--+
| jack  |
| mart  |
+-------+--+

没有使用开窗函数前

select name,count(*)
from business
where orderdate like '2017-04%'
group by name;

结果是这个 不能满足需求 这种情况下需要使用开窗函数

+-------+------+--+
| name  | _c1  |
+-------+------+--+
| jack  | 1    |
| mart  | 4    |
+-------+------+--+

over指的就是开窗函数, 默认的窗口是我们的整个窗口,over主要决定的是我们开窗函数的范围,count(*)这个聚合函数会被我们每条数据调用一次.

最终的接口

select name, count(*) over()
from business
where orderdate like '2017-04%'
group by name;

结果

+-------+-----------------+--+
| name  | count_window_0  |
+-------+-----------------+--+
| mart  | 2               |
| jack  | 2               |
+-------+-----------------+--+

执行逻辑先对name进行一次分组,得到一个窗口,在对这样的窗口为一个聚合窗口,over的作用就是指聚合的窗口,就是分组后整个窗口,聚合函数会被每一条数据都调用一次.

总结

over():结合聚合函数一起使用，决定数据的聚合范围，默认的聚合范围是整个数据窗口。

（2）查询顾客的购买明细及月购买总额 原始数据

+----------------+---------------------+----------------+--+
| business.name  | business.orderdate  | business.cost  |
+----------------+---------------------+----------------+--+
| jack           | 2017-01-01          | 10             |
| tony           | 2017-01-02          | 15             |
| jack           | 2017-02-03          | 23             |
| tony           | 2017-01-04          | 29             |
| jack           | 2017-01-05          | 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             |
+----------------+---------------------+----------------+--+

需求分析 第二的需求聚合的范围改变了,是按照月聚合的,那么这个需求如何实现? 首先要使用我们的聚合函数.

直接用over是按我们的整个窗口进行聚合的,eg: select *,sum(cost) over() 求得是我们的整个的总和,数据不对.

使用partition by 会按照我们的聚合范围进行一个分区,聚合的范围是按照我们分区后的数据进行的聚合.

查看month函数的用法

0: jdbc:hive2://hadoop101:10000> desc function extended month;
+----------------------------------------------------------------------------+--+
|                                  tab_name                                  |
+----------------------------------------------------------------------------+--+
| month(param) - Returns the month component of the date/timestamp/interval  |
| param can be one of:                                                       |
| 1. A string in the format of 'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd'.        |
| 2. A date value                                                            |
| 3. A timestamp value                                                       |
| 4. A year-month interval valueExample:                                     |
|   > SELECT month('2009-07-30') FROM src LIMIT 1;                           |
|   7                                                                        |
+----------------------------------------------------------------------------+--+

eg:

0: jdbc:hive2://hadoop101:10000> select month('2017-01-01');
+------+--+
| _c0  |
+------+--+
| 1    |
+------+--+

最终的sql语句 按月份进行一个分组

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

最终结果

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

如果不加partition by 的sql

select *, sum(cost) over()
from business;

​

不加partition by 的结果

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

其他语句的测试

0: jdbc:hive2://hadoop101:10000> select *, sum(cost) from business;
Error: Error while compiling statement: FAILED: SemanticException [Error 10025]: Expression not in GROUP BY key name (state=42000,code=10025)
0: jdbc:hive2://hadoop101:10000> select sum(cost) from business;

结果

+------+--+
| _c0  |
+------+--+
| 661  |
+------+--+

结论:

partition by:将数据进行分组后聚合

（3）上述的场景, 将每个顾客的cost按照日期进行累加

分析 聚合范围在动态变化,第一条聚合一条,第二条聚合两条,第三条聚合三条,

我们最终要得到的结果,如下图所示

+----------------+---------------------+----------------+--+
| business.name  | business.orderdate  | business.cost  | sum
+----------------+---------------------+----------------+--+
| jack           | 2017-01-01          | 10             | 10
| jack           | 2017-01-05          | 46             | 56
| jack           | 2017-02-03          | 23             | 79
| jack           | 2017-04-06          | 42             | 121
| tony           | 2017-01-02          | 15             | 15
| tony           | 2017-01-04          | 29             | 44

order by 排序,

sql语句 按照name进行分区,按照日期进行排序,在用sum进行求和

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

结果

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

结论

order by:(1) 排序，（2）改变聚合范围：从开始行到当前行的一个聚合(这也是它默认的聚合范围)

这种需求还可以 使用窗口子句实现来实现

CURRENT ROW：当前行
n PRECEDING：往前n行数据
n FOLLOWING：往后n行数据
UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING表示到后面的终点

窗口字句的sql

select *,sum(cost) over(partition by name order by orderdate 
rows between 1 PRECEDING and 1 FOLLOWING)
from business;

结果

+----------------+---------------------+----------------+---------------+--+
| business.name  | business.orderdate  | business.cost  | sum_window_0  |
+----------------+---------------------+----------------+---------------+--+
| jack           | 2017-01-01          | 10             | 56            |
| jack           | 2017-01-05          | 46             | 111           |
| jack           | 2017-01-08          | 55             | 124           |
| jack           | 2017-02-03          | 23             | 120           |
| jack           | 2017-04-06          | 42             | 65            |
| mart           | 2017-04-08          | 62             | 130           |
| mart           | 2017-04-09          | 68             | 205           |
| mart           | 2017-04-11          | 75             | 237           |
| mart           | 2017-04-13          | 94             | 169           |
| neil           | 2017-05-10          | 12             | 92            |
| neil           | 2017-06-12          | 80             | 92            |
| tony           | 2017-01-02          | 15             | 44            |
| tony           | 2017-01-04          | 29             | 94            |
| tony           | 2017-01-07          | 50             | 79            |
+----------------+---------------------+----------------+---------------+--+

需求变了,不是从当前行了,而是变成到最后行的累加

结论

窗口子句：对聚合数据进行进一步的动态划分

Spark SQL

hive中的HQL本质就是通过写sql语句,简化MapReduce的开发,

早期数据库叫DBA,很牛的.

数据要分们别类的管理起来. 但是学习的成本太高.

不会写sql,怎么办,出现了Hibernate = HQL + Java + XML 可以带替正个sql,但是数据库无法优化,应为它不知道业务.

最后被Mybatis给替换了.

大数据来了和我们的关系型数据库不一样,得写大量的MR,学习的成本是比较高的,那么能不能简化一下,只是mysql的数据量小,大叔的数据量比较多,于是Hive => HQL => MR,Hive出来了,通过写SQL来简化MR

SparkSQL把MR给替换了,但是每一个都写RDD比较麻烦

于是就出现了Shark => Hive, Shark是比较强大的.Shark把它变成RDD,在2013年的时候就把Shark这个框架给了Hive了,SparkSQL自己发明了自己的框架.思想来自于Hive,所以底层可以和Hive连接,可以Hive进行无缝对接.通过写SparkSQL可以自动转换为RDD.

SparkSQl就是参考了HIve然后形成了自己的语法.Hive走MR比较慢,而SparkSQl是转换的RDD,然后提交的集群执行,执行效率是非常快的.

DataFrame记录了数据的结构信息,即schema,

疯子@123
关注
  • 1
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
窗口函数OVER(PARTITION BY)详细用法——语法+函数+开窗范围ROWS和RANGE
Ahuuua的博客
10-01 2万+
窗口函数OVER(PARTITION BY)详细用法，PARTITION BY写法，ROWS和RANGE的区别，各种函数共功能说明
2023.11.16 hivesql高阶函数之开窗函数
白白的wj的博客
11-16 819
-当前行至最后一行--第一行到最后一行 也就是分组内的所有行。
开窗函数!
最新发布
htydowd的博客
06-03 273
开窗函数（Window Function）是SQL中的一种高级功能，允许你在一组相关行（一个“窗口”）上执行聚合操作，而不像传统聚合函数（如SUM(), AVG(), COUNT()）那样将所有匹配行合并成单个汇总行。分析函数：如LEAD(), LAG(), FIRST_VALUE(), LAST_VALUE()，用于访问同一窗口内的其他行数据。聚合函数：如SUM(), AVG(), COUNT(), MIN(), MAX()，但它们在每个窗口内计算，而不是整个结果集。
Hive 窗口函数（开窗函数） over
热门推荐
不是一枚开发
09-16 6万+
文章目录OVER函数1.含义：2.示例： OVER函数 1.含义： 窗口函数主要解决 分析整体数据时，多个数据和一个结果对应，为每一条数据开启指定行范围的窗口； over() 指定 分析函数 工作时的数据窗口大小，这个窗口大小可能会随行的改变而改变； 用于between … and … 表示范围概念： CURRENT ROW：当前行 n PRECEDING：往前n行数据 n FOLLOWING：往后n行数据 UNBOUNDED：起点， UNBOUNDED PRECEDING 表示从前面的起点， UNBOUN
开窗函数总结
m0_54423474的博客
06-30 8000
4.2.1，表的数据 4.2.3，开窗函数查询 1，结果如下： 2，结果如下，可以参照这个结果进行理解rows和range的区别 3，结果如下，可以用于获取当前数据行的 上次登录时间 的需求 4，结果如下，结合lead()函数 可以获取用户 上次登录时间与下次登录时间的 需求 5，结果如下，可以用于指定时间内最新或最旧数据的需求。 6，结果如下，可用于求比例的需求 7，结果如下： 7，结果如下： 8，结果如下 9，结果如下： ,10，结果
窗口函数的定义、分类及用法
下山化缘的DJ
07-16 2332
开窗函数的使用 定义： 开窗函数也叫分析函数，有两类：一类是聚合开窗函 数，一类是排序开窗函数。 格式：函数名(列) OVER(partition by 列名 order by列名) 使用：一般和 聚合函数使用，组成开窗函数， 常用聚合函数： count()求个数 sum() 求和 avg()求平均数 bai min()最小值 max()最大值 这些函数常与group by子句连用。除了 COUNT 以外，聚合函数忽略空值。 2 .OVER 关键字： OVER 关键字表示把函
SQL开窗函数的具体实现详解
09-08
SQL开窗函数是一种在SQL查询中执行复杂分析的高级技术，它允许你在同一查询中对一组相关行（称为“窗口”或“分组”）进行聚合操作，而不仅仅是整个结果集。这使得处理复杂的排序、分组和计算变得更加便捷。在2003年...
SQL中的开窗函数详解可代替聚合函数使用
12-14
开窗函数是SQL中一种强大的工具，它扩展了SQL的功能，使得在不使用GROUP BY的情况下也能执行聚合操作。开窗函数允许我们在一个查询中同时处理分组和非分组的数据，提高了查询的灵活性和效率。 在传统的SQL查询中，...
开窗函数有浅入深详解（一）
09-10
开窗函数是SQL查询中的一个强大工具，它允许在单个SQL语句中对一组行进行复杂的计算，而不仅仅是聚合操作。在SQL标准中，开窗函数是在2003年的ISO SQL标准引入的，目的是解决一些需要复杂子查询或存储过程才能解决的...
hive开窗函数知识点总结.txt
05-24
工作中用到了几个hive开窗函数,便想把hive开窗函数系统梳理一遍。 开窗函数 普通的聚合函数聚合的行集是组,开窗函数聚合的行集是窗口。因此,普通的聚合函数每组(Group by)只返回一个值，而开窗函数则可为窗口中的每...
开窗函数简介
奋斗的小青年
01-17 7149
与聚合函数一样，开窗函数也是对行集组进行聚合计算，但是普通聚合函数每组只能返回一个值，而开窗函数可以每组返回多个值。 实验一 比如我们想查询每个工资小于5000元的员工信息（城市以及年龄），并且在每行中都显示所有工资小于5000元的员工个数，执行下面的SQL语句 select t.fcity,t.fage,count(*) from person t where t.fsalary
mysql开窗函数有哪些_mysql开窗函数
weixin_29955689的博客
01-20 2400
开窗函数：它可以理解为记录集合，开窗函数也就是在满足某种条件的记录集合上执行的特殊函数。对于每条记录都要在此窗口内执行函数，有的函数随着记录不同，窗口大小都是固定的，这种属于静态窗口；有的函数则相反，不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口。开窗函数的本质还是聚合运算，只不过它更具灵活性，它对数据的每一行，都使用与该行相关的行进行计算并返回计算结果。开窗函数和普通聚合函数的区别聚合...
窗口函数(开窗函数)及OVER子句(1)：基础
黄邦勇帅的博客
06-15 1629
窗口函数及OVER子句(1)：基础 若觉得本文写得还可以，请多多关注本人所作书籍《C++语法详解》电子工业出版社 本文为原创文章，转载请注明出处，或注明转载自“黄邦勇帅(原名：黄勇) 一、窗口的概念 1、窗口(window)是标准SQL中的术语，可理解为是运算将要操作的行的集合，窗口与分组或分区是类似的，都是对表中的行进行的一种划分方法，分组由GROUP BY子句划分，而窗口是用OVER子句划分的...
窗口函数(开窗函数)及OVER子句(3)：OVER子句完整语法及各选项详解
黄邦勇帅的博客
06-27 4046
窗口函数及OVER子句(3)：OVER子句完整语法及各选项详解 若觉得本文写得还可以，请多多关注本人所作书籍《C++语法详解》电子工业出版社出版，作者 黄勇 本文为原创文章，转载请注明出处，或注明转载自“黄邦勇帅(原名：黄勇) 四、OVER子句完整语法 OVER子句完整语法如下： 为避免混淆，本小节会把分区子句称为PARTITION BY从属子句，排序子句称为ORDER BY从属子句 分区子句&...
深入浅出谈开窗函数（一）
web开发自学
04-30 2682
在开窗函数出现之前存在着很多用 SQL 语句很难解决的问题，很多都要通过复杂的相关子查询或者存储过程来完成。为了解决这些问题，在2003年ISO SQL标准加入了开窗函数，开窗函数的使用使得这些经典的难题可以被轻松的解决。目前在 MSSQLServer、Oracle、DB2 等主流数据库中都提供了对开窗函数的支持，不过非常遗憾的是 MYSQL 暂时还未对开窗函数给予支持。 为了更加清楚地...
窗口函数&开窗函数
weixin_43852674的博客
02-26 2359
文章目录创建数据语法rank排序例1例2rank，dense_rank与row_number例3 创建数据 #建立商品 create table goodsTable( id varchar(20) not null, name varchar(20), type varchar(20), price int) ENGINE=InnoDB DEFAULT CHARSET=utf8; # 插入...
hive 开窗函数理解区别range和row以及默认省略语句
xinqiku的博客
07-20 2571
数据准备：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 mar...
DB2 运用开窗函数统计数据
weixin_34179968的博客
09-17 778
SELECT T3.FRAMENO AS FRAMENO , CASE WHEN T3.WalkDistanceCount = 1 THEN 'L1' WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) < 4999 THEN 'L4' WHEN (T3.MaxWalkDistanc...

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

疯子@123

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值