程序员经验分享-hwhale

hive窗口函数(开窗函数)

2023-11-07

一、【窗口函数概述】

窗口函数（Window functions）是一种SQL函数，非常适合于数据分析，因此也叫做OLAP函数，其最大特点是：输入值是从SELECT语句的结果集中的一行或多行的“窗口”中获取的。你也可以理解为窗口有大有小（行有多有少）。
通过OVER子句，窗口函数与其他SQL函数有所区别。如果函数具有OVER子句，则它是窗口函数。如果它缺少OVER子句，则它是一个普通的聚合函数。
窗口函数可以简单地解释为类似于聚合函数的计算函数，但是通过GROUP BY子句组合的常规聚合会隐藏正在聚合的各个行，最终输出一行，窗口函数聚合后还可以访问当中的各个行，并且可以将这些行中的某些属性添加到结果集中。
在这里插入图片描述
通过一下案例来初步体验窗口函数

----sum+group by普通常规聚合操作------------
select sum(salary) as total from employee group by dept;

----sum+窗口函数聚合操作------------
select id,name,deg,salary,dept,sum(salary) over(partition by dept) as total from employee;

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

二、窗口函数语法

Function(arg1,..., argn) OVER ([PARTITION BY <...>] [ORDER BY <....>] [<window_expression>])

--其中Function(arg1,..., argn) 可以是下面分类中的任意一个
    --聚合函数：比如sum max avg等
    --排序函数：比如rank row_number等
    --分析函数：比如lead lag first_value等

--OVER [PARTITION BY <...>] 类似于group by 用于指定分组  每个分组你可以把它叫做窗口
--如果没有PARTITION BY 那么整张表的所有行就是一组

--[ORDER BY <....>]  用于指定每个分组内的数据排序规则 支持ASC、DESC

--[<window_expression>] 用于指定每个窗口中 操作的数据范围 默认是窗口中所有行

三、举例：网站用户页面浏览次数分析

在网站访问中，经常使用cookie来标识不同的用户身份，通过cookie可以追踪不同用户的页面访问情况，有下面两份数据：
在这里插入图片描述
字段含义：cookieid 、访问时间、pv数（页面浏览数）
在这里插入图片描述
字段含义：cookieid、访问时间、访问页面url
在Hive中创建两张表表，把数据加载进去用于窗口分析。

---建表并且加载数据
create table website_pv_info(
   cookieid string,
   createtime string,   --day
   pv int
) row format delimited
fields terminated by ',';

create table website_url_info (
    cookieid string,
    createtime string,  --访问时间
    url string       --访问页面
) row format delimited
fields terminated by ',';


load data local inpath '/root/hivedata/website_pv_info.txt' into table website_pv_info;
load data local inpath '/root/hivedata/website_url_info.txt' into table website_url_info;

select * from website_pv_info;
select * from website_url_info;

3.1 窗口聚合函数
这里以sum()函数为例，其他聚合函数使用类似

-----窗口聚合函数的使用-----------
--1、求出每个用户总pv数  sum+group by普通常规聚合操作
select cookieid,sum(pv) as total_pv from website_pv_info group by cookieid;

--2、sum+窗口函数 总共有四种用法 注意是整体聚合 还是累积聚合
--sum(...) over( )对表所有行求和
--sum(...) over( order by ... ) 连续累积求和
--sum(...) over( partition by... ) 同组内所有行求和
--sum(...) over( partition by... order by ... ) 在每个分组内，连续累积求和

--需求：求出网站总的pv数 所有用户所有访问加起来
--sum(...) over( )对表所有行求和
select cookieid,createtime,pv,
       sum(pv) over() as total_pv
from website_pv_info;

--需求：求出每个用户总pv数
--sum(...) over( partition by... )，同组内所行求和
select cookieid,createtime,pv,
       sum(pv) over(partition by cookieid) as total_pv
from website_pv_info;

--需求：求出每个用户截止到当天，累积的总pv数
--sum(...) over( partition by... order by ... )，在每个分组内，连续累积求和
select cookieid,createtime,pv,
       sum(pv) over(partition by cookieid order by createtime) as current_total_pv
from website_pv_info;

在这里插入图片描述
在这里插入图片描述
3.2 窗口表达式
我们知道，在sum(…) over( partition by… order by … )语法完整的情况下，进行的累积聚合操作，默认累积聚合行为是：从第一行聚合到当前行。
Window expression窗口表达式给我们提供了一种控制行范围的能力，比如向前2行，向后3行。
语法如下：

关键字是rows between，包括下面这几个选项
- preceding：往前
- following：往后
- current row：当前行
- unbounded：边界
- unbounded preceding 表示从前面的起点
- unbounded following：表示到后面的终点

---窗口表达式
--第一行到当前行
select cookieid,createtime,pv,
       sum(pv) over(partition by cookieid order by createtime rows between unbounded preceding and current row) as pv2
from website_pv_info;

--向前3行至当前行
select cookieid,createtime,pv,
       sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and current row) as pv4
from website_pv_info;

--向前3行 向后1行
select cookieid,createtime,pv,
       sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5
from website_pv_info;

--当前行至最后一行
select cookieid,createtime,pv,
       sum(pv) over(partition by cookieid order by createtime rows between current row and unbounded following) as pv6
from website_pv_info;

--第一行到最后一行 也就是分组内的所有行
select cookieid,createtime,pv,
       sum(pv) over(partition by cookieid order by createtime rows between unbounded preceding  and unbounded following) as pv6
from website_pv_info;

3.3窗口排序函数
窗口排序函数用于给每个分组内的数据打上排序的标号。注意窗口排序函数不支持窗口表达式。总共有4个函数需要掌握：
【row_number】 在每个分组中，为每行分配一个从1开始的唯一序列号，递增，不考虑重复；
【rank:】 在每个分组中，为每行分配一个从1开始的序列号，考虑重复，挤占后续位置；
【dense_rank:】 在每个分组中，为每行分配一个从1开始的序列号，考虑重复，不挤占后续位置；

-----窗口排序函数
SELECT
    cookieid,
    createtime,
    pv,
    RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn1,
    DENSE_RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn2,
    ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn3
FROM website_pv_info
WHERE cookieid = 'cookie1';

在这里插入图片描述
上述这三个函数用于分组TopN的场景非常适合。

--需求：找出每个用户访问pv最多的Top3 重复并列的不考虑
SELECT * from
(SELECT
    cookieid,
    createtime,
    pv,
    ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS seq
FROM website_pv_info) tmp where tmp.seq <4;

在这里插入图片描述
还有一个函数，叫做ntile函数，其功能为：将每个分组内的数据分为指定的若干个桶里（分为若干个部分），并且为每一个桶分配一个桶编号。
如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差1。
有时会有这样的需求:如果数据排序后分为三部分，业务人员只关心其中的一部分，如何将这中间的三分之一数据拿出来呢?NTILE函数即可以满足。

--把每个分组内的数据分为3桶
SELECT
    cookieid,
    createtime,
    pv,
    NTILE(3) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn2
FROM website_pv_info
ORDER BY cookieid,createtime;

在这里插入图片描述

--理解：将数据根据cookieid分 根据pv倒序排序 排序之后分为3个部分 取第一部分
SELECT * from
(SELECT
     cookieid,
     createtime,
     pv,
     NTILE(3) OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn
 FROM website_pv_info) tmp where rn =1;

在这里插入图片描述
3.4窗口分析函数
LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值
第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）；
LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值
第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）；
FIRST_VALUE 取分组内排序后，截止到当前行，第一个值；
LAST_VALUE 取分组内排序后，截止到当前行，最后一个值；

-----------窗口分析函数----------
--LAG
SELECT cookieid,
       createtime,
       url,
       ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
       LAG(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS last_1_time,
       LAG(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS last_2_time
FROM website_url_info;


--LEAD
SELECT cookieid,
       createtime,
       url,
       ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
       LEAD(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS next_1_time,
       LEAD(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS next_2_time
FROM website_url_info;

--FIRST_VALUE
SELECT cookieid,
       createtime,
       url,
       ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
       FIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS first1
FROM website_url_info;

--LAST_VALUE
SELECT cookieid,
       createtime,
       url,
       ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
       LAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1
FROM website_url_info;

在这里插入图片描述

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

hive

Hadoop

数据仓库

hive窗口函数(开窗函数) 的相关文章

随机推荐

  • 深入Java微服务之网关系列2：常见Java网关实现方案对比

    什么是服务网关 前文我们已经了解了构建微服务的基础springboot 同时也能使用springboot构建服务 接下来我们就基于springboot聊一下springcloud 这个springcloud并不是一个特定的技术 它指的是微服

  • this关键字和super关键字异同

    this关键字 1 在同一类中成员变量和局部变量名称相同时 区分两者和调用成员变量解决两者冲突问题 2 同一类中调用调用构造方法 3 指明成员方法 super关键字 1 在父类和子类中有相同变量时 调用父类变量 2 调用父类构造方法 必须放

  • 国内时间同步 ntp服务器地址

    国内时间同步 ntp服务器地址 ntp sjtu edu cn 202 120 2 101 上海交通大学网络中心NTP服务器地址 s1a time edu cn 北京邮电大学 s1b time edu cn 清华大学 s1c time ed

  • mysql8安装和驱动jar包下载

    方式一 基于docker安装 下拉镜像 docker pull mysql 8 0 21 启动镜像 docker run p 3307 3306 name mysql e MYSQL ROOT PASSWORD hadoop d mysql

  • 路由ui-router

    路由ui router Angular ngRoute针对于单视图 而ui router可用于多视图 这里说的视图是指在页面内我们可控制的 可变化的区域 比如我们点击了一个link 我们需要在视图中跳转到指定的一个页面 那么ngRoute已

  • 【Electron-vue】构建桌面应用（30）- child_proccess多次输出结果

    使用child process启动子进程 并与子进程通信的时候 发现会有多条打印结果 其原因是 不同的操作会触发stdin write操作 而每一个操作都需要通过stdout on来监听返回结果 如果使用stdout on来监听返回结果 那

  • 千聊视频的爬取

    import requests import random import os filedir 摩羯座周期下的黄金市场 if not os path exists filedir os makedirs filedir print 目录创建

  • 【BZOJ 2219】【超详细题解】数论之神

    2219 数论之神 Time Limit 3 Sec Memory Limit 259 MB Submit 365 Solved 33 Submit Status Discuss Description 在ACM DIY群中 有一位叫做 傻

  • 电机控制学习之路：simulink仿真之速度环、电流环PI参数设计

    前言 首先声明 笔者在电机控制之路也只是一个新手 撰写本文主要是为了对自己学习内容做一个总结和记录 网上FOC双闭环控制时的PI调参方法种类繁多 笔者在看的眼花缭乱之后以德州仪器的InstaSPIN FOC and InstaSPIN MO

  • 英语专栏——shell account

    shell account 外壳账号 A class of cheap but restricted internet dial up access Instead of connecting the computer directly t

  • ensp模拟器中云设备的使用及相关问题解决办法

    ensp模拟器中云设备的使用及相关问题解决办法 eNSP工具中的云代表通过各种网络技术连接起来的计算机网络环境 目前可实现的功能包括 仿真设备之间建立映射关系 绑定网卡与仿真设备之间进行通信 以及通过开放UDP端口方式与外部程序进行通信 基

  • Oracle-SQL脚本记录

    多字段匹配关键词查询 旧写法 where email like abc or address like abc 组合查询写法 where concat email address like abc

  • c语言练习题 ATM机流程

    自学c语言自娱自乐的 看到有的练习题上有模拟ATM机流程的练习就试着写了一个 include

  • 开源的杀毒软件

    开源的杀毒软件 有 免费的午餐 我们为什么不吃呢 杀毒软件一定要购买或用D版吗 先别忙着下结论 请耐心看完本文 然后再告诉我你是怎么想的 一 ClamWin Free Antivirus 开源反病毒软件 GPL协议 SourceForge页

  • Vmware 分辨率设置

    1 点击查看 2 点击自动调整大小 3 选择自动适应客户机即可

  • 简单怕忘笔记

    1 and REGEXP LIKE 字段名 匹配串1 匹配串2 全模糊匹配 2 and REGEXP LIKE 字段名 匹配串1 匹配串2 右模糊匹配 3 and REGEXP LIKE 字段名 匹配串1 匹配串2 左模糊匹配 4 LTRI

  • GNSS精密单点定位（PPP）基本原理（进阶篇）

    上节介绍了精密单点定位的基本原理 本文继续在精密单点定位的基础上进行更深层次的介绍 一 精密单点定位的函数模型 上节说过 在精密单点定位之前 也有一种绝对定位技术 那就是伪距单点定位 伪距单点定位靠的伪距进行单点定位 但是伪距的精度较差 主

  • 字符串转int数据类型的三种方式

    方法一 Integer valueOf 它将返回一个包装器类型 Integer 当然可以通过自动拆箱的方式将其转成 int 类型 String a 100 String b 50 int A Integer valueOf a int B

  • 睿智的智能优化算法3——利用遗传算法实现字符串配对

    睿智的智能优化算法3 利用遗传算法实现字符串配对 字符串配对 求解过程 1 编码方式 2 计算适应度 3 自然选择 4 基因突变 5 个体杂交 实现代码 GITHUB下载连接 好久都没有用过遗传算法了 觉得需要重新复习一下 而且考虑到遗传算

  • hive窗口函数(开窗函数)

    一 窗口函数概述 窗口函数 Window functions 是一种SQL函数 非常适合于数据分析 因此也叫做OLAP函数 其最大特点是 输入值是从SELECT语句的结果集中的一行或多行的 窗口 中获取的 你也可以理解为窗口有大有小 行有多

热门标签