sql - 根据生成的结果过滤 SQL 查询

Question

我有一个 plpgsql 函数，它根据存储在名为 timeslots 的表中的规则在两个日期之间生成 TSTZRANGE。 .它工作正常，但现在我有了一个新要求并添加了一个名为 datespan 的 TSTZRANGE 字段到 timeslots我还需要过滤的表，以便只返回包含在该字段值中的范围。

在这里向您展示我需要更改的部分功能。 eid , fromdate和 todate是传递给函数的参数。

SELECT TSTZRANGE(
    (series::DATE + ts1.start)::TIMESTAMPTZ AT TIME ZONE 'UTC',
    (series::DATE + ts1.end)::TIMESTAMPTZ AT TIME ZONE 'UTC'
) AS range
FROM
  timeslots AS ts1,
  generate_series(fromdate, todate, '1 day') AS series
WHERE
  eid = ts1.event_id AND
  EXTRACT(DOW FROM series) = ANY(ts1.weekdays)
ORDER BY
  range

所以我想做的是添加一个像WHERE range <@ ts1.datespan这样的过滤器但我无法将它放在任何可用的地方。

例子:在 timeslots 中给出此信息表:

 id |  start   |   end    |   weekdays    | event_id |           datespan 
----+----------+----------+---------------+----------------------------------------------------------------
 1  | 12:00:00 | 14:00:00 | {1,2,3,5,6,0} |       1  | ["2017-01-01 00:00:00+00","2017-02-01 00:00:00+00")
 2  | 09:00:00 | 11:00:00 | {1,2,3,5,6,0} |       1  | [null, null)
 3  | 15:00:00 | 17:00:00 | {1,2,3,5,6,0} |       1  | ["2017-02-01 00:00:00+00","2017-03-01 00:00:00+00")

如果 eid=1 调用上述 SELECT 的结果, fromdate='2017-02-02'和 todate='2017-02-03'应该是这样的:

                        range                        
-----------------------------------------------------
 ["2017-02-02 09:00:00+00","2017-02-02 11:00:00+00")
 ["2017-02-02 15:00:00+00","2017-02-02 17:00:00+00")
 ["2017-02-03 09:00:00+00","2017-02-03 11:00:00+00")
 ["2017-02-03 15:00:00+00","2017-02-03 17:00:00+00")

Answer 1

最简单的是使用lateral:

The LATERAL key word can precede a sub-SELECT FROM item. This allows the sub-SELECT to refer to columns of FROM items that appear before it in the FROM list. (Without LATERAL, each sub-SELECT is evaluated independently and so cannot cross-reference any other FROM item.)

select ts1.*, range
from
    timeslots as ts1,
    generate_series('2017-02-02'::date, '2017-02-03', '1 day') as series
    cross join lateral
    (
        select tstzrange (
            (series::date + ts1.start)::timestamptz at time zone 'utc',
            (series::date + ts1.end)::timestamptz at time zone 'utc'
        ) as range
    ) range
where
    1 = ts1.event_id and
    extract(dow from series) = any(ts1.weekdays) and
    range <@ ts1.datespan
order by range
;
 id |  start   |   end    |   weekdays    | event_id |                      datespan                       |                        range                        
----+----------+----------+---------------+----------+-----------------------------------------------------+-----------------------------------------------------
  2 | 09:00:00 | 11:00:00 | {1,2,3,5,6,0} |        1 | (,)                                                 | ["2017-02-03 09:00:00+00","2017-02-03 11:00:00+00")
  3 | 15:00:00 | 17:00:00 | {1,2,3,5,6,0} |        1 | ["2017-02-01 00:00:00+00","2017-03-01 00:00:00+00") | ["2017-02-03 15:00:00+00","2017-02-03 17:00:00+00")

另一种方法是将其包装在外部查询中。现在您需要决定/告知如何处理空边界。