PostgreSQL: изберете най-близките редове според реда на сортиране

Тестов случай:

CREATE TEMP TABLE tbl(a float, user_id bigint);
INSERT INTO tbl VALUES
 (0.1133, 2312882332)
,(4.3293, 7876123213)
,(3.1133, 2312332332)
,(1.3293, 7876543213)
,(0.0033, 2312222332)
,(5.3293, 5344343213)
,(3.2133, 4122331112)
,(2.3293, 9999942333);

Запитване:

WITH x AS (
    SELECT a
          ,user_id
          ,row_number() OVER (ORDER BY a, user_id) AS rn
    FROM   tbl
    ), y AS (
    SELECT rn, LEAST(rn - 3, (SELECT max(rn) - 5 FROM x)) AS min_rn
    FROM   x
    WHERE  (a, user_id) = (1.3293, 7876543213)
    )
SELECT *
FROM   x, y
WHERE  x.rn  > y.min_rn
AND    x.rn <> y.rn
ORDER  BY x.a, x.user_id
LIMIT  4;

Връща резултата, както е описан във въпроса. Ако приемем, че (a, user_id) е уникален.

Не е ясно дали a трябва да е уникален. Ето защо сортирам по user_id допълнително, за да прекъсна връзките. Ето защо използвам и прозоречна функция row_number(), не rank() за това. row_number() е правилният инструмент във всеки случай. Искаме 4 реда. rank() би дал неопределен брой редове, ако имаше равностойни в реда на сортиране.

Това е подобрена версия на публикуваното от @Tim Landscheidt. Гласувайте за неговия отговор, ако харесвате идеята с индекса. Не се занимавайте с малки маси. Но ще повиши производителността за големи маси - при условие че разполагате с подходящ индекс. Най-добрият избор би бил индекс с няколко колони на (a, user_id).

WITH params(_a, _user_id) AS (SELECT 5.3293, 5344343213) -- enter params once
    ,x AS  (
    (
    SELECT a
          ,user_id
          ,row_number() OVER (ORDER BY a DESC, user_id DESC) AS rn
    FROM   tbl, params p
    WHERE  a < p._a
       OR  a = p._a AND user_id < p._user_id -- a is not defined unique
    ORDER  BY a DESC, user_id DESC
    LIMIT  5  -- 4 + 1: including central row
    )
    UNION ALL -- UNION right away, trim one query level
    (
    SELECT a
          ,user_id
          ,row_number() OVER (ORDER BY a ASC, user_id ASC) AS rn
    FROM   tbl, params p
    WHERE  a > p._a
       OR  a = p._a AND user_id > p._user_id
    ORDER  BY a ASC, user_id ASC
    LIMIT  5
    )
    )
    , y AS (
    SELECT a, user_id
    FROM   x, params p
    WHERE (a, user_id) <> (p._a, p._user_id) -- exclude central row
    ORDER  BY rn  -- no need to ORDER BY a
    LIMIT  4
    )
SELECT *
FROM   y
ORDER  BY a, user_id   -- ORDER result as requested

Основни разлики спрямо версията на @Tim:

• Според въпроса (a, user_id) формирайте критериите за търсене, а не само a. Това променя рамката на прозореца, клаузите ORDER BY и WHERE по съвсем различни начини.

• UNION веднага, няма нужда от допълнително ниво на заявка. Имате нужда от скоби около двете заявки UNION, за да позволите отделни ORDER BY.

• Сортирайте резултата според заявката. Изисква друго ниво на заявка (едва ли на всяка цена).

• Тъй като параметрите се използват на множество места, централизирах входа във водещ CTE.
  За многократна употреба можете да обвиете тази заявка почти „както е“ в SQL или plpgsql функция.

И още едно:

WITH prec_rows AS
  (SELECT a,
          user_id,
          ROW_NUMBER() OVER (ORDER BY a DESC) AS rn
   FROM tbl
   WHERE a < 1.3293
   ORDER BY a DESC LIMIT 4),
     succ_rows AS
  (SELECT a,
          user_id,
          ROW_NUMBER() OVER (ORDER BY a ASC) AS rn
   FROM tbl
   WHERE a > 1.3293
   ORDER BY a ASC LIMIT 4)
SELECT a, user_id
FROM
  (SELECT a,
          user_id,
          rn
   FROM prec_rows
   UNION ALL SELECT a,
                    user_id,
                    rn
   FROM succ_rows) AS s
ORDER BY rn, a LIMIT 4;

AFAIR WITH ще създаде екземпляр на таблица с памет, така че фокусът на това решение е да ограничи размера й колкото е възможно повече (в този случай осем реда).

set search_path='tmp';

DROP TABLE lutser;
CREATE TABLE lutser
        ( val float
        , num bigint
        );
INSERT INTO lutser(val, num)
VALUES ( 0.1133  ,  2312882332  )
      ,( 4.3293  ,  7876123213  )
      ,( 3.1133  ,  2312332332  )
      ,( 1.3293  ,  7876543213  )
      ,( 0.0033  ,  2312222332  )
      ,( 5.3293  ,  5344343213  )
      ,( 3.2133  ,  4122331112  )
      ,( 2.3293  ,  9999942333  )
        ;

WITH ranked_lutsers AS (
        SELECT val, num
        ,rank() OVER (ORDER BY val) AS rnk
        FROM lutser
        )
SELECT that.val, that.num
        , (that.rnk-this.rnk) AS relrnk
FROM ranked_lutsers that
JOIN ranked_lutsers this ON (that.rnk BETWEEN this.rnk-2 AND this.rnk+2)
WHERE this.val = 1.3293
        ;

Резултати:

DROP TABLE
CREATE TABLE
INSERT 0 8
  val   |    num     | relrnk 
--------+------------+--------
 0.0033 | 2312222332 |     -2
 0.1133 | 2312882332 |     -1
 1.3293 | 7876543213 |      0
 2.3293 | 9999942333 |      1
 3.1133 | 2312332332 |      2
(5 rows)

Както Ервин посочи, централния ред не е желан в изхода. Освен това row_number() трябва да се използва вместо rank().

WITH ranked_lutsers AS (
        SELECT val, num
        -- ,rank() OVER (ORDER BY val) AS rnk
        , row_number() OVER (ORDER BY val, num) AS rnk
        FROM lutser
) SELECT that.val, that.num
        , (that.rnk-this.rnk) AS relrnk
FROM ranked_lutsers that
JOIN ranked_lutsers this ON (that.rnk BETWEEN this.rnk-2 AND this.rnk+2 )
WHERE this.val = 1.3293
AND that.rnk <> this.rnk
        ;

Резултат2:

  val   |    num     | relrnk 
--------+------------+--------
 0.0033 | 2312222332 |     -2
 0.1133 | 2312882332 |     -1
 2.3293 | 9999942333 |      1
 3.1133 | 2312332332 |      2
(4 rows)

UPDATE2: винаги да избираме четири, дори ако сме в горната или долната част на списъка. Това прави заявката малко по-грозна. (но не толкова грозен като този на Ервин ;-)

WITH ranked_lutsers AS (
        SELECT val, num
        -- ,rank() OVER (ORDER BY val) AS rnk
        , row_number() OVER (ORDER BY val, num) AS rnk
        FROM lutser
) SELECT that.val, that.num
        , ABS(that.rnk-this.rnk) AS srtrnk
        , (that.rnk-this.rnk) AS relrnk
FROM ranked_lutsers that
JOIN ranked_lutsers this ON (that.rnk BETWEEN this.rnk-4 AND this.rnk+4 )
-- WHERE this.val = 1.3293
WHERE this.val = 0.1133
AND that.rnk <> this.rnk
ORDER BY srtrnk ASC
LIMIT 4
        ;

Изход:

  val   |    num     | srtrnk | relrnk 
--------+------------+--------+--------
 0.0033 | 2312222332 |      1 |     -1
 1.3293 | 7876543213 |      1 |      1
 2.3293 | 9999942333 |      2 |      2
 3.1133 | 2312332332 |      3 |      3
(4 rows)

АКТУАЛИЗАЦИЯ: Версия с вложен CTE (включващ външно присъединяване!!!). За удобство добавих първичен ключ към таблицата, което така или иначе звучи като добра идея IMHO.

WITH distance AS (
        WITH ranked_lutsers AS (
        SELECT id
        , row_number() OVER (ORDER BY val, num) AS rnk
        FROM lutser
        ) SELECT l0.id AS one
        ,l1.id AS two
        , ABS(l1.rnk-l0.rnk) AS dist
        -- Warning: Cartesian product below
        FROM ranked_lutsers l0
        , ranked_lutsers l1 WHERE l0.id <> l1.id

        )
SELECT lu.*
FROM lutser lu
JOIN distance di
ON lu.id = di.two
WHERE di.one= 1
ORDER by di.dist
LIMIT 4 
        ;