说，我有一张这样的桌子：
我想找到每个会话的性能差异最高的一对中心，如下所示：
我有以下查询，
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行的表格。 如何修改查询以更快地获得相同的输出？
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上都有索引就可以了。
使用row_number()：
select session,center1,center2,performance
from (select t1.center as center1,t2.center as center2,(t1.performance - t2.performance) as performance,row_number() over (partition by t1.session order by (t1.performance - t2.performance) desc) as seqnum
from mytable t1 join
mytable t2
on t1.session = t2.session
where seqnum = 1;
或者为了获得更好的性能。最大差异是最大值减去最小值。您需要中心，这是没有子查询的方法：
select session,max(case when seqnum_desc = 1 then center end) as center1,max(case when seqnum_asc = 1 then center end) as center2,max(performance) - min(performance)
from (select t.*,row_number() over (partition by session order by performance) as seqnum_asc,row_number() over (partition by session order by performance desc) as seqnum_desc
from mytable t
where 1 in (seqnum_asc,seqnum_desc)
group by session
按会话分组并获取组的最低和最高性能似乎合乎逻辑。 不幸的是，实际的中心需要在此处进行子查询/连接。
select g.session as Session,(select min(center) from mytable
where session = g.session and performance = g.maxim) as Center1,(select min(center) from mytable
where session = g.session and performance = g.minim) as Center2,g.maxim - g.minim as Performance
from (select
t1.session,min(t1.performance) as minim,max(t1.performance) as maxim
from mytable t1
group by t1.session)
as g
确保会话和性能的索引。
,select distinct(session) * from (
select t1.session,t1.center,t2.center,(case when t1.performance > t2.performance then (t1.performance-t2.performance) else (t2.performance-t1.performance))as performance_diff
from mytable t1,mytable t2
where t1.session=t2.session and t1.center!=t2.center) as T1 order by session,performance_diff desc limit 1;