如何基于SQL Server中单个字段中的值创建相关矩阵或透视

ddrv8njm 于 2022-12-17 发布在 SQL Server

关注(0)|答案(2)|浏览(133)

我试图在SQL Server中创建类似于相关矩阵的东西，查看单个字段中的值之间的关系，目标如下（我被限制共享实际的字段和值）：如果我有一个购买和产品的列表，我是否可以输出一个矩阵，显示购买了给定商品的人 * 同时 * 购买了另一个不同的商品。如果这是我的数据：

[Account    Item        Amount
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][1]

我想知道有多少购买了瑜伽垫的客户也购买了泡沫滚轮等。我想要的输出是这样的：

[Bands  Foam Roller Shirt   Weights Yoga Mat
Bands       2               1   1
Foam Roller     2           1   1
Shirt                   1       
Weights     2                   2
Yoga Mat    2   2           1   4][1]

商业问题有两个方面：
1.有多少人在购买一个产品类别时也购买了另一个类别的产品？
1.每个类别的平均支出是多少？
我尝试了一个透视查询，但它不起作用。我敢肯定我没有正确地构建它。有人有什么建议或其他线索，我应该看看吗？（我遵循了这一个，但它没有让我只有一个字段：Creating a correlation matrix in SQL Server）
由此产生的结果集只是一行，顶部是项目名称和总计。
因为我的行和列都在一起;现在粘贴为图像。再次编辑由于反馈'转换行到列'的问题是相同的。不幸的是，它不是。我可以得到我的行到列与值;我已经做到了。
我真正想做的是看到交叉表或列中的值相互重叠。例如，有多少瑜伽垫购买者也买了泡沫辊？

sql-server

来源：https://stackoverflow.com/questions/74804569/how-to-create-a-correlation-matrix-or-pivot-based-on-values-in-a-single-field-in

2条答案

按热度按时间

ehxuflar1#

在将表提供给PIVOT之前，您需要将表连接到其自身。
透视数据值必须与行值和列值分开。在本例中使用Account，但它也可以是1 AS DummyValue。

SELECT PVT.*
FROM (
    SELECT First.Account, First.Item, Second.Item AS OtherItem
    FROM @Data First
    JOIN @Data Second ON Second.Account = First.Account
) D
PIVOT (
  COUNT(Account)
  FOR OtherItem IN ([Bands], [Foam Roller], [Shirt], [Weights], [Yoga Mat])
) PVT

结果：
| 项目|条带|泡沫滚筒|衬衫|重量|瑜伽垫|
| - ------|- ------|- ------|- ------|- ------|- ------|
| 条带|第二章|无|无|1个|第二章|
| 泡沫滚筒|无|第二章|无|1个|第二章|
| 衬衫|无|无|1个|无|无|
| 重量|1个|1个|无|第二章|第二章|
| 瑜伽垫|第二章|第二章|无|第二章|四个|
有关演示，请参见this db<>fiddle。

赞(0）回复(0）举报 2022-12-17

b1zrtrql2#

大概是这样的：

if object_id('tempdb..#t') is not null
    drop table #t
select LTRIM(rtrim(col1)) AS id, ltrim(rtrim(col2)) AS prod, stuff(ltrim(rtrim(col3)), 1, 1, '') AS price
into #t
from 

(
    VALUES  (N'ID1', 'Bands       ','$20.00 ')
    ,   (N'ID2', 'Bands       ','$20.00 ')
    ,   (N'ID4', 'Foam Roller ','$40.00 ')
    ,   (N'ID5', 'Foam Roller ','$40.00 ')
    ,   (N'ID3', 'Shirt       ','$30.00 ')
    ,   (N'ID1', 'Weights     ','$100.00 ')
    ,   (N'ID4', 'Weights     ','$100.00 ')
    ,   (N'ID1', 'Yoga Mat    ','$25.00 ')
    ,   (N'ID2', 'Yoga Mat    ','$25.00 ')
    ,   (N'ID4', 'Yoga Mat    ','$25.00 ')
    ,   (N'ID5', 'Yoga Mat    ','$25.00')
) t (col1, col2, col3)

;with cte as (
    select distinct prod from #t
    )
, cte2 AS (
select c.prod, t2.prod AS prod2, COUNT(*) AS cnt
from CTE c
inner join #t t
    ON  t.prod = c.prod
inner join #t t2
    ON  t2.id = t.id
GROUP BY c.prod, t2.prod
)
select prod, 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 cte2  c
pivot (MAX(c.cnt) for prod2 IN ([Bands], [Foam Roller], [Shirt],[Weights], [Yoga Mat])) p

赞(0）回复(0）举报 2022-12-17

我来回答

如何基于SQL Server中单个字段中的值创建相关矩阵或透视

2条答案

相关问题

热门标签

最新问答