Как генерировать случайные данные для таблицы ассоциаций?

Я работаю над интернет-магазином с тремя столами: products, atributes и product_atributes. Я уже заполнил таблицы products и atributes:

insert into atribute(name)
select random_atribute()
from generate_series(1,10000) as seq(i);

insert into product(price)
select floor(1 + random() * 100)::int
from generate_series(1,20000) as seq(i);

Я хочу сгенерировать случайные данные для таблицы product_atributes: может быть 1 атрибут для 1 продукта, но также и все атрибуты только для 1 продукта. Результат должен выглядеть так:

product_atributes
id  product_id     atribute_id
1      5               12
2      5               76
3      5               10
4      5                7
5      1               45
6      1               109
...

Таблица product_atributes создается так:

create table product_atributes(
  id serial primary key,
  atribute_id integer references atribute(id),
  product_id integer references product(id)
);

Я пробовал что-л. вот так, но не работает:

with data as (
    select  s.i,
            random_atribute_id() as atribute_id,
            s.id as product_id
   
    from (generate_series(1, 1000) as seq(i)
             cross join lateral (select seq.i, * from product order by random() limit 1) as s)
)

insert into product_atributes(atribute_id, product_id)
select atribute_id, product_id from data;

Как я могу это сделать?

ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
1
0
35
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Простой

Чтобы вставить все возможные комбинации с равным шансом в 1 процент:

INSERT INTO product_atributes(product_id, atribut_id)
SELECT p.product_id, a.atribut_id
FROM   product p
JOIN   atribute a ON random() >= 0.99;

Более сложный и быстрый

Это обеспечивает больший контроль над минимальным и максимальным количеством атрибутов.

И это намного быстрее для вашей конкретной настройки с идентификаторами атрибутов без зазоров (от 1 до 10000):

INSERT INTO product_atributes(product_id, atribut_id)
SELECT p.product_id, a.atribut_id
FROM  (SELECT product_id, trunc(random() * 10)::int AS ct FROM product) p
CROSS  JOIN LATERAL (
   SELECT DISTINCT 1 + trunc(random() * 10000) AS atribut_id
   FROM   generate_series (0, p.ct) g
   ) a;

Это генерирует от 0 до 10 совершенно случайных ассоциаций для каждого продукта.
2 + trunc(random() * 6)::int AS ct вызовет от 3 до 8 ассоциаций. (Обратите внимание на generate_series() на основе 0!)

Создайте количество атрибутов (ct) в подзапросе, чтобы избежать однократной оценки random(), приводящей к одному и тому же количеству для всех продуктов.

Мы можем немного оптимизировать производительность для идентификаторов без пропусков. Вместо того, чтобы просматривать все 10000 атрибутов для каждого продукта, просто сгенерируйте случайные числа для заданного диапазона идентификаторов. Много быстрее.
Добавьте DISTINCT, чтобы исключить (маловероятные) дубликаты. Вряд ли имеет значение для 10 из 10000, но мы не можем допустить дублирования. (Таким образом, это возможно, чтобы получить меньше ассоциаций в редких случаях.)

Мы могли бы даже работать с несколькими пробелами. В выборе случайных строк есть немало тонкостей. Видеть:

В стороне

Вы также можете заполнить все три таблицы случайными данными в одном запросе, используя CTE, изменяющие данные. Простой пример для отношения 1:n:

Тот же принцип для вашего случая, только два CTE...

Связанный:

«Атрибуты» пишутся так.

@Kate: Вы уже приняли. Но вас все равно может заинтересовать добавленная более быстрая альтернатива.

Erwin Brandstetter 05.05.2022 00:58

Другие вопросы по теме