下面是一个网友的问题
问:表test(yyyymmdd,price)
按yyyymmdd--yyyymmdd+3,sum(price)做统计,sql怎么写?
例如:
20131101--20131103 100
20131102--20131104 105
20131103--20131105 95
于是生成环境及语句如下:
SQL> DROP TABLE TEST PURGE;
Table dropped
SQL> CREATE TABLE TEST AS
2 SELECT trunc(SYSDATE + LEVEL /2,'dd') AS c1,LEVEL AS c2 FROM dual CONNECT BY LEVEL <=20;
Table created
SQL> DELETE FROM TEST WHERE c2 IN (10,11,12);
3 rows deleted
SQL> SELECT * FROM TEST;
C1 C2
----------- ----------
2013-11-08 1
2013-11-08 2
2013-11-09 3
2013-11-09 4
2013-11-10 5
2013-11-10 6
2013-11-11 7
2013-11-11 8
2013-11-12 9
2013-11-14 13
2013-11-14 14
2013-11-15 15
2013-11-15 16
2013-11-16 17
2013-11-16 18
2013-11-17 19
2013-11-17 20
17 rows selected
SQL> SELECT c1,
2 SUM(SUM(c2)) over(ORDER BY c1 RANGE BETWEEN CURRENT ROW AND 2 FOLLOWING) AS t_c2,
3 SUM(c2) AS t_c3
4 FROM test
5 GROUP BY c1
6 ORDER BY 1;
C1 T_C2 T_C3
----------- ---------- ----------
2013-11-08 21 3
2013-11-09 33 7
2013-11-10 35 11
2013-11-11 24 15
2013-11-12 36 9
2013-11-14 93 27
2013-11-15 105 31
2013-11-16 74 35
2013-11-17 39 39
9 rows selected
SQL>