sql - 如何编写为特定条件选择不同对值的 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;

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;