PostgreSQL 13：如何判断风险权限列表是否被员工权限列表包含？

你需要找出员工与风险的组合，要求员工完全具备该风险所需的所有权限，并存入新表results_table。你的原查询因嵌套子查询和全表扫描，数据量大时效率极低，下面给你几个高效的实现方案：

方案一：分组统计匹配（最通用高效） #

核心思路：先统计每个风险需要的总权限数，再统计每个员工对该风险已拥有的权限数，当两者相等时，说明员工握有该风险的全部权限。

假设你的表结构：

• permission_table：employee（员工姓名）、permission（权限）
• risk_table：risk_id（风险ID）、permission（该风险要求的权限）

对应的SQL：

-- 先创建结果表（若未创建）
CREATE TABLE IF NOT EXISTS results_table (
    employee TEXT,
    risk_id INT, -- 根据实际字段类型调整，比如字符串类型改为TEXT
    PRIMARY KEY (employee, risk_id) -- 可选，避免重复插入同一组合
);

-- 插入符合条件的员工-风险组合
INSERT INTO results_table (employee, risk_id)
SELECT 
    p.employee, 
    r.risk_id
FROM 
    permission_table p
JOIN 
    risk_table r ON p.permission = r.permission
GROUP BY 
    p.employee, r.risk_id
HAVING 
    COUNT(DISTINCT p.permission) = (SELECT COUNT(DISTINCT permission) FROM risk_table WHERE risk_id = r.risk_id);

优化关键：给两张表加联合索引，大幅提升分组和查询速度：

CREATE INDEX idx_risk_riskid_perm ON risk_table(risk_id, permission);
CREATE INDEX idx_perm_employee_perm ON permission_table(employee, permission);

方案二：NOT EXISTS反向排查（适合权限少的场景） #

思路反转：如果某个风险没有任何一个权限是员工缺失的，那这个员工就符合要求。

SQL代码：

INSERT INTO results_table (employee, risk_id)
SELECT 
    p.employee, 
    r.risk_id
FROM 
    permission_table p
CROSS JOIN (SELECT DISTINCT risk_id FROM risk_table) r
WHERE NOT EXISTS (
    SELECT 1 
    FROM risk_table rt 
    WHERE rt.risk_id = r.risk_id
    AND rt.permission NOT IN (SELECT permission FROM permission_table WHERE employee = p.employee)
);

注意：若某个风险需要的权限特别多，该方案的子查询次数会增加，更适合权限数量少的场景，同样需搭配上述索引优化。

方案三：PostgreSQL数组子集匹配（代码简洁+高效） #

利用PostgreSQL的数组特性，将每个员工的权限、每个风险的要求权限转成数组，再判断风险的权限数组是否为员工权限数组的子集。

SQL代码：

INSERT INTO results_table (employee, risk_id)
SELECT 
    emp.employee,
    risk.risk_id
FROM 
    -- 聚合每个员工的所有权限为数组
    (SELECT employee, ARRAY_AGG(DISTINCT permission) AS perms FROM permission_table GROUP BY employee) emp
JOIN 
    -- 聚合每个风险的要求权限为数组
    (SELECT risk_id, ARRAY_AGG(DISTINCT permission) AS req_perms FROM risk_table GROUP BY risk_id) risk
ON 
    risk.req_perms <@ emp.perms; -- <@ 是PostgreSQL的数组子集判断操作符

若数据量极大，可给数组建GIN索引进一步提速：

-- 创建物化视图存储聚合后的权限数组
CREATE MATERIALIZED VIEW emp_perms AS 
SELECT employee, ARRAY_AGG(DISTINCT permission) AS perms FROM permission_table GROUP BY employee;
CREATE INDEX idx_emp_perms_gin ON emp_perms USING GIN(perms);

CREATE MATERIALIZED VIEW risk_perms AS
SELECT risk_id, ARRAY_AGG(DISTINCT permission) AS req_perms FROM risk_table GROUP BY risk_id;
CREATE INDEX idx_risk_perms_gin ON risk_perms USING GIN(req_perms);

-- 用物化视图查询插入
INSERT INTO results_table (employee, risk_id)
SELECT emp.employee, risk.risk_id FROM emp_perms emp JOIN risk_perms risk ON risk.req_perms <@ emp.perms;

三个方案中，方案一的兼容性和性能平衡最好，适合绝大多数场景；方案三在权限数量较多时，借助GIN索引的数组匹配会有不错的表现。

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