SQL的结束日期从给定的开始日期起任意中断

admin

SQL的结束日期从给定的开始日期起任意中断

sql

我有一个可变长度的“学期”表，它们之间有可变的中断，并且有一个约束，使得“ 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

我试图通过加入“学期”中的“学生”来计算“结束日期”，其中考虑了学期之间的可变长度休息时间。

我可以得出前一个学期的结束日期（即从上一行的end_date开始），并通过减法找到使用以下方法的两个学期之间的天数：

    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;

显然，如果只有两个术语，则只需在几天之内添加“休息”（也许转换为“周”）即可，从而将“ end_date”延长相同的时间。

但是给定学生的“ n_weeks”跨度不应该超过一个学期，这样的查询如何构成？

在过去的几天里，我的头一直撞在墙上，对于任何人都能提供的任何帮助，我将深表感激。

非常感谢。

阅读 177

2021-06-07

共1个答案

admin

而不是只盯着学期的长度或它们之间的差距，可以产生都是一个学期之内，使用日期的列表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演示）

这会为一个学期中的每一天分配一个任意但连续的“天数”，从而跳过了两个学期之间的所有间隔。

然后，您可以将其用作学生表的子查询/
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

2021-06-07