PostgreSQL中如何将列值与另一数组列元素进行比较？

问题说明 #

我有一个PostgreSQL表，包含3列，结构如下：

col A            col B        col C                                     
----------      ---------   ---------                                 
2020-01-01      2024-01-01   {2020-01-01, 2020-05-01, 2022-03-01}    
2020-05-01      2021-05-01   {2020-01-01, 2020-05-01, 2022-03-01}
2022-03-01      2023-03-01   {2020-01-01, 2020-05-01, 2022-03-01}

其中col C是通过窗口函数对col A执行array_agg得到的结果。我需要判断每行的col A的值是否大于等于col C数组中的任意元素；实际场景中还有col D（对col B执行array_agg得到的数组），要同时满足col A >= col C数组中的任意元素，且col B <= col D数组中的任意元素，不清楚如何将单个值与数组中的每个元素进行比较。

可行解决方案 #

方法1：使用ANY操作符（推荐） #

PostgreSQL的ANY操作符专门用于单值与数组元素的批量比较，只要数组中有任意一个元素满足比较条件，就返回true，写法简洁且性能高效。

针对需求的SQL示例：

SELECT 
    "col A", 
    "col B", 
    "col C", 
    "col D",
    -- 判断col A >= col C中任意元素
    "col A" >= ANY("col C") AS a_ge_any_c,
    -- 判断col B <= col D中任意元素
    "col B" <= ANY("col D") AS b_le_any_d,
    -- 同时满足两个条件的结果
    ("col A" >= ANY("col C") AND "col B" <= ANY("col D")) AS meet_both_conditions
FROM your_table_name;

方法2：使用unnest展开数组（适合需查看匹配细节的场景） #

如果需要明确知道是数组中的哪个元素满足条件，可以用unnest将数组拆分为行，再逐行比较后聚合结果：

SELECT 
    t."col A",
    t."col B",
    t."col C",
    t."col D",
    -- 判断是否存在col C元素 <= col A
    MAX(CASE WHEN t."col A" >= c_element THEN true ELSE false END) AS a_ge_any_c,
    -- 判断是否存在col D元素 >= col B
    MAX(CASE WHEN t."col B" <= d_element THEN true ELSE false END) AS b_le_any_d,
    -- 同时满足两个条件的结果
    MAX(CASE WHEN t."col A" >= c_element AND t."col B" <= d_element THEN true ELSE false END) AS meet_both_conditions
FROM your_table_name t
LEFT JOIN unnest(t."col C") AS c_element ON true
LEFT JOIN unnest(t."col D") AS d_element ON true
GROUP BY t."col A", t."col B", t."col C", t."col D";

注意事项 #

• 优先使用ANY操作符，当数组元素数量较多时，它的性能远优于unnest展开的方式。
• 确保数组元素类型与单值类型一致（例如都是date类型），避免出现类型不匹配的错误。

内容的提问来源于stack exchange，提问作者nakkhatra-opti