SQL өзара қатынастарды табу

Мен Stack Exchange Data Explorer (SEDE) арқылы Stack Overflow екі түрлі пайдаланушылар бір-бірінен жауап қабылдаған жағдайды табуға тырысамын. Мәселен, мысалы:

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

және

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

Мен қазіргі уақытта сұраққа жауап беруші ретінде сұралғаннан гөрі бірлесіп жұмыс істеген екі пайдаланушыны табуға болатын сұраным бар, бірақ ол бұл қарым-қатынастың өзара байланысын анықтайды:

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
және p.OwnerUserId IS NOT NULL
және p2.OwnerUserId IS NOT NULL
және user1.Id <> user2.Id
GROUP BY user1.Id, user2.Id HAVING COUNT(*) > 1;

Схемамен таныс емес адамдар үшін келесідей екі кесте бар:

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

және

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 жауаптар

Сұрау ең қарапайым пішінде (ол 16М сұрақтарын сұрастыруды күту уақыты жоқ):

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

Нәтиже:

1
қосылды
Нәтижелер ақылға сыймайды.
қосылды автор Brock Adams, көзі

Міне, мен осылай жүретінмін. Міне, кейбір жеңілдетілген деректер:

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)

Біздің мақсатымыз үшін PostId туралы шынымен қамқорлық етпейміз және бастапқы нүкте ретінде бізде бар пошта иелерінің реттелген жұптарының жиынтығы ( OwnerUserId ) және қабылданады жауап берушілер ( AcceptedAnswerUserId ).

(Қажет болмаса да, сол сияқты жиынтығын визуализациялауға болады)

select distinct OwnerUserId, AcceptedAnswerUserId
from #Posts

Енді біз осы екі өрістің өзгерген жиынтықтағы барлық жазбаларды табуға тырысамыз. И. егер бір лауазым басқа біреудің қабылданған жауап берушісі болса, иесі. Сондықтан жұп (1, 2) болса, біз (2, 1) табуды қалаймыз.

Мен оны біріктіру арқылы жасадым, сондықтан сіз оны өткізіп жіберген жолдарды көре аласыз, бірақ оны ішкі біріктіруге өзгерту оны сіз сипатталған жиынтыққа шектейді. Сіз ақпаратты (мысалы, шляпалардан бағандардың кез келгенін таңдау арқылы немесе бір қатарда бағандарды екеуі де кестелердің біреуінен қайтару арқылы) жинай аласыз.

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.

Жеке жазба бойынша, мен әрдайым өзімнің SQL-те және Relational Algebra-дің көптеген теорияда қаншалықты тереңдікте екенін білдім. Негізінен, тапсырыстың болмауын сақтау үшін, сіз мүшелердің бір бағанында өкілдік етуіңіз керек. Содан кейін SQL элементтерін салыстыру үшін жиынтығы мүшелерді бөлек бағандар ретінде көрсету қажет.

Енді бұл туралы ойланыңыз, мұны сол лауазымға түсініктеме берген пайдаланушылардың үштігіне қалай жеткізу керек?

1
қосылды

ETA: Oops. Сұрақты бұрмаңыз; Op Op Қабылданған жауаптарын және кез келген жауаптарына жауап береді. (Оңай өзгертуге болады, бірақ бәрібір соңғы кезекте қызықтырады.)


Өте үлкен деректер жиынтығынан (және SEDE-ны уақытты жоғалтудың қажеті жоқ) байланысты, мен AMAP жиынтығын шектеп, сол жерден салуды таңдадым.

Сонымен, бұл сұраныс:

  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

Ол нәтижелерді шығарады (Үлкен көрініс үшін нұқыңыз):

results


Барлық осындай жұптардың тізімі үшін бұл SEDE сұранысы .

0
қосылды

Salman A-ның жауабынан техниканы пайдалану сұрыптауды жақсартты және бірнеше пайдалы бағандар қосылды.

менің басқа жауап бөліміндегі сұраулармен бірге кейбір қызықты қарым-қатынастар көрсетіледі.

оны 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

Нәтижелері:

results

0
қосылды

Бір CTE және қарапайым ішкі қосылыстар тапсырманы орындайды. Басқа жауаптарда байқағаным сонша код қажет емес. Менің шағымда көптеген ескертулерді ескеріңіз.

Link to StackExchange Data Explorer with sample result saved

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

This brings as an example posts:

Дегенмен, StackExchange сізге үлкен деректер жиынтығы мен бөлек бөлігінің арқасында күту уақытын тастауы мүмкін. Егер сіз кейбір деректерді көргіңіз келсе different алып тастаңыз және басында top N қосыңыз:

with questions as (
...
)
select top 3 ...
0
қосылды