Блог

Главная - Вопросы по программированию - Эффективная повторная выборка с заменой таблицы в аналогичном PostgreSQL?

Эффективная повторная выборка с заменой таблицы в аналогичном PostgreSQL?

#sql #postgresql #resampling

#sql #postgresql #повторная выборка

Вопрос:

Я пытаюсь проверить распределение чисел в столбце таблицы. Вместо того, чтобы вычислять по всей таблице (которая большая — десятки гигабайт) Я хочу оценить с помощью повторной выборки. Я думаю, что типичный метод Postgres для этого

 select COLUMN
from TABLE
order by RANDOM()
limit 1;

но это медленно для повторной выборки, тем более что (я подозреваю) она манипулирует всем столбцом при каждом моем запуске.

Есть ли способ лучше?

РЕДАКТИРОВАТЬ: просто чтобы убедиться, что я выразил это правильно, я хочу сделать следующее:

 for(i in 1:numSamples)
  draw 500 random rows
end

без необходимости каждый раз переупорядочивать всю массивную таблицу. Возможно, я мог бы получить все идентификаторы строк таблицы и выполнить выборку из нее в R или что-то в этом роде, а затем просто запросить эти строки?

Комментарии:

1. В SQL2003 введено предложение tablesample, где (DB2 реализовал его, не уверен, есть ли другие). Это обсуждалось ( wiki.postgresql.org /… ) для postgres, но я не знаю текущего состояния.

Ответ №1:

Поскольку вам нужна выборка данных, как насчет использования расчетного размера таблицы, а затем вычисления процента от этого в качестве выборки?

В таблице pg_class хранится оценка количества строк для каждой таблицы (обновляется процессом vacuum, если я не ошибаюсь).

Таким образом, следующее будет выбирать 1% всех строк из этой таблицы:

 with estimated_rows as (
  select reltuples as num_rows
  from pg_class t
    join pg_namespace n on n.oid = t.relnamespace
  where t.relname = 'some_table'
  and n.nspname = 'public'
)
select *
from some_table
limit (select 0.01 * num_rows from estimated_rows)
;

Если вы делаете это очень часто, возможно, вам захочется создать функцию, чтобы вы могли делать что-то вроде этого:

 select *
from some_table
limit (select estimate_percent(0.01, 'public', 'some_table'))
;

Комментарии:

1. Но не будет ли так, что в таблице из 100 строк 50%-ная выборка даст мне строки 1: 50, а 20%-ная выборка даст мне строки 1: 20? Моя цель здесь — иметь возможность выполнять несколько независимых отрисовок.

2. @PatrickMcCarthy поскольку строки в таблице не сортируются (особенно если они часто обновляются), это должно дать вам «случайную» выборку. Но вы правы: если вы запустите его дважды, вы, скорее всего, получите одни и те же строки. Но вы можете по-прежнему использовать order by random() , но это сделает запрос довольно медленным, поскольку он всегда будет считывать таблицу целиком.

Ответ №2:

Создайте временную таблицу из целевой таблицы, добавив столбец с номером строки

 drop table if exists temp_t;
create temporary table temp_t as
select *, (row_number() over())::int as rn
from t

Создайте более легкую временную таблицу, выбрав только те столбцы, которые будут использоваться при выборке и фильтрации по мере необходимости.

Проиндексируйте ее по столбцу номер строки

 create index temp_t_rn on temp_t(rn);
analyze temp_t;

Выполняйте этот запрос для каждой выборки

 with r as (
    select ceiling(random() * (select max(rn) from temp_t))::int as rn
    from generate_series(1, 500) s
)
select *
from temp_t
where rn in (select rn from r)

SQL Fiddle