时间汇总求解

Question

原始数据表格,表名ta(名称，name|日期SDate|数量SumNum)名称 | 日期 | 数量
----------------------------------------
A 2006-01-01 10
A 2006-01-02 10
A 2006-01-03 10
A 2006-01-04 10
A 2006-01-05 10
A 2006-01-06 10
A 2006-01-07 10
A 2006-01-08 10
A 2006-01-09 10
A 2006-01-10 10
A 2006-01-11 10
A 2006-01-12 10
A 2006-01-13 10
A 2006-01-14 10
A 2006-01-15 10
A 2006-01-16 10
B 2006-01-01 10
B 2006-01-02 10
B 2006-01-03 10
B 2006-01-04 10
B 2006-01-05 10
B 2006-01-06 10
B 2006-01-07 10
B 2006-01-08 10
B 2006-01-09 10
B 2006-01-10 10
B 2006-01-11 10
B 2006-01-12 10
B 2006-01-13 10
B 2006-01-14 10
B 2006-01-15 10
B 2006-01-16 10现在要按名称和时间周期进行汇总,时间周期是4天，最小汇总时间指定是2006-01-01,汇总的结果如下,在oracle里怎样实现名称 | 日期 | 数量
----------------------------------------
A 2006-01-04 40
A 2006-01-08 40
A 2006-01-12 40
A 2006-01-16 40
B 2006-01-04 40
B 2006-01-08 40
B 2006-01-12 40
B 2006-01-16 40
请问这样的sql语句在oracle怎样写
在mssql可以这样写
select dateadd(dd,(datediff(dd,'2005-1-1',tDate)/4)*4,'2005-1-1'),sum(tnum) from testgroup by dateadd(dd,(datediff(dd,'2005-1-1',tDate)/4)*4,'2005-1-1')

Answer 1

select tName,to_date('2006-01-01','yyyy-mm-dd')+(trunc(tDate-to_date('2006-01-01','yyyy-mm-dd'))/4)*4,sum(tnum) 
from test
group by tName,to_date('2006-01-01','yyyy-mm-dd')+(trunc(tDate-to_date('2006-01-01','yyyy-mm-dd'))/4)*4

Answer 2

SQL> SELECT NAME,TO_DATE('2006-01-04','YYYY-MM-DD') + TRUNC(TO_NUMBER(SDATE - TO_DATE('2006-01-01','YYYY-MM-DD'))/4)*4 AS DAY,
  2  SUM(SumNum) FROM TA
  3  GROUP BY NAME, TRUNC(TO_NUMBER(SDATE - TO_DATE('2006-01-01','YYYY-MM-DD'))/4)*4
  4  /NAME DAY         SUM(SUMNUM)
---- ----------- -----------
A    2006-1-4             40
A    2006-1-8             40
A    2006-1-12            40
A    2006-1-16            40
B    2006-1-4             40
B    2006-1-8             40
B    2006-1-12            40
B    2006-1-16            408 rows selected