问一个分析函数的问题，如果在分析函数中加入条件？

Question

下面是从一个表中，取当天向前推移99个样本（+当天的话为100个样本了）的每天的标准差。select id,stdev(CountNum) over (PARTITION BY id order by s_date asc rows between 99 preceding and 0 following)
from table
order by id,s_date;现在我要这样一个效果：在取每天的标准差时，过滤不合理的数据（假设CountNum>10的为不合理），请问怎样实现这种效果？附：因为过滤了不合理的数据，所以样本天数是会变化的，不再=100。所以，这个CountNum>10的条件，是不能直接加到where 条件里面去的。下面这个是错误的：
select id,stdev(CountNum) over (PARTITION BY id order by s_date asc rows between 99 preceding and 0 following)
from table
where CountNum>10
order by id,s_date;这个写法是正确的（假设sql语法正确的话）
select id,stdev(CountNum) over (PARTITION BY id order by s_date asc rows between 99 preceding and 0 following where CountNum>10)
from table
order by id,s_date;

Answer 1

可以试下这个:select id stdev(countnum) over (partition by id order by s_date asc rows between 99 precedint and 0 following)from ( select * from table where countnum<=10) newtableorder by id,s_date

Answer 2

http://www.itpub.net/viewthread.php?tid=608107&extra=&page=1
oracle分析函数
oracle分析函数
zhouwf0726 | 25 七月, 2006 12:51oracle分析函数--SQL*PLUS环境
--1、GROUP BY子句 --CREATE TEST TABLE AND INSERT TEST DATA.
create table students
(id number(15,0),
area varchar2(10),
stu_type varchar2(2),
score number(20,2));insert into students values(1, '111', 'g', 80 );
insert into students values(1, '111', 'j', 80 );
insert into students values(1, '222', 'g', 89 );
insert into students values(1, '222', 'g', 68 );
insert into students values(2, '111', 'g', 80 );
insert into students values(2, '111', 'j', 70 );
insert into students values(2, '222', 'g', 60 );
insert into students values(2, '222', 'j', 65 );
insert into students values(3, '111', 'g', 75 );
insert into students values(3, '111', 'j', 58 );
insert into students values(3, '222', 'g', 58 );
insert into students values(3, '222', 'j', 90 );
insert into students values(4, '111', 'g', 89 );
insert into students values(4, '111', 'j', 90 );
insert into students values(4, '222', 'g', 90 );
insert into students values(4, '222', 'j', 89 );
commit;col score format 999999999999.99--A、GROUPING SETSselect id,area,stu_type,sum(score) score 
from students
group by grouping sets((id,area,stu_type),(id,area),id)
order by id,area,stu_type;/*--------理解grouping sets
select a, b, c, sum( d ) from t
group by grouping sets ( a, b, c )等效于select * from (
select a, null, null, sum( d ) from t group by a
union all
select null, b, null, sum( d ) from t group by b 
union all
select null, null, c, sum( d ) from t group by c 
)
*/--B、ROLLUPselect id,area,stu_type,sum(score) score 
from students
group by rollup(id,area,stu_type)
order by id,area,stu_type;/*--------理解rollup
select a, b, c, sum( d )
from t
group by rollup(a, b, c);等效于select * from (
select a, b, c, sum( d ) from t group by a, b, c 
union all
select a, b, null, sum( d ) from t group by a, b
union all
select a, null, null, sum( d ) from t group by a
union all
select null, null, null, sum( d ) from t
)
*/--C、CUBEselect id,area,stu_type,sum(score) score 
from students
group by cube(id,area,stu_type)
order by id,area,stu_type;/*--------理解cube
select a, b, c, sum( d ) from t
group by cube( a, b, c)等效于select a, b, c, sum( d ) from t
group by grouping sets( 
( a, b, c ), 
( a, b ), ( a ), ( b, c ), 
( b ), ( a, c ), ( c ), 
() )
*/--D、GROUPING/*从上面的结果中我们很容易发现,每个统计数据所对应的行都会出现null,
如何来区分到底是根据那个字段做的汇总呢,grouping函数判断是否合计列!*/select decode(grouping(id),1,'all id',id) id,
decode(grouping(area),1,'all area',to_char(area)) area,
decode(grouping(stu_type),1,'all_stu_type',stu_type) stu_type,
sum(score) score
from students
group by cube(id,area,stu_type)
order by id,area,stu_type; --2、OVER()函数的使用
--1、RANK()、DENSE_RANK() 的、ROW_NUMBER()、CUME_DIST()、MAX()、AVG()break on id skip 1
select id,area,score from students order by id,area,score desc;select id,rank() over(partition by id order by score desc) rk,score from students;--允许并列名次、名次不间断
select id,dense_rank() over(partition by id order by score desc) rk,score from students;--即使SCORE相同，ROW_NUMBER()结果也是不同
select id,row_number() over(partition by ID order by SCORE desc) rn,score from students;select cume_dist() over(order by id) a, --该组最大row_number/所有记录row_number 
row_number() over (order by id) rn,id,area,score from students;select id,max(score) over(partition by id order by score desc) as mx,score from students;select id,area,avg(score) over(partition by id order by area) as avg,score from students; --注意有无order by的区别--按照ID求AVG
select id,avg(score) over(partition by id order by score desc rows between unbounded preceding 
and unbounded following ) as ag,score from students;
--2、SUM()select id,area,score from students order by id,area,score desc;select id,area,score,
sum(score) over (order by id,area) 连续求和, --按照OVER后边内容汇总求和
sum(score) over () 总和, -- 此处sum(score) over () 等同于sum(score)
100*round(score/sum(score) over (),4) "份额(%)"
from students;select id,area,score,
sum(score) over (partition by id order by area ) 连id续求和, --按照id内容汇总求和
sum(score) over (partition by id) id总和, --各id的分数总和
100*round(score/sum(score) over (partition by id),4) "id份额(%)",
sum(score) over () 总和, -- 此处sum(score) over () 等同于sum(score)
100*round(score/sum(score) over (),4) "份额(%)"
from students;--4、LAG(COL,n,default)、LEAD(OL,n,default) --取前后边N条数据select id,lag(score,1,0) over(order by id) lg,score from students;select id,lead(score,1,0) over(order by id) lg,score from students;--5、FIRST_VALUE()、LAST_VALUE()select id,first_value(score) over(order by id) fv,score from students;select id,last_value(score) over(order by id) fv,score from students;http://zhouwf0726.itpub.net/post/9689/158090
-------------------------------------------------再次理解分析函数！
/*********************************************************************************************http://www.itpub.net/620932.html问题提出：一个高级SQL语句问题 
假设有一张表，A和B字段都是NUMBER，
A B
1 2
2 3
3 4
4 
有这样一些数据
现在想用一条SQL语句，查询出这样的数据
1－》2－》3—》4
就是说，A和B的数据表示一种连接的关系，现在想通过A的一个值，去查询A所对应的B值，直到B为NULL为止，
不知道这个SQL语句怎么写？请教高手！谢谢*********************************************************************************************/--以下是利用分析函数的一个简单解答：
--start with connect by可以参考http://www.itpub.net/620427.htmlCREATE TABLE TEST(COL1 NUMBER(18,0),COL2 NUMBER(18,0));INSERT INTO TEST VALUES(1,2);
INSERT INTO TEST VALUES(2,3);
INSERT INTO TEST VALUES(3,4);
INSERT INTO TEST VALUES(4,NULL);INSERT INTO TEST VALUES(5,6);
INSERT INTO TEST VALUES(6,7);
INSERT INTO TEST VALUES(7,8);
INSERT INTO TEST VALUES(8,NULL);INSERT INTO TEST VALUES(9,10);
INSERT INTO TEST VALUES(10,NULL);INSERT INTO TEST VALUES(11,12);
INSERT INTO TEST VALUES(12,13);
INSERT INTO TEST VALUES(13,14);
INSERT INTO TEST VALUES(14,NULL);
CREATE TABLE TEST(COL1 NUMBER(18,0),COL2 NUMBER(18,0));INSERT INTO TEST VALUES(1,2);
INSERT INTO TEST VALUES(2,3);
INSERT INTO TEST VALUES(3,4);
INSERT INTO TEST VALUES(4,NULL);INSERT INTO TEST VALUES(5,6);
INSERT INTO TEST VALUES(6,7);
INSERT INTO TEST VALUES(7,8);
INSERT INTO TEST VALUES(8,NULL);INSERT INTO TEST VALUES(9,10);
INSERT INTO TEST VALUES(10,NULL);select max(col) from(
select SUBSTR(col,1,CASE WHEN INSTR(col,'->')>0 THEN INSTR(col,'->') - 1 ELSE LENGTH(col) END) FLAG,col from(
select ltrim(sys_connect_by_path(col1,'->'),'->') col from (
select col1,col2,CASE WHEN LAG(COL2,1,NULL) OVER(ORDER BY ROWNUM) IS NULL THEN 1 ELSE 0 END FLAG 
from test
)
start with flag=1 connect by col1=prior col2
)
)
group by flag
;

Answer 3

试试这个：
select   id,stdev(decode(sign(CountNum-10),1,null,CountNum))   over   (PARTITION   BY   id   order   by   s_date   asc   rows   between   99   preceding   and   0   following) 
from   table 
order   by   id,s_date; stdev(decode(sign(CountNum-10),1,null,CountNum))
CountNum>10时，设为null，这样基数也就相应变小

Answer 4

楼主,你看错我写的SQL语句了吧,你仔细看一下你的"附:"与我的SQL语句之间的区别.在你的"附:"中,你的条件是加在分析函数中,而基表是原有的表(table)而我的SQL语句中,基表是所有符合条件(countnum<=10,因为countnum>10为不符合条件的)的记录组成的表.而分析函数也是建立在该表上的.哪里跟你的"附:"是一样的啊!

Answer 5

非常感谢bjt_ 的帮助，这样是可行的，再次感谢！TO bai_jiong：感谢你的再次参与。我说的不太清楚，让你误解，表示歉意。