가장 좋은 방법은 임의의 행을 선택하는 PostgreSQL을

나는 PostgreSQL을 행의 임의의 선택, 나는이 시도합니다 :

select * from table where random() < 0.01;

그러나 다른이를 추천합니다 :

select * from table order by random() limit 1000;

나는 그것을 빨리 할, 500 만 개 행이 매우 큰 테이블이 있습니다.

어떤 방법이 더 좋다? 차이점은 무엇입니까? 임의의 행을 선택하는 가장 좋은 방법은 무엇입니까?

해결법

==============================
1.을 감안할 때 귀하의 사양 (플러스 코멘트에 추가 정보)

을 감안할 때 귀하의 사양 (플러스 코멘트에 추가 정보)

쿼리는 아래의 큰 테이블의 순차 검색 만 인덱스 스캔을 필요로하지 않는다.

먼저, 메인 쿼리에 대한 추정치를 얻을 :
```
SELECT count(*) AS ct              -- optional
     , min(id)  AS min_id
     , max(id)  AS max_id
     , max(id) - min(id) AS id_span
FROM   big;
```
유일한 가능성이 비싼 부분은 (큰 테이블) 개수 (*)입니다. 사양 이상을 감안할 때, 당신은 필요하지 않습니다. 추정치는 거의 비용 (여기에 자세히 설명)에서 사용할 수 잘을 할 것입니다 :
```
SELECT reltuples AS ct FROM pg_class WHERE oid = 'schema_name.big'::regclass;
```
한 CT가 id_span보다 훨씬 작으로, 쿼리는 다른 접근 방식을 능가 할 것이다.
```
WITH params AS (
    SELECT 1       AS min_id           -- minimum id <= current min id
         , 5100000 AS id_span          -- rounded up. (max_id - min_id + buffer)
    )
SELECT *
FROM  (
    SELECT p.min_id + trunc(random() * p.id_span)::integer AS id
    FROM   params p
          ,generate_series(1, 1100) g  -- 1000 + buffer
    GROUP  BY 1                        -- trim duplicates
    ) r
JOIN   big USING (id)
LIMIT  1000;                           -- trim surplus
```
이 쿼리를 단순화 할 수 있습니다. 위의 쿼리의 CTE는 교육 목적을위한 것입니다 :
```
SELECT *
FROM  (
    SELECT DISTINCT 1 + trunc(random() * 5100000)::integer AS id
    FROM   generate_series(1, 1100) g
    ) r
JOIN   big USING (id)
LIMIT  1000;
```
당신은 빈틈과 평가에 대해 너무 확실하지 않은 경우에 특히.
```
WITH RECURSIVE random_pick AS (
   SELECT *
   FROM  (
      SELECT 1 + trunc(random() * 5100000)::int AS id
      FROM   generate_series(1, 1030)  -- 1000 + few percent - adapt to your needs
      LIMIT  1030                      -- hint for query planner
      ) r
   JOIN   big b USING (id)             -- eliminate miss

   UNION                               -- eliminate dupe
   SELECT b.*
   FROM  (
      SELECT 1 + trunc(random() * 5100000)::int AS id
      FROM   random_pick r             -- plus 3 percent - adapt to your needs
      LIMIT  999                       -- less than 1000, hint for query planner
      ) r
   JOIN   big b USING (id)             -- eliminate miss
   )
SELECT *
FROM   random_pick
LIMIT  1000;  -- actual limit
```
우리는 기본 쿼리에 작은 흑자로 작업 할 수 있습니다. 우리가 첫 번째 반복에서 충분한 행을 찾을 수없는, 그래서 너무 많은 차이가있는 경우 rCTE는 재귀 적 용어로 반복하고 있습니다. 우리는 여전히 ID 공간에서 상대적으로 적은 격차를 필요로하거나 한계에 도달하기 전에 재귀 건조를 실행할 수 있습니다 - 또는 우리는 성능을 최적화의 목적을 아무리 충분히 큰 버퍼로 시작해야합니다.

중복은 rCTE의 조합에 의해 제거된다.

외부 LIMIT는 곧 우리가 충분한 행이 같은 CTE 정지합니다.

이 쿼리는 신중하게 사용 가능한 인덱스를 사용하는 초안을 작성하고, 실제로 임의의 행을 생성하지 우리가 한계 (재귀 실행이 건조하지 않는)을 충족 정지 할 때까지. 당신이 그것을 다시 작성하려는 경우 함정의 숫자가 여기에있다.

다양한 매개 변수를 사용하여 반복 사용의 경우 :
```
CREATE OR REPLACE FUNCTION f_random_sample(_limit int = 1000, _gaps real = 1.03)
  RETURNS SETOF big AS
$func$
DECLARE
   _surplus  int := _limit * _gaps;
   _estimate int := (           -- get current estimate from system
      SELECT c.reltuples * _gaps
      FROM   pg_class c
      WHERE  c.oid = 'big'::regclass);
BEGIN

   RETURN QUERY
   WITH RECURSIVE random_pick AS (
      SELECT *
      FROM  (
         SELECT 1 + trunc(random() * _estimate)::int
         FROM   generate_series(1, _surplus) g
         LIMIT  _surplus           -- hint for query planner
         ) r (id)
      JOIN   big USING (id)        -- eliminate misses

      UNION                        -- eliminate dupes
      SELECT *
      FROM  (
         SELECT 1 + trunc(random() * _estimate)::int
         FROM   random_pick        -- just to make it recursive
         LIMIT  _limit             -- hint for query planner
         ) r (id)
      JOIN   big USING (id)        -- eliminate misses
   )
   SELECT *
   FROM   random_pick
   LIMIT  _limit;
END
$func$  LANGUAGE plpgsql VOLATILE ROWS 1000;
```
요구:
```
SELECT * FROM f_random_sample();
SELECT * FROM f_random_sample(500, 1.05);
```
당신은 모든 테이블에 대한 작업이 제네릭을 만들 수있는 다음 PK 컬럼 및 다형성 유형과 사용 EXECUTE 같은 테이블의 이름을 가지고 ...하지만이 질문의 범위를 넘어 그의. 보다:

귀하의 요구 사항을 반복 호출에 대해 동일한 설정을 허용 (우리가 반복 호출에 대해 얘기)에 묻 으면 나는이보기를 구체화 고려할 것입니다. 한 번 쿼리 위에 실행하고 테이블에 결과를 작성합니다. 사용자는 속도를 번개에 준 무작위 선택을 얻는다. 간격 또는 사용자가 선택한 이벤트에서 무작위로 선택을 새로 고칩니다.

여기서 n은 백분율입니다. 수동 :

굵게 강조 광산. 그것은 매우 빠르게, 그러나 결과는 정확하게 무작위로하지 않습니다. 수동 다시 :

반환 된 행의 수는 격렬하게 다를 수 있습니다. 우리의 예를 들어, 약 1000 행을 얻을 수 있습니다 :
```
SELECT * FROM big TABLESAMPLE SYSTEM ((1000 * 100) / 5100000.0);
```
관련 :

또는 (충분히있는 경우) 정확히 요청 행의 수를 얻고 더 편리한 구문을 허용하기 위해 추가 모듈 tsm_system_rows를 설치 :
```
SELECT * FROM big TABLESAMPLE SYSTEM_ROWS(1000);
```
자세한 내용은 에반의 답변을 참조하십시오.

그러나 아직 정확히 무작위 아니다.
==============================
2.당신은 검사하고 사용하여 모두의 실행 계획을 비교할 수 있습니다

당신은 검사하고 사용하여 모두의 실행 계획을 비교할 수 있습니다
```
EXPLAIN select * from table where random() < 0.01;
EXPLAIN select * from table order by random() limit 1000;
```
먼저 종류 전체 테이블과 BY 순서는 처음 1000 개 항목을 선택합니다 것을 큰 표 쇼에 빠른 테스트. 큰 테이블을 정렬하는 것은 해당 테이블을 읽어뿐만 아니라 읽기 및 임시 파일을 작성하는 것을 포함한다. 를 Where 랜덤 () <0.1 번만 테이블 전체를 스캔한다.

큰 테이블의 경우이 힘 당신은 심지어 하나의 완전한 테이블 스캔이 오래 걸릴 수 싶지 것을.

세 번째 제안 될 것이다
```
select * from table where random() < 0.01 limit 1000;
```
1000 개 행을 따라서 반환 빨리 발견 한 것처럼이 사람은 곧 같은 테이블 스캔을 중지합니다. 임의성 아래 과정이 습지의 비트,하지만 아마도이 귀하의 경우 충분한입니다.

편집 :이 고려 외에도,이에 대한 이미 묻는 질문을 확인 할 수 있습니다. 쿼리 [PostgreSQL의] 임의 반환 꽤 많은 안타를 사용.

그리고 몇 가지 더 요약 depez의 링크 된 문서 접근 :

"전체 테이블이 메모리에 맞지 않습니다"와 같이 1 "큰".

==============================

3.

select your_columns from your_table ORDER BY random()

select * from 
  (select distinct your_columns from your_table) table_alias
ORDER BY random()

select your_columns from your_table ORDER BY random() limit 1

==============================
4.PostgreSQL의 9.5부터 테이블에서 임의의 요소를 얻는하기위한 새로운 구문이있다 :

PostgreSQL의 9.5부터 테이블에서 임의의 요소를 얻는하기위한 새로운 구문이있다 :
```
SELECT * FROM mytable TABLESAMPLE SYSTEM (5);
```
이 예제는 당신에게 MYTABLE의 요소의 5 %를 제공 할 것입니다.

이 블로그 게시물에 대한 자세한 설명을 참조하십시오 http://www.postgresql.org/docs/current/static/sql-select.html
==============================
5.순서에가있는 사람은 느린 하나가 될 것입니다.

순서에가있는 사람은 느린 하나가 될 것입니다.

표에서 * 여기서 랜덤 선택 () <0.01; 기록에 의해 기록을 간다, 무작위을 필터링 여부를 결정합니다. 이것은 한 번만 각 레코드를 확인해야하기 때문에 O (N)를 될 것입니다.

랜덤 ()에 의해 제한 1,000 테이블 주문 *을 선택; 다음, 전체 테이블을 정렬 배후를 제외하고 모든 부두 마법의 첫 번째 1000을 선택하는 것입니다 의해 순서는 O (N * 로그 N)입니다.

임의의 () <0.01 하나의 단점은 출력 기록의 변수 번호를 얻을 수 있습니다 것입니다.

O (N)에서 실행 피셔 - 예이츠 셔플 : 주, 무작위로 정렬보다 데이터 세트를 셔플에 더 나은 방법이있다. 하지만, 매우 도전과 같은 SQL 사운드에 셔플을 구현.
==============================
6.여기에 나를 위해 작동 결정이다. 나는 그것을 이해하고 실행하는 것은 매우 간단 같아요.

여기에 나를 위해 작동 결정이다. 나는 그것을 이해하고 실행하는 것은 매우 간단 같아요.
```
SELECT 
  field_1, 
  field_2, 
  field_2, 
  random() as ordering
FROM 
  big_table
WHERE 
  some_conditions
ORDER BY
  ordering 
LIMIT 1000;
```
==============================
7.
```
select * from table order by random() limit 1000;
```
당신은 당신이 원하는 행 수를 알고있는 경우에, tsm_system_rows을 확인하십시오.

먼저 확장을 설치
```
CREATE EXTENSION tsm_system_rows;
```
그런 다음 쿼리,
```
SELECT *
FROM table
TABLESAMPLE SYSTEM_ROWS(1000);
```
==============================
8.당신은 단지 하나 개의 행을 원하는 경우에, 당신은이 계산에서 파생 된 오프셋 계산 사용할 수 있습니다.

당신은 단지 하나 개의 행을 원하는 경우에, 당신은이 계산에서 파생 된 오프셋 계산 사용할 수 있습니다.
```
select * from table_name limit 1
offset floor(random() * (select count(*) from table_name));
```
==============================
9.어윈 Brandstetter에 의해 설명 된 구체화 된 뷰 "가능한 대안"의 변형이 가능합니다.

어윈 Brandstetter에 의해 설명 된 구체화 된 뷰 "가능한 대안"의 변형이 가능합니다.

말은, 예를 들어, 당신은 반환되는 무작위 값의 중복 싶지 않아. 당신은 값의 사용자 (비 무작위) 세트를 포함하는 기본 테이블에 부울 값을 설정해야합니다 그래서.

이 입력 테이블이라고 가정 :
```
id_values  id  |   used
           ----+--------
           1   |   FALSE
           2   |   FALSE
           3   |   FALSE
           4   |   FALSE
           5   |   FALSE
           ...
```
필요에 따라 ID_VALUES 테이블을 채 웁니다. 어윈에 의해 설명 된대로 그런 다음 ID_VALUES 테이블 번을 무작위로 구체화 된 뷰를 생성 :
```
CREATE MATERIALIZED VIEW id_values_randomized AS
  SELECT id
  FROM id_values
  ORDER BY random();
```
이 빠르게 구식이 될 것이기 때문에 구체화 된 뷰, 사용 된 열을 포함하지 않습니다. 도 아니다 id_values 테이블에있을 수있는 다른 열을 포함하는보기 필요하지 않습니다.

얻기 위하여, 임의의 값 (및 "소비")는, id_values에 업데이트 리턴하는 조인과 id_values_randomized에서 id_values을 선택하고 만 관련 가능성을 얻기 위해 원하는 기준을 적용 사용. 예를 들면 :
```
UPDATE id_values
SET used = TRUE
WHERE id_values.id IN 
  (SELECT i.id
    FROM id_values_randomized r INNER JOIN id_values i ON i.id = r.id
    WHERE (NOT i.used)
    LIMIT 5)
RETURNING id;
```
필요에 따라 변경 LIMIT - 당신이 한 번에 하나 개의 임의의 값 1로 변경 LIMIT이 필요합니다.

id_values에 적절한 인덱스로, 나는 작은 부하 매우 신속하게 실행해야 UPDATE가 돌려주는 생각합니다. 그것은 하나의 데이터베이스 왕복으로 무작위 값을 반환합니다. 필요에 따라 "자격"행에 대한 기준은 복잡 할 수 있습니다. 새로운 행은 언제든지 id_values 테이블에 추가 할 수 있습니다, 그들은 구체화 된 뷰가 갱신 될 때 곧 (아마 오프 피크 시간에 실행 할 수있는)로 응용 프로그램에 접근하게 될 것이다. 창조와 구체화 된 뷰의 새로 고침 속도가 느린 수 있지만 그것은 단지 새로운 아이디의이 id_values 테이블에 추가 될 때 실행해야합니다.
==============================
10.내 경험에서 하나 명의 교훈 :

내 경험에서 하나 명의 교훈 :

오프셋 층 (랜덤 () * N) 한도 1 난수 ()에 의해 제한 한 순서 이상인 빠르다.

나는 포스트 그레스에 정렬의 시간을 저장해야하기 때문에 오프셋 방식이 빠를 거라 생각 했어요. 그것은 밝혀 아니었다.
==============================
11.형 시리얼과 연구라는 컬럼을 추가합니다. 인덱스 R.

형 시리얼과 연구라는 컬럼을 추가합니다. 인덱스 R.

우리는 20 개의 행을 가정, 우리는 임의의 수 (N), 0
R과 행을 선택> N, 일종의 그들 ASC를 가장 작은 하나를 선택합니다.

암호:
```
select * from YOUR_TABLE 
where r > (
    select (
        select reltuples::bigint AS estimate
        from   pg_class
        where  oid = 'public.YOUR_TABLE'::regclass) * random()
    )
order by r asc limit(1);
```
코드는 자명하다. 중간에 하위 쿼리를 신속하게 https://stackoverflow.com/a/7945274/1271094에서 테이블 행 수를 추정하는 데 사용됩니다.

N> 행 또는 필요의 수는 여러 행을 선택하는 경우 응용 프로그램 수준에서 당신은 다시 문을 실행해야합니다.
==============================
12.나는 파티에 조금 늦게 해요 알고 있지만 난 그냥 pg_sample라는이 멋진 도구를 발견 :

나는 파티에 조금 늦게 해요 알고 있지만 난 그냥 pg_sample라는이 멋진 도구를 발견 :

나는 350M 행 데이터베이스와이를 시도하고 임의성 모르는, 정말 빨랐다.
```
./pg_sample --limit="small_table = *" --limit="large_table = 100000" -U postgres source_db | psql -U postgres target_db
```

from https://stackoverflow.com/questions/8674718/best-way-to-select-random-rows-postgresql by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] PDO 준비된 문을에서 원시 SQL 쿼리 문자열 얻기 (0)	2020.03.11
[SQL] 왜 것 사람이 사용 WHERE 1 = 1 AND <조건>은 SQL 절의? (0)	2020.03.11
[SQL] PHP는 mysqli 준비된 문장 LIKE (0)	2020.03.11
[SQL] 쿼리 문자열에서 숫자 만 얻을 수 있습니다 (0)	2020.03.11
[SQL] 열 이름 피벗 해제 (0)	2020.03.11

복붙노트

[SQL] 가장 좋은 방법은 임의의 행을 선택하는 PostgreSQL을

가장 좋은 방법은 임의의 행을 선택하는 PostgreSQL을

해결법

1.을 감안할 때 귀하의 사양 (플러스 코멘트에 추가 정보)

2.당신은 검사하고 사용하여 모두의 실행 계획을 비교할 수 있습니다

3.

4.PostgreSQL의 9.5부터 테이블에서 임의의 요소를 얻는하기위한 새로운 구문이있다 :

5.순서에가있는 사람은 느린 하나가 될 것입니다.

6.여기에 나를 위해 작동 결정이다. 나는 그것을 이해하고 실행하는 것은 매우 간단 같아요.

7.

8.당신은 단지 하나 개의 행을 원하는 경우에, 당신은이 계산에서 파생 된 오프셋 계산 사용할 수 있습니다.

9.어윈 Brandstetter에 의해 설명 된 구체화 된 뷰 "가능한 대안"의 변형이 가능합니다.

10.내 경험에서 하나 명의 교훈 :

11.형 시리얼과 연구라는 컬럼을 추가합니다. 인덱스 R.

12.나는 파티에 조금 늦게 해요 알고 있지만 난 그냥 pg_sample라는이 멋진 도구를 발견 :

'SQL' 카테고리의 다른 글

티스토리툴바