SQL-从给定的开始日期开始计算结束日期并进行任意中断 [英] SQL -- computing end dates from a given start date with arbitrary breaks
问题描述
我有一张可变长度的学期表，它们之间有可变的中断，且有一个约束，使得开始日期始终大于上一个结束日期：
id开始日期结束日期
-----------------------------
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开始），然后通过减法找到使用以下方法的每学期之间的天数：
选择开始日期
，结束日期
，滞后（结束日期）OVER（）AS prev_end_date
，开始日期-lag（end_date）OVER（）AS days_break
从条款
开始订购日期；
很明显，如果只有两个术语，则只需添加'中断（可能转换为周），从而将 end_date延长相同的时间段。
但是给定学生的 n_weeks跨度不应该超过一个学期，这样的查询该如何构成？
在过去的几天里，我把头撞在墙上，对任何人都能提供的帮助我深表谢意。 p>
非常感谢。
而不仅仅是看学期的长短或两者之间的差距，您可以使用 generate_series（） ，例如：
选择
row_number（）OVER（）作为day_number，
天
FROM
（
SELECT
generate_series（start_date，end_date，'1 day' ）从
个学期开始
个学期
）作为day_series
ORDER BY
day
（ SQLFiddle演示）
这将为一个学期中的每一天分配任意但连续的天数，跳过学期之间的所有间隔。
然后，您可以将其用作子查询/ CTE JOIN 已添加到您的学生桌：首先找到他们的开始日期的天数，然后添加 7 * n_weeks 来找到其结束日期的天数，最后再加入以查找其实际日期该天数。
这假设在部分星期内不需要特殊处理-即，如果 n_weeks 是4，则该学生必须在第二学期内注册28天。该方法可适用于测量周数（将 1周作为 generate_series（）的最后一个参数）。查找学生的开始日期属于哪一周的另一步。
这是一个完整的查询（ SQLFiddle演示此处）：
与semester_days AS
（
选择
semester_id，
row_number（）OVER（）作为day_number，
day_date :: date
FROM
（
选择
id作为semester_id，
generate_series（start_date，end_date，'1 day'）作为day_date
FROM
semesters
）as day_series
OR BY BY
day_date
）
SELECT
S.id作为student_id，
S.start_date，
SD_start.semester_id作为start_semester_id，
S.n_weeks，
SD_end.day_date作为end_date，
SD_end.semester_id作为end_semester_id
来自
的学生为S
JOIN
semester_days作为SD_start
在SD_start.day_date = S.start_date
JOIN
semester_days作为SD_end
SD_end.day_number = SD_start.day_number +（7 * S.n_weeks）
OR BY BY
S.start_date
I have a table of 'semesters' of variable lengths with variable breaks in between them with a constraint such that a 'start_date' is always greater than the previous '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
And a table of students as follows, where a start date may occur at any time within a given semester:
id start_date n_weeks
-------------------------
1 2012-11-15 25
2 2013-02-12 8
3 2013-03-02 12
I am attempting to compute an 'end_date' by joining the 'students' on 'semesters' which takes into account the variable-length breaks in-between semesters.
I can draw in the previous semester's end date (ie from the previous row's end_date) and by subtraction find the number of days in-between semesters using the following:
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;
Clearly, if there were to be only two terms, it would simply be a matter of adding the 'break' in days (perhaps, cast to 'weeks') -- and thereby extend the 'end_date' by that same period of time.
But should 'n_weeks' for a given student span more than one term, how could such a query be structured ?
Been banging my head against a wall for the last couple of days and I'd be immensely grateful for any help anyone would be able to offer....
Many thanks.
Rather than just looking at the lengths of semesters or the gaps between them, you could generate a list of all the dates that are within a semester using generate_series(), like this:
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
This assigns each day that is during a semester an arbitrary but sequential "day number", skipping out all the gaps between semesters.
You can then use this as a sub-query/CTE JOINed to your table of students: first find the "day number" of their start date, then add 7 * n_weeks to find the "day number" of their end date, and finally join back to find the actual date for that "day number".
This assumes that there is no special handling needed for partial weeks - i.e. if n_weeks is 4, the student must be enrolled for 28 days which are within the duration of a semeseter. The approach could be adapted to measure weeks (pass 1 week as the last argument to generate_series()), with the additional step of finding which week the student's start_date falls into.
Here's a complete query (SQLFiddle demo here):
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
这篇关于SQL-从给定的开始日期开始计算结束日期并进行任意中断的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
