SQL - даты окончания вычислений с заданной даты начала с произвольными перерывами

Question

У меня есть таблица "семестров" переменной длины с переменными перерывами между ними с ограничением, так что "start_date" всегда больше, чем предыдущая "end_date":

    id   start_date    end_date
    -----------------------------
     1   2012-10-01   2012-12-20 
     2   2013-01-05   2013-03-28
     3   2013-04-05   2013-06-29
     4   2013-07-10   2013-09-20

И следующая таблица студентов, где дата начала может произойти в любое время в течение данного семестра:

   id    start_date  n_weeks
   -------------------------
    1    2012-11-15     25
    2    2013-02-12      8 
    3    2013-03-02     12 

Я пытаюсь вычислить "конечную дату", присоединившись к "студентам" на "семестрах", которая учитывает разрывы переменной длины между семестрами.

Я могу нарисовать дату окончания предыдущего семестра (то есть из даты окончания предыдущей строки) и вычесть количество дней между семестрами, используя следующее:

    SELECT  start_date
          , end_date
          , lag(end_date) OVER () AS prev_end_date
          , start_date - lag(end_date) OVER () AS days_break  
    FROM terms 
    ORDER BY start_date;

Ясно, что если бы существовало только два термина, это было бы просто вопросом добавления "перерыва" в днях (возможно, приведения к "неделям") - и тем самым продления "конечной даты" на тот же период времени.

Но как "n_weeks" для данного студента охватывать более одного термина, как такой запрос может быть структурирован?

Последние два дня я бился головой о стену, и я был бы безмерно благодарен за любую помощь, которую кто-либо сможет предложить...

Большое спасибо.

Answer 1

Вместо того, чтобы просто смотреть на продолжительность семестров или промежутки между ними, вы можете сгенерировать список всех дат внутри семестра, используя generate_series(), как это:

SELECT
  row_number() OVER () as day_number,
  day
FROM
(
  SELECT
    generate_series(start_date, end_date, '1 day') as day
  FROM
    semesters
) as day_series
ORDER BY 
  day

( SQLFiddle demo)

Это назначает каждому дню в течение семестра произвольный, но последовательный "номер дня", пропуская все промежутки между семестрами.

Затем вы можете использовать это как подзапрос / CTE JOIN Перейдите к таблице учеников: сначала найдите "номер дня" их начальной даты, затем добавьте 7 * n_weeks чтобы найти "номер дня" их даты окончания, и, наконец, присоединиться, чтобы найти фактическую дату для этого "номера дня".

Это предполагает, что не требуется никакой специальной обработки в течение неполных недель, т.е. если n_weeks это 4, студент должен быть зачислен на 28 дней, которые в течение семестра. Подход может быть адаптирован для измерения недель (пройти 1 week в качестве последнего аргумента generate_series()), с дополнительным шагом нахождения какой недели start_date падает в.

Вот полный запрос ( демонстрация SQLFiddle здесь):

WITH semester_days AS
(
  SELECT
    semester_id,
    row_number() OVER () as day_number,
    day_date::date
  FROM
  (
    SELECT
      id as semester_id,
      generate_series(start_date, end_date, '1 day') as day_date
    FROM
      semesters
  ) as day_series
  ORDER BY 
    day_date
)
SELECT
  S.id as student_id,
  S.start_date,
  SD_start.semester_id as start_semester_id,
  S.n_weeks,
  SD_end.day_date as end_date,
  SD_end.semester_id as end_semester_id
FROM
  students as S
JOIN
  semester_days as SD_start
  On SD_start.day_date = S.start_date
JOIN
  semester_days as SD_end
  On SD_end.day_number = SD_start.day_number + (7 * S.n_weeks)
ORDER BY
  S.start_date