Есть таблица Recipients
id, id_mail, email, status (ИД, ИД письма, email, статус получения);
1, 1, '[email protected]', 'error';
2, 1, '[email protected]', 'error';

3, 2, '[email protected]', 'success';
4, 2, '[email protected]', 'error';

5, 3, '[email protected]', 'success';
6, 3, '[email protected]', 'error';

7, 4, '[email protected]', 'error';
8, 4, '[email protected]', 'error';

Нужно выбрать пользователей, которые последние 3 раза не получили письмо, т.е. status='error' должно быть в 4, 3 и 2-й рассылке 

Ну это я для примера привел.
А на практике id_mail может быть и более 1 000 000 и не обязательно могут идти по порядку.

Какая СУБД?
Где в таблице пользователь? Это email?

СУБД: MySQL
Пользователь - email
Проблему уже решил с помощью 2-х запросов.
Но был бы признателен, если бы подсказали, как решить с помощью одного.

  могу подсказать решение для MSSQL, может наведет на мысль:

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

1)Начнем с того, что не понятно что такое "пользователи". Я правильно полагаю что пользователи идентифицируются емайлами?
2)Что должно в конце концов отобраться - то, что имеет статус error для каждой из трех последних попыток, или же достаточно чтобы этот статус был у одной из последних трех попыток?
3)Количество попыок для каждого "пользователя" - свое? 
   3.а) если ответ на предыдущий вопрос "нет" -  существует ли обособленный справочник попыток, с id_mail в качестве первичного ключа?
   3.б) если ответ на предыдущий вопрос "нет" - может ли так статься что стока с id = 8 отсутствует, и если да, то что означает ее отсутствие - должны ли мы идентифицировать отсутствие строки как успешную попытку или же должны интерпретировать как ошибку?

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


Покажите что-ли эти два запроса

Добавлено через 5 минут и 27 секунд

Так разве можно? В смысле использовать аллиас присвоенный в селект-листе в конструкции where.
Дело в том, что where применяется до того как считаются статистические функци.
Или у MS тут особенность какая?

Скорее всего тут просто надо еще один уровень подзапроса ввести.

Добавлено через 8 минут и 33 секунды

ранжироваться надо, я так полагаю по id_mail

Хотя, конечно это вопрос к ТС - 4)что определяет порядок для вычисления критерия "последние три раза"

На оракле можно обойтись без группировки, определив окно для ранжирующей функции в две строки помимо текущей и каким нибудь образом посчитав все ли там значения error
Например - так

здесь я исхожу из того что 'success' болше чем 'error' и  вслучае, если одна из трех последних записей примет это значение, мною будет полученно именно оно

Это сообщение отредактировал(а) Zloxa - 22.5.2013, 14:21

FAQ: Выборка первой/последней записи в группах 
Выборка нескольких последних записей в неких группах

Zloxa, 
1) Правильно
2) все 3 раза, я ж написал, вроде, понятно: последние 3 раза.
3) 
    а) нет
    б) Если строка отсутствует, то понимаем ее как status !='error' не обязательно это 'success'.

P.S. Всем спасибо! Вопрос решен.
Оставил в 2 запроса
1.

2.

Пока оставлю так. Может вечером передалаю в один запрос с помощью переменных:

собсна в скобочки вместо "ид с первого запроса" подставьте текст самого запроса и будет у вас один запрос

Добавлено через 1 минуту и 16 секунд

На сколько я понимаю это имело бы смысл, если бы на третий вопрос ответ был "да"

Обязательно оттестируйте на реальной БД. ОБычно запросы на переменных сравнительно медленные.

Zloxa, В MySQL в подзапросе не работает LIMIT - выдается ошибка, а мне нужно только 3 последних.

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

Т.е. это не разовый запрос, вы это решение готовите в продакшн?

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

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

Если уж так, то может лучше вообще ввести отдельный счетчик для фейлов? Что-то же записывает в БД error либо success. Вот в том же месте делать +1 при error и обнуление при success. Получается таблица с чисто количеством последних фейлов.
Хоть это и может показаться кривоватым решением, но запись и чтение одной цифры побыстрее будет работать, чем выборка с этих логов.

Кстати, таблица не содержит никаких подробностей, ни даты, ни примечаний... В чем ее смысл? В чем польза знать только лишь успешность всех миллионов отправленных писем?
Почему бы не оставлять столько последний, сколько нужно (например, те же 3), как говорит Zloxa?

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

А id последних рассылок всегда одинаковые для всех подписанных пользователей?
Если так, то, наверное, в 2 запроса самый эффективный метод. В первом только выбор трех последних, во втором запросе просто 4 сравнения на каждую строку. Чего-то более быстрого придумать не могу.

Но все равно подумайте про отдельную таблицу. Если бы id рассылок было в отдельной таблице, то они выбирались бы куда быстрее, чем через группировку. Кроме того, количество рассылок меньшее за количество писем в столько раз, сколько подписчиков, а если учесть, что таблица забита еще и старыми подписчиками, то вообще совсем не рационально из нее часто что-то выбирать.

Это сообщение отредактировал(а) Arantir - 22.5.2013, 19:28

Судя по тому, что было озвучено раньше - да.


Наверное Злоха плохо выразился и был не понят.
Злоха предлагает ввести дополнительную сущность(таблицу) - рассылка. При осуществлении рассылки она регестрируется в таблице, представляющей эту сущность, пусть хотя бы из триггера, оставляющего запись в логе рассылки. Возможно, если позволяет конкуренция - сразу же можно и подчищать заведомо устаревшие, не нужные значения. Если конкуренция не позволяет захватить этот ресурс в монопольный доступ, можно подчищать по расписанию.

При такой организации у нас будет фуллскан малого набора данных + индексный доступ к логам. Такое решение, мне кажестся - достаточно мастшабируемое, в отличии от исходного. Потеря производительности, как и ранее, будет происходить с повышением количества абонентов рассылки, но с течением времени при равном количестве абонентов - не должна б.

Добавлено @ 00:33

Фуллскан с динстинктом первого запроса это то, что заставляет сжиматься сфинктор Злохи  

Добавлено через 7 минут и 16 секунд

Мне кажется что либо Злоха поспешил с заключением того, что он не был понят, либо Злоха составил это заключение до того, как пост подлежал редакции   

Это сообщение отредактировал(а) Zloxa - 23.5.2013, 00:35

Да, в сравнении с этим посты из темы Времени не существует просто отдыхают =)


Потому и спросил, что пришел к такому выводу на основе имеющихся данных.

Я бы вообще предложил последние 3 этих id хранить в кеше в оперативной памяти самого приложения. При потребности их можно восстановить из таблицы, но зато не придется каждый раз ради этого заставлять БД искать самые большие id среди миллионов строк.

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

Ну просто как-то не оптимально все это выглядит. Всего-то надо знать последние 3 результата отправки каждого пользователя, а столько ресурсов тратится. Если 1000 пользователей и 5000 писем, то это 5 млн. строк. Если иметь таблицу только с количеством последних ошибок каждого пользователя, то это 1000 строк. Даже, если придется пару строчек в программе дописать, то 5000000 против 1000 — разница немалая. Кроме того, отписанные там же в таблице писем и остаются, а из таблицы с ошибками отписанных можно удалять и искать придется только среди активных пользователей, что еще больше уменьшает затраты по сравнению с выборкой из таблицы всех когда-либо совершенных отправок писем.

Это сообщение отредактировал(а) Arantir - 23.5.2013, 01:17