如何构造中间虚拟表或用更优方法实现SQL数组匹配查询？

嘿，我来帮你捋清楚这个问题～首先我们先解决你提到的中间表构造，然后再聊聊更优的实现方案。

一、如何构造你想要的中间表？ #

你要的中间表，本质是给每个value生成包含该行所有类别、且每个类别仅选一个编码的所有可能数组组合。比如foo对应的fun_data是{A1,B1,B2}，类别是A和B，所以要生成{A1,B1}和{A1,B2}这两个组合；bar的fun_data每个类别只有一个编码，所以只生成{A1,B1,C1}。

用PostgreSQL的递归CTE可以完美实现这个需求，SQL代码如下：

WITH RECURSIVE split_data AS (
    -- 把原表的fun_data拆成单个元素，顺便提取每个元素的类别（A/B/C）
    SELECT 
        value,
        substring(elem from '^[A-Z]') AS category,
        elem
    FROM your_table
    CROSS JOIN unnest(fun_data) AS elem
),
category_code_groups AS (
    -- 按value和类别分组，把同类别下的所有编码聚合成数组
    SELECT 
        value,
        category,
        array_agg(DISTINCT elem) AS codes
    FROM split_data
    GROUP BY value, category
),
recursive_combinations AS (
    -- 初始步骤：每个类别下的每个编码单独作为初始组合
    SELECT 
        value,
        ARRAY[code] AS fun_data,
        ARRAY[category] AS used_categories
    FROM category_code_groups
    CROSS JOIN unnest(codes) AS code
    UNION ALL
    -- 递归拼接：把还没用到的类别的编码，和现有组合拼在一起
    SELECT 
        rc.value,
        rc.fun_data || ccg.code,
        rc.used_categories || ccg.category
    FROM recursive_combinations rc
    JOIN category_code_groups ccg 
        ON ccg.value = rc.value 
        AND NOT ccg.category = ANY(rc.used_categories)
    CROSS JOIN unnest(ccg.codes) AS code
),
full_category_combinations AS (
    -- 只保留包含该行所有类别的组合（比如foo的组合必须同时有A和B类）
    SELECT 
        rc.value,
        rc.fun_data
    FROM recursive_combinations rc
    JOIN (
        SELECT value, count(DISTINCT category) AS total_categories
        FROM split_data
        GROUP BY value
    ) vc 
        ON rc.value = vc.value 
        AND array_length(rc.used_categories, 1) = vc.total_categories
)
-- 这就是你要的中间表啦
SELECT value, fun_data FROM full_category_combinations;

执行后就能得到你给出的中间表结果，之后你就可以用SELECT value FROM full_category_combinations WHERE fun_data <@ 你的输入数组;来查询了。

二、更优的实现方案：不用构造中间表 #

说实话，构造中间表的方式虽然直观，但当原表数据量较大时，会生成大量中间数据，性能会受影响。其实我们可以直接在原表上通过聚合判断实现匹配，完全不需要中间表：

-- 先定义你的输入数组，替换成实际要查的内容就行
WITH input_params AS (
    SELECT '{A1,B1,C1}'::text[] AS input_data
),
input_category_map AS (
    -- 把输入数组转成「类别=>编码」的映射表，方便后续匹配
    SELECT 
        substring(elem from '^[A-Z]') AS category,
        elem AS code
    FROM input_params
    CROSS JOIN unnest(input_data) AS elem
),
row_category_info AS (
    -- 拆分原表的fun_data，提取每行每个元素的类别和编码
    SELECT 
        t.id,
        t.value,
        substring(elem from '^[A-Z]') AS category,
        elem AS code
    FROM your_table t
    CROSS JOIN unnest(t.fun_data) AS elem
)
SELECT DISTINCT rci.value
FROM row_category_info rci
-- 匹配输入中对应类别的编码
JOIN input_category_map icm 
    ON rci.category = icm.category 
    AND rci.code = icm.code
GROUP BY rci.id, rci.value
HAVING 
    -- 条件1：该行的所有类别，在输入数组里都有对应的编码（比如行2有C类，输入必须有C类的编码才会匹配）
    count(DISTINCT rci.category) = (
        SELECT count(DISTINCT category) 
        FROM row_category_info 
        WHERE id = rci.id
    )
    -- 条件2：该行有的所有类别，都能在输入数组里找到匹配的编码（比如行1有A和B类，输入必须有A1和B1/B2）
    AND count(DISTINCT icm.category) = (
        SELECT count(DISTINCT category) 
        FROM input_category_map
        WHERE category IN (
            SELECT DISTINCT category 
            FROM row_category_info 
            WHERE id = rci.id
        )
    );

这个方案的好处很明显：

1. 避免了生成大量中间数据，节省内存和磁盘IO
2. 可以利用原表的索引（比如给fun_data建GIN索引，或者给拆分后的类别/编码建普通索引），查询速度会快很多
3. 逻辑直接对应你设定的匹配规则，更易于维护和修改

验证你的示例 #

用这个方案测试你给出的示例：

• 输入{A1,B1}：只返回foo（符合预期）
• 输入{A1,B2}：只返回foo（符合预期）
• 输入{A1,B1,C1}：返回foo和bar（符合预期）
• 输入{A1,C1}：返回空（符合预期）

内容的提问来源于stack exchange，提问作者msladecek