PostgreSQL range_intersect_agg() 函数使用指南

PostgreSQL range_intersect_agg() 函数是一个聚合函数,它返回一个分组中所有非 NULL 输入范围值的交集。

range_intersect_agg() 语法

这里是 PostgreSQL range_intersect_agg() 的语法:

range_intersect_agg(expr) -> RANGE

我们通常在 SQLite 中按如下方式使用 range_intersect_agg() 函数:

SELECT range_intersect_agg(expr), ...
FROM table_name
[WHERE ...]
[GROUP BY group_expr1, group_expr2, ...];

参数

expr
必需的。一个列名或者表达式。

返回值

PostgreSQL range_intersect_agg() 函数返回一个数组,其中包含了一个分组中所有非 NULL 输入范围值的交集。

如果多个范围值之间没有交集,则返回 empty

range_intersect_agg() 示例

为了演示 PostgreSQL range_intersect_agg() 的用法,我们使用以下 UNIONSELECT 语句模拟一个表:

SELECT 'Tim' name, '[3,7)'::int4range range_value
UNION
SELECT 'Tim' name, '[8,10]'::int4range range_value
UNION
SELECT 'Tom' name, '(3,7)'::int4range range_value
UNION
SELECT 'Tom' name, '[4,9)'::int4range range_value;
 name | range_value
------+-------------
 Tom  | [4,9)
 Tom  | [4,7)
 Tim  | [3,7)
 Tim  | [8,11)
(4 rows)

这里,我们拥有了关于用户选择的范围值的一些行。其中 name 列中是用户的姓名, range_value 列中是用户的一个区间值。

假如,要想以用户为单位(具有相同名称的用户视为一个用户),将每一个用户的所有的范围值都放在一个数组中,您可以使用 GROUP BY 子句按照 name 分组,并使用 range_intersect_agg() 函数计算每组中所有的range_value 的交集。下面的语句实现了这个需求:

SELECT
    t.name,
    range_intersect_agg(t.range_value) range_intersect_agg
FROM (
    SELECT 'Tim' name, '[3,7)'::int4range range_value
    UNION
    SELECT 'Tim' name, '[8,10]'::int4range range_value
    UNION
    SELECT 'Tom' name, '(3,7)'::int4range range_value
    UNION
    SELECT 'Tom' name, '[4,9)'::int4range range_value
) t
GROUP BY t.name;
 name | range_intersect_agg
------+---------------------
 Tim  | empty
 Tom  | [4,7)
(2 rows)

这里:

  • 对于 Tim, [3,7)[8,10] 没有交集,因此返回值 empty
  • 对于 Tom, (3,7)[4,9) 的交集为 {[4,9)}