Как получить данные SQL запросом, начианя с конкретное строки?

Question

[Роман Дубинин]

Есть таблица со следующими полями

• id — строка случайных символов, первичный ключ
  
• date - дата с точностью до дня, неуникальна
  
• ...
  

Из неё данные извлекаются порциями с сортировкой по дате:
SELECT * FROM `table` ORDER BY `date` DESC LIMIT 4
После обработки первого запроса сохраняется id последней полученной строки.
Вопрос: как при следующем запросе указать, что начать нужно со строки с конкретным id?

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

Answer 1

[ZhukovAN]

Отказаться от подхода "черт-те-как спроектируем БД, а потом будем задалбывать ее сложными SQL-запросами, жрущими непомерные ресурсы на тривиальных задачах". Если id, действительно, "строка случайных символов", то полезной информации в ней ноль. В этом случае сделать рефакторинг и заменить id на автоинкрементное целочисленное поле с индексом и делать выборку простейшимWHERE id > :thePreviousMaxId. Если в id все-таки имеется осмысленная информация, используемая не только для обеспечения уникальности - добавить автоинкрементный альтернативный ключ, сведя тем самым решение к предыдущему варианту. В любом случае это будет эффективнее, чем заниматься запоминанием ранее полученных id

Comments

[Роман Дубинин]

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

[Роман Дубинин]

Я согласен, что проектировщик из меня так себе. Может вы мне тогда поможете переформировать её?
Есть некоторое количество ресурсов, с которых я периодически должен собирать данные и сохранять их у себя.
Можно считать, что я получаю от них id и date для каждой записи.
Необходимо сохранить эти данные в мою БД, обеспечив возможность извлекать их потом в вышеописанном виде

[ZhukovAN]

romash: Непонятно, какова конечная задача: если требуется вернуть страницу записей с датой, бОльшей, нежели последняя полученная при предыдущем запросе, то почему просто не сравнивать даты? Если надо показывать новые записи безотносительно их даты, то в чем проблема автоинкремента?

[ZhukovAN]

romash: Упрощенно: сделать у себя таблицу с полями FK_ID (VARCHAR), FK_DATE (DATE), ID (AUTOINCREMENT). Сохранять в FK_-поля значения из внешней таблицы/БД/системы, использовать свой автоинкрементный ID для определения того, с какой записи начинать обработку на своей стороне. Или данные из внешней таблицы приходится тоже считывать вручную? Тогда, скажем, для Oracle можно прикапывать у себя значение псевдостолбца ROWNUM из внешней таблицы

[ZhukovAN]

romash: Вариант "в лоб": повесить триггер на вставку, не рассматривается? Меня смущает противоречие между "должен собирать данные" и "я получаю от них id и date". Судя по сложностям, речь идет именно о первом варианте, т.к., если внешние данные "заталкиваются" в вашу систему, то задача решается автоинкрементом. Если требуется первый вариант, то получается, что засада именно в том, что данные на стороне "поставщика" никак не упорядочены и меняются в процессе работы вашей системы. Тогда, ИМХО, малой кровью проблему не решить: потребуется либо рефакторинг на стороне поставщика данных чтобы он передавал некий автоинкрементный идентификатор, либо мучать его БД запросами, т.к. на стороне поставщика данных сейчас банально нет атрибута, который бы гарантированно возрастал от запроса к запросу

[Роман Дубинин]

ZhukovAN: Давайте я приведу пример:
Перед первым запросом таблица, отсортированная по дате (с вашим автоинкрементом) выглядит

вот так

id_ai	id	date
1	abc	2017-05-25
2	fer	2017-05-23
3	swe	2017-05-21
4	vgf	2017-05-21
5	zrt	2017-05-21
6	jyg	2017-05-20

допустим мы получаем записи по 4 штуки и запоминаем последнюю полученную.
За время до предыдущего запроса появились 2 новых записи за 25-ое число и за 21-ое.

новая таблица

id_ai	id	date
1	abc	2017-05-25
7	zuc	2017-05-25
2	fer	2017-05-23
3	swe	2017-05-21
4	vgf	2017-05-21
8	tur	2017-05-21
5	zrt	2017-05-21
6	jyg	2017-05-20

и вот теперь я вижу, что если сортировать такую таблицу по дате и id_ai, то условие

WHERE CONCAT(`date`, '-', `id`) > CONCAT(:lastDate, '-', :lastId)

мою проблему решает.
Как вы думаете, нормально будет так решить эту проболему?

[ZhukovAN]

romash: Я боюсь, что нет. В ситуации, когда id - случаен по условиям его формирования, а date - случайна по причине того, что, скажем, сегодня можно получить запись за 1812 год просто по той причине, что данные доставлялись голубиной почтой и попали во внешнюю систему только сегодня, конкатенация этих двух значений будет не менее случайным полем. Здесь же для оптимизации выборки нужно иметь хотя бы одно поле, закономерность изменений которого нам известна и не меняется со временем. Кстати, при таком раскладе оракловый ROWID тоже бесполезен. Есть хоть какие-то ограничения на даты записей во внешней таблице? Не хочется попасть в ситуацию, когда придется делать гетерогенный запрос с условием вида WHERE ext_tbl.id NOT IN (SELECT my_tbl_ext_id FROM my_tbl)

[Роман Дубинин]

ZhukovAN: Я опечатался, простите. Я хотел сортировать таблицу по дате и id_ai (автоинкременту) и по их же конкатенации делать условие:

WHERE CONCAT(`date`, '-', `id_ai`) > CONCAT(:lastDate, '-', :lastId_ai)

[ZhukovAN]

romash: Давайте определим исходные условия, а то я перестал понимать, что мы можем, а что не можем делать :) Я отталкиваюсь от следующих:

1. В структуре внешней таблицы нет столбца, значение которого последовательно увеличивается с каждой новой записью и не модифицируется в ходе работы
   
2. Мы не можем добавить в структуру внешней таблицы такой столбец, который будет корректно заполняться, скажем, автоинкрементом
   
3. Во внешнюю таблицу по заранее неизвестным правилам извне пишутся данные с отсутствующей закономерностью. В 2017 году может появиться запись за 1812. Запись может произойти между двумя последовательными чтениями.
   

Если хоть что-то из этих условий неверно - задачу можно решить малой кровью: если неверен пункт 1 и/или 2, то сортируем по такому столбцу и по завершении каждой выборки "по 4 записи" сохраняем значение этого столбца для последней записи, это и есть приведенный вами пример, причем нам даже не нужна приведенная в примере конкатенация с датой. Даже если в промежутке между выборками произойдет добавление записей - значение этого столбца окажется гарантированно бОльшим того, что мы сохранили. Если неверно условие 3 - тогда сортируем выборку по ключу и точно так же сохраняем значение ключа для четвертой записи. Поскольку мы считаем, что между выборками запись не присходит - все точно так же успешно выполняется.
Но если у нас есть внешняя система с таблицей, в которой нет закономерности значений хоть каких-то полей от времени записи этого поля (а у нас, насколько я понимаю, именно такая ситуация: ключ - в принципе случаен, дата от текущей даты и времени никак не зависит - во внешней системе могут последовательно появиться записи с датой 2017, 1812 и 1941). Вот в этой ситуации нас, боюсь, не спасет никакая сортировка так как отсутствует любая зависимость между теми данными, что мы уже считали из внешней системы и теми, что в ней появились.

Answer 2

[Сергей Соколов]

Можно брать с «нахлёстом». Раз день неуникален – коллизии возможны в рамках этого дня.

Придётся запоминать все id последнего дня выборки.

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

Comments

[Роман Дубинин]

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

[Сергей Соколов]

romash: запоминать все полученные id последнего полученного дня (раз день неуникален). Следующий поиск начинать с этой даты, включая её полностью, и сопоставлять id в рамках этого дня.

[Роман Дубинин]

Сергей Соколов: Это неплохая мысль... Спасибо
Но мне всё-таки кажется, что есть более правильный способ... Может быть можно как-то получить offset строки в запросе? Типа, была бы функция offset() которая возвращает порядковый номер строки в текущем запросе

SELECT * FROM `table` ORDER BY `date` DESC LIMIT (SELECT `offset` FROM (SELECT t.`id`, offset() FROM `table` t ORDER BY t.`date` DESC) sorted WHERE sorted.`id` = $lastId), 4

Хотя сейчас я это написал и как-то оно совсем по извращенски выглядит... Получается 3 запроса вместо одного

[Сергей Соколов]

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

[Роман Дубинин]

Сергей Соколов: то есть у меня на одних и тех же данных при сортировке по неуникальному полю строки с одинаковой датой могут раполагаться по-разному от запроса к запросу?

[Сергей Соколов]

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

Answer 3

[Arman]

Если нужно сохранить состояние, то или промежуточная/временная таблица или, если записей не так много, запросить id'шки Всех записей и потом спрашивать уже по id через IN(). Как вариант еще можно update'ом метки поставить всем записям и когда будет обход, то новые записи не будут иметь эту метку, а значит не будут учавствовать в выборке с лимитом, но это для одного клиента, если демоном надо обойти все записи.

Answer 4

[vyrkmod]

LIMIT $gotten, 4

- как раз вариант. Если нужно чтобы выборка не сдвигалась при добавлении новых полей - запоминаем наибольшее значение date при первом запросе, в последующие добавляем "`date` <= $maxdate". в WHERE.