Как найти недостающие строки (даты) в таблице mysql?

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

У меня есть таблица с именем posts в MySQL, в которой я сохраняю дневники пользователей каждый день. Иногда пользователи забывают написать пост на день, и я хочу дать им возможность отправить его позже. Итак, структура БД выглядит следующим образом:

date           userid
2011-10-01     1
2011-10-02     1
(missing)
2011-10-04     1
2011-10-05     1
(missing)
2011-10-07     1

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

Как я могу это сделать? Спасибо.


mysql gaps-and-islands
person Sallar    schedule 02.04.2011    source источник

Ответы (3)


arrow_upward
3
arrow_downward

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

DROP PROCEDURE IF EXISTS FillDateTable;

delimiter //
CREATE PROCEDURE FillDateTable()
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
  drop table if exists datetable;
  create table datetable (thedate datetime primary key, isweekday smallint);

  SET @x := date('2000-01-01');
  REPEAT 
    insert into datetable (thedate, isweekday) SELECT @x, case when dayofweek(@x) in (1,7) then 0 else 1 end;
    SET @x := date_add(@x, interval 1 day);
    UNTIL @x >= '2030-12-31' END REPEAT;
END//
delimiter ;

CALL FillDateTable;

Затем вы можете просто использовать обычное LEFT JOIN

SELECT thedate
FROM datetable
LEFT JOIN posts on posts.date = datetable.thedate
WHERE posts.date IS NULL

Конечно, вам не нужны все «отсутствующие» даты с 2000 по 2030 год. Ограничьте их датами MIN и MAX в таблице сообщений (для пользователя), т.е.

SELECT thedate
FROM datetable
INNER JOIN (select min(date) postStart, max(date) postEnd
            FROM posts
            where userid=123) p on datetable.thedate BETWEEN p.postStart and p.postEnd
LEFT JOIN posts on posts.date = datetable.thedate
WHERE posts.date IS NULL
person RichardTheKiwi    schedule 02.04.2011
comment
@Sallar / Производительность довольно хорошая. Место для 10 000 дат .. ничтожно мало. Около 100кб? 1 МБ? (дикие догадки), но точно совсем не большие. - person RichardTheKiwi; 02.04.2011
comment
генерируется с 2000 по 2101 год и занимает примерно 1,5 МБ - person It's K; 11.02.2021

arrow_upward
5
arrow_downward

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

С таблицей календаря ваш запрос довольно прост и понятен. Чтобы найти пропущенные даты на октябрь 2011 года, вы должны использовать что-то в этом роде. (Догадка по вашей таблице "posts".)

select c.cal_date
from calendar c
left join posts p on (c.cal_date = p.date)
where p.date is null
  and c.cal_date between '2011-10-01' and '2011-10-31'
  and p.userid = 1
order by c.cal_date
person Mike Sherrill 'Cat Recall'    schedule 02.04.2011

arrow_upward
1
arrow_downward

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

Это не должно быть проблемой места, если они напишут больше, чем пропустят. Например, если они пишут 4 дня и пропускают 1.

Кроме того, вы запустите скрипт и удалите записи с нулевыми заголовками, нулевым содержимым и датой старше X дней. Если они не добавляли отсутствующий пост в течение X дней, они, вероятно, никогда этого не сделают.

Прошу прощения, если мое решение тривиально/слишком абстрактно.

person Buffalo    schedule 02.04.2011
comment
Спасибо за ответ. Я думал об этом, но пользователей больше 2000, и у них, вероятно, много пропущенных вещей. Мне не нравится так много пустых строк в моей таблице :( - person Sallar; 02.04.2011