SQL窗口分析函数使用详解系列三之偏移量类窗口函数

这篇具有很好参考价值的文章主要介绍了SQL窗口分析函数使用详解系列三之偏移量类窗口函数。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

1.综述

本文以HiveSQL语法进行代码演示。

对于其他数据库来说同样也适用,比如SparkSQL,FlinkSQL以及Mysql8,Oracle,SqlServer等传统的关系型数据库。

已更新第一类聚合函数类,点击这里阅读 ①SQL窗口函数系列一之聚合函数类

②SQL窗口函数系列二之分组排序窗口函数

本节介绍Hive窗口分析函数中的第三类窗口函数:偏移量类窗口函数。

在实际的应用场景中,顾名思义,偏移量分析函数主要应用于求解和指定偏移数据的差值。例如和上一行数据差值,和下一行数据差值。

有什么实际意义呢?例如,每行数据是天粒度的,那么上下行的差值计算就是前后天的数据增长量或者减少量,比left join,right join的方式更为简单,效率更高。

1.1 偏移量类窗口函数

lead() over();
lag() over();
first_value() over();

1.2 窗口函数语法

分析函数 over(partition by 列名 order by 列名 rows between 开始位置 and 结束位置)

具体解析

over()括号内为空时,是直接进行计算。

其中partition by 列名 是按指定列进行分组,进而进行计算。

最后的order by 列名 是按照指定列进行排序,进而进行计算。

1.3 基础数据准备

create table if not exists temp.user_info (
  `id` bigint comment '用户id',
  `client` string comment '客户端',
  `gender` int comment '性别,0女1男',
  `constellation` string comment '星座',
  `age` int comment '年龄',
  `pv` bigint comment '访问量',
  `chat_num` bigint comment '聊天次数'
) comment '用户信息测试临时表' 

数据预览

id client gender constellation age pv chat_num
1 ios 0 处女座 29 174 3
2 ios 1 双鱼座 26 263 2
3 android 1 双鱼座 35 232 39
4 ios 1 水瓶座 32 57 3
5 ios 1 射手座 33 67 6
6 ios 1 双子座 36 81 5
7 ios 1 狮子座 29 68 4
8 ios 1 狮子座 28 19 3
9 ios 0 射手座 32 479 2
10 ios 1 白羊座 26 255 36

2.各偏移量函数的使用

2.1 lag

  • 功能

Lag函数用于获取指定列的前n(取决于偏移量的设置)个行的值,按照我们设定的分区以及排序规则。

  • 语法
lag(column_name, offset, default_value) over (partition by partition_col order by order_col)

column_name要查询的列名

offset 要查找的偏移量,即要获取的行数的偏移量,默认为1,例如往前1行或者n行。

default_value 一个可选的默认值(当没有找到前一个行时返回的值

  • 示例

按客户端分组,按id排序,取出上一行的年龄。

select id,client,age,lag(age,1,10) over(partition by client order by id) as lag_1_age from temp.user_info
where id <= 10
order by id;

数据结果

id client age lag_1_age
1 ios 29 10
2 ios 26 29
3 android 35 10
4 ios 32 26
5 ios 33 32
6 ios 36 33
7 ios 29 36
8 ios 28 29
9 ios 32 28
10 ios 26 32

可以看到id为1的用户没有上一行,所以取到的值为我设置的默认值10.如果不设置默认值,返回null

Id 为2的用户渠道的偏移值是id为1的用户的年龄。

  • 拓展使用

偏移量最常见的使用是当数据最细粒度为天粒度时,查询该用户的前一天行为和今天行为的差值或者相比上一日上涨或者下降百分比等。伪SQL

-- 这里省略了偏移量和默认值
select id,pv,dt,pv-lag_pv as gap_pv  -- 当日和上一日的pv差值 
from (
		select id,pv,dt,lag(pv) over(partition by id order by dt) as lag_pv from temp.user_pv_info
) a

2.2 lead

  • 功能

和lag类似,却刚好相反。是取向下的偏移量的值。进而进行差值计算等。

用于获取指定列的后n(取决于偏移量的设置)个行的值,按照我们设定的分区以及排序规则。

  • 语法
lead(column_name, offset, default_value) over (partition by partition_col order by order_col)

column_name要查询的列名

offset 要查找的偏移量,即要获取的行数的偏移量,默认为1,例如往前1行或者n行。

default_value 一个可选的默认值(当没有找到前一个行时返回的值)

  • 示例

按客户端分组,按id排序,取出下二行的年龄。

select id,client,age,lead(age,2,10) over(partition by client order by id) as lead_2_age from temp.user_info
where id <= 10
order by id;

数据结果

id client age lead_2_age
1 ios 29 32
2 ios 26 33
3 android 35 10
4 ios 32 36
5 ios 33 29
6 ios 36 28
7 ios 29 32
8 ios 28 26
9 ios 32 10
10 ios 26 10

如上,我把偏移量设置为2,可以看到id为9和10的向下两行没有数据。

  • 拓展使用

和lag使用场景一致,很多场景lag和lead都可以互换,需要设置排序是正序或者倒序的区别。

2.3 first_value

  • 功能

first_value用于返回分组中的第一个值,按指定的排序列。我们在使用中可以根据特定的排序规则来确定和查询获取每个分组的第一个值

  • 语法
first_value(expression) over(
	[partition by 列名1,列名2]
  [order by 列名3,列名4]
)

expression要获取第一个值的列或者表达式

partition by 用于指定分组的列

order by 用于指定排序的列

  • 示例

查询不同客户端,年龄最小的用户。

select id,client,age,first_value(age) over(partition by client order by age) as min_age from temp.user_info
where id <= 10
order by id;

数据结果

id client age min_age
1 ios 29 26
2 ios 26 26
3 android 35 35
4 ios 32 26
5 ios 33 26
6 ios 36 26
7 ios 29 26
8 ios 28 26
9 ios 32 26
10 ios 26 26

可以看到当前ios客户端的最小年龄为26,android客户端最小年龄为35.

  • 拓展使用

这样查有什么用呢?

例如可以进一步求解当前用户年龄和最小年龄或者最大年龄的差值。

如果是其他例如销售数据,或者活跃数据等,就更加有实用意义了。

总之,SQL窗口分析函数能够支持我们在更多的场景直接进行数据处理,进而更加深入和高效的进行数据分析

以上,关于SQL窗口函数的三类就更完了。后续更多以SQL每日一题的方式体现。

感谢阅读。

下一期:还没想好。

按例,欢迎点击此处关注我的个人公众号,交流更多知识。文章来源地址https://www.toymoban.com/news/detail-859139.html

到了这里,关于SQL窗口分析函数使用详解系列三之偏移量类窗口函数的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • sql 分组讨论,二级分组(非2个字段分组),使用 窗口函数和普通分组实现

    先按照一个字段分组,在按照 第二个字段分组。 之后,如果 这个 二级分组中的数据,是 1条的。就筛选出来。 比如: 先按照 站点分组,再按照 设备分组, 即:如果站点上配置了2个设备。就筛选出来。 然后:这2个设备 都必须是屏幕 查出配置了2个设备的站点 要求 这两个

    2024年02月13日
    浏览(37)
  • 【大数据Hive】Hive 窗口函数使用详解

    目录 一、前言 二、hive 窗口函数概述 2.1 聚合函数与窗口函数差别 2.1.1 创建一张表

    2024年02月11日
    浏览(42)
  • 开窗函数的使用详解(窗口范围ROWS与RANGE图文详解)

    函数名(参数) OVER (PARTITION BY子句 ORDER BY子句 ROWS/RANGE子句) 由三部分组成: 函数名:如sum、max、min、count、avg等聚合函数以及lead、lag行比较函数等; over: ,表示前面的函数是分析函数,不是普通的集合函数; 分组子句:over后面挂号内的内容; 分析子句又由下

    2024年02月04日
    浏览(74)
  • SQL函数 - 开窗(窗口)函数

    开窗函数对一组值进行操作,它不像普通聚合函数那样需要使用GROUP BY子句对数据进行分组,能够在同一行中同时返回基础行的列和聚合列 开窗函数的语法形式为:函数 + over(partition by 分组用列 order by 排序用列),表示对数据集按照分组用列进行分区,并且并且对每个分区按

    2024年02月14日
    浏览(53)
  • SQL—排序专用窗口函数

    下面介绍三种用于进行排序的专用窗口函数: 1、RANK()     在计算排序时,若存在相同位次,会跳过之后的位次。     例如,有3条排在第1位时,排序为:1,1,1,4······ 2、DENSE_RANK()     这就是题目中所用到的函数,在计算排序时,若存在相同位次,不会跳过之后的位

    2024年01月22日
    浏览(48)
  • SQL - 开窗(窗口)函数

    开窗函数对一组值进行操作,它不像普通聚合函数那样需要使用GROUP BY子句对数据进行分组,能够在同一行中同时返回基础行的列和聚合列 开窗函数的语法形式为:函数 + over(partition by 分组用列 order by 排序用列),表示对数据集按照分组用列进行分区,并且并且对每个分区按

    2024年02月12日
    浏览(44)
  • SQL-窗口函数

    什么是窗口函数 可以像聚合函数一样对一组数据进行分析并返回结果,二者的不同之处在于,窗口函数不是将一组数据汇总成单个结果,而是为每一行数据都返回一个结果。 窗口函数组成部分 1.创建数据分区 窗口函数OVER子句中的PARTITION BY选项用于定义分区,其作用类似于查

    2024年01月19日
    浏览(40)
  • 【SQL】MySQL中的窗口函数(开窗函数)

    窗口函数是 MYSQL8.0 新增的 聚合函数: 多行变一行,常见的sum,count,max,min 窗口函数: 行数不变,常见的row_number,rank 语法格式: 窗口函数(表达式) over (partition by … order by … frame_clause) partition by是分区,类似于group by,如去掉相当于对所有数据进行计算 order by排序 frame_c

    2024年02月07日
    浏览(50)
  • SQL助你面大厂(窗口函数)

           在面试过程中窗口函数的应用可谓是数不胜数,前提你要知道什么是窗口函数,最常用的窗口函数有哪些?语法是什么?分别用的场景是什么?今天会以这三个问题开始我们今天的学习 什么是窗口函数?        所谓的窗口函数就是动态处理数据的一类函数,也叫做

    2024年02月12日
    浏览(37)
  • 【sql高级】postgresql之窗口函数用法

    窗口函数在在SQL中是非常有用的工具,特别是在需要对查询结果进行分析、排名、聚合或者对结果进行一些特定的计算时。以下是一些常见的场景: 排名和分组:窗口函数可以轻松地对结果集进行排名、分组和分区。例如,你可以使用 ROW_NUMBER()、RANK()、DENSE_RANK() 等函数来为

    2024年03月23日
    浏览(45)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包