当前位置:网站首页>postgresql Window Functions
postgresql Window Functions
2022-08-09 06:59:00 【谷满满】
Window Functions
窗口函数不会像非窗口聚合调用那样使行分组到单个输出行中。相反,这些行保留了它们单独的身份。不可以和GROUP BY、 WHERE子句一起用。如果在执行窗口计算后需要过滤或分组行,则可以使用子选择。
SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
您还可以使用OVER中的顺序BY来控制窗口函数处理行的顺序。(窗口顺序BY甚至不必与行的顺序匹配。)下面是一个例子:
SELECT depname, empno, salary,
rank() OVER (PARTITION BY depname ORDER BY salary DESC)
FROM empsalary;
窗口函数结合where子句使用示例
SELECT depname, empno, salary, enroll_date
FROM
(SELECT depname, empno, salary, enroll_date,
rank() OVER (PARTITION BY depname ORDER BY salary DESC,
empno) AS pos
FROM empsalary
) AS ss
WHERE pos < 3;
当一个查询涉及多个窗口函数时,可以用一个单独的OVER子句写出每个窗口函数,但如果多个函数需要相同的窗口行为,这是重复的和容易出错的。相反,每个窗口行为都可以在窗口子句中命名,然后在OVER中引用。例如
SELECT sum(salary) OVER w, avg(salary) OVER w
FROM empsalary
WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);
边栏推荐
- 【Oracle 11g】Redhat 6.5 安装 Oracle11g
- 找出数组中不重复的值php
- Use of PlantUML plugin in idea
- crc计算
- Flask failed to create database without error
- AD画PCB板教程 20分钟讲清楚操作流程 铺铜 网络标号
- rsync:recv_generator: mkdir (in backup) failed:Permission denied (13) |failed to set times on '.'
- (本章节完结)排序第五节——非比较排序(计数排序+基数排序+桶排序)(附有自己的视频讲解)
- The JVM thread state
- 搭载开源鸿蒙系统的嵌入式XM-RK3568工业互联方案
猜你喜欢
随机推荐
leetcode 之盛水问题
db.sqlite3 has no "as Data Source" workaround
Flask failed to create database without error
图论,二叉树,dfs,bfs,dp,最短路专题
imageio读取.exr报错 ValueError: Could not find a backend to open `xxx.exr‘ with iomode `r`
Silently start over, the first page is also a new page
Service
【修电脑】系统重装但IP不变后VScode Remote SSH连接失败解决
Variable used in lambda expression should be final or effectively final报错解决方案
The working principle of the transformer (illustration, schematic explanation, understand at a glance)
字节也开始缩招了...
集合内之部原理总结
无重复的字符的最长子串
P6 ali machine test of 2020 Fibonacci number
【Docker】Docker安装MySQL
物理层课后作业
基于布朗运动的文本生成方法-LANGUAGE MODELING VIA STOCHASTIC PROCESSES
Use baidu EasyDL intelligent bin
MVN 中配置flyway mysq
DSP+ARM+FPGA高速PCIE/千兆网口信号仿真介绍