PostgreSQL에서 임의의 행 선택

1. PostgreSQL에서 행 선택을 위해 Random()을 사용한 기본 구현
2. PostgreSQL의 더 큰 테이블에 대한 임의 행 선택
3. OFFSET FLOOR를 사용하여 PostgreSQL에서 TABLE을 무작위 샘플링
4. OID에서 RANDOM을 사용하여 PostgreSQL의 테이블에서 행 가져오기

오늘 PostgreSQL에서는 테이블에서 임의의 행을 선택하는 방법을 배웁니다. 이것이 임의의 계획되지 않은 행 또는 요청되지 않은 행을 반환하는 경향이 있다는 것을 이름에서 짐작했을 것입니다.

따라서 PostgreSQL에서 무작위 행 선택을 구현할 수 있는 몇 가지 방법을 살펴보겠습니다.

PostgreSQL에서 행 선택을 위해 Random()을 사용한 기본 구현

‘RANDOM()‘은 정의된 범위에서 임의의 값을 반환하는 함수인 경향이 있습니다. 0.0 <= x < 1.0. 이는 DOUBLE PRECISION 유형을 사용하며 구문은 예제와 함께 다음과 같습니다.

random ( ) → double precision
random() → 0.897124072839091       - (example)

이제 이 RANDOM() 함수를 사용하여 고유하고 임의의 값을 얻을 수 있습니다. 따라서 RANDOM() 값이 0.05 정도인 경향이 있는 경우에만 테이블의 데이터 집합에 대한 SELECT 작업을 쿼리하려는 경우 다른 결과가 있을 것임을 확신할 수 있습니다. 매번 획득.

다음과 같은 쿼리를 통해 이를 증명할 수 있습니다.

select * from DOGGY where random() <= 0.02

우리는 TAGS 및 OWNER_IDs 세트를 포함하는 DOGGY 테이블을 사용했습니다. 위의 내용을 실행하면 어떻게 될까요?

SELECT * 작업을 호출하면 요구되는 조건이 충족되는지 여부를 확인하기 위해 WHERE 절이 추가될 때 각 행을 검사하는 경향이 있습니다. 즉, RANDOM() 값이 0.02보다 작거나 같은 데이터에 대해 TABLE을 확인합니다.

따라서 SELECT 아래 TABLE에서 행을 수신할 때마다 RANDOM() 함수를 호출하고 고유 번호를 수신하며 해당 숫자가 미리 정의된 값 (0.02)보다 작은 경우 , 최종 결과에서 ROW를 반환합니다.

그렇지 않으면 해당 행을 건너뛰고 후속 행을 확인합니다. 이것은 PostgreSQL 테이블에서 무작위 행을 쿼리하는 가장 간단한 방법인 경향이 있습니다.

무작위 행에 대한 SELECT 쿼리와 유사한 조작

완전히 임의의 행을 얻는 데 사용할 수 있는 또 다른 매우 쉬운 방법은 WHERE 절 대신 ORDER BY 절을 사용하는 것입니다. ORDER BY는 해당 시나리오의 절에 정의된 조건으로 테이블을 정렬합니다.

이 경우 매번 RANDOM 값으로 정렬하여 원하는 특정 결과 집합을 얻을 수 있습니다.

select * from DOGGY order by random();

위의 처리는 매번 다른 결과를 반환합니다. 다음은 DOGGY 테이블에서 이것을 쿼리한 두 가지 출력 결과입니다.

출력 1:

출력 2:

따라서 우리는 어떻게 다른 결과가 얻어지는지 볼 수 있습니다. 더 좋게 만들기 위해 LIMIT [NUMBER] 절을 사용하여 이 임의로 정렬된 테이블에서 우리가 원하는 첫 번째 2,3 행을 가져올 수 있습니다.

다음과 같이 쿼리하면 정상적으로 작동합니다.

select * from DOGGY order by random() limit 2;

무작위 행 선택을 위한 위의 방법 중 가장 좋은 방법에 대한 간단한 참고 사항:

ORDER BY 절을 사용하는 두 번째 방법은 전자보다 훨씬 나은 경향이 있습니다. 많은 경우 RANDOM()은 미리 정의된 숫자보다 작거나 크지 않은 값을 제공하거나 임의의 행에 대해 특정 조건을 충족하는 경향이 있기 때문입니다.

이는 결국 잘못된 결과 또는 빈 테이블로 이어질 수 있습니다. 따라서 이 경우 후자가 이깁니다.

정렬된 행은 다른 조건에서 동일할 수 있지만 빈 결과는 없습니다.

PostgreSQL의 더 큰 테이블에 대한 임의 행 선택

효율적이고 즉각적인 결과는 쿼리를 고려할 때 훨씬 더 나은 경향이 있습니다. 많은 테이블에는 백만 개 이상의 행이 있을 수 있으며 데이터 양이 많을수록 테이블에서 무언가를 쿼리하는 데 필요한 시간이 길어집니다.

이러한 시나리오에서 오버헤드를 줄이고 더 빠른 속도를 제공하는 솔루션을 살펴봅니다. 우선 동일한 테이블인 DOGGY를 사용하고 오버헤드를 줄이는 다양한 방법을 제시한 다음 기본 RANDOM 선택 방법으로 이동합니다.

오버헤드를 줄이는 방법 중 하나는 메인 쿼리의 실행을 기다린 후 이를 이용하는 것보다 훨씬 일찍 테이블 내부의 중요한 데이터를 추정하는 것이다.

PostgreSQL은 더 큰 데이터에 대해 매우 느린 COUNT 작업을 수행하는 경향이 있습니다. 왜?

500만 개의 테이블에서 각 행을 추가한 다음 1백만 행에 대해 5초로 계산하면 COUNT에 대해서만 25초를 소비하게 됩니다. 완료합니다. COUNT(*)를 호출하는 대신 카운트를 얻는 방법 중 하나는 RELTUPLE을 사용하는 것입니다.

‘RELTUPLE’은 ‘ANALYZED’된 후 테이블에 있는 데이터를 추정하는 경향이 있습니다. 계속해서 다음과 같이 실행할 수 있습니다.

analyze doggy;
select reltuples as estimate from pg_class where relname = 'doggy';

그런 다음 결과를 확인하고 이 쿼리에서 얻은 값이 COUNT에서 얻은 값과 동일한지 확인할 수 있습니다. 데이터에 대한 몇 가지 RANDOM 숫자를 생성해 보겠습니다.

더 효율적이고 큰 오버헤드를 줄이기 위해 큰 테이블에 대한 간단한 프로세스를 따를 것입니다.

1. 중복 랜덤값 생성 금지
2. 최종 테이블에서 초과 결과 제거
3. JOIN을 사용하여 임의 테이블 결과 고정

다음과 같은 쿼리가 제대로 작동합니다.

SELECT (r.tag::int / random())::int as x
FROM  (
   SELECT Distinct * from generate_series(6, 7) as tag limit 1
   ) r
JOIN  DOGGY USING (tag)

이렇게 하면 계산에서 받은 무작위 값 R.TAG과 일치하는 값이 있는 DOGGY의 테이블이 반환됩니다. SUB-QUERY에서 LIMIT 1을 사용하면 DOGGY 테이블에 조인할 단일 난수를 얻는 경향이 있습니다.

물론 테스트용입니다. 계속해서 이것을 다른 숫자로 조작할 수 있습니다.

DOGGY에 3 행이 포함되어 있다는 점을 고려하면 LIMIT 2 또는 3이 좋습니다. 이것은 훨씬 더 나은 솔루션으로 사용되며 이전 제품보다 빠릅니다.

테이블 내에서 중복 값을 제거할 수 있는 방법 중 하나는 UNION을 사용하는 것입니다. 동일한 쿼리를 반복하고 이전 항목과 UNION을 만들어 모든 고유하고 다른 요소를 생성할 수 있습니다.

그래서 우리가 다음과 같이 하려고 한다면:

SELECT (r.tag::int / random())::int as x
FROM  (
   SELECT Distinct * from generate_series(6, 7) as tag limit 1
   ) r
JOIN  DOGGY USING (tag)
union
SELECT (r.tag::int / random())::int as x
FROM  (
   SELECT Distinct * from generate_series(5, 6) as tag limit 1
   ) r
JOIN  DOGGY USING (tag)

우리는 모든 다른 값과 더 적은 간격으로 최종 결과를 얻을 것입니다. 값이 순서대로 있지 않지만 누락되어 있고 간격에 포함되지 않은 값을 의미합니다.

격차는 비효율적인 결과를 낳는 경향이 있습니다. 따라서 어떤 대가를 치르더라도 피해야 합니다.

마찬가지로 이 쿼리에서 TABLE과 RANDOM SELECTION의 값을 매개변수로 사용하는 함수를 만들 수 있습니다. 데이터베이스 세션에 일단 익숙해지면 많은 사용자가 나중에 이 기능을 쉽게 재사용할 수 있습니다.

이제 이를 처리할 수 있는 함수를 작성해 보겠습니다.

CREATE OR REPLACE FUNCTION random_func(limite int, limite_sup int)
  RETURNS table (val int, val2 int)
  LANGUAGE plpgsql VOLATILE ROWS 3 AS
$$
BEGIN
   RETURN QUERY SELECT *
      FROM  (
         SELECT Distinct * from generate_series(limite, limite_sup) as tag limit 1
         ) r
      JOIN DOGGY USING (tag)

      UNION
      SELECT *
      FROM  (
         SELECT Distinct * from generate_series(limite, limite_sup) as tag limit 1
         ) r
      JOIN DOGGY USING (tag);
END
$$

이 기능은 예상한 대로 작동합니다. UNION 쿼리를 실행하고 매개변수에 제공된 LIMIT와 함께 TABLE을 반환합니다.

임의 선택을 위해 다음과 같이 이 함수를 호출할 수 있습니다.

SELECT * FROM random_func(6, 7);

다시 한 번, RANDOM이 FUNCTION에 정의된 범위의 숫자가 아닌 경우가 많기 때문에 쿼리가 어떤 값도 반환하지 않고 그대로 유지되는 경우가 있음을 알 수 있습니다.

오히려 원하지 않는 값이 반환될 수 있으며 테이블에 유사한 값이 없으므로 빈 결과가 생성됩니다.

더 나은 결과를 생성하려면 TABLES 대신 MATERIALIZED VIEWS를 사용할 수 있습니다. 초기화하는 데 사용된 쿼리를 기억하고 나중에 새로 고칩니다.

이 REFRESH는 RANDOM에 대한 새 값을 더 빠른 속도로 반환하는 경향이 있으며 효과적으로 사용할 수 있습니다.

PostgreSQL에서 테이블의 무작위 샘플링에 권장되지 않는 방법

테이블에서 임의의 행을 가져오는 또 다른 훌륭한 방법은 PostgreSQL 설명서의 SELECT (FROM) 섹션에 정의된 TABLESAMPLE 방법일 수 있습니다.

통사론:

TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ]

인수가 반환하려는 테이블의 백분율인 경우 반환되는 테이블의 이 하위 집합은 완전히 무작위이며 다양합니다. 그러나 대부분의 경우 결과는 정렬된 테이블이거나 원래 버전의 테이블이며 일관되게 동일한 테이블을 반환합니다.

DOGGY에서 다음과 같은 쿼리를 실행하면 처음 몇 번의 실행에 대해 다양하지만 일관된 결과가 반환됩니다.

간단히 말해서 TABLESAMPLE에는 두 가지 다른 sampling_methods가 있을 수 있습니다. BERNOULLI 및 SYSTEM. 먼저 SYSTEM을 사용합니다.

select * from DOGGY tablesample system (30);

다음은 SYSTEM을 사용한 첫 번째 3 반복에 대한 결과입니다.

결과가 우리가 기대한 것과는 다르지만 잘못된 하위 집합을 제공한다는 것을 알 수 있습니다. 짧은 데이터 테이블 DOGGY는 SYSTEM이 아닌 BERNOULLI를 사용합니다. 그러나 그것은 우리가 원하는 것을 정확히 수행하는 경향이 있습니다.

select * from DOGGY tablesample bernoulli (30);

다음은 BERNOULLI를 사용한 첫 번째 3 반복에 대한 결과입니다.

따라서 인수에 전달된 백분율을 사용하여 다른 무작위 결과가 올바르게 얻어지는 것을 볼 수 있습니다.

SYSTEM과 BERNOULLI가 다른 점은 BERNOULLI는 지정된 인수 외부에 바인딩된 결과를 무시하는 반면 SYSTEM은 모든 행을 포함하는 테이블의 BLOCK을 임의로 반환하므로 무작위 샘플이 적다는 점입니다. “체계”.

아무 것도 제공되지 않을 때와 훨씬 다른 무작위 샘플링을 위해 다음과 같이 SAMPLING 쿼리에 대한 시드를 정의할 수도 있습니다.

select * from DOGGY tablesample bernoulli (30) repeatable (1);

TSM_SYSTEM_ROWS의 확장은 어떻게든 클러스터링을 종료하는 경우 무작위 샘플을 얻을 수도 있습니다. 확장 프로그램을 먼저 추가한 다음 사용해야 합니다.

CREATE EXTENSION tsm_system_rows;

select * from DOGGY tablesample system_rows(1);

그러나 시스템에 따라 다릅니다. 90%의 경우 무작위 샘플링이 없지만 클러스터링 효과가 발생하는 경우, 즉 우리의 경우에는 모집단에서 분할된 블록을 무작위로 선택하는 경우 무작위 값을 얻을 가능성이 거의 없습니다. 테이블.

효과가 없기 때문에 권장되지 않습니다.

OFFSET FLOOR를 사용하여 PostgreSQL에서 TABLE을 무작위 샘플링

테이블에서 임의의 행을 가져오는 데 사용할 수 있는 쿼리는 다음과 같습니다.

select * from DOGGY OFFSET floor(random() * 3) LIMIT 1;

‘OFFSET’은 테이블에서 하위 집합을 반환하기 전에 행을 건너뛰는 것을 의미합니다. 따라서 RANDOM() 값이 0.834이면 여기에 3을 곱하면 2.502가 반환됩니다.

2.502의 FLOOR는 2이고 2의 OFFSET은 행 번호 3부터 시작하여 DOGGY 테이블의 마지막 행을 반환합니다. LIMIT는 OFFSET 번호를 정의하여 얻은 하위 집합에서 한 행을 반환하는 경향이 있습니다.

OID에서 RANDOM을 사용하여 PostgreSQL의 테이블에서 행 가져오기

select * from DOGGY
where tag > floor((
    select (
        select reltuples::bigint AS estimate
        from   pg_class
        where  oid = 'public.DOGGY'::regclass) * random()
    ))
order by tag asc limit(1);

그렇다면 이 쿼리는 무엇을 할까요? 우리의 경우 위의 쿼리는 ROW ESTIMATE를 곱한 난수로 행 수를 추정하고 계산된 값보다 큰 TAG 값을 가진 행이 반환됩니다.

간단하면서도 효과적입니다. FLOOR를 사용하면 십진법의 바닥 값을 반환한 다음 이를 사용하여 DOGGY 테이블에서 행을 가져옵니다.

이제 PostgreSQL의 테이블에서 무작위 행을 찾기 위해 취할 수 있는 다양한 접근 방식을 이해하셨기를 바랍니다.