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

postgresql random for-loop stored-procedures plpgsql
person user2840106    schedule 02.10.2013    source источник

Ответы (4)


arrow_upward
167
arrow_downward

Процедурный такие элементы, как циклы, не являются частью языка SQL и могут использоваться только внутри тела процедурного языка функция, процедура (Postgres 11 или новее) или DO< /a>, где такие дополнительные элементы определяются соответствующим процедурным языком. По умолчанию используется 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():

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

person Erwin Brandstetter    schedule 02.10.2013

arrow_upward
7
arrow_downward

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

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$;
person Gabriel    schedule 23.04.2019

arrow_upward
2
arrow_downward

Я только что столкнулся с этим вопросом, и, хотя он старый, я решил добавить ответ для архивов. ОП спрашивал о циклах, но их целью было собрать случайную выборку строк из таблицы. Для этой задачи 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);
person Morris de Oryx    schedule 12.10.2019

arrow_upward
1
arrow_downward

Я считаю более удобным устанавливать соединение с помощью процедурного языка программирования (например, Python) и выполнять такие типы запросов.

import psycopg2
connection_psql = psycopg2.connect( user="admin_user"
                                  , password="***"
                                  , port="5432"
                                  , database="myDB"
                                  , host="[ENDPOINT]")
cursor_psql = connection_psql.cursor()

myList = [...]
for item in myList:
  cursor_psql.execute('''
    -- The query goes here
  ''')

connection_psql.commit()
cursor_psql.close()
person LoMaPh    schedule 05.11.2019
comment
Я думал о том же, но тогда это может стать проблемой, когда количество вызовов к БД, которое вам нужно сделать, велико. - person shwifty chill; 02.06.2021