[PostgreSQL] seg — 오차 범위째 저장하는 측정값 구간 타입

설치·접속: PostgreSQL 설치와 접속

부제: 실험실 pH 계측값을 6.25~6.50처럼 오차 구간으로 저장하고, "이 구간과 겹치는 샘플"을 인덱스로 찾을 때

CREATE EXTENSION IF NOT EXISTS seg;

CREATE TABLE meas (id serial primary key, sample text, ph seg);
INSERT INTO meas(sample, ph) VALUES
  ('A', '6.25 .. 6.50'),
  ('B', '5(+-)0.3'),
  ('C', '6.4 .. 6.9'),
  ('D', '7.0 .. 7.2');
SELECT sample, ph FROM meas ORDER BY id;

 sample |      ph
--------+--------------
 A      | 6.25 .. 6.50
 B      | 4.7 .. 5.3
 C      | 6.4 .. 6.9
 D      | 7.0 .. 7.2
(4 rows)

seg는 부동소수점 하나가 아니라 구간을 값으로 다룬다. 6.25 .. 6.50은 하한·상한을 직접 준 것이고, 5(+-)0.3은 "5, 오차 ±0.3"이라는 계측기식 표기인데 저장하면 4.7 .. 5.3으로 전개된다. float은 6.50과 6.5를 구분하지 못하고 측정 오차라는 개념 자체가 없지만, seg는 불확실성을 1급 타입으로 보존한다.

진짜 쓸모는 구간 연산자다. 특정 밴드와 겹치는(&&) 샘플을 찾는다.

SELECT sample, ph FROM meas WHERE ph && '6.3 .. 6.6'::seg ORDER BY id;

 sample |      ph
--------+--------------
 A      | 6.25 .. 6.50
 C      | 6.4 .. 6.9
(2 rows)

6.3 .. 6.6에 A(6.256.50)와 C(6.46.9)가 걸친다. 포함(<@)으로 특정 범위 안에 온전히 들어오는 샘플만 뽑을 수도 있다.

SELECT sample, ph FROM meas WHERE ph <@ '6.0 .. 7.0'::seg ORDER BY id;

 sample |      ph
--------+--------------
 A      | 6.25 .. 6.50
 C      | 6.4 .. 6.9
(2 rows)

이 겹침·포함 질의는 GiST 인덱스로 가속된다. 데이터가 쌓이면 플래너가 인덱스를 골라 전 구간 스캔을 피한다.

CREATE INDEX meas_gist ON meas USING gist (ph);
EXPLAIN (COSTS OFF) SELECT sample FROM meas WHERE ph && '6.3 .. 6.6'::seg;

                  QUERY PLAN
-----------------------------------------------
 Bitmap Heap Scan on meas
   Recheck Cond: (ph && '6.3 .. 6.6'::seg)
   ->  Bitmap Index Scan on meas_gist
         Index Cond: (ph && '6.3 .. 6.6'::seg)
(4 rows)

(위 플랜은 표본 5천 행을 채운 뒤 측정한 것. 행이 몇 개뿐이면 플래너가 seq scan을 택한다.) 함정은 seg가 유효숫자를 최대 몇 자리로 제한(문서상 소수 유효숫자 관리)한다는 점과, 표기가 ../(+-)로 특수하다는 점이다.

이렇게도 쓴다

계측기 정밀도가 낮으면 ~ 근사 표기로 넣는다 — 표시 자릿수에 불확실성을 반영.

SELECT '~6.5'::seg AS approx, '<6.5'::seg AS below, '>6.5'::seg AS above;

 

두 구간의 위치 관계를 따진다 — 완전히 왼쪽(<<)/오른쪽(>>)인지. (조합: seg 위치 연산자)

SELECT '6.25 .. 6.50'::seg << '7.0 .. 7.2'::seg AS strictly_left,
       '6.25 .. 6.50'::seg >> '4.0 .. 5.0'::seg AS strictly_right;
-- strictly_left = t, strictly_right = t

 

특정 값이 어느 샘플의 오차 구간 안에 드는지 조회한다 — 점을 폭 0 구간으로.

SELECT sample, ph FROM meas WHERE '6.45'::seg <@ ph;

 

다차원 측정(온도×압력 등)으로 넘어가면 seg 대신 cube를 쓴다. seg는 1차원 구간 전용이므로, 축이 늘면 cube의 다차원 박스가 맞다. (조합: cube — 경계)

-- cube: '(6.25),(6.50)' 같은 1D 박스부터 n차원까지, GiST 겹침도 동일 패턴

순수 범위 조회만 필요하고 오차·유효숫자 보존이 중요하지 않다면 코어 numrange + GiST가 더 단순하다. seg는 "측정 불확실성 자체가 데이터"일 때 값을 한다. (조합: numrange — 경계)