在SQl服务器中构建比较矩阵 [英] Build Matrix of Comparisons in SQl Server

问题描述

我有一个关于进行复杂的SQL查询的问题。 （我使用sql server）在我的数据库中，我有一组类，以及一组这些类具有的技能（在多对多关系中）。

我想构建一个用于构建矩阵比较的查询（类似于如何使用SQL创建矩阵）但是没有硬编码每列，如果可能。
我希望比较重叠的数量每个类对：

  overlap =（Skills_Both_Classes_Have / Total_Skills）* 100 
  
 
 
 
 我的主要问题是如何找到每对类的重叠，我可以修改/显示。 p> 
 
 
格式化类表：
  | ClassID | ClassName | 
 ------------------------ 
 | 1 | Class1 | 
 | 2 | Class2 | 
 | 3 | Class3 | 
  
 
 
技能表格格式：
  | SkillID | SkillName | 
 ------------------------ 
 | 1 | Skill1 | 
 | 2 | Skill2 | 
 | 3 | Skill3 | 
  
格式化中位数表格：
  | ClassID | SkillID | 
 ------------------ 
 | 1 | 1 | 
 | 1 | 2 | 
 | 1 | 3 | 
 | 2 | 2 | 
 | 2 | 4 | 
 | 2 | 5 | 
 | 3 | 1 | 
 | 3 | 2 | 
 | 3 | 5 | 
  
示例输出：
  | Class1 | Class2 | Class3 | 
 -------------------------------- 
 Class1 | 100 | 033 | 066 | 
 Class2 | 033 | 100 | 066 | 
 Class3 | 066 | 066 | 100 | 
  
我一直在使用枢轴等，但我无法用最好的方式在SQL中实现这一点。 
 
 
 
在任何其他语言中，我会为每个循环使用一对夫妇，并将输出发送到数组，但这似乎不是一个很好的解决方案在SQL 。 
解决方案
我设法写了一个查询，得到我想要的，但不是我想要的那么容易阅读。
   - 构建一个列表类的组合
 SELECT [ClassID]，[ClassName] INTO #classnames FROM [Glad1]。[dbo]。[Classes] 
 SELECT a.classname AS Class1，a.ClassID AS Class1ID，b.classname AS class2，b.ClassID AS Class2ID INTO #combos 
 FROM #classnames a cross join #classnames b 
 SELECT Classes.ClassName，COUNT（*）AS SkillCount INTO #skillcounts FROM [Glad1]。[dbo] 。[ClassSkills] 
 RIGHT JOIN [Glad1]。[dbo]。[Classes] 
 ON ClassSkills.ClassID = Classes.ClassID 
 GROUP BY ClassName 
 
 
  - 查找每个类组合的百分比重叠
 SELECT ClassOne，ClassTwo，CAST（ROUND（（（SharedSkills * 2.0）/（sc1.skillCount + sc2.SkillCount）* 100.0），0）AS DECIMAL 8,0））
 AS PercentOverlap INTO #percentoverlaps FROM 
（SELECT cn1.ClassName AS ClassOne，cn2.ClassName AS ClassTwo，SharedSkills FROM #classnames cn1 JOIN 
（SELECT Class1ID，Class2ID，Count （Class1Skills。[SkillID]）AS SharedSkills FROM 
（SELECT DISTINCT [Class1ID]，[SkillID] FROM [Glad1]。[dbo]。[ClassSkills] 
 JOIN #combos ON＃combos.Class1ID = ClassSkills .ClassID）Class1Skills 
 INNER JOIN 
（SELECT DISTINCT [Class2ID]，[SkillID] FROM [Glad1]。[dbo]。[ClassSkills] 
 JOIN #combos ON＃combos.Class2ID = ClassSkills .ClassID）CLass2Skills 
 ON Class1Skills。[SkillID] = Class2Skills。[SkillID] 
分组由Class1ID，CLass2ID 
）AllSharedSkills 
 ON cn1.ClassID = Class1ID 
 JOIN #classnames cn2 
 ON cn2.ClassID = Class2ID）命名为
 JOIN #skillcounts sc1 
 ON sc1.ClassName = ClassOne 
 JOIN #skillcounts sc2 
 ON sc2.ClassName = ClassTwo 
 
  - 动态构建列以将结果转换为百分比重叠的矩阵
 DECLARE @cols AS NVARCHAR（MAX），
 @query AS NVARCHAR（MAX）; 
 
 select @cols = STUFF（（SELECT distinct'，'+ QUOTENAME（c.ClassTwo）
 FROM #percentoverlaps c 
 FOR XML PATH（''），TYPE 
）.value（'。'，'NVARCHAR（MAX）'）
，1,1，''）
 
 set @query ='SELECT ClassOne，'+ @cols + 'from 
（
 select ClassOne，ClassTwo，PercentOverlap 
 from #percentoverlaps 
）x 
 pivot 
（
 max（PercentOverlap）
 for ClassTwo in（'+ @cols +'）
）p'
 
 
 execute（@query）
 
 
 b $ b  - 清除临时表
 DROP TABLE #classnames 
 DROP TABLE #combos 
 DROP TABLE #skillcounts 
 DROP TABLE #percentoverlaps 
  
 
I have a question on going about a decently complicated SQL query. (I am using sql server) In my database, I have a set of Classes, and a set of Skills that those classes have (in a many-to-many relationship).

I want to build a query that builds a matrix comparison (similar to that in How to create a matrix with SQL) But without hardcoding each column, if possible.
I am hoping to compare the amount of overlap for each class pair, by:
overlap = (Skills_Both_Classes_Have / Total_Skills) * 100
My main issue is how to find the overlap of each pair of classes in a way I can modify/display.

formatting for class table:
|ClassID |   ClassName |
------------------------
|1       |    Class1   |
|2       |    Class2   |
|3       |    Class3   |
formatting for skill table:
|SkillID |   SkillName |
------------------------
|1       |    Skill1   |
|2       |    Skill2   |
|3       |    Skill3   |
formatting for median table:
|ClassID |SkillID|
------------------
|1       |   1   |
|1       |   2   |
|1       |   3   |
|2       |   2   |
|2       |   4   |
|2       |   5   |
|3       |   1   |
|3       |   2   |
|3       |   5   |
example output:
       |Class1 |Class2 |Class3 | 
--------------------------------
Class1 |  100  |  033  |  066  |
Class2 |  033  |  100  |  066  |
Class3 |  066  |  066  |  100  |
I have been playing with pivot and the like,but am having trouble wrapping my head around the best way of implementing this in SQL. 

In any other language, I would use a couple for each loops, and send the output into an array, but that doesn't seem like a very good solution in SQL. This isn't for an assignment or anything, just for my own personal curiosity.
解决方案
I managed to write up a query that gets what I want, but isn't exactly as easy to read as I would like.
--Builds a list of all combinations of classes
SELECT [ClassID], [ClassName] INTO #classnames FROM [Glad1].[dbo].[Classes] 
SELECT a.classname AS Class1, a.ClassID AS Class1ID, b.classname AS Class2, b.ClassID AS    Class2ID INTO #combos 
FROM #classnames a cross join #classnames b
SELECT Classes.ClassName,COUNT(*) AS SkillCount INTO #skillcounts FROM [Glad1].[dbo].[ClassSkills]
RIGHT JOIN [Glad1].[dbo].[Classes]
ON ClassSkills.ClassID=Classes.ClassID
GROUP BY ClassName


--Finds the percent overlap for each class combination
SELECT ClassOne, ClassTwo,CAST(ROUND(((SharedSkills * 2.0) / (sc1.skillCount + sc2.SkillCount) * 100.0),0) AS DECIMAL(8,0)) 
AS PercentOverlap INTO #percentoverlaps FROM
(SELECT cn1.ClassName AS ClassOne, cn2.ClassName AS ClassTwo, SharedSkills FROM #classnames cn1 JOIN
(SELECT Class1ID, Class2ID, Count(Class1Skills.[SkillID]) AS SharedSkills FROM
(SELECT DISTINCT [Class1ID],[SkillID] FROM [Glad1].[dbo].[ClassSkills] 
JOIN #combos ON #combos.Class1ID = ClassSkills.ClassID) Class1Skills
INNER JOIN
(SELECT DISTINCT [Class2ID], [SkillID] FROM [Glad1].[dbo].[ClassSkills] 
JOIN #combos ON #combos.Class2ID = ClassSkills.ClassID) CLass2Skills
ON Class1Skills.[SkillID] = Class2Skills.[SkillID]
Group by Class1ID, CLass2ID
) AllSharedSkills
ON cn1.ClassID = Class1ID
JOIN #classnames cn2
ON cn2.ClassID = Class2ID) Named
JOIN #skillcounts sc1
ON sc1.ClassName = ClassOne
JOIN #skillcounts sc2
ON sc2.ClassName = ClassTwo

--Dynamically builds the columns to turn the results into a matrix of percent overlap
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.ClassTwo) 
            FROM #percentoverlaps c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT ClassOne, ' + @cols + ' from 
            (
                select ClassOne, ClassTwo, PercentOverlap
                from #percentoverlaps
           ) x
            pivot 
            (
                 max(PercentOverlap)
                for ClassTwo in (' + @cols + ')
            ) p '


execute(@query)



--cleans up temp tables
DROP TABLE #classnames
DROP TABLE #combos
DROP TABLE #skillcounts
DROP TABLE #percentoverlaps


                        
这篇关于在SQl服务器中构建比较矩阵的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！