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

Ответ 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.

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