大家帮我一下啊，求个平均数我怎么就求不明白了呢

Question

本帖最后由 zhxh1026 于 2010-12-10 10:20:31 编辑

Answer 1

是求a1 单列的平均值，还是a1 到a10之和，再取平均？

Answer 2

select 日期, avg(a1),avg(a2) from test where a1<>0 and a2<>0 group by 日期;
列举两个   其他应该没问题

Answer 3

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> 

Answer 4

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

Answer 5

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

Answer 6

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;

Answer 7

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;

Answer 8

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

Answer 9

如果还要对日期做统计平均：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;

Answer 10

用下数据
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

Answer 11

大家的算法都差不多啊，分别算出SUM 和 AVG个数，然后手工相除