Найти соседние (верхнее и нижнее) поля таблицы

Question

Не получается придумать или найти метод нахождения ближайших соседних полей от заданного.

Для примера таблица:

SELECT chapter, title FROM chapters 
ORDER BY chapter ::float 
WHERE title = '2';

 chapter | title
---------+-------
 1       |     2
 2       |     2
 3       |     2
 5       |     2
 5.5     |     2
 6       |     2
 12      |     2
 13      |     2
 14.5    |     2

Допустим, я хочу найти соседние поля от chapter = '5'.

Должно найти верхнее 5.5 и нижнее 3.

Единственное, к чему я смог прийти - это просто изменять шаг на +1 или -1 относительно заданного значения, но таким образом я могу пропустить те самые 5.5 или относительно дробных - целые.

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

Я нашёл странный, но кажется рабочий способ у ChatGPT (от безнадёги), но он выглядит страшно, не элегантно и, скорее всего, ненадёжно. Моих нынешних значний SQL недостаточно, чтобы точно определить и сказать.

            WITH nearest_chapters AS (
            SELECT chapter, title,
            LAG(chapter) OVER (ORDER BY chapter::float) AS prev_chapter,
            LEAD(chapter) OVER (ORDER BY chapter::float) AS next_chapter
            FROM chapters WHERE title = '2'
            )
                SELECT chapter, title
                FROM nearest_chapters
                WHERE chapter = '5'
                OR prev_chapter = '5'
                OR next_chapter = '5';

 chapter | title
---------+-------
 3       |     2
 5       |     2
 5.5     |     2

На самом то деле, не обязательно одним запросом это делать. Главное найти соседние значения, независимо от того, float там или int. Имею ввиду, в строковом представлении, ибо тип данных chapter - varchar.

Answer 1

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

ROW_NUMBER() OVER (ORDER BY chapter ASC) seq

После этого все легко.

Пожалуйста, смотрите ниже.

dbfiddle

SQL

create table tbl (chapter DECIMAl(10,2), title VARCHAR(20));
insert into tbl (chapter, title) values
(1   , '2'),
(2   , '2'),
(3   , '2'),
(5   , '2'),
(5.5 , '2'),
(6   , '2'),
(12  , '2'),
(13  , '2'),
(14.5, '2');

WITH rs AS
(
    SELECT * 
        , ROW_NUMBER() OVER (ORDER BY chapter ASC) seq
    FROM tbl
)
SELECT a.* 
FROM rs AS a INNER JOIN rs AS b 
    ON b.chapter = 5 AND ABS(a.seq - b.seq) <= 1
ORDER BY a.chapter;

Результат

chapter	title	seq
3.00	2	3
5.00	2	4
5.50	2	5

БЛОГ НА HUSL

Найти соседние (верхнее и нижнее) поля таблицы

Ответы (1 шт):