postgresql里有没有像oracle中的那类分析函数?

2010-04-12 04:09:19 559 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

PostgreSQL 实现Oracle分析函数之keep keep是Oracle下的另一个分析函数，他的用法不同于通过over关键字指定的分析函数。使用KEEP 时和DENSE_RANK FIRST /DENSE_RANK LAST一起使用，获取一组中排名第一或者排名最后的记录。必须有order by 子句用来排序。后面也可以接over()分析函数部分。 SELECT deptno, MIN(t.mgr) KEEP (DENSE_RANK FIRST O... 阅读详情

postgresql里有没有像oracle中的那类分析函数?
如果没有的话,有没有什么其他办法可以实现类似功能呢?

...全文

Oracle转PostgreSQL Oracle转PostgreSQL PostgreSQL 13.1 手册阿里云帮助中心前言从2019年开始，就有一个很火热的话题：“去O化”。O就是oracle，也就是将oracle替换成别的数据库。原因： oracle是收费的，为了进一步降低成本；以美国为首的西方国家对华科技种种遏制行为，最近越闹越厉害，最近docker的付费服务就禁止中国企业使用；甲骨文公司中国区大幅度裁员，或将放弃中国市场也说不定； 2020年12月31起，甲骨文公司将不再对oracle11.2版本提供技术支持，阅读详情

wh62592855

MySQL 57,062 成员 56,759 内容

+ 关注 + 加入社区

切换为时间正序

wh62592855 2010-04-13

打赏
举报

我比较奇怪的就是avg并没出现在window functions里
可为什么还是可以使用over(partition by id)呢?

wh62592855 2010-04-13

打赏
举报

Table 9-42. General-Purpose Aggregate Functions

Function Argument Type Return Type Description
array_agg(expression) any array of the argument type input values concatenated into an array
avg(expression) smallint, int, bigint, real, double precision, numeric, or interval numeric for any integer-type argument, double precision for a floating-point argument, otherwise the same as the argument data type the average (arithmetic mean) of all input values
bit_and(expression) smallint, int, bigint, or bit same as argument data type the bitwise AND of all non-null input values, or null if none
bit_or(expression) smallint, int, bigint, or bit same as argument data type the bitwise OR of all non-null input values, or null if none
bool_and(expression) bool bool true if all input values are true, otherwise false
bool_or(expression) bool bool true if at least one input value is true, otherwise false
count(*) bigint number of input rows
count(expression) any bigint number of input rows for which the value of expression is not null
every(expression) bool bool equivalent to bool_and
max(expression) any array, numeric, string, or date/time type same as argument type maximum value of expression across all input values
min(expression) any array, numeric, string, or date/time type same as argument type minimum value of expression across all input values
sum(expression) smallint, int, bigint, real, double precision, numeric, or interval bigint for smallint or int arguments, numeric for bigint arguments, double precision for floating-point arguments, otherwise the same as the argument data type sum of expression across all input values
xmlagg(expression) xml xml concatenation of XML values (see also Section 9.14.1.7)

Table 9-44. General-Purpose Window Functions

Function Return Type Description
row_number() bigint number of the current row within its partition, counting from 1
rank() bigint rank of the current row with gaps; same as row_number of its first peer
dense_rank() bigint rank of the current row without gaps; this function counts peer groups
percent_rank() double precision relative rank of the current row: (rank - 1) / (total rows - 1)
cume_dist() double precision relative rank of the current row: (number of rows preceding or peer with current row) / (total rows)
ntile(num_buckets integer) integer integer ranging from 1 to the argument value, dividing the partition as equally as possible
lag(value any [, offset integer [, default any ]]) same type as value returns value evaluated at the row that is offset rows before the current row within the partition; if there is no such row, instead return default. Both offset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to null
lead(value any [, offset integer [, default any ]]) same type as value returns value evaluated at the row that is offset rows after the current row within the partition; if there is no such row, instead return default. Both offset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to null
first_value(value any) same type as value returns value evaluated at the row that is the first row of the window frame
last_value(value any) same type as value returns value evaluated at the row that is the last row of the window frame
nth_value(value any, nth integer) same type as value returns value evaluated at the row that is the nth row of the window frame (counting from 1); null if no such row

trainee 2010-04-13

打赏
举报

postgresql的文挡很详细。官方网站有啊。
聚集函数、窗口函数

wh62592855 2010-04-12

打赏
举报

呵呵谢谢老大回答

那上面那两个分析函数呢?我在那个MANUALS里也看到说只有你在#1列出的那些窗口函数
可为什么sum和avg也同样可以使用啊?

ACMAIN_CHM 2010-04-12

打赏
举报

[Quote]就只有MANUALS这一篇文档是吗?
不像oracle那样分什么管理开发概念之类[/Quote]

是的，免费的你让它拿什么请人去写那么复杂的文档啊。

wh62592855 2010-04-12

打赏
举报

avg也一样

gputf8=# select * from t2;

 id | name 

----+------

  2 |    3

  2 |    7

  3 |    3

  1 |    2

  1 |    3

(5 rows)



gputf8=# select id,name,avg(name) over(partition by id) avg from t2;

 id | name | avg 

----+------+-----

  2 |    3 |   5

  2 |    7 |   5

  3 |    3 |   3

  1 |    2 | 2.5

  1 |    3 | 2.5

(5 rows)

wh62592855 2010-04-12

打赏
举报

gputf8=# select id,name,sum(id) over(partition by id) sum from t1 order by id;

 id | name | sum 

----+------+-----

  1 | wp   |   2

  1 | wh   |   2

  2 | wjj  |   6

  2 | kkkk |   6

  2 | wr   |   6

(5 rows)



gputf8=# select * from t1;

 id | name 

----+------

  2 | wr

  2 | wjj

  2 | kkkk

  1 | wh

  1 | wp

(5 rows)

wh62592855 2010-04-12

打赏
举报

Manuals

Release Online Version Downloadable Version
8.4 •With user comments
•Without user comments
•Comprehensive Manual: A4 PDF (17 MB)
•Comprehensive Manual: US PDF (17 MB)
•Windows help file (3.3 MB)

8.3 •With user comments
•Without user comments
•Comprehensive Manual: A4 PDF (16.5 MB)
•Comprehensive Manual: US PDF (16.7 MB)
•Windows help file (3 MB)

8.2 •With user comments
•Without user comments
•Comprehensive Manual: A4 PDF (14.1 MB)
•Comprehensive Manual: US PDF (14.1 MB)

8.1 •With user comments
•Without user comments
•Comprehensive Manual: A4 PDF (12.1 MB)
•Comprehensive Manual: US PDF (12.1 MB)

8.0 •With user comments
•Without user comments
•Comprehensive Manual: A4 PDF (9.8 MB)
•Comprehensive Manual: US PDF (9.9 MB)

7.4 •With user comments
•Without user comments
•Comprehensive Manual: A4 PDF (9.0 MB)
•Comprehensive Manual: US PDF (9.1 MB)

就只有MANUALS这一篇文档是吗?
不像oracle那样分什么管理开发概念之类

ACMAIN_CHM 2010-04-12

打赏
举报

请问一下又没有什么好点的地方或者文档可以学习POSTGRESQL
直接到PostgreSQL 官网上下载它的文档就行了，不过不如ＯＲＡＬＣＥ的详细。

wh62592855 2010-04-12

打赏
举报

[Quote=引用 2 楼 wwwwb 的回复:]
具体是什么分析函数?
[/Quote]呵呵现在倒还没具体到某个函数
只是最近可能会在GREENPLUM上做开发
以前从来没用过只用过ORACLE
所以现在想比较一下二者的不同之处学习学习POSTGRESQL的语法

PS：你的小花多得过分……

wh62592855 2010-04-12

打赏
举报

[Quote=引用 1 楼 acmain_chm 的回复:]
有如下函数。

Function
row_number()
rank()
dense_rank()
percent_rank()
cume_dist()
ntile(num_buckets, integer)
lag(value, any [, offset, integer [, default, any ]])
lead(value, any [, offset, int……
[/Quote]谢谢
我刚试了下row_number()是有的
http://www.postgresql.org/docs/8.4/interactive/functions-window.html
刚刚去搜了一下搜到了这个链接
不过里面没有每个函数更加详细的讲解
请问一下又没有什么好点的地方或者文档可以学习POSTGRESQL

wwwwb 2010-04-12

打赏
举报

具体是什么分析函数?

ACMAIN_CHM 2010-04-12

打赏
举报

有如下函数。

Function
row_number()
rank()
dense_rank()
percent_rank()
cume_dist()
ntile(num_buckets, integer)
lag(value, any [, offset, integer [, default, any ]])
lead(value, any [, offset, integer [, default, any ]])
first_value(value, any)
last_value(value, any)
nth_value(value, any, nth, integer)

postgresql里有没有像oracle中的那类分析函数?

相关推荐