插入从周五开始的每周日期

Question

我试图在表中插入每周日期，开始日期总是在周五，结束日期总是在周四。我使用的是这个代码：

 CREATE TABLE WEEK AS
  WITH generator AS (
   SELECT DATE '2015-01-02' + LEVEL - 1 dt
     FROM dual
   CONNECT BY LEVEL <= DATE '2016-01-21' - DATE '2015-01-02' + 1
           )
    SELECT to_char(dt, 'YYYY "SEM"IW') "KEY",
       dt "DATE_START",
       least(next_day(dt - 1, to_char(DATE '2015-01-08', 'DAY')),
            last_day(dt)) "DATE_END"
 FROM generator
WHERE to_char(dt, 'D') = to_char(DATE '2015-01-02', 'D');

代码在同一个月工作数周，但如果我有一个月的开始日期和下个月的结束日期，则没有数据插入我的表中。例如：

Date_ START | DATE_END
29-05-2015  | 31-05-2015     
05-06-2015  | 11-05-2015

而不是2015年5月31日，我应该有2015年4月6日。

Answer 1

我认为以下是您想要的：

with generator as (select     date '2015-05-29' + (level - 1)*7 dt
                   from       dual
                   connect by level <= (date '2016-01-21' - date '2015-05-29')/7 + 1)
select to_char(dt, 'YYYY "SEM"IW') "KEY",
       dt "DATE_START",
       dt + 6 "DATE_END"
from   generator;
KEY        DATE_START DATE_END  
---------- ---------- ----------
2015 SEM22 2015-05-29 2015-06-04
2015 SEM23 2015-06-05 2015-06-11
2015 SEM24 2015-06-12 2015-06-18
2015 SEM25 2015-06-19 2015-06-25
<snip>
2016 SEM01 2016-01-08 2016-01-14
2016 SEM02 2016-01-15 2016-01-21

这是假设您在生成器子查询中指定的日期已经确定为星期五。否则，您可以使用类似trunc(<date> - 4, 'iw') + 4或trunc(<date> + 3, 'iw') + 4的内容（取决于您希望指定日期包含上周五还是下周五）来确保种子日期绝对是周五。

Answer 2

也许你可以在这里尝试分析函数。但是作为Bonest建议"只增加天数"将是更好的方法。有有趣的

WITH generator AS
  (SELECT DATE '2015-01-02' + LEVEL - 1 dt
  FROM dual
    CONNECT BY LEVEL <= DATE '2016-01-21' - DATE '2015-01-02' + 1
  )
--  select * from generator;
SELECT TO_CHAR(dt, 'YYYY "SEM"IW') "KEY",
  dt "DATE_START",
  lead(dt) over (ORDER BY (dt)) -1 "End Date"
FROM generator
WHERE TO_CHAR(dt, 'D') = TO_CHAR(DATE '2015-01-02', 'D');
----------------------------------OUTPUT-----------------------------------------
**KEY       DATE_START  End Date**
2015 SEM18  05/01/2015  05/07/2015
2015 SEM19  05/08/2015  05/14/2015
2015 SEM20  05/15/2015  05/21/2015
2015 SEM21  05/22/2015  05/28/2015
2015 SEM22  05/29/2015  06/04/2015
----------------------------------------------------------------------------------