我的查询花了太长时间才能完成查找那对行的差异，其中列的差值是按另一列分组的最大值 [英] My query is taking too long to finish for finding the pair of rows where the difference of columns value is maximum group by another column

问题描述

说，我有一张这样的桌子:

Say, I've a table like this:

我想找到每个会话的性能差异最高的一对中心，如下所示:

I want to find the pair of Centers whose Performance difference is highest for each session, like this:

我有以下查询，

select 
    t1.session,
    t1.center center1,
    t2.center center2,
    t1.performance - t2.performance performance
from mytable t1
inner join mytable t2 on t1.session = t2.session
where t1.performance - t2.performance = (
    select max(t11.performance - t22.performance)
    from mytable t11
    inner join mytable t22 on t11.session = t22.session
    where t11.session = t1.session
)

它可以工作，但是花了很长时间，几分钟才花20列200行的表格. 如何修改查询以更快地获得相同的输出?

It works but took long time, few minutes for a table of 20 columns and 200 rows. How can I modify the query to achieve the same output faster?

推荐答案

select 
        t1.session,
        t1.center center1,
        t2.center center2,
        t1.performance - t2.performance performance
    from mytable t1
    inner join mytable t2 
       on t1.session = t2.session
    WHERE t1.performance = (SELECT MAX(performance) 
                            FROM mytable t3 WHERE t3.session = t1.session)
      AND t2.performance = (SELECT MIN(performance) 
                            FROM mytable t3 WHERE t3.session = t2.session)

     // Im thinking this will solve the border case when performance is a tie 
     // and difference 0 will return 2 rows

     AND (CASE WHEN t1.performance = t2.performance 
               THEN CASE WHEN t1.center < t2.center
                         THEN 1
                         ELSE 0
                    END
               ELSE 1
          END) = 1

只要您在performance和session上都有索引就可以了.

As long as you have an index on performance and session should be fine.

这篇关于我的查询花了太长时间才能完成查找那对行的差异，其中列的差值是按另一列分组的最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！