SQL Server 2016：查询从同一表创建多个唯一的ID对？

发布于 2025-01-25 11:50:34 字数 1284 浏览 4 评论 0原文

我正在努力构建一个查询来处理随机配对的人，以便每个人都可以评估许多其他人。我正在寻找一种批量处理的方法 - 也许交叉加入？ - 比起一个光标一次循环一个人，当经过测试时，这很慢，因为一次可能会有数百个配对。

有几个主要参数：

每对必须是唯一的 - 两个ID只能配对一次。
每个ID都会有特定数量的对数 - 正在评估的人和进行评估的人都不比特定数量的成对数更多。
所有ID都在这张表中。
必须能够以随机顺序创建对。
没有ID可以与自己配对。

关于我如何处理这个的想法吗？

这是我一直在研究的查询

DECLARE @assessmentID INT=[N];
DECLARE @assessmentPairs TABLE( 
    assessorID INT,
    authorID INT,
    assessorCounter INT,
    authorCounter INT
    UNIQUE NONCLUSTERED ([assessorID], [authorID])
);

INSERT INTO @assessmentPairs
SELECT assessorID,authorID,assessorCounter,authorCounter
FROM (
    SELECT 
        e1.personID AS assessorID,
        e2.personID AS authorID,
        assessorCounter=ROW_NUMBER() OVER(PARTITION BY e1.personID ORDER BY e1.personID),
        authorCounter=ROW_NUMBER() OVER(PARTITION BY e2.personID ORDER BY NEWID())
    FROM People e1
    JOIN Assessments a ON a.courseOfferingID=e1.courseOfferingID
    CROSS JOIN People e2
    WHERE e2.personID<>e1.personID 
    AND a.assessmentID=@assessmentID
    GROUP BY e1.personID,e2.personID
) AS x
WHERE authorCounter<=10
ORDER BY assessorID,authorCounter,authorID,assessorCounter

SELECT *
FROM @assessmentPairs
ORDER BY authorID,assessorID

原文

I'm working on building a query to handle random pairings of people so that each one can assess many others. I'm looking for a way to handle this in bulk - cross join perhaps? -rather than using a cursor to loop through people one at a time, which when tested was pretty slow as there will likely be hundreds of pairings at a time.

There are a few main parameters:

Each pair must be unique - two IDs can only be paired once.
There will be a specific number of pairs per ID - both the person being assessed and the person doing the assessing can have no more or less than the specific number of pairs.
All IDs are in this one table.
Must be able to create the pairs in random order rather.
No ID can be paired with itself.

Any ideas for how I could approach this?

Here's the query I've been working on

DECLARE @assessmentID INT=[N];
DECLARE @assessmentPairs TABLE( 
    assessorID INT,
    authorID INT,
    assessorCounter INT,
    authorCounter INT
    UNIQUE NONCLUSTERED ([assessorID], [authorID])
);

INSERT INTO @assessmentPairs
SELECT assessorID,authorID,assessorCounter,authorCounter
FROM (
    SELECT 
        e1.personID AS assessorID,
        e2.personID AS authorID,
        assessorCounter=ROW_NUMBER() OVER(PARTITION BY e1.personID ORDER BY e1.personID),
        authorCounter=ROW_NUMBER() OVER(PARTITION BY e2.personID ORDER BY NEWID())
    FROM People e1
    JOIN Assessments a ON a.courseOfferingID=e1.courseOfferingID
    CROSS JOIN People e2
    WHERE e2.personID<>e1.personID 
    AND a.assessmentID=@assessmentID
    GROUP BY e1.personID,e2.personID
) AS x
WHERE authorCounter<=10
ORDER BY assessorID,authorCounter,authorID,assessorCounter

SELECT *
FROM @assessmentPairs
ORDER BY authorID,assessorID

分享到QQ

分享到微博