Postgres FOR LOOP

Я пытаюсь получить 25 случайных выборок из 15 000 идентификаторов из таблицы. Вместо ручного нажатия запуска каждый раз, я пытаюсь сделать цикл. Я полностью понимаю, что это не оптимальное использование Postgres, но это инструмент, который у меня есть. Это то, что у меня есть до сих пор:

for i in 1..25 LOOP
   insert into playtime.meta_random_sample
   select i, ID
   from   tbl
   order  by random() limit 15000
end loop

Ответ 1

Процедурные элементы, такие как loop, не являются частью языка SQL и могут использоваться только внутри тела процедурного языка , функция, процедура (Postgres 11 или позже) или оператор DO, где такие дополнительные элементы определяются соответствующим процедурным языком. По умолчанию используется PL/pgSQL, но есть и другие.

Пример с plpgsql:

DO
$do$
BEGIN 
   FOR i IN 1..25 LOOP
      INSERT INTO playtime.meta_random_sample
         (col_i, col_id)                       -- declare target columns!
      SELECT  i,     id
      FROM   tbl
      ORDER  BY random()
      LIMIT  15000;
   END LOOP;
END
$do$;

Для многих задач, которые можно решить с помощью цикла, существует более короткое и быстрое решение на основе множеств за углом. Чистый эквивалент SQL для вашего примера:

INSERT INTO playtime.meta_random_sample (col_i, col_id)
SELECT t.*
FROM   generate_series(1,25) i
CROSS  JOIN LATERAL (
   SELECT i, id
   FROM   tbl
   ORDER  BY random()
   LIMIT  15000
   ) t;

О generate_series():

Каково ожидаемое поведение для нескольких функций, возвращающих множество в предложении select?

Об оптимизации производительности случайных выборов:

Лучший способ выбрать случайные строки PostgreSQL

Ответ 2

Ниже приведен пример, который вы можете использовать:

create temp table test2 (
  id1  numeric,
  id2  numeric,
  id3  numeric,
  id4  numeric,
  id5  numeric,
  id6  numeric,
  id7  numeric,
  id8  numeric,
  id9  numeric,
  id10 numeric) 
with (oids = false);

do
$do$
declare
     i int;
begin
for  i in 1..100000
loop
    insert into test2  values (random(), i * random(), i / random(), i + random(), i * random(), i / random(), i + random(), i * random(), i / random(), i + random());
end loop;
end;
$do$;

Ответ 3

Я только что столкнулся с этим вопросом и, хотя он старый, я решил добавить ответ для архивов. ОП спрашивал о циклах, но их целью было собрать случайную выборку строк из таблицы. Для этой задачи Postgres 9. 5+ предлагает предложение TABLESAMPLE для WHERE. Вот хорошее краткое изложение:

https://www.2ndquadrant.com/en/blog/tablesample-in-postgresql-9-5-2/

Я склонен использовать Бернулли, поскольку он основан на строках, а не на страницах, но первоначальный вопрос касается конкретного количества строк. Для этого есть встроенное расширение:

https://www.postgresql.org/docs/current/tsm-system-rows.html

CREATE EXTENSION tsm_system_rows;

Затем вы можете получить любое количество строк:

select * from playtime tablesample system_rows (15);