PostgreSQL unnest, int4range with where clause

Question

我有一个名为mytable的数据表：

╔════╤══════════════════════════════╤══════════╗
║ id │ segments                     │ duration ║
╠════╪══════════════════════════════╪══════════╣
║ 1  │ {"[1,4)","[6,13)","[15,19)"} │ 14       ║
╟────┼──────────────────────────────┼──────────╢
║ 2  │ {"[3,16)","[19,22)"}         │ 16       ║
╚════╧══════════════════════════════╧══════════╝

segments是以秒为单位的时间间隔数组。
duration是以秒为单位的时间间隔的总和，以segments为单位。

例如，对于id=1，segments中的三个间隔分别为 3、7 和 4 秒。他们总共 14 秒duration.

我想从此表仅提取满足两个条件的行：

• 没有不少于10秒的单个segments
duration至少为
• 10 秒

查询应仅返回 =1id，因为其各个segments都小于 10 秒，并且其duration至少为 10 秒。

查询不应返回id=2，因为它的一个segments长度为 13 秒。它的duration至少为 10 秒，但它不符合有关segments的第一个条件。

Answer 1

在横向连接中取消嵌套数组，并通过id计算组中的总和。使用布尔聚合bool_and()消除小于 10 秒的段。

select id, segments, sum(elem.upper- elem.lower) as duration
from my_table
cross join unnest(segments) elem
group by id
having bool_and(elem.upper- elem.lower < 10)
and sum(elem.upper- elem.lower) >= 10

Db<>小提琴。

Answer 2

您可以执行横向连接：

select t.*
from mytable t
inner join lateral (
select bool_and(seg.upper - seg.lower < 10) to_keep
from unnest(t.segments) seg 
) x on x.to_keep
where t.duration >+ 10

子查询取消嵌套数组并使用bool_or()来确保所有间隔都小于 10 秒;连接条件会消除不需要的行。

DB小提琴上的演示：

ID | 区段 | 持续时间 -: |:--------------------------- |-------:  1 |{"[1,4("，"[6,13("，"[15,19("} |      14