Как получить случайную комбинацию строк, которые суммируют до определенного значения?

Таблица у меня есть:

+--------+--------------------+
|item_id|        value        |
+--------+--------------------+
|  1     | 1                  |
|  2     | 4                  |
|  3     | 2                  |
|  4     | 6                  |
+--------+--------------------+

Каким должен быть результат SQL-запроса: случайная комбинация элементов, сумма которых достигает 10, с переменным числом различных элементов (в данном случае 2.)

+--------------+--------------------+-------------+
|item_id       |        amount      |      sum    |
+--------------+--------------------+-------------+
|2             |2                   |8            |
|3             |1                   |2            |
+--------------+--------------------+-------------+

Результаты показывают

Вы получаете 2 раза пункт 2 (который имеет значение 4, поэтому он добавляет к сумме 8).

И вы получаете один раз пункт 3 (который имеет значение 2)

Эта комбинация суммирует до 10.

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

Источник

2 ответа

Решение

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

select
  *
from (
  select
    a.item_id as item1, 
    x.n as amount1, 
    a.value * x.n as sum1,
    b.item_id as item2, 
    y.n as amount2, 
    b.value * y.n as sum2,
    rand() as r
  from my_table a
  join my_table b on b.item_id <> a.item_id
  cross join (
    select 1 as n union select 2 union select 3 union select 4 
    union select 5 union select 6 union select 7 union select 8 
    union select 9 union select 10) x
  cross join (
    select 1 as n union select 2 union select 3 union select 4
    union select 5 union select 6 union select 7 union select 8 
    union select 9 union select 10) y
  where a.value * x.n + b.value * y.n = 10
) z
order by r -- sorted randomly
limit 1 -- to get only one combination; remove to get them all

Каждый раз, когда вы запускаете этот запрос, он выбирает случайное [другое] решение.

Сценарий для создания таблицы и данных, которые вы упомянули (которые я использовал для тестирования):

create table my_table (
  item_id int,
  value int
);

insert into my_table (item_id, value) values (1, 1);
insert into my_table (item_id, value) values (2, 4);
insert into my_table (item_id, value) values (3, 2);
insert into my_table (item_id, value) values (4, 6);

РЕДАКТИРОВАТЬ 1 июля 2019 года. По запросу, вот эквивалентное [более короткое] решение, использующее рекурсивное CTE (выражение общей таблицы), доступное в MariaDB начиная с 10.2.2 (см. Рекурсивные выражения общих таблиц):

with recursive
val as (select 1 as n union all select n + 1 from val where n < 10)
select
  *
from (
  select
    a.item_id as item1, 
    x.n as amount1, 
    a.value * x.n as sum1,
    b.item_id as item2, 
    y.n as amount2, 
    b.value * y.n as sum2,
    rand() as r
  from my_table a
  join my_table b on b.item_id <> a.item_id
  cross join val x
  cross join val y
  where a.value * x.n + b.value * y.n = 10
) z
order by r -- sorted randomly
limit 1 -- to get only one combination; remove to get all 22 answers

Это решение масштабируется намного лучше, если вам нужно использовать более высокие числа.

Источник

Вы можете получить все такие комбинации, используя самостоятельное соединение:

select t1.item_id, t2.item_id
from t t1 join
     t t2
     on t1.value + t2.value = 10;

Это помещает значения в столбцы, а не в отдельные строки.

Источник
Другие вопросы по тегам