Как написать SQL-запрос, который выбирает отдельные значения пар для определенных критериев?

У меня возникли проблемы с формулировкой запроса для следующей проблемы:

Для значений пары, которые имеют определенный балл, как вы группируете их таким образом, чтобы возвращать только отдельные значения пары с лучшими соответствующими баллами?

Например, скажем, у меня есть таблица со следующими значениями строк:

(t1,p1,65)
(t1,p2,60)
(t1,p3,20)
(t2,p1,60)
(t2,p2,59)
(t2,p3,15)

Первые два столбца указывают значения пары, а третий столбец представляет парную оценку. Лучший результат (t1,p1,65). Поскольку теперь используются t1 и p1, я хочу исключить их из дальнейшего анализа.

Следующий лучший результат - (t2,p2,59). Даже если (t1,p2) имеет оценку 60, я хочу исключить его, потому что "t1" уже используется. (t2,p1) также имеет оценку 60, но так как p1 также уже используется, эта пара исключается.

Это приводит к отдельным значениям парной пары:

(t1,p1,65)
(t2,p2,59)

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

EDIT:

Чтобы сгенерировать данные:

with t(t, p, score) as (
    (values ('t1','p1',65),
           ('t1','p2',60),
           ('t1','p3',20),
           ('t2','p1',60),
           ('t2','p2',59),
           ('t2','p3',15)
     ))
select t.* from t;

Ответ 1

Это относительно просто с использованием сохраненной функции:

--drop function if exists f();
--drop table if exists t;
create table t(x text,y text, z int);
insert into t values
  ('t1','p1',65),
  ('t1','p2',60),
  ('t1','p3',20),
  ('t2','p1',60),
  ('t2','p2',59),
  ('t2','p3',15)/*,
  ('t3','p1',20),
  ('t3','p2',60),
  ('t3','p3',40)*/;

create function f() returns setof t immutable language plpgsql as $$
declare
  ax text[];
  ay text[];
  r t;
begin
  ax := '{}'; ay := '{}';
  loop
    select * into r
      from t
      where x <> all(ax) and y <> all(ay)
      order by z desc, x, y limit 1;
    exit when not found;
    ax := ax || r.x; ay := ay || r.y;
    return next r;
  end loop;
end $$;

select * from f();
╔════╤════╤════╗
║ x  │ y  │ z  ║
╠════╪════╪════╣
║ t1 │ p1 │ 65 ║
║ t2 │ p2 │ 59 ║
╚════╧════╧════╝

Однако, если раскомментировать третью связку значений, результат будет другим:

╔════╤════╤════╗
║ x  │ y  │ z  ║
╠════╪════╪════╣
║ t1 │ p1 │ 65 ║
║ t3 │ p2 │ 60 ║
║ t2 │ p3 │ 15 ║
╚════╧════╧════╝

Обновление: и эквивалент с использованием рекурсивного CTE по тем же тестовым данным:

with recursive r as (
  (select x, y, z, array[x] as ax, array[y] as ay from t order by z desc, x, y limit 1)
  union all
  (select t.x, t.y, t.z, r.ax || t.x, r.ay || t.y from t, r 
  where not (t.x = any(r.ax) or t.y = any(r.ay)) 
  order by t.z desc, t.x, t.y limit 1))
select * from r;

Ответ 2

Эта проблема, очевидно, меня беспокоила. Ниже приведена реализация вашей логики, содержащая массивы посещенных значений в строках:

with recursive t(t, p, score) as (
    (values ('t1','p1',65),
           ('t1','p2',60),
           ('t1','p3',20),
           ('t2','p1',60),
           ('t2','p2',59),
           ('t2','p3',15)
     )),
     cte(t, p, score, cnt, lastt, lastp, ts, ps) as (
        (select t.*, count(*) over ()::int, tt.t, tt.p, ARRAY[tt.t], ARRAY[tt.p]
         from t cross join
              (select t.* from t order by score desc limit 1) tt
        ) 
        union all
        select t, p, score, 
               sum(case when not (ts @> ARRAY[t] or ps @> ARRAY[p]) then 1 else 0 end) over ()::int,
               first_value(t) over (order by case when not (ts @> ARRAY[t] or ps @> ARRAY[p]) then score end desc nulls last),
               first_value(p) over (order by case when not (ts @> ARRAY[t] or ps @> ARRAY[p]) then score end desc nulls last),
               ts || first_value(t) over (order by case when not (ts @> ARRAY[t] or ps @> ARRAY[p]) then score end desc nulls last),
               ps || first_value(p) over (order by case when not (ts @> ARRAY[t] or ps @> ARRAY[p]) then score end desc nulls last)
        from cte 
        where cnt > 0
       )
 select *
 from cte
 where lastt = t and lastp = p and cnt > 0;

Ответ 3

t1 использовался, поэтому вы исключили (t1, p2), но p1 также использовался, и вы его не исключили. Для меня это похоже на просто группировку по первому столбцу.

select t1.c1, t2.c2, t1.s 
  from table1 t2 
  inner join (select c1, max(score) s from table1 group by t1) t1 
    on (t1.s=t2.score and t1.c1=t2.c1);

Где table1 - имя для вашей таблицы, а c1 - это сначала, c2 second и score третий столбец;

Ответ 4

Если значение первой пары и второе значение пары - разные столбцы (например, X и Y), вы можете группировать по X и делать MAX (оценка) как функцию агрегации, чтобы получить максимальный балл для кортежей, начиная с X.

Дальнейшие действия зависят от ваших данных, потому что вы все равно можете получить нежелательные дубликаты, если каждый кортеж будет отменен. Таким образом, чтобы исключить такие обратные кортежи, вы можете сначала выполнить самоподключение.