如何编写SQL查询以选择特定条件下的不同配对值？

Question

我在制定以下问题的查询时遇到了麻烦：

对于具有特定分数的成对值，如何以一种方式对它们进行分组，以便仅返回具有最佳各自得分的不同成对值？

例如，假设我有一个包含以下行值的表：

(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)

有没有办法只用一个查询生成这个结果？我尝试过对结果进行分组和分区的方法，但由于必须根据分数排名计算已使用的值，所以我发现这非常难以处理。

编辑：

生成数据的方法：

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;

- Stephen Tableau

---

这是一个有趣的问题 :) - borowis

如果你说我们要排除 p1，为什么结果中有 (t2, p1, 60)？ - borowis

你需要使用递归CTE来解决这个问题。 - Gordon Linoff

这是我的错误。正确的结果应该是：（t1，p1，65）（t2，p2，59） - Stephen Tableau

玩了一下，这似乎比我想象的更难。Postgres（以及大多数数据库）对于递归CTE有某些限制，这使得自然地表达这个问题变得不可能。这个问题有些东西让我怀疑不能用递归CTE来处理。这与寻找剩余最高分并从考虑中去除节点这两个不同步骤有关。 - Gordon Linoff

Answer 1

这个问题显然一直困扰着我。下面的代码似乎实现了您的逻辑，将访问过的值保留在行中的数组中：

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;

- Gordon Linoff

Answer 2

使用存储函数相对简单:

--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;

- Abelisto

---

美丽。谢谢！ - Stephen Tableau

我实际上在一个有几十万行的数据集上尝试了这个，但由于递归的原因非常慢。有什么想法可以提高这里的效率吗？ - Stephen Tableau

@StephenTableau如果没有实际数据结构、选择性、索引等知识，很难说出什么。尝试创建覆盖where和order by部分的索引（对于我的例子，它将是...on t(x,y)和...on t(z desc)）。戈登·林霍夫（Gordon Linoff）的解决方案是否更快？可能是更有效率的解决方案。 - Abelisto

Answer 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是第二列，score是第三列；

- Kacper

---

问题是你可能有相同分数的t1、p1和p1、t1元组，你不应该将它们列出两次。 - borowis

我的示例结果有误。我已更新它以排除p2。 - Stephen Tableau

@StephenTableau 在这种情况下，我的答案是不正确的。 - Kacper

Answer 4

如果第一对值和第二对值是不同的列（比如，X 和 Y），你可以按 X 进行分组，并使用 MAX(score) 作为聚合函数来获取以 X 开头的元组的最大分数。

进一步的步骤取决于你的数据，因为如果每个元组都被反转，你可能仍然会得到不需要的重复项。因此，你可能需要先进行自连接来排除这些反转的元组。

- borowis