SQL 创建以周为周期的表 - 在月末有休息（Oracle）

Question

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

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

，包含从 2007 年开始直到本周的所有周。

特殊之处在于，当月末在一周内时，该周被切成两部分 - 一个记录的开始日期是该周的开始，结束日期是该月的最后一天，以及一条包含一周剩余日期的记录（开始日期是新月的第一天，结束日期是一周的最后一天）。

示例（一周的开始是星期一）：
日历周；周开始日期；周末结束日期； ... 2009 CW48； 2009年11月23日； 2009年11月29日——“正常”周，为期7天，从星期一开始到星期日结束，2009年cW49； 2009年11月30日； 2009年11月30日——CW49的第一部分，结束于2009年cW49月份的最后一天； 2009 年 12 月 1 日； 06.12.2009 --CW49 的第二部分，从 2009 年新月 CW50 的第一天开始； 2009 年 12 月 7 日； 13.12.2009 --“正常”一周，没有每月休息...

如何在 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 格式掩码来获取周数和月份数，然后按这两者对结果进行分组。