SQL Zoek wederkerige relatie

Ik probeer een situatie te vinden met behulp van Stack Exchange Data Explorer (SEDE) waarbij twee afzonderlijke gebruikers van Stack Overflow een antwoord van elkaar hebben geaccepteerd. Dus bijvoorbeeld:

Post A { Id: 1, OwnerUserId: "user1", AcceptedAnswerId: "user2" }

en

Post B { Id: 2, OwnerUserId: "user2", AcceptedAnswerId: "user1" }

Ik heb momenteel een query waarin twee gebruikers die samen hebben, meer dan de vraag als vragensteller kunnen beantwoorden, maar het bepaalt niet of die relatie wederzijds is:

SELECT user1.Id AS User_1, user2.Id AS User_2
FROM Posts p
INNER JOIN Users user1 ON p.OwnerUserId = user1.Id
INNER JOIN Posts p2 ON p.AcceptedAnswerId = p2.Id
INNER JOIN Users user2 ON p2.OwnerUserId = user2.Id
WHERE p.OwnerUserId <> p2.OwnerUserId
en p.OwnerUserId IS NOT NULL
en p2.OwnerUserId IS NOT NULL
en user1.Id <> user2.Id
GROUP BY user1.Id, user2.Id HAVING COUNT(*) > 1;

Voor iedereen die niet bekend is met het schema, zijn er twee tabellen zoals:

Posts
--------------------------------------
Id                      int
PostTypeId              tinyint
AcceptedAnswerId        int
ParentId                int
CreationDate            datetime
DeletionDate            datetime
Score                   int
ViewCount               int
Body                    nvarchar (max)
OwnerUserId             int
OwnerDisplayName        nvarchar (40)
LastEditorUserId        int
LastEditorDisplayName   nvarchar (40)
LastEditDate            datetime
LastActivityDate        datetime
Title                   nvarchar (250)
Tags                    nvarchar (250)
AnswerCount             int
CommentCount            int
FavoriteCount           int
ClosedDate              datetime
CommunityOwnedDate      datetime

en

Users
--------------------------------------
Id                      int
Reputation              int
CreationDate            datetime
DisplayName             nvarchar (40)
LastAccessDate          datetime
WebsiteUrl              nvarchar (200)
Location                nvarchar (100)
AboutMe                 nvarchar (max)
Views                   int
UpVotes                 int
DownVotes               int
ProfileImageUrl         nvarchar (200)
EmailHash               varchar (32)
AccountId               int
3

5 antwoord

De vraag in zijn eenvoudigste vorm (zodat er geen time-by vragen van 16M vragen) zou zijn:

WITH accepter_acceptee(a, b) AS (
    SELECT q.OwnerUserId, a.OwnerUserId
    FROM Posts AS q
    INNER JOIN Posts AS a ON q.AcceptedAnswerId = a.Id
    WHERE q.PostTypeId = 1 AND q.OwnerUserId <> a.OwnerUserId
), collaborations(a, b, type) AS (
    SELECT a, b, 'a accepter b' FROM accepter_acceptee
    UNION ALL
    SELECT b, a, 'a acceptee b' FROM accepter_acceptee
)
SELECT a, b, COUNT(*) AS [collaboration count]
FROM collaborations
GROUP BY a, b
HAVING COUNT(DISTINCT type) = 2
ORDER BY a, b

Resultaat:

1
toegevoegd
Resultaten lijken aannemelijk.
toegevoegd de auteur Brock Adams, de bron

Dit is hoe ik het zou aanpakken. Hier zijn enkele vereenvoudigde gegevens:

if object_id('tempdb.dbo.#Posts') is not null drop table #Posts
create table #Posts
(
    PostId char(1),
    OwnerUserId int,
    AcceptedAnswerUserId int
)

insert into #Posts
values
('A', 1, 2),
('B', 2, 1),
('C', 2, 3),
('D', 2, 4),
('E', 3, 1),
('F', 4, 1)

Voor onze doeleinden geven we niet echt om de PostId , en wat we als uitgangspunt hebben, is een reeks geordende paren van eigenaarseigenaren ( OwnerUserId ) en geaccepteerd antwoordapparaten ( AcceptedAnswerUserId ).

(Hoewel niet noodzakelijk, kunt u de set als zodanig visualiseren)

select distinct OwnerUserId, AcceptedAnswerUserId
from #Posts

Nu willen we alle items in deze set vinden die deze twee velden hebben omgekeerd. D.w.z. waarbij de eigenaar als de ene post de aanvaarde aanvrager van de andere is. Dus waar een paar is (1, 2), willen we vinden (2, 1).

Ik deed dit met een link-join, zodat je de rijen kon zien die het wegliet, maar door het in een inner join te veranderen, beperk je het tot de set die je hebt beschreven. U kunt de informatie desgewenst oogsten (door een van de kolommen uit een hoed te halen, of als u ze op één rij wilt hebben, en beide kolommen uit precies één van de tabellen terug te halen).

select 
    u1.OwnerUserId, 
    u1.AcceptedAnswerUserId, 
    u2.OwnerUserId, 
    u2.AcceptedAnswerUserId
from #Posts u1
left outer join #Posts u2
    on u1.AcceptedAnswerUserId = u2.OwnerUserId
        and u1.OwnerUserId = u2.AcceptedAnswerUserId

Edit If you want to exclude self answers, just add and u1.AcceptedAnswerUserId != u1.OwnerUserId to the on clause.

Persoonlijk heb ik het altijd grappig gevonden dat diepgewortelde SQL en relationele algebra zich in de verzamelingenleer bevinden, en toch doen set-gebaseerde operaties zoals deze in SQL de neiging om erg onhandig te zijn. Vooral omdat u het ontbreken van een bestelling wilt behouden, moet u setleden in één kolom weergeven. Maar als u setleden in SQL wilt vergelijken, moet u de setleden als afzonderlijke kolommen weergeven.

Overweeg dit, hoe zou je dit uitbreiden naar drieklanken van gebruikers die reageren op dezelfde post?

1
toegevoegd

ETA: Oeps. De vraag verkeerd gelezen; Op wil Geaccepteerde antwoorden en hieronder is voor elk wederzijds antwoord. (Het is gemakkelijk aan te passen, maar ik ben hoe dan ook meer geïnteresseerd in het laatste.)


Vanwege de zeer grote dataset (en de noodzaak om geen time-out te geven aan SEDE), heb ik ervoor gekozen om de sets AMAP te beperken en vanaf daar te bouwen.

Dus deze vraag:

  1. Only returns any rows if there is a reciprocal relationship.
  2. Returns all such Q&A pairs.
  3. Excludes self answers.
  4. Leverages SEDE's query parameters and magic columns for usability.

See it live in SEDE.

-- UserA: Enter ID of user A
-- UserB: Enter ID of user B
WITH possibleAnswers AS (
    SELECT
                a.Id                AS aId
                , a.ParentId        AS qId
                , a.OwnerUserId   
                , a.CreationDate
    FROM        Posts a
    WHERE       a.PostTypeId        = 2  --  answers
    AND         a.OwnerUserId       IN (##UserA:INT##, ##UserB:INT##)
),
possibleQuestions AS (
    SELECT
                q.Id                AS qId
                , q.OwnerUserId   
                , q.Tags
    FROM        Posts q
    INNER JOIN  possibleAnswers pa  ON q.Id = pa.qId
    WHERE       q.PostTypeId        = 1  --  questions
    AND         q.OwnerUserId       IN (##UserA:INT##, ##UserB:INT##)
    AND         q.OwnerUserId       != pa.OwnerUserId  --  No self answers
)
SELECT 
            pa.OwnerUserId          AS [User Link]
            , 'answers'             AS [Action]
            , pq.OwnerUserId        AS [User Link]
            , pa.CreationDate       AS [at]
            , pq.qId                AS [Post Link]
            , pq.Tags
FROM        possibleQuestions pq
INNER JOIN  possibleAnswers pa      ON pq.qId = pa.qId
WHERE       pq.OwnerUserId          =  ##UserB:INT##
AND         EXISTS (SELECT * FROM possibleQuestions pq2  WHERE pq2.OwnerUserId =  ##UserA:INT##)

UNION ALL SELECT 
            pa.OwnerUserId          AS [User Link]
            , 'answers'             AS [Action]
            , pq.OwnerUserId        AS [User Link]
            , pa.CreationDate       AS [at]
            , pq.qId                AS [Post Link]
            , pq.Tags
FROM        possibleQuestions pq
INNER JOIN  possibleAnswers pa      ON pq.qId = pa.qId
WHERE       pq.OwnerUserId          =  ##UserA:INT##
AND         EXISTS (SELECT * FROM possibleQuestions pq2  WHERE pq2.OwnerUserId =  ##UserB:INT##)

ORDER BY    pa.CreationDate

Het produceert resultaten zoals (Klik voor grotere weergave):

results


Zie deze SEDE-query .

0
toegevoegd

Met behulp van de techniek van het antwoord van Salman A is de sortering verbeterd en zijn er wat bruikbare kolommen toegevoegd.

In combinatie met de vragen in mijn andere antwoord , toont het enkele interessante relaties.

Zie het in SEDE.

WITH QandA_users AS (
    SELECT      q.OwnerUserId   AS userQ
                , a.OwnerUserId AS userA
    FROM        Posts q
    INNER JOIN  Posts a         ON q.AcceptedAnswerId = a.Id
    WHERE       q.PostTypeId    = 1
),
pairsUnion (user1, user2, whoAnswered) AS (
    SELECT  userQ, userA, 'usr 2 answered'
    FROM    QandA_users
    WHERE   userQ <> userA
    UNION ALL
    SELECT  userA, userQ, 'usr 1 answered'
    FROM    QandA_users
    WHERE   userQ <> userA
),
collaborators AS (
    SELECT      user1, user2, COUNT(*) AS [Reciprocations]
    FROM        pairsUnion
    GROUP BY    user1, user2
    HAVING COUNT (DISTINCT whoAnswered) > 1
)
SELECT
            'site://u/' + CAST(c.user1 AS NVARCHAR) + '|Usr ' + u1.DisplayName      AS [User 1]
            , 'site://u/' + CAST(c.user2 AS NVARCHAR) + '|Usr ' + u2.DisplayName    AS [User 2]
            , c.Reciprocations                                                      AS [Reciprocal Accptd posts]
            , (SELECT COUNT(*)  FROM QandA_users qau  WHERE qau.userQ = c.user1)    AS [Usr 1 Qstns wt Accptd]
            , (SELECT COUNT(*)  FROM QandA_users qau  WHERE qau.userQ = c.user1  AND qau.userA = c.user2) AS [Accptd Ansr by Usr 2]
            , (SELECT COUNT(*)  FROM QandA_users qau  WHERE qau.userA = c.user2)    AS [Usr 2 Ttl Accptd Answrs]
FROM        collaborators c
INNER JOIN  Users u1        ON u1.Id = c.user1
INNER JOIN  Users u2        ON u2.Id = c.user2
ORDER BY    c.Reciprocations DESC
            , u1.DisplayName
            , u2.DisplayName

Resultaten zoals:

results

0
toegevoegd

Eén CTE en eenvoudige inner joins zullen het werk doen. Er is niet zoveel code nodig als bij andere antwoorden. Let op veel opmerkingen in de mijne.

Link naar StackExchange Data Explorer met voorbeeldresultaat opgeslagen

with questions as ( -- this is needed so that we have ids of users asking and answering
select
   p1.owneruserid as question_userid
 , p2.owneruserid as answer_userid
 --, p1.id -- to view sample ids
from posts p1
inner join posts p2 on -- to fetch answer post
  p1.acceptedanswerid = p2.id
)
select distinct -- unique pairs
    q1.question_userid as userid1
  , q1.answer_userid as userid2
  --, q1.id, q2.id -- to view sample ids
from questions q1
inner join questions q2 on
      q1.question_userid = q2.answer_userid -- accepted answer from someone
  and q1.answer_userid = q2.question_userid -- who also accepted our answer
  and q1.question_userid <> q1.answer_userid -- and we aren't self-accepting

Dit brengt als voorbeeld berichten:

Hoewel, StackExchange kan je time-out opleveren vanwege de grote gegevensset en het afzonderlijke -gedeelte. Als u bepaalde gegevens wilt bekijken, verwijdert u onderscheid en voegt u top N toe bij het starten:

with questions as (
...
)
select top 3 ...
0
toegevoegd