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() 的用法，我们使用以下 UNION 和 SELECT 语句模拟一个表：

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)}。