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

[wh62592855]

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

[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, integer [, default, any ]])
first_value(value, any)
last_value(value, any)
nth_value(value, any, nth, integer)

[wwwwb]

具体是什么分析函数?

[wh62592855]

[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

[wh62592855]

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

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

[ACMAIN_CHM]

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

[wh62592855]

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那样分什么管理 开发 概念之类

[wh62592855]

[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]这里没有列出sum
不过刚才我好像试了一下也可以哦

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]

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)

[ACMAIN_CHM]

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

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

[wh62592855]

呵呵 谢谢老大回答

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

[trainee]

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

[wh62592855]

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

[wh62592855]

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