现有一表
id stationid regionid date data 1 1 1 2009-1-1 30
2 1 1 2009-2-1 31
3 2 1 2008-12-30 29
4 2 2 2009-5-2 25
5 1 2 2009-3-1 31
6 3 1 2009-5-1 28现在需要按照stationid分组查询今年同一regionid编号下的data平均数要怎么才能查到啊?要求data如果为空的话不计算,小弟SQL技术有限想破脑筋也不知道怎么写,高手们来帮帮忙吧
id stationid regionid date data 1 1 1 2009-1-1 30
2 1 1 2009-2-1 31
3 2 1 2008-12-30 29
4 2 2 2009-5-2 25
5 1 2 2009-3-1 31
6 3 1 2009-5-1 28现在需要按照stationid分组查询今年同一regionid编号下的data平均数要怎么才能查到啊?要求data如果为空的话不计算,小弟SQL技术有限想破脑筋也不知道怎么写,高手们来帮帮忙吧
from table1 group by stationid,regionid order by stationid
from statinfo
where extract(year from date)=extract(year from sysdate)
group by stationid, regionid
create table tablea(id int,stationid int,regionid int,daydate date,data int);
insert into tablea values(1,1,1,to_date('2009-1-1','yyyy-mm-dd'),30);
insert into tablea values(2,1,1,to_date('2009-2-1','yyyy-mm-dd'),31);
insert into tablea values(3,2,1,to_date('2008-12-30','yyyy-mm-dd'),29);
insert into tablea values(4,2,2,to_date('2009-5-2','yyyy-mm-dd'),25);
insert into tablea values(5,1,2,to_date('2009-3-1','yyyy-mm-dd'),31);
insert into tablea values(6,3,1,to_date('2009-5-1','yyyy-mm-dd'),28);select id,stationid,regionid,daydate,data,avg(decode(data,'',0,data)) over(partition by stationid,regionid) avgdata
from tablea
order by id
from tablea
order by id
create table t1(id number(38) ,
stationid number(38),
regionid number(38),
l_date date,
l_data number(38)
);
--
insert into t1 values (1 , 1 , 1 , to_date('2009-01-01','yyyy-mm-dd') , 30);
insert into t1 values (2 , 1 , 1 , to_date('2009-02-01','yyyy-mm-dd') , 31);
insert into t1 values (3 , 2 , 1 , to_date('2009-12-30','yyyy-mm-dd') , 29);
insert into t1 values (4 , 2 , 2 , to_date('2009-05-02','yyyy-mm-dd') , 25);
insert into t1 values (5 , 1 , 2 , to_date('2009-03-01','yyyy-mm-dd') , 31);
insert into t1 values (6 , 3 , 1 , to_date('2009-05-01','yyyy-mm-dd') , 28);
commit;
select
t.stationid,t.regionid,avg(nvl(t.l_data,0))
from t1 t
where to_char(t.l_date,'yyyy') = to_char(sysdate,'yyyy')
group by t.stationid,t.regionid
select stationid,regionid,avg(nvl(data,0))
from table1 where data is not null group by stationid,regionid order by stationid
select stationid,regionid,to_char(daydate,'yy'),avg(nvl(data,0)) avgdata
from tablea
group by stationid,regionid,to_char(daydate,'yy')
--加l_data不为空
select
t.stationid,t.regionid,avg(nvl(t.l_data,0))
from t1 t
where to_char(t.l_date,'yyyy') = to_char(sysdate,'yyyy') and t.l_data is not null
group by t.stationid,t.regionid
select
t.stationid,avg(nvl(t.l_data,0))
from t1 t
where to_char(t.l_date,'yyyy') = to_char(sysdate,'yyyy') and t.l_data is not null
group by t.stationid