PostgreSQL unnest，带有where子句的int4range

debugcn 发表于 Dev

bbk611

我有一个名为的数据表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。

我只想从该表中提取同时满足这两个条件的行：

没有任何人segments不少于10秒
有一个duration是至少10秒

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

该查询不应返回id= 2，因为查询之一的segments长度为13秒。它duration至少有10秒，但它没有涉及到第一个条件segments。

克林

取消横向连接中的数组的嵌套，并通过分组计算总和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 <>提琴。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。