SQL - схема сообщения - необходимо найти существующий поток сообщений с учетом набора пользователей

ИЗМЕНИТЬ:

Я понял, пытаясь объяснить запрос, что он не всегда будет работать правильно. Итак, я вернулся и понял, как это проверить. Меня все еще беспокоит настройка схемы, а именно, она подразумевает, что новые пользователи не могут быть добавлены в существующий поток, и что определенный набор пользователей сможет общаться только в одном потоке, но это было хорошо, чтобы исправить запрос.

WITH Selected_Users(id) as (VALUES (@id1), (@id2), --etc--),
     Threads(id) as (SELECT DISTINCT threadFk
                     FROM ThreadMembers as a
                     JOIN Selected_Users as b
                     ON b.id = a.userFk)
SELECT a.id
FROM Threads as a
WHERE NOT EXISTS (SELECT '1'
                  FROM ThreadMembers as b
                  LEFT JOIN Selected_Users as c
                  ON c.id = b.userFk
                  WHERE c.id IS NULL
                  AND b.threadFk = a.id)
AND NOT EXISTS (SELECT '1'
                FROM Selected_Users as b
                LEFT JOIN ThreadMembers as c
                ON c.userFk = b.id
                AND c.threadFk = a.id
                WHERE c.userFk IS NULL) 

Оператор, скорее всего, должен быть динамическим, чтобы построить список выбранных пользователей, если только SQL Server не имеет способа предоставить список в качестве переменной хоста (я знаю, что DB2 делает это, по крайней мере, из iSeries). У меня нет идеального набора данных для проверки этого, но для таблицы с несколькими миллионами строк (только с отношением «многие-один») он возвращается почти мгновенно — для этого я получаю доступ только к индексу (подсказка) .

Пояснения:

WITH Selected_Users(id) as (VALUES (@id1), (@id2), --etc--),

Этот CTE строит список пользователей, чтобы на него можно было ссылаться как на таблицу. Это упрощает работу, хотя можно было бы просто везде заменить его оператором IN (хотя требуется несколько ссылок).

     Threads(id) as (SELECT DISTINCT threadFk
                     FROM ThreadMembers as a
                     JOIN Selected_Users as b
                     ON b.id = a.userFk)

Этот CTE получает список (отдельных) потоков, в которых участвуют пользователи. В основном это просто сокращение списка до отдельных ссылок на threadFk.

SELECT a.id
FROM Threads as a

... Получить выбранный набор потоков...

WHERE NOT EXISTS (SELECT '1'
                  FROM ThreadMembers as b
                  LEFT JOIN Selected_Users as c
                  ON c.id = b.userFk
                  WHERE c.id IS NULL
                  AND b.threadFk = a.id)

Где нет никого, кто «пропал без вести» из выбранного списка пользователей, то есть он исключает потоки со списками пользователей, которые являются подмножествами большего списка. Кроме того, исключаются потоки, в которых есть некоторые пользователи, перечисленные в списке, а также несколько, которые не входят в список, а это означает, что количество пользователей будет совпадать, а фактические пользователи — нет (это где моя первая версия не удалась).

Я понял, что в то время как существующий оператор заботится о ситуации, когда предоставленный список пользователей является подмножеством пользователей, перечисленных для данного потока, я не позаботился о ситуации, когда список выбранных пользователей содержит подмножество, которое список пользователей для данного потока.

AND NOT EXISTS (SELECT '1'
                FROM Selected_Users as b
                LEFT JOIN ThreadMembers as c
                ON c.userFk = b.id
                AND c.threadFk = a.id
                WHERE c.userFk IS NULL) 

Этот пункт исправляет это. Это гарантирует, что в списке выбора не останется никаких оставшихся пользователей после исключения пользователей для определенного потока.

Утверждение теперь меня немного беспокоит - для меня может быть немного лучший способ сделать это...

Мувахаха, есть есть версия COUNT(*), которая тоже должна быть быстрее:

WITH Selected_Users(id) as (VALUES (@id1), (@id2), --etc--),
SELECT a.threadFk
FROM ThreadMembers as a
JOIN Selected_Users as b
ON b.id = a.userFk
GROUP BY a.threadFk
HAVING COUNT(*) = (SELECT COUNT(*) FROM Selected_Users)
AND COUNT(*) = (SELECT COUNT(*) from ThreadMembers as c
                WHERE c.threadFk = a.threadFk)

Пояснения:

SELECT a.threadFk
FROM ThreadMembers as a
JOIN Selected_Users as b
ON b.id = a.userFk

Это объединение, чтобы получить все потоки, частью которых являются перечисленные участники. Это внутренний эквивалент Threads CTE выше. На самом деле вы можете удалить этот CTE и в приведенном выше запросе.

GROUP BY a.threadFk

В конце концов, нам нужен только один экземпляр данного потока. Кроме того (по крайней мере, в DB2) остальная часть оператора недействительна, если она не присутствует.

HAVING COUNT(*) = (SELECT COUNT(*) FROM Selected_Users)

Убедитесь, что для данного потока присутствуют все выбранные пользователи. Или все выбранные пользователи должны присутствовать в данном потоке.

AND COUNT(*) = (SELECT COUNT(*) from ThreadMembers as c
                WHERE c.threadFk = a.threadFk)

Убедитесь, что для данного потока нет невыбранных пользователей. Или не должно быть никаких пользователей, «не учтенных»

Для этого вы должны получить доступ только к индексу (кажется, да). COUNT(*) строк результатов (для GROUP BY) следует выполнять только один раз и использовать повторно. Предложение HAVING оценивается после выполнения GROUP BY (если я правильно помню), поэтому подвыбор количества из исходной таблицы должен выполняться только один раз за threadFk .

Я не рекомендую это легкомысленно, но я думаю, что вам лучше немного денормализировать, добавив столбец в Thread, который содержит отсортированный список внешних ключей, разделенных запятыми, в User. И индексация этого столбца. Затем ваше приложение просто должно отсортировать идентификаторы пользователей отправителя + всех получателей, соединить отсортированный список запятыми и найти запись Thread.

Поскольку по определению список пользователей в потоке никогда не меняется, вам просто нужно правильно заполнить эти вещи при вставке, и вам не нужно беспокоиться о согласованности последующих обновлений.

(Для ясности: то, что вы описываете, определенно возможно с правильно нормализованной схемой. Но это будет уродливо, и я думаю, что это будет плохо работать.)