Oracle按指定日期生成连续的时间范围

比如现在有个表 记录着某个 sid 的开始 结束时间 大概有70w条数据
sid              start_date         end_date
1                2014/01/01       2014/07/24
3                2014/05/22       2014/06/05
8                2014/04/11       2014/04/16

我现在想生成一个将 start_date 和end_date展开的表 记录着每个周日，sid存在的记录：

生成后结果如下：

sid           snp_date
1              2014/01/05
1              2014/01/12
1              2014/01/19
......
3              2014/05/25
......

 

 

 

WITH t AS (
 SELECT 1 sid,DATE '2014-1-1' start_date,DATE '2014-7-24' end_date FROM DUAL
 UNION ALL SELECT 3,DATE '2013-1-1',DATE '2014-6-5' FROM DUAL
 UNION ALL SELECT 8,DATE '2014-4-11',DATE '2014-4-16' FROM DUAL
 UNION ALL SELECT 9,DATE '2014-10-18',DATE '2014-10-30' FROM DUAL
 UNION ALL SELECT 10,DATE '2014-10-18',DATE '2014-10-26' FROM DUAL
 UNION ALL SELECT 11,DATE '2014-10-19',DATE '2014-10-30' FROM DUAL
 UNION ALL SELECT 12,DATE '2014-10-19',DATE '2014-10-26' FROM DUAL
 UNION ALL SELECT 13,DATE '2014-10-19',DATE '2014-10-25' FROM DUAL
)
SELECT sid,start_date+7-to_char(start_date,'DAY','NLS_DATE_LANGUAGE=''numeric date language''')+(LEVEL-1)*7 dt
   FROM t
WHERE start_date+7-to_char(start_date,'DAY','NLS_DATE_LANGUAGE=''numeric date language''')+(LEVEL-1)*7 BETWEEN start_date AND end_date
CONNECT BY sid=PRIOR sid AND LEVEL<=(end_date-start_date)/7+1 AND PRIOR SYS_GUID() IS NOT NULL;