[SQL] 포스트 그레스 가장 가까운 운영자가 있습니까?

포스트 그레스 가장 가까운 운영자가 있습니까?

내가 같은 테이블을 제공, 뭔가를 찾고 있어요 :

| id | number |
|  1 |     .7 |
|  2 |   1.25 |
|  3 |   1.01 |
|  4 |    3.0 |

MY_TABLE WHEREnumberCLOSEST (1)에서 쿼리 SELECT * 내가 숫자 만 신경 행 (3)을 반환합니다. 지금은 단지 모든 행을 통해 루프와 비교를 수행하는 절차를 가지고 있지만, 나는 이것이 내장으로 가능할 수 있도록 정보가 A B 트리 인덱스에서 사용할 수 있어야 그림,하지만 난 하나를 찾을 수 없습니다 문서는 않는다는 것을 시사한다.

해결법

1. ==============================

   1.내가 구문에 조금 떨어져 있지만, 기본적으로 빠른 2 B-트리 조회를 실행해야이 매개 변수가있는 쿼리 (원래의 질문의 모든? 걸릴 '1')이 될 수있다 [가정 수는 색인].

   내가 구문에 조금 떨어져 있지만, 기본적으로 빠른 2 B-트리 조회를 실행해야이 매개 변수가있는 쿼리 (원래의 질문의 모든? 걸릴 '1')이 될 수있다 [가정 수는 색인].

   SELECT * FROM
   (
     (SELECT id, number FROM t WHERE number >= ? ORDER BY number LIMIT 1) AS above
     UNION ALL
     (SELECT id, number FROM t WHERE number < ? ORDER BY number DESC LIMIT 1) as below
   ) 
   ORDER BY abs(?-number) LIMIT 1;
   

   (번호에 인덱스) ~ 5e5 행의 테이블이에 대한 쿼리 계획은 다음과 같습니다 :

   psql => explain select * from (
           (SELECT id, number FROM t WHERE number >= 1 order by number limit 1) 
           union all
           (select id, number from t where number < 1 order by number desc limit 1)
   ) as make_postgresql_happy 
   order by abs (1 - number) 
   limit 1;
                                                     QUERY PLAN
   --------------------------------------------------------------------------------------------------------------
    Limit  (cost=0.24..0.24 rows=1 width=12)
      ->  Sort  (cost=0.24..0.24 rows=2 width=12)
            Sort Key: (abs((1::double precision - public.t.number)))
            ->  Result  (cost=0.00..0.23 rows=2 width=12)
                  ->  Append  (cost=0.00..0.22 rows=2 width=12)
                        ->  Limit  (cost=0.00..0.06 rows=1 width=12)
                              ->  Index Scan using idx_t on t  (cost=0.00..15046.74 rows=255683 width=12)
                                    Index Cond: (number >= 1::double precision)
                        ->  Limit  (cost=0.00..0.14 rows=1 width=12)
                              ->  Index Scan Backward using idx_t on t  (cost=0.00..9053.67 rows=66136 width=12)
                                    Index Cond: (number < 1::double precision)
   (11 rows)
   

2. ==============================

   2.당신이 뭔가를 시도 할 수 있습니다 :

   당신이 뭔가를 시도 할 수 있습니다 :

   select *
   from my_table
   where abs(1 - number) = (select min(abs(1 - number)) from t)
   

   이 아니라고 수동으로 테이블을 통해 반복하지만 적어도이 데이터베이스를 수보다 훨씬 다양한 아니라 앞뒤로 기능과 데이터베이스 내부 사이에 이동하는 것보다 루핑 내부의 "데이터베이스 공간을"않습니다. 또한, 단일 쿼리로 모든 것을 밀어 것은 당신이해야 할 노력하고 다음이 합리적인 방법으로 그것을 수행하려고 할 수있는 쿼리 엔진 노하우를 할 수 있습니다.

3. ==============================

   3.제 2 대답은 정확하지만 나는 "UNION ALL"에 오류가 발생했습니다 :

   제 2 대답은 정확하지만 나는 "UNION ALL"에 오류가 발생했습니다 :

   DBD :: 대학원 :: 세인트 실패 실행 오류 : 또는 "UNION"근처에 구문 오류

   이 코드로 고정 :

   SELECT * FROM
     (
       (SELECT * FROM table WHERE num >= ? ORDER BY num LIMIT 1)
           UNION ALL
       (SELECT * FROM table WHERE num < ?  ORDER BY num DESC LIMIT 1)
     ) as foo
   ORDER BY abs(?-num) LIMIT 1;
   

   트릭은 내부 테이블에서 AS를 제거 만 UNION에 그것을 사용하는 것입니다.

4. ==============================

   4.당신이 그룹 내에서 가장 가까운 값을 찾을하려는 경우이 코드는 도움이된다. 여기, 내 열 발 가까이 내 목표 값 0.5에 얼마나 가까이에 따라 column_you_wish_to_group_by으로 내 테이블 TB를 분할합니다.

   당신이 그룹 내에서 가장 가까운 값을 찾을하려는 경우이 코드는 도움이된다. 여기, 내 열 발 가까이 내 목표 값 0.5에 얼마나 가까이에 따라 column_you_wish_to_group_by으로 내 테이블 TB를 분할합니다.

   SELECT *
   FROM (
     SELECT
       ROW_NUMBER() OVER (PARTITION BY t.column_you_wish_to_group_by ORDER BY abs(t.val - 0.5) ASC) AS r,
       t.*
     FROM
       tb t) x 
   WHERE x.r = 1;

from https://stackoverflow.com/questions/6102824/is-there-a-postgres-closest-operator by cc-by-sa and MIT license