原始数据月份有间隔,你自己去拉平:
SQL> with t1 as(
2 select '201806' mon ,1 score from dual
3 union all
4 select '201805' mon ,1 score from dual
5 union all
6 select '201804' mon ,2 score from dual
7 union all
8 select '201803' mon ,1 score from dual
9 union all
10 select '201802' mon ,1 score from dual
11 union all
12 select '201801' mon ,1 score from dual
13 union all
14 select '201712' mon ,1 score from dual
15 ),
16 t2 as (
17 select mon,score,row_number() over(order by mon) rn from t1),
18 t3 as (
19 select a.rn rn1,a.mon mon1,a.score score1,b.rn rn2,b.mon mon2,b.score score2,c.rn rn3,c.mon mon3,c.score score3,
20 nvl(a.score,0)+nvl(b.score,0)+nvl(c.score,0) total_score,
21 case when a.mon is not null then 1 else 0 end
22 + case when b.mon is not null then 1 else 0 end
23 + case when c.mon is not null then 1 else 0 end
24 as cnt
25 from t2 a
26 left join t2 b
27 on a.rn = b.rn -1
28 left join t2 c
29 on a.rn = c.rn - 2)
30 select rn1,mon1,score1,total_score,cnt,round(total_score/cnt,2) as average
31 from t3
32 /
RN1 MON1 SCORE1 TOTAL_SCORE CNT AVERAGE
---------- ------ ---------- ----------- ---------- ----------
1 201712 1 3 3 1
2 201801 1 3 3 1
3 201802 1 4 3 1.33
4 201803 1 4 3 1.33
5 201804 2 4 3 1.33
6 201805 1 2 2 1
7 201806 1 1 1 1
7 rows selected
SQL>