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
AND p.OwnerUserId IS NOT NULL
AND p2.OwnerUserId IS NOT NULL
AND 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
5 ответов
Один CTE и просто inner joins сделаю работу. Нет необходимости в таком большом количестве кода, который я наблюдал в других ответах. Обратите внимание на множество комментариев в моем.
Ссылка на StackExchange Data Explorer с сохраненным примером результата
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
Это приводит в качестве примера сообщения:
- Могу ли я запустить rubygems в I ronruby? спросил Сиань, принял ответ от Ориона Эдвардса
- Будет ли сборщик мусора вызывать IDisposable. Утилизировать для меня? спросил Орион Эдвардс, принял ответ от Сианя
Тем не менее, StackExchange может привести к превышению времени ожидания из-за большого набора данных и distinct часть. Если вы хотите просмотреть некоторые данные, удалите distinct и добавить top N в начале:
with questions as (
...
)
select top 3 ...
Запрос в его простейшей форме (чтобы он не занимал 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
Результат:
Используя технику из ответа Салмана А., улучшил сортировку и добавил еще несколько полезных столбцов.
В сочетании с запросами в моем другом ответе, он показывает некоторые интересные отношения.
Смотрите это в СЕДЕ.
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
Результаты как:
Вот как я бы это сделал. Вот некоторые упрощенные данные:
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
Изменить Если вы хотите исключить самостоятельные ответы, просто добавьте and u1.AcceptedAnswerUserId != u1.OwnerUserId к on пункт.
Что касается меня, я всегда находил забавным, как глубоко внедренный SQL и реляционная алгебра в теории множеств, и все же выполнение операций на основе множеств, подобных этой, в SQL, как правило, кажется очень неуклюжим. Главным образом потому, что для сохранения неупорядоченности вы должны представлять элементы набора в одном столбце. Но затем для сравнения элементов набора в SQL вам необходимо представить элементы набора в виде отдельных столбцов.
Теперь подумайте, как вы можете распространить это на триады пользователей, комментирующих один и тот же пост?
ЭТА: Упс. Неправильно прочитал вопрос; Op хочет Принятые ответы и ниже для любых взаимных ответов. (Это легко изменить, но я все равно больше интересуюсь последним.)
Из-за очень большого набора данных (и необходимости не превышать время ожидания SEDE) я решил ограничить наборы AMAP и выполнить сборку оттуда.
Итак, этот запрос:
- Возвращает любые строки, только если есть взаимные отношения.
- Возвращает все такие пары вопросов и ответов.
- Исключает самостоятельные ответы.
- Использует параметры запроса и магические столбцы 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
Это приводит к результатам как (Нажмите для увеличения):
