Запрос в MySQL. Есть идея?

Question

[N]

Структура таблицы:
id | link_id | flag

Пример данных:
1 | 111 | 1
2 | 111 | 1
3 | 111 | 0
4 | 111 | 1
5 | 222 | 1
6 | 222 | 1
7 | 222 | 1
8 | 333 | 1
9 | 333 | 0

Суть задачи:
Выбрать все записи у которых flag равен 1 у всех записей с одинаковыми link_id.
1,2,3,4 - имеют один link_id, но не у вссех flag равен 1.
8,9 - та же ситуация...
5,6,7 - подходят под условие.

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

Comments

[d'Ivan]

а почему записи с id 1,2,4,8 не вошли в результат?

[Сергей П]

Роман Мирр, ну автор же сказал, что затупил. Чего вы сразу?=)

[N]

Роман Мирр, Повторяю:

у которых flag равен 1 у всех записей с одинаковыми link_id

1,2,3,4 - имеют один link_id, но не у вссех flag равен 1.
8,9 - та же ситуация...

[Сергей П]

N, вот это из вашей формулировки было не очевидно

[N]

Сергей Паньков, Добавил в формулировку вопроса...

Answer 1

[Melkij]

Для поиска самих link_id

select link_id from tablename
group by link_id
having count(*) = sum(flag  = 1)

Либо

select id from links
where exists(select null from tablename where flag  = 1 and link_id = links.id) -- возможно это условие вам по задаче не нужно
and not exists(select null from tablename where flag != 1 and link_id = links.id)

Вычитывать сами строки можно попробовать через

select ...
from tablename t
where t.flag = 1
and not exists(select null from tablename sq where sq.link_id = t.link_id and sq.flag != 1)

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

select ... from tablename t where t.link_id in (
select q.link_id from tablename q
group by q.link_id
having count(*) = sum(flag  = 1)
)

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

Третий запрос по ожиданию лучше отзовётся на случай малого числа flag = 1 строк в таблице.

Самый быстрый на чтение вариант, впрочем, всё равно не выполнять обработку этого фильтра налету, а каким-то образом его материализовать и хранить предварительно рассчитанным.

Comments

[N]

Первый немного не то, но мысль уловил...
Второй - более похоже, ща потестим.

Просто забыл сказать, что в таблице могут быть несколько миллионов записей...

[N]

По второму немного не так опять же)
Но первый можно объединить с другим запросом в эту же таблицу, но хочется более быстрый вариант в плане скорости...

[Melkij]

ещё раз прочитав задачу... Оба варианта выбирают уникальные link_id.

Можно попробовать

select ...
from tablename t
where t.flag = 1
and not exists(select null from tablename sq where sq.link_id = t.link_id and sq.flag != 1)

А быстрой эта задача не будет. Всё равно много телодвижений базе. Только если материализовывать эти данные в отдельные поля/таблицу и вести триггерами.

[N]

Melkij, Вот этот последний похож как ниже вперёд написали. Спасибо за уделенное время.
Насчёт материализации - пока не хочется :)

[Melkij]

немного отличается.

[N]

Melkij, Перепишите запрос в ответ. Отмечу тоже решением.

[N]

У меня примерно такое соотношение данных:

10млн записей всего, из них уникальных link_id примерно 2-2.5млн, а flag равен 1 рандомно, но "стремится" быть заполненным у всех...

Answer 2

[Сергей П]

например так:

SELECT t.*
FROM my_table t
WHERE NOT EXISTS(
  SELECT null FROM my_table t2 WHERE t2.link_id = t.link_id and t2.flag=0
)

Можно и с джойном:

WITH tt AS(SELECT DISTINCT link_id WHERE flag = 0)
SELECT t1.*
FROM
    my_table t1
    LEFT JOIN tt t2 ON t2.link_id = t.1link_id
WHERE t2.link_id is None

Comments

[N]

Этот запрос по условиям задачи норм.
Как поведёт себя при миллионах записях позже буду тестить.
Спасибо за вариант :)

Если есть ещё мысли - напишите)

Если например этот же запрос переписать с использованием JOIN вместо EXISTS...есть опыт: что будет быстрее?

[Сергей П]

N, попробуйте вариант с джойном на большой таблице. Нет базы под рукой. Добавил запрос в ответ

[N]

Мне главное ход мыслей для выборки было понять - притупил:)
Запросы повертеть уже смогу)
Потестирую на реальных данных - там будет видно по скорости...но это уже отдельная тема)

Answer 3

[Александр]

Если нужно, прям как выделено жирным, то подзапросом выбираем записи с link_id = 0 в поле flag, а условием выводим все записи не в этом диапазоне.

SELECT * FROM new_table 
WHERE 
link_id NOT IN (SELECT DISTINCT(link_id) FROM new_table WHERE flag=0);

Comments

[N]

Тоже вариант. Спасибо.

[Сергей П]

Вы бы хоть distinct в подзапрос докинули. Зачем тратить лишнюю память?