ManualsRelease 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那样分什么管理 开发 概念之类
这里没有列出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)
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)
Table 9-42. General-Purpose Aggregate FunctionsFunction 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 FunctionsFunction 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
我比较奇怪的就是avg并没出现在window functions里 可为什么还是可以使用over(partition by id)呢?
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)
我刚试了下row_number()是有的
http://www.postgresql.org/docs/8.4/interactive/functions-window.html
刚刚去搜了一下 搜到了这个链接
不过里面没有每个函数更加详细的讲解
请问一下又没有什么好点的地方或者文档可以学习POSTGRESQL
只是最近可能会在GREENPLUM上做开发
以前从来没用过 只用过ORACLE
所以现在想比较一下二者的不同之处学习学习POSTGRESQL的语法PS：你的小花多得过分……
直接到PostgreSQL 官网上下载它的文档就行了，不过不如ＯＲＡＬＣＥ的详细。
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那样分什么管理 开发 概念之类
不过刚才我好像试了一下也可以哦
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)
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)
可为什么sum和avg也同样可以使用啊?
聚集函数、窗口函数
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 FunctionsFunction 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
可为什么还是可以使用over(partition by id)呢?