Найти номер строки в сортировке на основе идентификатора строки, а затем найти ее соседей

Question

Скажи, что у меня есть некоторые SELECT заявление:

SELECT id, name FROM people
   ORDER BY name ASC;

У меня есть несколько миллионов строк в people стол и ORDER BY Предложение может быть гораздо более сложным, чем то, что я показал здесь (возможно, работает на дюжине столбцов).

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

1. Найти номер строки с заданным id,
2. Показать 5 пунктов до и 5 пунктов после строки с заданным id,

Проблему 2 легко решить, как только я решу проблему 1, так как тогда я могу использовать что-то вроде этого, если я знаю, что искомый элемент имеет номер строки 1000 в отсортированном наборе результатов (это диалект Firebird SQL):

SELECT id, name FROM people
   ORDER BY name ASC
   ROWS 995 TO 1005;

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

Существуют ли какие-либо средства для достижения этого с помощью некоторых функций SQL:2003 (таких как row_number поддерживается в Firebird 3.0)? Я ни в коем случае не гуру SQL, и мне нужны некоторые указатели здесь. Могу ли я создать кэшированное представление, где результат будет включать в себя индекс ранг / плотный ранг / ряд?

Answer 1

Похоже, что Firebird поддерживает оконные функции (аналитические функции в Oracle). Таким образом, вы можете сделать следующее:

Чтобы найти номер "строки" строки с заданным идентификатором:

select id, row_number() over (partition by NULL order by name, id)
from t
where id = <id>

Это предполагает, что идентификаторы являются уникальными.

Чтобы решить вторую проблему:

select t.*
from (select id, row_number() over (partition by NULL order by name, id) as rownum
      from t
     ) t join 
     (select id, row_number() over (partition by NULL order by name, id) as rownum
      from t
      where id = <id>
     ) tid
     on t.rownum between tid.rownum - 5 and tid.rownum + 5

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