SQL常用窗口函数

最新推荐文章于 2024-08-03 23:10:08 发布
最新推荐文章于 2024-08-03 23:10:08 发布
阅读量5.8k 收藏 98
点赞数 12
文章标签： sql 大数据 数据库 窗口函数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_44670803/article/details/125844287
版权
本文详细介绍了SQL中的窗口函数，包括排名函数（row_number(), rank(), dense_rank()）、聚合函数（max(), min(), avg(), sum(), median()）、向前/向后取值（lag(), lead()）、百分位（percent_rank()）以及取值和分箱函数。通过实例展示了这些函数的用法和效果，适用于数据分析和OLAP操作。
摘要由CSDN通过智能技术生成

SQL运行顺序

在这里插入图片描述

窗口函数

窗口函数是一种分析型的 OLAP（Online Anallytical Processing，联机分析处理）函数，意思是对数据库数据进行实时分析处理。

语法

select  <窗口函数> over ( partition by <分组列>， order by <排序列> )

分类

  • 排名函数：row_number()，rank()，dense_rank()
  • 聚合函数：max()，min()，count()，sum()，avg()，median()
  • 向前向后取值：lag()，lead()
  • 百分位：percent_rank()
  • 取值函数：first_value()，last_value()，nth_value()
  • 分箱函数：ntile()

使用说明

1、排名函数
函数用法
row_number()同薪不同名，相当于行号，例如3000、2000、2000、1000排名后为1、2、3、4
rank()同薪同名，有跳级，例如3000、2000、2000、1000排名后为1、2、2、4
dense_rank()同薪同名，无跳级，例如3000、2000、2000、1000排名后为1、2、2、3
2、聚合函数
函数用法
max()最大值
min()最小值
avg()平均值
sum()求和
median()中位数

示例：

select sales_id, qty,
max(qty) over (order by sales_id rows unbounded preceding) as max_value
from winsales
order by sales_id;
---------------------
sales_id| qty | max
---------+-----+-----
10001 |  10 |  10
10005 |  30 |  30
10006 |  10 |  30
20001 |  20 |  30
20002 |  20 |  30
30001 |  10 |  30
30003 |  15 |  30
30004 |  20 |  30
30007 |  30 |  30
40001 |  40 |  40
40005 |  10 |  40
3、向前向后取值
函数用法
lag(field,n,default)前N行，在一次查询中取出当前行的同一字段（field参数）的前面第n行的数据，如果没有用default代替
lead(field,n,default)后N行，在一次查询中取出当前行的同一字段（field参数）的后面第n行的数据，如果没有用default代替

lag() 示例：
以下示例显示 买家 ID 为 3 的购票时间、购票数。
需求是：将每次购票与上一购票进行比较，返回每次购票的上一购票数。

select buyer_id , buy_time, buynum,
lag(buynum,1) over (order by buyer_id, buy_time) as pre_buynum
from sales 
where buyer_id= 3 
order by buyer_id, buy_time;

--------------------------------------------------------
buyer_id |      buy_time       | buynum | pre_buynum
---------+---------------------+---------+--------------
3 | 2008-01-16 01:06:09 |       1 |
3 | 2008-01-28 02:10:01 |       1 |            1
3 | 2008-03-12 10:39:53 |       1 |            1
3 | 2008-03-13 02:56:07 |       1 |            1
3 | 2008-03-29 08:21:39 |       2 |            1
3 | 2008-04-27 02:39:01 |       1 |            2
3 | 2008-08-16 07:04:37 |       2 |            1
3 | 2008-08-22 11:45:26 |       2 |            2
3 | 2008-09-12 09:11:25 |       1 |            2
3 | 2008-10-01 06:22:37 |       1 |            1
3 | 2008-10-20 01:55:51 |       2 |            1
3 | 2008-10-28 01:30:40 |       1 |            2

lead() 示例：
以下示例提供了 SALES 表中于 2008 年 1 月 1 日与 1 月 2 日已售票的事件的佣金以及为后续销售中售票所付的佣金。

select eventid, commission, saletime,
lead(commission, 1) over (order by saletime) as next_comm
from sales 
where saletime between '2008-01-01 00:00:00' and '2008-01-02 12:59:59'
order by saletime;

--------------------------------------------------------
eventid | commission |      saletime       | next_comm
---------+------------+---------------------+-----------
6213 |      52.05 | 2008-01-01 01:00:19 |    106.20
7003 |     106.20 | 2008-01-01 02:30:52 |    103.20
8762 |     103.20 | 2008-01-01 03:50:02 |     70.80
1150 |      70.80 | 2008-01-01 06:06:57 |     50.55
1749 |      50.55 | 2008-01-01 07:05:02 |    125.40
8649 |     125.40 | 2008-01-01 07:26:20 |     35.10
2903 |      35.10 | 2008-01-01 09:41:06 |    259.50
6605 |     259.50 | 2008-01-01 12:50:55 |    628.80
6870 |     628.80 | 2008-01-01 12:59:34 |     74.10
6977 |      74.10 | 2008-01-02 01:11:16 |     13.50
4650 |      13.50 | 2008-01-02 01:40:59 |     26.55
4515 |      26.55 | 2008-01-02 01:52:35 |     22.80
5465 |      22.80 | 2008-01-02 02:28:01 |     45.60
5465 |      45.60 | 2008-01-02 02:28:02 |     53.10
7003 |      53.10 | 2008-01-02 02:31:12 |     70.35
4124 |      70.35 | 2008-01-02 03:12:50 |     36.15
1673 |      36.15 | 2008-01-02 03:15:00 |   1300.80
...
4、百分位
函数用法
percent_rank()计算分组内每一行所在的百分比排名

以下示例计算每个卖家的销售数量所在的百分比排名：

select sellerid, qty, 
percent_rank() over (partition by sellerid order by qty) 
from winsales;

----------------------------------------
sellerid	qty		percent_rank
----------------------------------------
1		10.00		0.0
1		10.64		0.5
1		30.37		1.0
3		10.04		0.0
3		15.15		0.33
3		20.75		0.67
3		30.55		1.0
2		20.09		0.0
2		20.12		1.0
4		10.12		0.0
4		40.23		1.0
5、取值函数
函数用法
first_value()分组内第一行的值
last_value()分组内最后一行的值
nth_value(expr, n)分组内第n行的值
6、分箱函数

函数 用法

函数用法
ntile(n)将分区中已排序的行划分为大小尽可能相等的n个已排名组（等频分箱）

以下示例将于 2008 年 8 月 26 日购买 Hamlet 门票所付价格划分到四个排名组中。结果集为 17 个行，几乎均匀地划分到排名 1 到 4 中：

select eventname, caldate, pricepaid, 
ntile(4) over(order by pricepaid desc) from sales, event, date
where sales.eventid=event.eventid 
	  and event.dateid=date.dateid 
	  and eventname='Hamlet'
	  and caldate='2008-08-26'
order by 4;
--------------------------------------------
eventname |  caldate   | pricepaid | ntile
-----------+------------+-----------+-------
Hamlet    | 2008-08-26 |   1883.00 |     1
Hamlet    | 2008-08-26 |   1065.00 |     1
Hamlet    | 2008-08-26 |    589.00 |     1
Hamlet    | 2008-08-26 |    530.00 |     1
Hamlet    | 2008-08-26 |    472.00 |     1
Hamlet    | 2008-08-26 |    460.00 |     2
Hamlet    | 2008-08-26 |    355.00 |     2
Hamlet    | 2008-08-26 |    334.00 |     2
Hamlet    | 2008-08-26 |    296.00 |     2
Hamlet    | 2008-08-26 |    230.00 |     3
Hamlet    | 2008-08-26 |    216.00 |     3
Hamlet    | 2008-08-26 |    212.00 |     3
Hamlet    | 2008-08-26 |    106.00 |     3
Hamlet    | 2008-08-26 |    100.00 |     4
Hamlet    | 2008-08-26 |     94.00 |     4
Hamlet    | 2008-08-26 |     53.00 |     4
Hamlet    | 2008-08-26 |     25.00 |     4
7、移动平均
函数用法
n PRECEDING前n行
n FOLLOWING后n行
CURRENT ROW当前行
UNBOUNDED PRECEDING窗口第一行
UNBOUNDED FOLLOWING窗口的最后一行
酱汁儿999
关注
  • 12
    点赞
  • 踩
  • 98
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
SQL篇-常用窗口函数
qq_27093831的博客
05-09 5672
前言 在我们日常数据分析中，常常遇到要在组内排名、组内取topn等等需求，例如下面的业务需求： 排名问题：每个品牌的商品按销售额来排名 topN问题：找出每个品牌排名前N的商品 面对这类需求，就需要使用sql的高级功能窗口函数了。窗口函数是个神奇的存在，可以把多行代码变一行处理，把复杂的子查询变简单。让我们来一起了解窗口函数吧！ 1、什么是窗口函数 窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），作用于一个数据集合。窗口函数的一个概念就是当前
SQL server之窗口函数应用
博客
02-15 1572
1.首先先简单描述一下SQL server的存储过程，通俗的说，就是将一个或多个Transact-SQL语句分组到逻辑单元中，并作为对象存储在数据库服务器中。 当第一次调用存储过程时，SQL Server会创建执行计划并将其存储在计划缓存中。 在之后的存储过程执行中，SQL Server重用该程序，以便存储过程可以非常快速地执行并具有可靠的性能. 下面是几种简单的窗口函数 1.cume_dist() 计算一组值中值的累积分布 场景:生成一个报表，其中包含数据集中的顶部或底部有x%值，如销售人员占销售额的
sql — 窗口函数
qq_47343046的博客
04-25 4053
统计窗口函数则用于进行统计计算，包括count、sum、avg、min、max、first_value、last_value、lag、lead、cume_dist。排序窗口函数主要用于对数据进行排序和排名，包括row_number、rank、dense_rank、percent_rank、ntile；与常规聚合函数不同，窗口函数可以在不影响查询结果集的情况下，对结果集中的每一行应用函数，生成额外的信息，例如排名、累计和等。count函数计算结果集中行的数量，可以结合分组函数使用，用于统计分组内的行数。
SQL窗口函数详解
weixin_45771607的博客
07-05 1264
窗口函数（Window Functions）是SQL中的一种功能强大的工具，用于对查询结果集中的每一行进行计算，而无需对数据进行分组（GROUP BY）。与聚合函数不同，窗口函数不会将行汇总到一条记录中，而是保留原始行，并在此基础上添加计算结果。窗口函数常用于排名、累积和、滑动窗口计算等。窗口帧定义了窗口函数计算的行范围。窗口帧由ROWS或RANGE子句指定。从2、3中，不难看出，这有一个易混淆的点（也是本人混淆过的点），那就是。
SQL中的窗口函数
最新发布
zhangyifang_009的博客
08-03 3311
窗口函数是SQL中的一项高级特性，用于在不改变查询结果集行数的情况下，对每一行执行聚合计算或者其他复杂的计算，也就是说窗口函数可以跨行计算，可以扫描所有的行，并把结果填到每一行中。这些函数通常与OVER()子句一起使用，可以定义窗口或分区，并在上面执行计算，使用窗口函数，可以使许多难以处理的棘手问题变得较为容易。窗口函数的特点输入多行（一个窗口），返回一个值：窗口函数为每行数据进行一次计算，但不会改变原始查询结果集的行数计算方式灵活：可以使用字句将数据分区，并使用order by。
SQL 中的窗口函数
qq_43430343的博客
02-17 3145
SQL 中的窗口函数（Window Functions）是一种特殊的函数，它可以在查询结果的某个区间内执行计算，而不仅仅是对单个行进行计算。
SQL-窗口函数
m0_53191230的博客
01-17 5324
​ 什么是窗口函数 可以像聚合函数一样对一组数据进行分析并返回结果，二者的不同之处在于，窗口函数不是将一组数据汇总成单个结果，而是为每一行数据都返回一个，窗口函数OVER子句中的PARTITION BY选项用于定义分区，其作用类似于查询语句中的GROUP BY子句。如果我们指定了分区选项，窗口函数将会分别针对每个分区单独进行分析。
sql常用函数-窗口函数
qq_43314839的博客
01-13 995
窗口函数详细语法：https://zhuanlan.zhihu.com/p/92654574 题目：https://www.nowcoder.com/practice/90778f5ab7d64d35a40dc1095ff79065?tpId=199&tags=&title=&difficulty=0&judgeStatus=0&rp=0 法1：使用窗口函数row_number： select device_id, university, gpa from
Hive SQL大厂必考常用窗口函数及面试题.pdf
02-28
【Hive SQL大厂必考常用窗口函数及面试题】主要涵盖了窗⼝函数在大数据分析中的应用，尤其在OLAP（在线分析处理）场景中的重要性。窗⼝函数是一种标准SQL功能，它允许对数据库数据进行实时分析处理，如市场分析、...
大厂SQL经典面试题（一）窗口函数
Firstlucky77的博客
05-23 1811
窗口函数是数据分析师常用的语法函数，几乎是大厂的必考题。 窗口函数，也叫OLAP(Online AnallyticalProcessing,联机分析处理) ,可 以对数据库数据进行实时分析处理。 下面给大家准备了两道经典的窗口函数题目。并准备了清晰的解题思路，具体可以看图片。 第一题：求连续天数 下面表格是用户访问表users,记录了用户id (usr_ id)和访问日期(log_ date)，求出连续3天下面表格是用户访问表users,记录了用户id以上访问的用户id。 解题思路..
4.SQL常用函数汇总
zhangke0426的博客
03-10 8306
SQL常用函数
SQL窗口函数
qq_43661117的博客
06-20 1818
窗口函数，也可以称为OLAP函数或分析函数。 它既可以分组，也可以排序，即兼具分组与排序功能，对于每一组能返回多行。 聚合函数是将某列多行中的值根据聚合规则返回（合并）为一行，对于每组只返回一行。 基本语法 <窗口函数> over (partition by <列清单> order by <排序用列清单>) 大体分类： 能够作为窗口函数的聚合函数：...
sql的窗口函数
zhangyida01的博客
10-13 2728
学习窗口函数的笔记
Lua中的错误处理与调试类
mozhi
02-23 1017
一、错误处理 pcall：返回两个参数值，第一个标记为true/false，第二个为错误信息；
SQL 窗口函数
前端精读周刊
03-28 2857
窗口函数形如：表达式OVER(PARTITIONBY分组字段ORDERBY排序字段)有两个能力：当表达式为 rank() dense_rank() row_number() 时，拥有分组排序能力。当表达式为 sum() 等聚合函数时，拥有累计聚合能力。无论何种能力，窗口函数都不会影响数据行数，而是将计算平摊在每一行。这两种能力需要区分理解。底表以上是示例底表...
SQL--窗口函数
weixin_43238031的博客
02-17 1274
参考文章： 通俗易懂的学会：SQL窗口函数 1.窗口函数语法 <窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>) 2.窗口函数有以下功能： 1）同时具有分组（partition by）和排序（order by）的功能 2）不减少原表的行数，所以经常用来在每组内排名。group by 会减少分组的行数 3.注意事项 窗口函数原则上只能写在select子句中 4.rank, dense_rank, r
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值