Как получить количество пользователей с 2 заявками?

Question

[sl0]

Вопрос простой, но туплю уже долго.
Есть таблица заявок: id, user_id, event_id. Мне нужно получить количество пользователей, у которых одновременно есть заявки на 2 разных события. Т.е. нужно что то вроде
count(user_id) ... where event_id in (1,2)

Comments

[FanatPHP]

заджойнить таблицу на саму себя

Answer 1

[Антон Антон]

select count(*) from (select user_id from events group by user_id having count(event_id) >= 2)

Comments

[FanatPHP]

это очень сложно

[sl0]

Спасибо!

[Akina]

у которых одновременно есть заявки на 2 разных события.

... having count(DISTINCT event_id) >= 2 ...

[Антон Антон]

Akina, согласен. но тут зависит от бизнес логики. может быть такая ситуация вообще запрещена )
FanatPHP, что сложного?

[sl0]

Akina, Поскольку двух заявок на одно событие быть не может, то это не обязательно.
Хотя в общем случае замечание полезное.

[Антон Антон]

ivanivanov15122021, с точки зрения когнитивной нагрузки этот вариант более предпочтителен (относительно формулировки задачи) ИМХО. С точки зрения производительности разницы не должно быть.
Хотя судя по вопросу там не >=, а = должно быть

[Akina]

sl0,

Поскольку двух заявок на одно событие быть не может

... то сразу возникает вопрос - а почему это не указано в вопросе?

[FanatPHP]

Антон Антон, группировка всегда сложная. нужно как минимум городить составной индекс

[Антон Антон]

FanatPHP, это к тому, что там full table (ну или index при наличии покрывающего) scan идет? ну так иначе никак с такими задачами.

[FanatPHP]

Антон Антон, ну где никак-то, если здесь нужен тупой селф-джойн?

[Антон Антон]

FanatPHP, там бужет тот же фуллскан + в лучшем случае мерж джоин

[FanatPHP]

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

[Антон Антон]

FanatPHP, ну так если поставить во вложенный запрос это условие то точно также будет работать индекс.

select count(*) from (select user_id from events where event_id in (1,2) group by user_id having count(sistinct event_id) == 2)

никакого джоина, который вывалится в нестед лупс или мердж джоин не будет.

[FanatPHP]

это новая фобия такая - джойнобоязнь?
никуда тут джойн не вывалится, и при этом не будет выбирать никакие данные, а просто сразу посчитает количество
select count(*) from t t1 join t t2 on t2.id=t1.id and t2.event_id=2 where t.event_id=1
где тут мердж джойн-то?

[Антон Антон]

FanatPHP, мердж джоин и нестед лупс - это способы получения результирующей таблицы из двух соединяемых подтаблиц на условно "физическом" уровне. это в sql профайлере надо смотреть. не уверен, что в mysql explain это покажет, а вот в postgres и ms sql - показывает. первый требует предварительную сортировку обеих таблиц по предикату соединения, второй - это цикл в цикле. соотвтетсвенно, планировщик СУБД оценивает, сколько нужно времени на сортировку двух таблиц + цикл и на цикл в цикле и выбирает вариант соединения. при неправильной статистике по данным может быть больно. В варианте с подзапросом планировщик всегда выберет сортировку (одну!) + цикл. Это почти наверняка будет лучше (и 100% не хуже), причем более стабильно (планировщик не сможет ошибиться).

[FanatPHP]

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

Akina рассудите нас?

[Антон Антон]

FanatPHP, по какому индексу? по дефолту индекс только на примари кей есть, больше информации у нас нет. если есть составной индекс на user_id, event_id, то все равно будет полный скан индекса, но таблицы будут предсортированы, да, если на event_id, user_id - то да, обе подтаблицы будут почти наверняка будут предсортированы, скорее всего будет два частичных сканирования индекса и мердж джоин (один цикл по двум таблицам одновременно). но при наличии такого индекса и вариант с подзапросом также отработает хорошо - скорее всего там будет одно частичное сканирование индекса + цикл агрегации.
При отсутствии индекса или при несоставных индексах - вариант с подзапросом будет лучше.
Все очень зависит от того, как это дело воспримет планировщик запросов СУБД. Повторюсь - при использовании подзапроса у него "пространства для маневра" просто меньше. Вот тут неплохо описаны различные способы выполнения джоинов: https://docs.microsoft.com/ru-ru/sql/relational-da...

>я, блин, такого словоблудия давно не видел
словоблудие началось с "почему не джоином?". тут-то мои навыки анализа затыков в производительности sql запросов и пригодились.

[sl0]

А если надо будет выбрать из 10 событий, надо будет 10 раз приджойнивать?
Вариант Антон Антон гораздо проще, лучше и понятнее.

[Akina]

FanatPHP, я как-то слабо понимаю суть вашего спора. И мне сдаётся, что он уже слабо связан с исходным вопросом, ибо непонятно откуда, но вылезли заданные значения event_id, которые в исходном вопросе не определены. К тому же не вижу смысла обсуждать абстрактно, какой способ связывания выберет сервер, проще посмотреть план - а он, в свою очередь, критично зависит от наполнения и статистики.

Но для меня главное - то, что к каждому событию прилагается определённый пользователь. Что, в свою очередь, означает, что заявки интерактивные, а количество записей невелико. А потому - ну не похрен ли? как запрос не построй, всё одно он будет быстрым, даже если в таблице вообще индекса нет, и даже если сервер решит, что записи надо отсортирить перед связыванием.

Да, если говорить о предложенных решениях, то подзапрос на выборку имеющих заявки на не менее чем 2 разных эвента я бы построил как

SELECT user_id FROM events GROUP BY user_id HAVING MIN(event_id) < MAX(event_id)

Так он будет немного быстрее, ибо индекс (user_id, event_id) будет использоваться полностью, а не как компактная версия таблицы. Если такой индекс, конечно, есть...