如何在SQL Server中基于单字段值创建关联矩阵或交叉表

一、商品关联矩阵（同时购买用户数统计） #

要生成商品间的关联矩阵，核心是先建立用户与商品的映射关系，再通过自连接统计共同用户数，最后用Pivot转换为矩阵格式。

1. 准备示例数据（可直接运行） #

CREATE TABLE Purchases (
    Account VARCHAR(10),
    Item VARCHAR(20),
    Amount DECIMAL(10,2)
);

INSERT INTO Purchases VALUES
('ID1', 'Bands', 20.00),
('ID2', 'Bands', 20.00),
('ID4', 'Foam Roller', 40.00),
('ID5', 'Foam Roller', 40.00),
('ID3', 'Shirt', 30.00),
('ID1', 'Weights', 100.00),
('ID4', 'Weights', 100.00),
('ID1', 'Yoga Mat', 25.00),
('ID2', 'Yoga Mat', 25.00),
('ID4', 'Yoga Mat', 25.00),
('ID5', 'Yoga Mat', 25.00);

2. 生成关联矩阵的SQL语句 #

WITH UserItems AS (
    -- 去重：每个用户每个商品只算一次购买
    SELECT DISTINCT Account, Item
    FROM Purchases
),
ItemPairs AS (
    -- 自连接获取所有商品组合，统计共同用户数
    SELECT
        ui1.Item AS Item1,
        ui2.Item AS Item2,
        COUNT(DISTINCT ui1.Account) AS UserCount
    FROM UserItems ui1
    JOIN UserItems ui2 ON ui1.Account = ui2.Account
    GROUP BY ui1.Item, ui2.Item
)
-- Pivot转换为矩阵格式
SELECT
    Item1 AS [品类],
    ISNULL([Bands], 0) AS [Bands],
    ISNULL([Foam Roller], 0) AS [Foam Roller],
    ISNULL([Shirt], 0) AS [Shirt],
    ISNULL([Weights], 0) AS [Weights],
    ISNULL([Yoga Mat], 0) AS [Yoga Mat]
FROM ItemPairs
PIVOT (
    MAX(UserCount)
    FOR Item2 IN ([Bands], [Foam Roller], [Shirt], [Weights], [Yoga Mat])
) AS PivotTable
ORDER BY Item1;

执行后会得到你期望的矩阵结果：对角线数值是该商品的购买用户总数，其他位置是同时购买两个商品的用户数。

二、各品类平均花费统计 #

直接通过分组聚合即可实现：

SELECT
    Item AS [品类],
    AVG(Amount) AS [平均花费]
FROM Purchases
GROUP BY Item
ORDER BY Item;

关于你之前Pivot失败的原因 #

你之前的问题大概率是没有先对用户-商品做去重，或者没通过自连接建立商品间的关联关系。直接对原交易表Pivot会统计交易次数而非用户数，且无法体现不同商品间的同时购买关联。

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