Уникальный индекс, чтобы гарантировать уникальность в течение нескольких последовательных дней

Question

Я хочу разослать дружеские письма-напоминания нашим пользователям. Например, когда пользователь не входил в систему в течение 30 дней подряд (есть несколько разных случаев, когда мы отправляем электронное письмо).

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

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

"Самое близкое", о котором я могу думать, - это использование текущей недели года. Но у этого есть две проблемы: это особый случай (работает только из-за особого случая 7 дней), и что более важно, он будет гарантировать только одно электронное письмо в неделю, но не последовательные дни. Например, пользователь получает электронное письмо в субботу, а другое - в понедельник.

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

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

Идеи до сих пор

Вот то, что я придумал, так как я отправил вопросы. Я не доволен ими.

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

2. Без уникального индекса. Нам нужна одна запись для каждого пользователя. Мы обновляем запись перед отправкой электронного письма. Псевдо SQL UPDATE email_lock WHERE user=[user] and last_send + 7 DAY < NOW() SET last_send = NOW(), Если количество обновленных записей 0, не отправляйте электронное письмо. Это будет работать и атомно.

Answer 1

Это должно сделать трюк:

select user_id, MAX(send_date) as last_send_date from emails 
group by user_id
having MAX(send_date) < NOW() - INTERVAL '7 days';

Он возвращает вам пользователей, которым вы можете отправить электронное письмо.

Скрипка: http://sqlfiddle.com/