楼主,你的SQL我也是第一次看到,我也没有使用过,我不一定比你强,大家一起学习我试验过了,min,max,count都没有问题文档上说Analytic functions(“sum(id) over(order by id)”是Analytic functions)包括: AVG CORR COVAR_POP COVAR_SAMP COUNT CUME_DIST DENSE_RANK LAG FIRST_VALUE LAST_VALUE LEAD MAX MIN NTILE PERCENT_RANK RATIO_TO_REPORT RANK REGR_ (linear regression) functions ROW_NUMBER STDDEV STDDEV_POP STDDEV_SAMP SUM VAR_POP VAR_SAMP VARIANCE 文档上说: Use the ORDER BY clause to specify how data is ordered within a partition. You can order the values in a partition on multiple keys, each defined by a value_expr and each qualified by an ordering sequence. ORDER BY子句用来指定分区内的数据顺序If you omit this clause, the function treats all rows of the query result set as a single group. 如果不指定分区,作为单一的一组我试验一了一下 create table aa (id number,value number); insert into aa values (1,6); insert into aa values (2,5); insert into aa values (3,4); insert into aa values (4,3); insert into aa values (5,4); insert into aa values (6,1); ID SUM(ID)OVER(ORDERBYVALUE) ---------- ------------------------- 6 6 4 10 3 18 5 18 2 20 1 21
楼上的大侠那里面的 ORDER BY 不写可以吗?那 sum(1)是记录数罗?order by 两个字段呢??这个是8i加的新的吗?
to pengdali(大力) ,你说得很对。 麻烦你别叫我大侠,你四个星星晃的眼晕,呵呵SQL> select id,sum(id) over() from aa; ID SUM(ID)OVER() ---------- ------------- 1 21 2 21 3 21 4 21 5 21 6 21已选择6行。 SQL> select id,sum(id) over(order by value,id) from aa; ID SUM(ID)OVER(ORDERBYVALUE,ID) ---------- ---------------------------- 6 6 4 10 3 13 5 18 2 20 1 21已选择6行。SQL> select id,sum(1) over(order by value,id) from aa; ID SUM(1)OVER(ORDERBYVALUE,ID) ---------- --------------------------- 6 1 4 2 3 3 5 4 2 5 1 6已选择6行。 至于是不是8i新加的我也不知道,我手里只有8i的文档。 但是,我感觉不是。
create table aa (id number,value number); insert into aa values (1,6); insert into aa values (2,5); insert into aa values (3,4); insert into aa values (4,3); insert into aa values (6,4); insert into aa values (6,1);你试试!sum(1)也不是记录数而是 count(distinct id) 也就是不相同的记录数
Analytic functions are commonly used to compute cumulative, moving, centered, and reporting aggregates。 如你的数据,别人要这个结果集合(一个数列求和),用别的SQL怎么写呢? ID SUM(ID)OVER(ORDERBYID) -- ---------------------- 1 1 2 3 3 6 4 10 5 15 6 27
这是我的数据,我又加了几行数据 create table aa (id number,value number); insert into aa values (1,6); insert into aa values (2,5); insert into aa values (3,4); insert into aa values (4,3); insert into aa values (5,4); insert into aa values (6,1); insert into aa values (6,1); insert into aa values (6,2); insert into aa values (6,3); 你的SQLselect id,sum(id) over(order by id) from aa; 中的orderby确实没有什么用,因为你没有使用分区你看看这个SQL和他的执行结果,按id分区,id等于6的有4条记录,降序排列 select value,id,sum(id) over(PARTITION BY id order by id,value desc) from aa; SQL> select value,id,sum(id) over(PARTITION BY id order by id,value desc) from aa; VALUE ID SUM(ID)OVER(PARTITIONBYIDORDERBYID,VALUEDESC) ---------- ---------- --------------------------------------------- 6 1 1 5 2 2 4 3 3 3 4 4 4 5 5 3 6 6 2 6 12 1 6 24 1 6 24已选择9行。
怪!PARTITION BY id 是什么意思?分区?和分组有什么不同?
以下是我个人体会: The group expressions in the PARTITION BY subclause divide the dataset into groups within which RANK operates.Analytic Functions 和Aggregate Functions 是并列的几类SQL函数中的两个,我读文档的体会是a "sliding" window---分组是把结果集合select后,按照一定的原则分组 而Analytic Functions可以以a "sliding" window of rows (一个滑动的窗口)方式来聚集结果集合) 至于分区和分组的概念好像没有冲突吧 3 6 6 2 6 12 1 6 24 1 6 24Analytic Functions Analytic functions compute an aggregate value based on a group of rows. The group of rows is called a window and is defined by the analytic clause. For each row, a "sliding" window of rows is defined. The window determines the range of rows used to perform the calculations for the "current row". Window sizes can be based on either a physical number of rows or a logical interval such as time.
斑竹的四星真是耀眼啊! partition by 相当于分组吧,分组后组内还可以再用order by排序。
为什么oracle的联机里什么也没有?连半点语法都没有
SELECT last_name, hire_date, salary, LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS prev_sal FROM employees WHERE job_id = ’PU_CLERK’; LAST_NAME HIRE_DATE SALARY PREV_SAL ------------------------- --------- ---------- ---------- Khoo 18-MAY-95 3100 0 Tobias 24-JUL-97 2800 3100 Baida 24-DEC-97 2900 2800 Himuro 15-NOV-98 2600 2900 Colmenares 10-AUG-99 2500 2600
to pengdali(大力) 在Oracle8i Server and SQL*Plus 项Functions中 查询Analytic Functions, LAG是其中之一
to pengdali(大力) oracle文档中有: 在Oracle8i Server and SQL*Plus 项Functions中 查询Analytic Functions, LAG是其中之一
to 楼上大侠 我是oralce 8i 中文版,连你说的 Oracle8i Server and SQL*Plus 项 都没有,帮助里什么也没有.to 西域大侠 我懂了,谢谢! 帮助在哪里有下载吗?怎么用呢?
AVG
CORR
COVAR_POP
COVAR_SAMP
COUNT
CUME_DIST
DENSE_RANK
LAG
FIRST_VALUE
LAST_VALUE
LEAD
MAX
MIN
NTILE
PERCENT_RANK
RATIO_TO_REPORT
RANK
REGR_ (linear regression) functions
ROW_NUMBER
STDDEV
STDDEV_POP
STDDEV_SAMP
SUM
VAR_POP
VAR_SAMP
VARIANCE 文档上说:
Use the ORDER BY clause to specify how data is ordered within a partition. You can order the values in a partition on multiple keys, each defined by a value_expr and each qualified by an ordering sequence.
ORDER BY子句用来指定分区内的数据顺序If you omit this clause, the function treats all rows of the query result set as a single group.
如果不指定分区,作为单一的一组我试验一了一下
create table aa (id number,value number);
insert into aa values (1,6);
insert into aa values (2,5);
insert into aa values (3,4);
insert into aa values (4,3);
insert into aa values (5,4);
insert into aa values (6,1); ID SUM(ID)OVER(ORDERBYVALUE)
---------- -------------------------
6 6
4 10
3 18
5 18
2 20
1 21
麻烦你别叫我大侠,你四个星星晃的眼晕,呵呵SQL> select id,sum(id) over() from aa; ID SUM(ID)OVER()
---------- -------------
1 21
2 21
3 21
4 21
5 21
6 21已选择6行。
SQL> select id,sum(id) over(order by value,id) from aa; ID SUM(ID)OVER(ORDERBYVALUE,ID)
---------- ----------------------------
6 6
4 10
3 13
5 18
2 20
1 21已选择6行。SQL> select id,sum(1) over(order by value,id) from aa; ID SUM(1)OVER(ORDERBYVALUE,ID)
---------- ---------------------------
6 1
4 2
3 3
5 4
2 5
1 6已选择6行。
至于是不是8i新加的我也不知道,我手里只有8i的文档。
但是,我感觉不是。
insert into aa values (1,6);
insert into aa values (2,5);
insert into aa values (3,4);
insert into aa values (4,3);
insert into aa values (6,4);
insert into aa values (6,1);你试试!sum(1)也不是记录数而是 count(distinct id) 也就是不相同的记录数
sum(1)
over(order by value,id) <<====这里的order by 可以和sum里的不一样.怪order by 有什么作用呢??
from aa;
我觉得可以这样解释。
Analytic functions应该遵循组函数的规则(不科学的说,它是一种粒度比较小的分组)
组函数有两个选项:
DISTINCT:只考虑参数表达式的非重复值
ALL:考虑包括重复值的所有值
缺省的好像是DISTINCT
如你的数据,别人要这个结果集合(一个数列求和),用别的SQL怎么写呢?
ID SUM(ID)OVER(ORDERBYID)
-- ----------------------
1 1
2 3
3 6
4 10
5 15
6 27
create table aa (id number,value number);
insert into aa values (1,6);
insert into aa values (2,5);
insert into aa values (3,4);
insert into aa values (4,3);
insert into aa values (5,4);
insert into aa values (6,1);
insert into aa values (6,1);
insert into aa values (6,2);
insert into aa values (6,3);
你的SQLselect id,sum(id) over(order by id) from aa;
中的orderby确实没有什么用,因为你没有使用分区你看看这个SQL和他的执行结果,按id分区,id等于6的有4条记录,降序排列
select value,id,sum(id) over(PARTITION BY id order by id,value desc) from aa;
SQL> select value,id,sum(id) over(PARTITION BY id order by id,value desc) from aa; VALUE ID SUM(ID)OVER(PARTITIONBYIDORDERBYID,VALUEDESC)
---------- ---------- ---------------------------------------------
6 1 1
5 2 2
4 3 3
3 4 4
4 5 5
3 6 6
2 6 12
1 6 24
1 6 24已选择9行。
The group expressions in the PARTITION BY subclause divide the dataset into groups within which RANK operates.Analytic Functions 和Aggregate Functions 是并列的几类SQL函数中的两个,我读文档的体会是a "sliding" window---分组是把结果集合select后,按照一定的原则分组
而Analytic Functions可以以a "sliding" window of rows (一个滑动的窗口)方式来聚集结果集合)
至于分区和分组的概念好像没有冲突吧
3 6 6
2 6 12
1 6 24
1 6 24Analytic Functions
Analytic functions compute an aggregate value based on a group of rows. The group of rows is called a window and is defined by the analytic clause. For each row, a "sliding" window of rows is defined. The window determines the range of rows used to perform the calculations for the "current row". Window sizes can be based on either a physical number of rows or a logical interval such as time.
partition by 相当于分组吧,分组后组内还可以再用order by排序。
LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS prev_sal
FROM employees
WHERE job_id = ’PU_CLERK’;
LAST_NAME HIRE_DATE SALARY PREV_SAL
------------------------- --------- ---------- ----------
Khoo 18-MAY-95 3100 0
Tobias 24-JUL-97 2800 3100
Baida 24-DEC-97 2900 2800
Himuro 15-NOV-98 2600 2900
Colmenares 10-AUG-99 2500 2600
在Oracle8i Server and SQL*Plus 项Functions中
查询Analytic Functions, LAG是其中之一
oracle文档中有:
在Oracle8i Server and SQL*Plus 项Functions中
查询Analytic Functions, LAG是其中之一
我是oralce 8i 中文版,连你说的 Oracle8i Server and SQL*Plus 项 都没有,帮助里什么也没有.to 西域大侠 我懂了,谢谢!
帮助在哪里有下载吗?怎么用呢?
http://rainbow.mimuw.edu.pl/oracle9i/index.htm
不会吧! :(浪子斑竹没有别的办法?花钱呢?
oracle 有自己的文档,单独放在一张盘上,有个ftp上有下载
ftp:211.147.192.17
user:freedown
pass:freedown好像有816的
to jiezhi(西域浪子)
啥叫马甲?
高手的“微服私访”。
两者都是用于分析函数用的,别的情况下失效的.