如何用PostgreSQL查询生成企业-服务关联矩阵？

需求描述 #

我需要通过PostgreSQL查询，基于3张表生成一份公司与服务的关联矩阵，该如何用SQL实现？

现有表结构 #

Company表 #

+----+-------------+
| id | name        |
+----+-------------+
|  1 | 9999999991  |
|  2 | 9999999992  |
|  3 | 9999999993  |
|  4 | 9999999994  |
|  5 | 9999999995  |
|  6 | 9999999996  |
|  7 | 9999999997  |
|  8 | 9999999998  |
+----+-------------+

Services表 #

+----+-------------+
| id | name        |
+----+-------------+
|  1 | Service 1   |
|  2 | Service 2   |
|  3 | Service 3   |
|  4 | Service 4   |
+----+-------------+

Service Company Map表 #

+----+-----------+--------------+
| id | company   | services     |
+----+-----------+--------------+
|  1 | 9999999991|    2         |
|  2 | 9999999991|    4         |
|  3 | 9999999992|    1         |
|  4 | 9999999992|    4         |
|  5 | 9999999993|    1         |
|  6 | 9999999993|    3         |
|  7 | 9999999993|    4         |
+----+-----------+--------------+

期望生成的矩阵 #

+----------+----------+----------+----------+----------+
|          | Service 1| Service 2| Service 3| Service 4|
+----------+----------+----------+----------+----------+
| Company 1|       -  |       X  |       -  |       X  |
| Company 2|       X  |       -  |       -  |       X  |
| Company 3|       X  |       -  |       X  |       X  | 
| Company 4|       -  |       -  |       -  |       -  |
+----------+----------+----------+----------+----------+

更新：补充DDL与插入语句 #

CREATE TABLE service_client_mappings
( id int NOT NULL,
  company_id int NOT NULL,
  service_id int NOT NULL,
  CONSTRAINT service_client_mappings_pk PRIMARY KEY (id)
);

CREATE TABLE services
( id int NOT NULL,
  name char(50) NOT NULL,
  CONSTRAINT services_pk PRIMARY KEY (id)
);

CREATE TABLE company
( id int NOT NULL,
  name char(50) NOT NULL,
  CONSTRAINT company_pk PRIMARY KEY (id)
);

INSERT INTO company
(id, name)
VALUES
(1, 'ACME');

INSERT INTO company
(id, name)
VALUES
(2, 'Target');

INSERT INTO company
(id, name)
VALUES
(3, 'Walmart');

INSERT INTO services
(id, name)
VALUES
(1, 'Service A');

INSERT INTO services
(id, name)
VALUES
(2, 'Service B');

INSERT INTO services
(id, name)
VALUES
(3, 'Service C');

INSERT INTO services
(id, name)
VALUES
(4, 'Service D');

INSERT INTO service_client_mappings
(id, company_id, service_id)
VALUES
(1, 1, 2);

INSERT INTO service_client_mappings
(id, company_id, service_id)
VALUES
(2, 1, 4);

INSERT INTO service_client_mappings
(id, company_id, service_id)
VALUES
(3, 2, 1);

INSERT INTO service_client_mappings
(id, company_id, service_id)
VALUES
(4, 3, 2);

INSERT INTO service_client_mappings
(id, company_id, service_id)
VALUES
(5, 3, 3);

（注：修正了原插入语句中service_client_mappings表的主键重复问题）

解决方案 #

核心思路是先生成公司与服务的全量笛卡尔积，关联映射表判断是否存在绑定关系，再通过行列转换实现矩阵格式输出。

静态列方案（服务数量固定） #

如果服务列表不会频繁变动，直接写死列名即可快速实现：

SELECT
    c.name AS "公司",
    CASE WHEN EXISTS (SELECT 1 FROM service_client_mappings scm WHERE scm.company_id = c.id AND scm.service_id = 1) THEN 'X' ELSE '-' END AS "Service A",
    CASE WHEN EXISTS (SELECT 1 FROM service_client_mappings scm WHERE scm.company_id = c.id AND scm.service_id = 2) THEN 'X' ELSE '-' END AS "Service B",
    CASE WHEN EXISTS (SELECT 1 FROM service_client_mappings scm WHERE scm.company_id = c.id AND scm.service_id = 3) THEN 'X' ELSE '-' END AS "Service C",
    CASE WHEN EXISTS (SELECT 1 FROM service_client_mappings scm WHERE scm.company_id = c.id AND scm.service_id = 4) THEN 'X' ELSE '-' END AS "Service D"
FROM company c
ORDER BY c.id;

动态列方案（服务数量可变） #

如果服务会动态新增/删除，推荐使用PostgreSQL的crosstab交叉表函数（需先安装tablefunc扩展）：

1. 启用扩展：

CREATE EXTENSION IF NOT EXISTS tablefunc;

2. 编写交叉查询：

SELECT * FROM crosstab(
    -- 生成基础数据集：公司、服务、关联标记
    'SELECT 
        c.name AS company_name,
        s.name AS service_name,
        CASE WHEN scm.company_id IS NOT NULL THEN ''X'' ELSE ''-'' END AS has_service
     FROM company c
     CROSS JOIN services s
     LEFT JOIN service_client_mappings scm 
         ON c.id = scm.company_id AND s.id = scm.service_id
     ORDER BY c.name, s.name',
    -- 指定所有服务列的名称列表
    'SELECT name FROM services ORDER BY id'
) AS ct(
    "公司" text,
    "Service A" text,
    "Service B" text,
    "Service C" text,
    "Service D" text
)
ORDER BY "公司";

完全动态列方案（无需手动指定列名） #

如果需要自动适配服务列表的变化，可以用PL/pgSQL编写存储过程，动态生成SQL：

CREATE OR REPLACE FUNCTION get_company_service_matrix()
RETURNS TABLE(result json) AS $$
DECLARE
    service_cols text;
BEGIN
    -- 动态生成服务列的定义语句
    SELECT string_agg(DISTINCT quote_ident(s.name) || ' text', ', ')
    INTO service_cols
    FROM services s;

    -- 执行动态构建的交叉查询，返回JSON格式结果
    RETURN QUERY EXECUTE format(
        'SELECT row_to_json(t) FROM (
            SELECT * FROM crosstab(
                ''SELECT 
                    c.name AS company_name,
                    s.name AS service_name,
                    CASE WHEN scm.company_id IS NOT NULL THEN ''''X'''' ELSE ''''-'''' END AS has_service
                 FROM company c
                 CROSS JOIN services s
                 LEFT JOIN service_client_mappings scm 
                     ON c.id = scm.company_id AND s.id = scm.service_id
                 ORDER BY c.name, s.name'',
                ''SELECT name FROM services ORDER BY id''
            ) AS ct(
                "公司" text, %s
            )
        ) t', service_cols
    );
END;
$$ LANGUAGE plpgsql;

-- 调用函数获取结果
SELECT * FROM get_company_service_matrix();

内容的提问来源于Stack Exchange，提问作者KevinL