如何在PostgreSQL中生成行列头同源的客户购买组合计数矩阵

需求说明 #

现有包含customerId（客户ID）和productName（产品名称）的表，需生成以productName同时作为行、列标题的矩阵，统计同时购买对应行、列产品组合的客户数量（同一客户多次购买同一产品仅计1次）。

原始数据表 #

customerId|productName
----------+-----------
1         | apple
2         | apple
3         | banana
1         | apple
1         | banana
3         | pizza

预期输出 #

(empty)|apple|banana|pizza
-------+-----+------+------
apple  | 2   |  1   |   0
banana | 1   |  2   |   1
pizza  | 0   |  1   |   1

解决方案 #

核心思路 #

1. 对客户-产品数据去重，避免同一客户多次购买同一产品重复统计；
2. 生成所有产品的笛卡尔积，得到行、列的所有组合；
3. 关联去重后的客户数据，统计每个产品组合对应的客户数；
4. 将行式结果转换为矩阵（透视表）格式。

方法1：固定产品列表（手动透视） #

适用于产品列表固定的场景，直接通过CASE语句生成矩阵：

WITH customer_products AS (
    -- 去重：每个客户对应唯一产品
    SELECT DISTINCT customerId, productName
    FROM your_table_name  -- 替换为实际表名
),
product_list AS (
    -- 获取所有唯一产品
    SELECT DISTINCT productName
    FROM your_table_name
),
product_pairs AS (
    -- 统计每个产品组合的客户数
    SELECT
        p1.productName AS row_product,
        p2.productName AS col_product,
        COUNT(DISTINCT cp1.customerId) AS customer_count
    FROM product_list p1
    CROSS JOIN product_list p2  -- 生成所有行、列产品组合
    LEFT JOIN customer_products cp1 ON p1.productName = cp1.productName
    LEFT JOIN customer_products cp2 
        ON p2.productName = cp2.productName 
        AND cp1.customerId = cp2.customerId  -- 匹配同时购买两种产品的客户
    GROUP BY p1.productName, p2.productName
)
-- 转换为矩阵格式
SELECT
    row_product AS "(empty)",
    MAX(CASE WHEN col_product = 'apple' THEN customer_count ELSE 0 END) AS apple,
    MAX(CASE WHEN col_product = 'banana' THEN customer_count ELSE 0 END) AS banana,
    MAX(CASE WHEN col_product = 'pizza' THEN customer_count ELSE 0 END) AS pizza
FROM product_pairs
GROUP BY row_product
ORDER BY row_product;

方法2：动态产品列表（使用crosstab函数） #

若产品列表不固定，可使用PostgreSQL的tablefunc扩展提供的crosstab函数实现动态透视：

1. 先启用tablefunc扩展（仅需执行一次）：

CREATE EXTENSION IF NOT EXISTS tablefunc;

2. 执行透视查询：

WITH customer_products AS (
    SELECT DISTINCT customerId, productName
    FROM your_table_name
),
product_list AS (
    SELECT DISTINCT productName
    FROM your_table_name
),
product_pairs AS (
    SELECT
        p1.productName AS row_product,
        p2.productName AS col_product,
        COUNT(DISTINCT cp1.customerId) AS customer_count
    FROM product_list p1
    CROSS JOIN product_list p2
    LEFT JOIN customer_products cp1 ON p1.productName = cp1.productName
    LEFT JOIN customer_products cp2 
        ON p2.productName = cp2.productName 
        AND cp1.customerId = cp2.customerId
    GROUP BY p1.productName, p2.productName
    ORDER BY p1.productName, p2.productName
)
SELECT *
FROM crosstab(
    -- 数据源查询：行、列、值
    'SELECT row_product, col_product, customer_count FROM product_pairs ORDER BY 1,2',
    -- 列名查询：所有产品作为列
    'SELECT DISTINCT productName FROM product_list ORDER BY 1'
) AS ct(
    "(empty)" text,
    apple integer,
    banana integer,
    pizza integer
);

注意：crosstab的结果列定义仍需手动匹配产品列表，若需完全动态生成，可结合PL/pgSQL编写存储过程实现。

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