用于创建具有期间周数的表的SQL -在月末具有中断(Oracle)

Question

我需要创建一个具有以下结构的表：

日历周；周开始日期；周结束日期

它包含从2007年开始到当前周的所有周。

特殊的是，当一个月的结束落在一周内时，该周被分成两个部分-一个记录的开始日期是该周的开始日期，结束日期是该月的最后一天，另一个记录包含该周其余时间的日期(开始日期是新月的第一天，结束日期是该周的最后一天)。

示例(周的开始是星期一)：

calendar week; week start date; week end date; ... 2009 cW48; 23.11.2009; 29.11.2009 --"normal" week with 7 days, beginning monday and ending sunday 2009 cW49; 30.11.2009; 30.11.2009 --first part of the CW49, which ends at last day of the month 2009 cW49; 01.12.2009; 06.12.2009 --second part of the CW49, which begins at fist day of the new month 2009 cW50; 07.12.2009; 13.12.2009 --"normal" week, without a monthly break ...

如何在Oracle (SQL或PL SQL)中创建这样的表？

Answer 1

您可以像这样创建表：

SQL> CREATE TABLE weeks AS
  2  WITH generator AS (
  3     SELECT DATE '2007-01-01' + LEVEL - 1 dt
  4       FROM dual
  5     CONNECT BY LEVEL <= SYSDATE - DATE '2007-01-01' + 1
  6  )
  7  SELECT to_char(dt, 'YYYY "cW"IW') "calendar week",
  8         dt "week start date",
  9         least(next_day(dt - 1, to_char(DATE '2007-01-07', 'DAY')),
 10               last_day(dt)) "week end date"
 11    FROM generator
 12   WHERE to_char(dt, 'D') = to_char(DATE '2007-01-01', 'D') -- only mondays
 13      OR to_char(dt, 'dd') = 1 --or first day of the month
 14  ;

Table created
SQL> SELECT *
  2    FROM weeks
  3   WHERE "week start date" BETWEEN DATE '2009-11-15' AND DATE '2009-12-15';

calendar week week start date week end date
------------- --------------- -------------
2009 cW47     16/11/2009      22/11/2009
2009 cW48     23/11/2009      29/11/2009
2009 cW49     30/11/2009      30/11/2009
2009 cW49     01/12/2009      06/12/2009

Answer 2

这是一个用于创建表的小PL/SQL块。如果需要，可以更改表名。

-- create table weeks(year number, week number, b_date date, e_date date);

DECLARE
    i  DATE;
    s  DATE;
    wk NUMBER;
    yr NUMBER;

    FUNCTION getweek(l DATE) RETURN NUMBER IS
    BEGIN
        -- !! week of year, iso standard, (31. dec can be on the first week of next year) !!
        RETURN to_char(l, 'IW');
    END;
BEGIN
    i := to_date('2007-01-01', 'yyyy-mm-dd');
    s := i;

    DELETE weeks;

    WHILE i <= to_date('2009-12-31', 'yyyy-mm-dd') LOOP

        IF trunc(s, 'MONTH') <> trunc(i, 'MONTH') OR
           getweek(s) <> getweek(i) THEN

            wk := getweek(s);
            yr := to_char(s, 'YYYY');
            INSERT INTO weeks VALUES (yr, wk, s, i - 1);

            s := i;
            i := s;
        END IF;

        i := i + 1;
    END LOOP;

    i := i - 1;

    wk := getweek(s);
    yr := to_char(s, 'YYYY');
    INSERT INTO weeks VALUES (yr, wk, s, i);

    COMMIT;
END;

Answer 3

您可以这样创建表：

    create table weeks(cw, start_date, end_date)
    as
    select to_char(gen.d,'YYYY "cW"IW')
         , min(gen.d)
         , max(gen.d)
      from (
             select to_date('01.01.2007','DD.MM.YYYY') + level -1 d
               from dual
            connect by level <= 1500 -- approx. number of days
           ) gen
     group by
           to_char(gen.d,'YYYY "cW"IW')
         , to_char(gen.d,'YYYY MM IW')
    having min(gen.d) <= sysdate

重点是使用IW和MM格式掩码来获取周数和月数，然后将结果按周和月进行分组。