百度360必应搜狗淘宝本站头条
mybatis 使用python中+=代表什么java中switch盒子垂直居中httpservletrequest 获取参数
当前位置：网站首页 > 技术文章 > 正文

大数据之Hive 窗口分析函数(HQL进阶)

wxin55 2024-10-25 18:04 9 浏览 0 评论

窗口函数(window functions)与普通聚合函数(aggregation functions)的区别

普通聚合函数是将多行具备相同属性的字段聚合为一行，处理的结果中不会对处理前的明细呈现出来。

而窗口函数可以认为是sql 增强函数，可以既展示集合后的数据结果，又可以展示聚合前的数据明细。

下面一个简单的例子对比下：

//表结构
+-----------+------------+----------+
| col_name  | data_type  | comment  |
+-----------+------------+----------+
| expodate  | string     |          |
| addcode   | string     |          |
| sbtid     | string     |          |
| rscode    | int        |          |
| rsname    | string     |          |
| scid      | int        |          |
+-----------+------------+----------+
select addcode,count(distinct scid) scid_num from tb_hive_window group by addcode;
+----------+-----------+
| addcode  | scid_num  |
+----------+-----------+
| 0002     | 2         |
| 000201   | 1         |
| 000202   | 1         |
| 000205   | 3         |
| 000206   | 2         |
| 000208   | 2         |
+----------+-----------+
select expodate,addcode,scid,count(distinct scid) over(partition by addcode)as scid_num from tb_hive_window;
+----------------------+----------+--------+-----------+
|       expodate       | addcode  |  scid  | scid_num  |
+----------------------+----------+--------+-----------+
| 2019-12-29 18:02:02  | 0002     | 36253  | 2         |
| 2019-12-29 18:02:01  | 0002     | 36251  | 2         |
| 2019-12-29 18:02:01  | 000201   | 36251  | 1         |
| 2019-12-29 18:02:04  | 000202   | 36258  | 1         |
| 2019-12-29 18:02:04  | 000205   | 37253  | 3         |
| 2019-12-29 18:02:04  | 000205   | 35253  | 3         |
| 2019-12-29 18:02:04  | 000205   | 36252  | 3         |
| 2019-12-29 18:02:04  | 000206   | 36258  | 2         |
| 2019-12-29 18:02:04  | 000206   | 38257  | 2         |
| 2019-12-29 18:02:04  | 000208   | 36257  | 2         |
| 2019-12-29 18:02:04  | 000208   | 36253  | 2         |
+----------------------+----------+--------+-----------+

通过对比上面的例子可以看出，通过窗口查询到了更加详细的明细数据，因此，根据不同的应用场景需求我们需要选择不同的函数处理方案。

sql 执行顺序

一条完整的select sql 语句时的执行顺序如下：

from... where...group by... having.... select ... over()... order by... limit

可以看出，窗口函数是在查询字段select后，order by 之前执行

普通聚合函数+over()

我们可以通过聚合函数sum()/count()/avg()/max()/min() 实现简单的计算

select addcode,rscode,count(*) over(partition by rscode order by rscode desc) from tb_hive_window where substring(expodate,1,10) = "2019-12-29";

select addcode,rscode,scid,avg(scid) over(partition by rscode order by rscode desc) from tb_hive_window where substring(expodate,1,10) = "2019-12-29";

select addcode,rscode,scid,sum(scid) over(partition by rscode order by rscode desc) from tb_hive_window where substring(expodate,1,10) = "2019-12-29"

+----------+---------+--------+---------------+
| addcode  | rscode  |  scid  | sum_window_0  |
+----------+---------+--------+---------------+
| 000205   | 395     | 35253  | 35253         |
| 0002     | 352     | 36253  | 36253         |
| 000208   | 351     | 36257  | 36257         |
| 000206   | 65      | 36258  | 36258         |
| 000206   | 40      | 38257  | 38257         |
| 000202   | 35      | 36258  | 182267        |
| 000208   | 35      | 36253  | 182267        |
| 000205   | 35      | 37253  | 182267        |
| 000205   | 35      | 36252  | 182267        |
| 000201   | 35      | 36251  | 182267        |
| 0002     | 34      | 36251  | 36251         |
+----------+---------+--------+---------------+

 select addcode,rscode,scid,sum(scid) over(partition by rscode order by scid desc) from tb_hive_window where substring(expodate,1,10) = "2019-12-29"
+----------+---------+--------+---------------+
| addcode  | rscode  |  scid  | sum_window_0  |
+----------+---------+--------+---------------+
| 0002     | 34      | 36251  | 36251         |
| 000205   | 35      | 37253  | 37253         |
| 000202   | 35      | 36258  | 73511         |
| 000208   | 35      | 36253  | 109764        |
| 000205   | 35      | 36252  | 146016        |
| 000201   | 35      | 36251  | 182267        |
| 000206   | 40      | 38257  | 38257         |
| 000206   | 65      | 36258  | 36258         |
| 000208   | 351     | 36257  | 36257         |
| 0002     | 352     | 36253  | 36253         |
| 000205   | 395     | 35253  | 35253         |
+----------+---------+--------+---------------+

需要特别注意下，对于over() 中的order by 问题，如果没有order by 排序或则是对partition 字段排序，那么sum 得到的结果是整合partition 中的此列所有值之和，如果是order by 非partition by字段，那么sum得到的是此partition中当前行此列与上一行此列值之和。

Analytics functions (分析函数)

row_number()：分组内记录的顺序，从1开始，记录不重复

select addcode,rscode,scid,row_number() over(partition by rscode order by scid desc) from tb_hive_window where substring(expodate,1,10) = "2019-12-29";
+----------+---------+--------+----------------------+
| addcode  | rscode  |  scid  | row_number_window_0  |
+----------+---------+--------+----------------------+
| 0002     | 34      | 36251  | 1                    |
| 000205   | 35      | 37253  | 1                    |
| 000202   | 35      | 36258  | 2                    |
| 000208   | 35      | 36253  | 3                    |
| 000205   | 35      | 36252  | 4                    |
| 000201   | 35      | 36251  | 5                    |
| 000206   | 40      | 38257  | 1                    |
| 000206   | 65      | 36258  | 1                    |
| 000208   | 351     | 36257  | 1                    |
| 0002     | 352     | 36253  | 1                    |
| 000205   | 395     | 35253  | 1                    |
+----------+---------+--------+----------------------+

rank()：分组中的排名，排名相等的将在下一个排名位置空出

dense_rank()：分组中的排名，排名相等的将在下一个排名位置不会空出，即上图中排名为 1 2 2 3 4

cume_dist()：分组中，小于或等于当前值的行数 / 分组中总行数

select addcode,rscode,scid,cume_dist() over(partition by rscode order by scid ) from tb_hive_window where substring(expodate,1,10) = "2019-12-29";
+----------+---------+--------+---------------------+
| addcode  | rscode  |  scid  | cume_dist_window_0  |
+----------+---------+--------+---------------------+
| 0002     | 34      | 36251  | 1.0                 |
| 000201   | 35      | 36251  | 0.2                 |
| 000205   | 35      | 36252  | 0.4                 |
| 000208   | 35      | 36253  | 0.6                 |
| 000202   | 35      | 36258  | 0.8                 |
| 000205   | 35      | 37253  | 1.0                 |
| 000206   | 40      | 38257  | 1.0                 |
| 000206   | 65      | 36258  | 1.0                 |
| 000208   | 351     | 36257  | 1.0                 |
| 0002     | 352     | 36253  | 1.0                 |
| 000205   | 395     | 35253  | 1.0                 |
+----------+---------+--------+---------------------+

percent_rank():分组内当前行的RANK值-1/分组内总行数-1

select addcode,rscode,scid,percent_rank() over(partition by rscode order by scid ) from tb_hive_window where substring(expodate,1,10) = "2019-12-29";
+----------+---------+--------+------------------------+
| addcode  | rscode  |  scid  | percent_rank_window_0  |
+----------+---------+--------+------------------------+
| 0002     | 34      | 36251  | 0.0                    |
| 000201   | 35      | 36251  | 0.0                    |
| 000205   | 35      | 36252  | 0.25                   |
| 000208   | 35      | 36253  | 0.5                    |
| 000202   | 35      | 36258  | 0.75                   |
| 000205   | 35      | 37253  | 1.0                    |
| 000206   | 40      | 38257  | 0.0                    |
| 000206   | 65      | 36258  | 0.0                    |
| 000208   | 351     | 36257  | 0.0                    |
| 0002     | 352     | 36253  | 0.0                    |
| 000205   | 395     | 35253  | 0.0                    |
+----------+---------+--------+------------------------+

lead()/lag() 函数

lag(field，num) ：滞后，即分组中向上第num 行field 字段的值

select addcode,rscode,scid,lag(scid,1) over(partition by rscode order by scid ) from tb_hive_window where substring(expodate,1,10) = "2019-12-29";
+----------+---------+--------+---------------+
| addcode  | rscode  |  scid  | lag_window_0  |
+----------+---------+--------+---------------+
| 0002     | 34      | 36251  | NULL          |
| 000201   | 35      | 36251  | NULL          |
| 000205   | 35      | 36252  | 36251         |
| 000208   | 35      | 36253  | 36252         |
| 000202   | 35      | 36258  | 36253         |
| 000205   | 35      | 37253  | 36258         |
| 000206   | 40      | 38257  | NULL          |
| 000206   | 65      | 36258  | NULL          |
| 000208   | 351     | 36257  | NULL          |
| 0002     | 352     | 36253  | NULL          |
| 000205   | 395     | 35253  | NULL          |
+----------+---------+--------+---------------+

lead(field，num) ：分组中向下第num 行field 字段的值

select addcode,rscode,scid,lead(scid,1) over(partition by rscode order by scid ) from tb_hive_window where substring(expodate,1,10) = "2019-12-29";
+----------+---------+--------+----------------+
| addcode  | rscode  |  scid  | lead_window_0  |
+----------+---------+--------+----------------+
| 0002     | 34      | 36251  | NULL           |
| 000201   | 35      | 36251  | 36252          |
| 000205   | 35      | 36252  | 36253          |
| 000208   | 35      | 36253  | 36258          |
| 000202   | 35      | 36258  | 37253          |
| 000205   | 35      | 37253  | NULL           |
| 000206   | 40      | 38257  | NULL           |
| 000206   | 65      | 36258  | NULL           |
| 000208   | 351     | 36257  | NULL           |
| 0002     | 352     | 36253  | NULL           |
| 000205   | 395     | 35253  | NULL           |
+----------+---------+--------+----------------+

first_value(): 分组中截止到当前行的第一个值

select addcode,rscode,scid,first_value(scid) over(partition by rscode order by scid ) from tb_hive_window where substring(expodate,1,10) = "2019-12-29";
+----------+---------+--------+-----------------------+
| addcode  | rscode  |  scid  | first_value_window_0  |
+----------+---------+--------+-----------------------+
| 0002     | 34      | 36251  | 36251                 |
| 000201   | 35      | 36251  | 36251                 |
| 000205   | 35      | 36252  | 36251                 |
| 000208   | 35      | 36253  | 36251                 |
| 000202   | 35      | 36258  | 36251                 |
| 000205   | 35      | 37253  | 36251                 |
| 000206   | 40      | 38257  | 38257                 |
| 000206   | 65      | 36258  | 36258                 |
| 000208   | 351     | 36257  | 36257                 |
| 0002     | 352     | 36253  | 36253                 |
| 000205   | 395     | 35253  | 35253                 |
+----------+---------+--------+-----------------------+

last_value():分组中截止到当前行的最后一个值

select addcode,rscode,scid,last_value(scid) over(partition by rscode order by scid ) from tb_hive_window where substring(expodate,1,10) = "2019-12-29";
+----------+---------+--------+----------------------+
| addcode  | rscode  |  scid  | last_value_window_0  |
+----------+---------+--------+----------------------+
| 0002     | 34      | 36251  | 36251                |
| 000201   | 35      | 36251  | 36251                |
| 000205   | 35      | 36252  | 36252                |
| 000208   | 35      | 36253  | 36253                |
| 000202   | 35      | 36258  | 36258                |
| 000205   | 35      | 37253  | 37253                |
| 000206   | 40      | 38257  | 38257                |
| 000206   | 65      | 36258  | 36258                |
| 000208   | 351     | 36257  | 36257                |
| 0002     | 352     | 36253  | 36253                |
| 000205   | 395     | 35253  | 35253                |
+----------+---------+--------+----------------------+

window 子句（控制窗口的子集）

preceding: 往前，例如 2 preceding 往前两行

current row:当前行

following：往后，例如 3 following 往后3行

unbounded preceding: 窗口最前面的行

unbounded following: 窗口最后一行

例如：

//前两行到当前行之间
select addcode,rscode,scid,last_value(scid) over(partition by rscode order by scid rows between 2 preceding and current row) from tb_hive_window where substring(expodate,1,10) = "2019-12-29";
//分组起点到当前行（如果不使用windwow子句，默认是从起点到当前行）
select addcode,rscode,scid,last_value(scid) over(partition by rscode order by scid rows between unbounded preceding and current row) from tb_hive_window where substring(expodate,1,10) = "2019-12-29";
//当前行当分组最后一行
select addcode,rscode,scid,last_value(scid) over(partition by rscode order by scid rows between current row and unbounded following) from tb_hive_window where substring(expodate,1,10) = "2019-12-29";

Hive Sql 窗口函数暂时就记这么多了，后面再说说Hive Sql 的常用函数和group by 分组处理

相关推荐

ES6中 Promise的使用场景?（es6promise用法例子）

一、介绍Promise，译为承诺，是异步编程的一种解决方案，比传统的解决方案（回调函数）更加合理和更加强大在以往我们如果处理多层异步操作，我们往往会像下面那样编写我们的代码doSomething(f...

JavaScript 对 Promise 并发的处理方法

Promise对象代表一个未来的值，它有三种状态：pending待定，这是Promise的初始状态，它可能成功，也可能失败，前途未卜fulfilled已完成，这是一种成功的状态，此时可以获取...

Promise的九大方法（promise的实例方法）

1、promise.resolv静态方法Promise.resolve(value)可以认为是newPromise方法的语法糖,比如Promise.resolve(42)可以认为是以下代码的语...

360前端一面~面试题解析（360前端开发面试题）

1.组件库按需加载怎么做的，具体打包配了什么-按需加载实现：借助打包工具（如Webpack的require.context或ES模块动态导入），在使用组件时才引入对应的代码。例如在V...

前端面试-Promise 的 finally 怎么实现的?如何在工作中使用?

Promise的finally方法是一个非常有用的工具，它无论Promise是成功（fulfilled）还是失败（rejected）都会执行，且不改变Promise的最终结果。它的实现原...

最简单手写Promise，30行代码理解Promise核心原理和发布订阅模式

看了全网手写Promise的，大部分对于新手还是比较难理解的，其中几个比较难的点：状态还未改变时通过发布订阅模式去收集事件实例化的时候通过调用构造函数里传出来的方法去修改类里面的状态，这个叫Re...

前端分享-Promise可以中途取消啦（promise可以取消吗）

传统Promise就像一台需要手动组装的设备，每次使用都要重新接线。而Promise.withResolvers的出现，相当于给开发者发了一个智能遥控器，可以随时随地控制异步操作。它解决了三大...

手写 Promise（手写输入法 中文）

前言都2020年了，Promise大家肯定都在用了，但是估计很多人对其原理还是一知半解，今天就让我们一起实现一个符合PromiseA+规范的Promise。附PromiseA+规范地址...

什么是 Promise.allSettled()!新手老手都要会?

Promise.allSettled()方法返回一个在所有给定的promise都已经fulfilled或rejected后的promise，并带有一个对象数组，每个对象表示对应的pr...

前端面试-关于Promise解析与高频面试题示范

Promise是啥，直接上图：Promise就是处理异步函数的API，它可以包裹一个异步函数，在异步函数完成时抛出完成状态，让代码结束远古时无限回掉的窘境。配合async/await语法糖，可...

宇宙厂:为什么前端离不开 Promise.withResolvers() ?

大家好，很高兴又见面了，我是"高级前端进阶"，由我带着大家一起关注前端前沿、深入前端底层技术，大家一起进步，也欢迎大家关注、点赞、收藏、转发。1.为什么需要Promise.with...

Promise 新增了一个超实用的 API!

在JavaScript的世界里，Promise一直是处理异步操作的神器。而现在，随着ES2025的发布，Promise又迎来了一个超实用的新成员——Promise.try()！这个新方法简...

一次搞懂 Promise 异步处理（promise 异步顺序执行）

PromisePromise就像这个词的表面意识一样，表示一种承诺、许诺，会在后面给出一个结果，成功或者失败。现在已经成为了主流的异步编程的操作方式，写进了标准里面。状态Promise有且仅有...

Promise 核心机制详解（promise机制的实现原理）

一、Promise的核心状态机Promise本质上是一个状态机，其行为由内部状态严格管控。每个Promise实例在创建时处于Pending（等待）状态，此时异步操作尚未完成。当异步操作成功...

javascript——Promise（js实现promise）

1.PromiseES6开始支持，Promise对象用于一个异步操作的最终完成（包括成功和失败）及结果值的表示。简单说就是处理异步请求的。之所以叫Promise，就是我承诺，如果成功则怎么处理，失败怎...

取消回复欢迎 你 发表评论:

一周热门
最近发表
标签列表