table: tbl_task
_________________________________________________
TASKID NOT NULL NUMBER(10)
HOPID NOT NULL NUMBER(2)
BW NUMBER(10)
NECKCONFINDEX NUMBER(1)
data:
________________________________________________ TASKID HOPID BW NECKCONFINDEX
---------- ---------- ---------- -------------
1000 1 100 1
1000 2 100 0
1000 1 400 0
1000 2 100 0
1000 2 300 1
1000 2 100 1
1000 1 100 0
1000 1 200 0
1000 3 100 1
1000 3 500 12000 2 100 0
2000 2 300 1
2000 2 100 1
2000 1 100 0
2000 1 200 0
...要求:按TASKID,HOPID分组查询出BW平均值,以及NECKCONFINDEX=0所占当前组的百分比RATIO
____________________________________________________________________________
TASKID HOPID AVABW RATIO
---------- ---------- ---------- -------------
1000 1 200 0.75
1000 2 150 0.50
1000 3 300 0
...满足条件taskid=1000 and hopid=1的记录有4条,而满足taskid=1000 and hopid=1 and neckconfindex=0的记录有3条,那个百分比RATIO是3/4=0.75
_________________________________________________
TASKID NOT NULL NUMBER(10)
HOPID NOT NULL NUMBER(2)
BW NUMBER(10)
NECKCONFINDEX NUMBER(1)
data:
________________________________________________ TASKID HOPID BW NECKCONFINDEX
---------- ---------- ---------- -------------
1000 1 100 1
1000 2 100 0
1000 1 400 0
1000 2 100 0
1000 2 300 1
1000 2 100 1
1000 1 100 0
1000 1 200 0
1000 3 100 1
1000 3 500 12000 2 100 0
2000 2 300 1
2000 2 100 1
2000 1 100 0
2000 1 200 0
...要求:按TASKID,HOPID分组查询出BW平均值,以及NECKCONFINDEX=0所占当前组的百分比RATIO
____________________________________________________________________________
TASKID HOPID AVABW RATIO
---------- ---------- ---------- -------------
1000 1 200 0.75
1000 2 150 0.50
1000 3 300 0
...满足条件taskid=1000 and hopid=1的记录有4条,而满足taskid=1000 and hopid=1 and neckconfindex=0的记录有3条,那个百分比RATIO是3/4=0.75
具体格式自己去调整 大致SQL就这样select taskid,hopid,avg(bw),sum(decode(neckconfindex,0,1,0))/count(*) from table_name
group by taskid,hopid
order by taskid,hopid
SELECT TASKID,HOPID,AVG(BW) AVABW,(1- SUM(NECKCONFINDEX)/COUNT(NECKCONFINDEX)) RATIO
FROM tbl_task
select t.*, t.rowid from tbl_task t;
select TASKID,HOPID,avg(BW),
sum(decode(NECKCONFINDEX,0,1,0))/sum(decode(NECKCONFINDEX,0,1,1))
from tbl_task
group by TASKID,HOPID
order by HOPID;
而且,我还少用了decode函数,相对来说,效率还高一点。
嗬嗬,没想到LZ只是认为他们是正确的,伤心呀。