python - 如何在 SQLAlchemy 或 SQL 的间隔交集中查找匹配月份的条目？

Question

我的一位用户想回答这个问题:

“2005 年到 2010 年三月的任何一个月中存在的请求是什么？”

DB 表“request”有 2 列，start_date 和 end_date，代表请求生命周期的间隔。

SQLAlchemy 模型如下所示:

class Request(SomeBaseModel):
    ...
    start_date = db.Column(db.Date, default=date.today)
    end_date = db.Column(db.Date, default=in_one_year)

然后我有一个进程在 Python 中动态获取 5 年零一个月的跨度:

`initial_date`, five_years_later, month_number = getTimePeriod()

根据这些参数，我必须列出在 initial_date 和 five_years_later 之间开始或结束的所有请求。通过将 start_date 和 end_date 与 initial_date 和 five_years_later 进行比较，我可以很容易地做到这一点。

然而，困难的部分是仅获取在该特定月份存在的请求，而该月份也是 (initial_date, five_years_later) 间隔的一部分。规则是:

• 请求可以在这个月之前和之后存在，但是这个月必须在它的生命周期内。
• 月份在请求生命周期内可以出现多次，但不能出现0次。
• 完全相同的月份必须出现在一个请求生命周期和(initial_date, five_years_later)间隔内。

我可以通过为 (initial_date, five_years_later) 间隔的每一年生成每个月的开始日期和结束日期来做到这一点，然后检查是否有任何这些对与请求生命周期在某处重叠:

        filters = []
        for year in range(initial_date, five_years_later + 1):
            month_start_date = datetime(year, month, 1)
            month_end_date = datetime(year, month, calendar.mdays[month])
            filters.append(
                (requeest.start_date <= month_end_date) &
                (request.end_date >= month_start_date)
            )
        is_active = functools.reduce(operator.or_, filters)
        auth_requests = auth_requests.filter(is_active)

但是我的直觉告诉我有更好的方法。

SQLAlchemy 查询可能是最好的答案，但 Postgres 的 SQL 版本就可以了。

Answer 1

我是这样分解问题的。

要么 - 请求的持续时间应大于等于 1 年。

或者 - start_date 必须在三月期间或之后并且end_date 应该在三月期间或之前。

和

开始日期/结束日期必须在范围内 (2005,2010)

此 postgres 查询将检查条件:

select * from request
where 1 = 
CASE
    WHEN extract(year from age(end_date,start_date)) >= 1 THEN 1
    WHEN (extract(month from start_date)::integer <= 3 
             AND extract(month from end_date)::integer >= 3 )
         AND extract(year from age(end_date,start_date)) < 1 THEN 1
    WHEN (extract(month from start_date)::integer >= 3 
             AND extract(month from end_date)::integer <= 3 )
         AND extract(year from age(end_date,start_date)) < 1 THEN 1
    ELSE 0
END
AND ((extract(year from start_date)::integer >= 2005
and  extract(year from start_date)::integer <= 2010)
OR (extract(year from end_date)::integer >= 2005
and  extract(year from end_date)::integer <= 2010))
;

编辑:这比我最初意识到的要复杂。编辑查询以满足所有条件。