PostgreSQL 高级SQL(三) 窗口函数

最新推荐文章于 2025-09-09 18:26:37 发布
原创 最新推荐文章于 2025-09-09 18:26:37 发布 · 631 阅读 · 1 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#postgresql #sql #数据库

本文介绍了在PostgreSQL中如何使用窗口函数over()来计算聚合值，特别是在创建报表时，例如计算国家年份GDP的平均值。通过实例展示了over()、partition by和order by子句的应用，解释了如何根据需求调整窗口以获取正确的平均值序列。

尚学堂给同学们带来全新的Java300集课程啦!java零基础小白自学Java必备优质教程_手把手图解学习Java，让学习成为一种享受_哔哩哔哩_bilibili

这一章节我们将了解postgresql 中聚合函数后面的over()子句，可能大家在工作的时候或多或少也涉及过over()子句的使用。 我们如果要实现一张这样的报表，这张报表有四列，国家名字，年份，年份GDP，1960-2018年该国家的GDP均值，第四列的结果的目的就是要拿对应年份的GDP和总的均值的GDP做比较，那么我可能会写出来这样的SQL: select ff.country_name,"year",gdp,cc.averg from country_gdp_year_final ff left join( select country_name,avg(gdp) as averg from country_gdp_year_final where country_code='CHN' group by country_name ) cc on cc.country_name=ff.country_name where ff.country_code='CHN'

不用窗口函数的均值列的获取 可以看到如果我们要在报表中多出一样这样的均值列的话，原生的SQL就只能以这种相似的方式来获取,然后做join，幸运的是PG为我们提供了这种原生的函数获取这种结果集，talk is cheap,show you the code! select country_name,"year",gdp,avg(gdp) over() from country_gdp_year_final f

最低0.47元/天 解锁文章
PostgreSQL之SQL开窗函数
奋斗的阿杰的博客
11-12 2022
PostgreSQL之SQL开窗函数
PostgreSQL之SQL高级特性
奋斗的阿杰的博客
11-11 812
1.with 查询 2.批量插入 3.returning 返回修改数据 4.upsert 5.数据抽样 6.聚合函数 7.窗口函数
PostgreSQL四类窗口函数总结
FlizhN的博客
04-29 4775
postgresql的窗口函数和Hive的hql基本一致，利用窗口函数能够解决绝大部分的常用业务数据分析需求。 先总结： 常用的4类窗口函数： 用于聚合计算的窗口函数：sum() over(); count() over(); avg() over; 用于分组排序的窗口函数：row_number() over(); rank() over(); 用于分组查询的窗口函数：ntile() o...
postgresql之窗口函数
youzi85的博客
11-25 5092
工作中可能会遇到按照部门业绩排名?找出前N的员工进行业绩奖励？两次消费时间隔了多久？等等这样的问题。对于这样的问题，使用窗口函数能很好的简化sql。并且窗口函数还可以配合连表使用，能简化语法，加快搜索速度。窗口函数是sql中一类特别的函数，通过查询筛选出的行的某些部分，窗口调用函数实现了类似于聚合函数的功能。但是两者又不同，通俗的讲，聚合函数是将结果合并成一行（每组一条数据），但是窗口函数是扫描所有的行，然后你的表有几行，结果就是有几行。
PostgreSQL 高级特性详解 —— 函数、高级查询、数据类型扩展、索引
最新发布
csdn_tom_168的博客
09-09 941
PostgreSQL 高级特性摘要 本文详细介绍了 PostgreSQL 的四大核心高级功能： 常用内置函数：涵盖字符串处理、数学运算和日期时间操作三类函数，如字符串截取、大小写转换、取整计算、时间戳提取等。 高级查询技术：包括子查询（标量子查询、派生表、EXISTS）、通用表表达式(CTE)和递归CTE，特别展示了递归CTE在树形结构查询中的应用。 窗口函数：提供分组内排序、累计计算等功能，支持PARTITION BY分组和ORDER BY排序。 这些特性使PostgreSQL能高效处理复杂查询逻辑和数据
Pgsql常用函数及方法（持续更新）
weixin_40863562的博客
05-18 5173
PostgreSQL是一个功能强大的开源对象关系型数据库系统，他使用和扩展了SQL语言，并结合了许多安全存储和扩展最复杂数据工作负载的功能，是目前功能最强大的开源数据库。
PostgresSQL的开窗函数
hanwb1的博客
03-12 2466
官方链接 4.2.8. 窗口函数调用   一个窗口函数调用表示在一个查询选择的行的某个部分上应用一个聚集类的函数。和非窗口聚集函数调用不同，这不会被约束为将被选择的行分组为一个单一的输出行 — 在查询输出中每一个行仍保持独立。不过，窗口函数能够根据窗口函数调用的分组声明（PARTITION BY列表）访问属于当前行所在分组中的所有行。一个窗口函数调用的语法是下列之一： function_name ([expression [, expression ... ]]) [ FILTER ( WHERE filt
18、PostgreSQL高级查询与窗口函数应用
c2d3e4f的博客
08-08 91
本文详细介绍了PostgreSQL中的高级查询技巧，包括窗口函数的应用、DISTINCT ON子句、集合返回函数、LATERAL连接以及特殊聚合函数的使用。通过实际案例解析，帮助读者更好地理解和掌握如何灵活运用这些功能来高效处理和分析数据库中的复杂数据。
10、PostgreSQL 高级 SQL 功能：分组集、窗口函数与分析
efc123456的博客
07-29 73
本文详细介绍了 PostgreSQL 中的多种高级 SQL 功能，包括分组集（GROUPING SETS、ROLLUP、CUBE）、有序集（用于中位数和最频繁值计算）、假设聚合（rank 函数）以及窗口函数的使用。通过具体示例展示了这些功能在数据分析中的强大能力，并探讨了性能优化策略，如合理使用 FILTER 子句、哈希聚合和索引。文章还提供了实际应用流程和注意事项，帮助开发者更高效地处理复杂查询和提升数据库性能。
PostgreSQL 高级SQL查询（三）
技术学习与分享
06-25 1077
窗口函数用于在查询结果集中执行计算，类似于聚合函数，但不缩小结果集的范围。子查询是嵌套在另一个查询中的查询，用于复杂的查询操作。可以分为两类：标量子查询和表子查询。左连接返回左表中的所有记录，即使右表中没有匹配的记录。右连接返回右表中的所有记录，即使左表中没有匹配的记录。全连接返回左表和右表中所有匹配的记录以及不匹配的记录。子句用于将结果集按照一个或多个列进行分组。内连接用于返回两个表中存在匹配关系的记录。子句用于过滤分组后的结果集。表子查询返回一个结果集，可以在。标量子查询返回单个值，可以在。
PostgreSQL高级查询与窗口函数全解析
### PostgreSQL高级查询与窗口函数全解析 在数据库操作中，掌握高级查询和窗口函数是提升数据处理能力和效率的关键。本文将深入探讨PostgreSQL中的高级查询语句，如MERGE、UPDATE...RETURNING、DELETE...RETURNING...
PG的SQL高级特性
xin_L_的博客
03-03 1950
sql高级特性
【PostgreSQL】使用开窗函数获取历史表最新记录 及 MySQL 旧版本实现
chenghan_yang的博客
03-19 2210
项目用了 PostgreSQL 作为数据库。用上了一直感兴趣的开窗函数，这里记录以下。考虑到使用MySQL的项目也可能有这种需求，思考 MySQL 8.0 以前的实现方式（8.0开始就有开窗函数了，不用自己造轮子）。探索的过程中，把 User-Defined Variables 也学起来了，结合官网资料做一下笔记。
PostgreSQL 的窗口函数 OVER, WINDOW, PARTITION BY, RANGE
IOsetting的专栏
05-27 2428
最近在数据处理中用到了窗函数, 把使用方法记录一下, 暂时只有分组排序和滑动时间窗口的例子, 以后再逐步添加. 在SQL查询时, 会遇到有两类需要分组统计的场景, 在之前的SQL语法中是不方便实现的. 使用窗函数直接SQL中使用窗函数就能解决这些问题, 否则需要使用临时表, 函数或存储过程进行处理. PostgreSQL 从2010年的版本8开始就支持窗函数了. 综合的说, 窗口函数就是在行的基础上, 允许对多行数据进行计算. 下面是一个简单的窗函数例子
PostgreSQL之高级数据选择
qq_34409900的博客
03-10 486
1、聚合函数： 聚集函数 描述 count(*) 提供行的计数 count(列名) 提供指定字段中值不是 NULL 的行的计数 min(列名) 返回指定列中的最小值 max(列名) 返回指定列中的最大值 sum(列名) 返回指定列的值的合计总数 avg(列名) 返回指定列的值的平均数 2、存在子查询（Existence Subq...
PostgreSQL 高级SQL(一)分组集
jmysql的博客
07-26 472
博主的数据是下载的CVS+XML,借助pgcopy命令和java解析xml的方式导入数据路的，在博主提供生成好的sql脚本以及java代码和copy导入过程俩种方式，有兴趣的读者可以自己尝试，代码在码云上(https//gitee.com/zhengxianlei/PGAdvanceSQL);作为一名开发者，平时工作中用到最多的可能就是SQL了，简单的SQL我们平时基本都用的差不多了，今天我们介绍一下PG的一些高级SQL，主要是PGSQL的分组集,这些SQL主要用于一些报表任务的开发。...
PostgreSQL用户应掌握的高级SQL特性
sweetfire的博客
08-22 460
PostgreSQL数据库在SQL和NoSQL方面具有很多丰富的特性，本文将先从SQL高级特性入手来进行介绍。 **一、PostgreSQL的SQL高级特性 ** 这一部分主要介绍PostgreSQL在SQL方面的高级特性，例如WITH查询、批量插入、RETURNING返回修改的数据、UPSERT、数据抽样、聚合函数、窗口函数等。   >>>阅读全文...
Postgresql从小白到高手 九 : psql高级查询及内部视图使用
simensk的博客
06-27 1461
pgsql 内部视图，pg高级查询
postgreSql利用sum() over()计算分组百分比
camary_niu的博客
07-07 5937
假设有下列结果集tmp： type num 1 5 2 5 需要根据type分组统计百分比如： type num percentage 1 5 50% 2 5 50% 使用sum() over()计算百分比的sql如下： SELECT tmp.type AS type, tmp.num AS num, -- 这里通过case when判断避免除0异常 CASE WHEN SUM (tmp.numm) ..
PostgreSQL窗口函数
08-25
### PostgreSQL 窗口函数使用指南 PostgreSQL 是一个功能强大的开源关系数据库管理系统，广泛应用于数据分析、Web 应用和企业级解决方案中。窗口函数是 PostgreSQL 中的一项重要特性，它允许在不减少行数的前提下，对一组相关行执行计算，从而实现复杂的数据分析任务 [^1]。 #### 窗口函数的基本语法结构如下： ```sql function_name() OVER ( [PARTITION BY <分组列>] [ORDER BY <排序列>] [windowing_clause] ) ``` 窗口函数与传统聚合函数不同之处在于，它不会将多行合并为一行输出，而是在每一行上保留原始数据，并附加基于窗口定义的计算值 。 #### 常见窗口函数分类及用途： - **排名函数**： - `ROW_NUMBER()`：为每一行分配唯一的序号。 - `RANK()`：相同值并列，后续排名跳跃。 - `DENSE_RANK()`：相同值并列，但排名连续 [^2]。 - **聚合窗口函数**： - `SUM()`, `AVG()`, `MIN()`, `MAX()`：在窗口范围内进行聚合计算。 - 常用于累计总和、区间平均值等场景 。 - **偏移函数**： - `LAG(column, offset, default)`：访问当前行之前的某一行数据。 - `LEAD(column, offset, default)`：访问当前行之后的某一行数据 [^1]。 - **分布函数**： - `CUME_DIST()`：计算当前行在窗口中的累积分布。 - `PERCENT_RANK()`：计算当前行的百分比排名 。 #### 示例：使用 ROW_NUMBER() 进行分组排名 ```sql SELECT employee_id, department_id, salary, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank FROM employees; ``` 该查询将按部门分组，对员工工资进行降序排列，并为每位员工分配一个部门内的排名 [^2]。 #### 示例：使用 LAG 函数比较前后行数据 ```sql SELECT employee_id, salary, LAG(salary, 1, 0) OVER (ORDER BY salary DESC) AS prev_salary FROM employees; ``` 该查询将显示每位员工的工资及其前一位员工的工资，便于进行数据比较 [^1]。 #### 示例：使用 SUM 窗口函数进行累计计算 ```sql SELECT order_date, amount, SUM(amount) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM orders; ``` 该查询将按订单日期顺序累计订单金额，生成运行总和 。 #### 窗口函数与 GROUP BY 的区别： - `GROUP BY` 会将数据合并为一组，每组只保留一行结果。 - 窗口函数则保留原始行数，同时添加基于窗口定义的计算值，适用于需要保留明细数据并附加聚合或排名信息的场景 [^1]。 #### 窗口函数的应用场景： - **排名分析**：如销售排名、成绩排名。 - **趋势分析**：如计算移动平均、增长率。 - **数据比较**：如比较当前行与前一行的数据。 - **窗口聚合**：如计算累计总和、区间平均值 。 PostgreSQL 的窗口函数功能非常强大，结合地理空间分析工具（如 PostGIS），可以实现更高级的数据分析任务。例如，以下查询展示了如何结合 `ST_Distance` 和 `RANK()` 进行地理距离排名 [^3]： ```sql SELECT location, timestamp, ST_Distance(location, 'POINT(0 0)') AS distance, RANK() OVER (ORDER BY distance) AS rank FROM gps_logs; ``` 该查询计算了每个 GPS 点与原点之间的距离，并对这些距离进行排名。 ---
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值