存在TMP 表table tmp
id 字段a(time) 字段B(tch)
1 8:00 10
1 9:00 20
2 8:00 30
2 9:00 10我想先按照时间粒度汇总字段B(TCH),得出8点的汇总是最大的10+30=40,然后按照8点按照ID呈现,其中字段B 是
按照排序平均值
1 8:00 20 (10+30 /2)
2 8:00 15 (20+10 /2)
id 字段a(time) 字段B(tch)
1 8:00 10
1 9:00 20
2 8:00 30
2 9:00 10我想先按照时间粒度汇总字段B(TCH),得出8点的汇总是最大的10+30=40,然后按照8点按照ID呈现,其中字段B 是
按照排序平均值
1 8:00 20 (10+30 /2)
2 8:00 15 (20+10 /2)
{
(tch的总和)/tch.Count
}
select rownum id, time, avg_tch from (select time, avg(tch) avg_tch from tab group by time order by avg(tch) desc) tb--ps:我实在是没有看出id是怎么得到的,楼主还是再解释下吧!
with t as
(
select 1 id, '8:00' as time, 10 as tch from dual
union all
select 1 id, '9:00' as time, 20 as tch from dual
union all
select 2 id, '8:00' as time, 30 as tch from dual
union all
select 2 id, '9:00' as time, 10 as tch from dual
)
select t2.*
from (select time, sum(tch) as tch, row_number() over(order by sum(tch) desc) as rn
from t
group by time) t1,
t t2
where t1.time = t2.time
and t1.rn = 1;
ID TIME TCH
---------- ---- ----------
1 8:00 10
2 8:00 30
(
select 1 id, '8:00' as time, 10 as tch from dual
union all
select 1 id, '9:00' as time, 20 as tch from dual
union all
select 2 id, '8:00' as time, 30 as tch from dual
union all
select 2 id, '9:00' as time, 10 as tch from dual
)
select t2.rn,t2.time,t2.tch
from (select time, AVG(tch) as tch, row_number() over(order by t.TIME) AS rn
from t
group by time) t2
ORDER BY t2.time
2 8:00 15
这里的15是9点的平均值,与该记录的8点矛盾。提供以下SQL供参考with t as
(
select 1 id, '8:00' as time, 10 as tch from dual
union all
select 1 id, '9:00' as time, 20 as tch from dual
union all
select 2 id, '8:00' as time, 30 as tch from dual
union all
select 2 id, '9:00' as time, 10 as tch from dual
)
select t.*,avg(tch) over(partition by time) avg from t order by id,time;--得到的结果如下,其中AVG列的值为该行TIME时间点的平均值
ID TIME TCH AVG
8:00 10 20
1 9:00 20 15
2 8:00 30 20
2 9:00 10 15
ID TIME TCH AVG
1 8:00 10 20
1 9:00 20 15
2 8:00 30 20
2 9:00 10 15
SQL> with t as
2 (
3 select 1 id, '8:00' as time, 10 as tch from dual
4 union all
5 select 1 id, '9:00' as time, 20 as tch from dual
6 union all
7 select 2 id, '8:00' as time, 30 as tch from dual
8 union all
9 select 2 id, '9:00' as time, 10 as tch from dual
10 )
11 SELECT time,COUNT(*) ,SUM(tch)/COUNT(*)
12 FROM t GROUP BY TIME
13 ORDER BY TIME;
TIME COUNT(*) SUM(TCH)/COUNT(*)
---- ---------- -----------------
8:00 2 20
9:00 2 15