select 日期, avg(a1),avg(a2) from test where a1<>0 and a2<>0 group by 日期; 列举两个 其他应该没问题
SQL> SQL> drop table test;Table droppedSQL> SQL> create table test 2 ( 3 id number, 4 adate date, 5 a1 number, 6 a2 number, 7 a3 number, 8 a4 number, 9 a5 number, 10 a6 number, 11 a7 number, 12 a8 number, 13 a9 number, 14 a10 number 15 ) 16 ;Table createdSQL> insert into test select 1 id,sysdate adate,2 a1,3 a2,0 a3,4 a4,5 a5,0 a6,1 a7,23 a8,11 a9,22 a10 from dual;1 row insertedSQL> commit;Commit completeSQL> SQL> select id, 2 adate, 3 sum(value) / count(case 4 when value >= 0 then 5 1 6 else 7 0 8 end) avg 9 from (select t.id, 10 t.adate, 11 decode(column_name, column_name, column_name) name, 12 decode(column_name, 13 'A1', 14 a1, 15 'A2', 16 a2, 17 'A3', 18 a3, 19 'A4', 20 a4, 21 'A5', 22 a5, 23 'A6', 24 a6, 25 'A7', 26 a7, 27 'A8', 28 a8, 29 'A9', 30 a9, 31 'A10', 32 a10) value 33 from user_tab_columns u, test t 34 where u.table_name = 'TEST') 35 where value is not null 36 group by id, adate 37 ; ID ADATE AVG ---------- ----------- ---------- 1 2010-12-10 7.1SQL>
SELECT rq 日期, Round( (Sum(a1)+Sum(a2)+Sum(a3)+Sum(a4)+Sum(a5)+Sum(a6)+Sum(a7)+Sum(a8)+Sum(a9)+Sum(a10)) / (Max(decode(a1,0,0,1))+Max(decode(a2,0,0,1))+ Max(decode(a3,0,0,1))+Max(decode(a4,0,0,1))+ Max(decode(a5,0,0,1))+Max(decode(a6,0,0,1))+ Max(decode(a7,0,0,1))+Max(decode(a8,0,0,1))+ Max(decode(a9,0,0,1))+Max(decode(a10,0,0,1) ) ) ,2) 平均值 FROM tab GROUP BY rq
WITH tab AS( SELECT 1 id,To_Date('2010-12-8 12:02:20','yyyy-mm-dd hh24:mi:ss') rq, 2.0 a1,2.5 a2, 2.8 a3,0 a4,2.5 a5,0 a6,2.3 a7,0 a8,2.6 a9,5.5 a10 FROM dual UNION ALL SELECT 2,To_Date('2010-12-8 12:02:20','yyyy-mm-dd hh24:mi:ss'),2.0,21.5,2.8,3.0,3.5,2.5,2.3,2.5,8.6,3.5 FROM dual UNION ALL SELECT 3,To_Date('2010-11-9 12:02:20','yyyy-mm-dd hh24:mi:ss'),4.0,12.5,2.8,1.0,2.5,2.5,7.3,8.5,9.6,8.5 FROM dual UNION ALL SELECT 4,To_Date('2010-11-9 12:02:20','yyyy-mm-dd hh24:mi:ss'),34.0,2.5,3.8,1.0,7.5,78.5,2.3,2.5,2.6,3.5 FROM dual UNION ALL SELECT 5,To_Date('2010-12-10 12:02:20','yyyy-mm-dd hh24:mi:ss'),23.0,2.5,34.8,1.0,2.5,2.5,2.3,2.5,8.6,0.5 FROM dual UNION ALL SELECT 6,To_Date('2010-12-9 12:02:20','yyyy-mm-dd hh24:mi:ss'),26.0,2.5,0.0,1.0,2.5,2.5,2.3,2.5,2.6,3.5 FROM dual ) --测试数据 SELECT rq 日期, Round( (Sum(a1)+Sum(a2)+Sum(a3)+Sum(a4)+Sum(a5)+Sum(a6)+Sum(a7)+Sum(a8)+Sum(a9)+Sum(a10)) / (Max(decode(a1,0,0,1))+Max(decode(a2,0,0,1))+ Max(decode(a3,0,0,1))+Max(decode(a4,0,0,1))+ Max(decode(a5,0,0,1))+Max(decode(a6,0,0,1))+ Max(decode(a7,0,0,1))+Max(decode(a8,0,0,1))+ Max(decode(a9,0,0,1))+Max(decode(a10,0,0,1)) ),2) 平均值 FROM tab GROUP BY rq--结果: 日期 平均值 --------------------------------------- 2010.11.09 12:02:20 19.74 2010.12.08 12:02:20 7.24 2010.12.09 12:02:20 5.04 2010.12.10 12:02:20 8.02
select 日期,(A1+A2+A3+A4+A5+A6+A7+A8+A9+A10)/(decode(A1,0,0,1)+ decode(A2,0,0,1+ decode(A3,0,0,1)+ decode(A4,0,0,1)+ decode(A5,0,0,1)+ decode(A6,0,0,1)+ decode(A7,0,0,1)+ decode(A8,0,0,1)+ decode(A9,0,0,1)+ decode(A10,0,0,1)) as avgAs from tab;
select 日期,(A1+A2+A3+A4+A5+A6+A7+A8+A9+A10)/(decode(A1,0,0,1)+ decode(A2,0,0,1+ decode(A3,0,0,1)+ decode(A4,0,0,1)+ decode(A5,0,0,1)+ decode(A6,0,0,1)+ decode(A7,0,0,1)+ decode(A8,0,0,1)+ decode(A9,0,0,1)+ decode(A10,0,0,1)) as avgAs from tab;
with tb2 as (select trunc(dt) dt,sum(a1)+sum(a2)+sum(a3)+sum(a4)+sum(a5)+sum(6)+sum(a7)+sum(a8)+sum(a9)+sum(a10) sm, sum(decode(sign(a1),1,1,0)+decode(sign(a2),1,1,0)+decode(sign(3),1,1,0)+decode(sign(a4),1,1,0)+decode(sign(a5),1,1,0)+decode(sign(a6),1,1,0)+decode(sign(a7),1,1,0)+ decode(sign(a8),1,1,0)+decode(sign(a9),1,1,0)+decode(sign(a10),1,1,0)) cnt from tb group by trunc(dt)) select dt,round(sm/cnt,2) avg_total from tb2
如果还要对日期做统计平均:select 日期,avg((A1+A2+A3+A4+A5+A6+A7+A8+A9+A10)/(decode(A1,0,0,1)+ decode(A2,0,0,1+ decode(A3,0,0,1)+ decode(A4,0,0,1)+ decode(A5,0,0,1)+ decode(A6,0,0,1)+ decode(A7,0,0,1)+ decode(A8,0,0,1)+ decode(A9,0,0,1)+ decode(A10,0,0,1)) ) as avgAs group by 日期 from tab;
用下数据 SQL> WITH tb AS( 2 SELECT 1 id,To_Date('2010-12-8 12:02:20','yyyy-mm-dd hh24:mi:ss') dt, 3 2.0 a1,2.5 a2, 2.8 a3,0 a4,2.5 a5,0 a6,2.3 a7,0 a8,2.6 a9,5.5 a10 FROM dual 4 UNION ALL 5 SELECT 2,To_Date('2010-12-8 12:02:20','yyyy-mm-dd hh24:mi:ss'),2.0,21.5,2.8,3.0,3.5,2.5,2.3,2.5,8.6,3.5 FROM dual 6 UNION ALL 7 SELECT 3,To_Date('2010-11-9 12:02:20','yyyy-mm-dd hh24:mi:ss'),4.0,12.5,2.8,1.0,2.5,2.5,7.3,8.5,9.6,8.5 FROM dual 8 UNION ALL 9 SELECT 4,To_Date('2010-11-9 12:02:20','yyyy-mm-dd hh24:mi:ss'),34.0,2.5,3.8,1.0,7.5,78.5,2.3,2.5,2.6,3.5 FROM dual 10 UNION ALL 11 SELECT 5,To_Date('2010-12-10 12:02:20','yyyy-mm-dd hh24:mi:ss'),23.0,2.5,34.8,1.0,2.5,2.5,2.3,2.5,8.6,0.5 FROM dual 12 UNION ALL 13 SELECT 6,To_Date('2010-12-9 12:02:20','yyyy-mm-dd hh24:mi:ss'),26.0,2.5,0.0,1.0,2.5,2.5,2.3,2.5,2.6,3.5 FROM dual 14 ), 15 tb2 as 16 (select trunc(dt) dt,sum(a1)+sum(a2)+sum(a3)+sum(a4)+sum(a5)+sum(6)+sum(a7)+sum(a8)+sum(a9)+sum(a10) sm, 17 sum(decode(sign(a1),1,1,0)+decode(sign(a2),1,1,0)+decode(sign(3),1,1,0)+decode(sign(a4),1,1,0)+decode(sign(a5),1,1,0)+decode(sign(a6),1,1,0)+decode(sign(a7),1,1,0)+ 18 decode(sign(a8),1,1,0)+decode(sign(a9),1,1,0)+decode(sign(a10),1,1,0)) cnt 19 from tb group by trunc(dt)) 20 select dt,round(sm/cnt,2) avg_total from tb2 21 /
列举两个 其他应该没问题
SQL>
SQL> drop table test;Table droppedSQL>
SQL> create table test
2 (
3 id number,
4 adate date,
5 a1 number,
6 a2 number,
7 a3 number,
8 a4 number,
9 a5 number,
10 a6 number,
11 a7 number,
12 a8 number,
13 a9 number,
14 a10 number
15 )
16 ;Table createdSQL> insert into test select 1 id,sysdate adate,2 a1,3 a2,0 a3,4 a4,5 a5,0 a6,1 a7,23 a8,11 a9,22 a10 from dual;1 row insertedSQL> commit;Commit completeSQL>
SQL> select id,
2 adate,
3 sum(value) / count(case
4 when value >= 0 then
5 1
6 else
7 0
8 end) avg
9 from (select t.id,
10 t.adate,
11 decode(column_name, column_name, column_name) name,
12 decode(column_name,
13 'A1',
14 a1,
15 'A2',
16 a2,
17 'A3',
18 a3,
19 'A4',
20 a4,
21 'A5',
22 a5,
23 'A6',
24 a6,
25 'A7',
26 a7,
27 'A8',
28 a8,
29 'A9',
30 a9,
31 'A10',
32 a10) value
33 from user_tab_columns u, test t
34 where u.table_name = 'TEST')
35 where value is not null
36 group by id, adate
37 ; ID ADATE AVG
---------- ----------- ----------
1 2010-12-10 7.1SQL>
SELECT rq 日期,
Round(
(Sum(a1)+Sum(a2)+Sum(a3)+Sum(a4)+Sum(a5)+Sum(a6)+Sum(a7)+Sum(a8)+Sum(a9)+Sum(a10))
/
(Max(decode(a1,0,0,1))+Max(decode(a2,0,0,1))+
Max(decode(a3,0,0,1))+Max(decode(a4,0,0,1))+
Max(decode(a5,0,0,1))+Max(decode(a6,0,0,1))+
Max(decode(a7,0,0,1))+Max(decode(a8,0,0,1))+
Max(decode(a9,0,0,1))+Max(decode(a10,0,0,1)
)
)
,2) 平均值
FROM tab
GROUP BY rq
SELECT 1 id,To_Date('2010-12-8 12:02:20','yyyy-mm-dd hh24:mi:ss') rq,
2.0 a1,2.5 a2, 2.8 a3,0 a4,2.5 a5,0 a6,2.3 a7,0 a8,2.6 a9,5.5 a10 FROM dual
UNION ALL
SELECT 2,To_Date('2010-12-8 12:02:20','yyyy-mm-dd hh24:mi:ss'),2.0,21.5,2.8,3.0,3.5,2.5,2.3,2.5,8.6,3.5 FROM dual
UNION ALL
SELECT 3,To_Date('2010-11-9 12:02:20','yyyy-mm-dd hh24:mi:ss'),4.0,12.5,2.8,1.0,2.5,2.5,7.3,8.5,9.6,8.5 FROM dual
UNION ALL
SELECT 4,To_Date('2010-11-9 12:02:20','yyyy-mm-dd hh24:mi:ss'),34.0,2.5,3.8,1.0,7.5,78.5,2.3,2.5,2.6,3.5 FROM dual
UNION ALL
SELECT 5,To_Date('2010-12-10 12:02:20','yyyy-mm-dd hh24:mi:ss'),23.0,2.5,34.8,1.0,2.5,2.5,2.3,2.5,8.6,0.5 FROM dual
UNION ALL
SELECT 6,To_Date('2010-12-9 12:02:20','yyyy-mm-dd hh24:mi:ss'),26.0,2.5,0.0,1.0,2.5,2.5,2.3,2.5,2.6,3.5 FROM dual
)
--测试数据
SELECT rq 日期,
Round(
(Sum(a1)+Sum(a2)+Sum(a3)+Sum(a4)+Sum(a5)+Sum(a6)+Sum(a7)+Sum(a8)+Sum(a9)+Sum(a10))
/
(Max(decode(a1,0,0,1))+Max(decode(a2,0,0,1))+
Max(decode(a3,0,0,1))+Max(decode(a4,0,0,1))+
Max(decode(a5,0,0,1))+Max(decode(a6,0,0,1))+
Max(decode(a7,0,0,1))+Max(decode(a8,0,0,1))+
Max(decode(a9,0,0,1))+Max(decode(a10,0,0,1))
),2) 平均值
FROM tab
GROUP BY rq--结果:
日期 平均值
---------------------------------------
2010.11.09 12:02:20 19.74
2010.12.08 12:02:20 7.24
2010.12.09 12:02:20 5.04
2010.12.10 12:02:20 8.02
select 日期,(A1+A2+A3+A4+A5+A6+A7+A8+A9+A10)/(decode(A1,0,0,1)+
decode(A2,0,0,1+
decode(A3,0,0,1)+
decode(A4,0,0,1)+
decode(A5,0,0,1)+
decode(A6,0,0,1)+
decode(A7,0,0,1)+
decode(A8,0,0,1)+
decode(A9,0,0,1)+
decode(A10,0,0,1)) as avgAs
from tab;
select 日期,(A1+A2+A3+A4+A5+A6+A7+A8+A9+A10)/(decode(A1,0,0,1)+
decode(A2,0,0,1+
decode(A3,0,0,1)+
decode(A4,0,0,1)+
decode(A5,0,0,1)+
decode(A6,0,0,1)+
decode(A7,0,0,1)+
decode(A8,0,0,1)+
decode(A9,0,0,1)+
decode(A10,0,0,1)) as avgAs
from tab;
(select trunc(dt) dt,sum(a1)+sum(a2)+sum(a3)+sum(a4)+sum(a5)+sum(6)+sum(a7)+sum(a8)+sum(a9)+sum(a10) sm,
sum(decode(sign(a1),1,1,0)+decode(sign(a2),1,1,0)+decode(sign(3),1,1,0)+decode(sign(a4),1,1,0)+decode(sign(a5),1,1,0)+decode(sign(a6),1,1,0)+decode(sign(a7),1,1,0)+
decode(sign(a8),1,1,0)+decode(sign(a9),1,1,0)+decode(sign(a10),1,1,0)) cnt
from tb group by trunc(dt))
select dt,round(sm/cnt,2) avg_total from tb2
decode(A2,0,0,1+
decode(A3,0,0,1)+
decode(A4,0,0,1)+
decode(A5,0,0,1)+
decode(A6,0,0,1)+
decode(A7,0,0,1)+
decode(A8,0,0,1)+
decode(A9,0,0,1)+
decode(A10,0,0,1))
) as avgAs
group by 日期
from tab;
SQL> WITH tb AS(
2 SELECT 1 id,To_Date('2010-12-8 12:02:20','yyyy-mm-dd hh24:mi:ss') dt,
3 2.0 a1,2.5 a2, 2.8 a3,0 a4,2.5 a5,0 a6,2.3 a7,0 a8,2.6 a9,5.5 a10 FROM dual
4 UNION ALL
5 SELECT 2,To_Date('2010-12-8 12:02:20','yyyy-mm-dd hh24:mi:ss'),2.0,21.5,2.8,3.0,3.5,2.5,2.3,2.5,8.6,3.5 FROM dual
6 UNION ALL
7 SELECT 3,To_Date('2010-11-9 12:02:20','yyyy-mm-dd hh24:mi:ss'),4.0,12.5,2.8,1.0,2.5,2.5,7.3,8.5,9.6,8.5 FROM dual
8 UNION ALL
9 SELECT 4,To_Date('2010-11-9 12:02:20','yyyy-mm-dd hh24:mi:ss'),34.0,2.5,3.8,1.0,7.5,78.5,2.3,2.5,2.6,3.5 FROM dual
10 UNION ALL
11 SELECT 5,To_Date('2010-12-10 12:02:20','yyyy-mm-dd hh24:mi:ss'),23.0,2.5,34.8,1.0,2.5,2.5,2.3,2.5,8.6,0.5 FROM dual
12 UNION ALL
13 SELECT 6,To_Date('2010-12-9 12:02:20','yyyy-mm-dd hh24:mi:ss'),26.0,2.5,0.0,1.0,2.5,2.5,2.3,2.5,2.6,3.5 FROM dual
14 ),
15 tb2 as
16 (select trunc(dt) dt,sum(a1)+sum(a2)+sum(a3)+sum(a4)+sum(a5)+sum(6)+sum(a7)+sum(a8)+sum(a9)+sum(a10) sm,
17 sum(decode(sign(a1),1,1,0)+decode(sign(a2),1,1,0)+decode(sign(3),1,1,0)+decode(sign(a4),1,1,0)+decode(sign(a5),1,1,0)+decode(sign(a6),1,1,0)+decode(sign(a7),1,1,0)+
18 decode(sign(a8),1,1,0)+decode(sign(a9),1,1,0)+decode(sign(a10),1,1,0)) cnt
19 from tb group by trunc(dt))
20 select dt,round(sm/cnt,2) avg_total from tb2
21 /
DT AVG_TOTAL
----------- ----------
2010-12-8 4.82
2010-11-9 6.42
2010-12-9 4.89
2010-12-10 8.37