sql - 优化使用子查询获取MIN和MAX

Question

希望找到一种更好的方法来编写此查询，因为目前我觉得性能不是很好。我在数据库方面的能力有限，例如，我无法在我正在使用的表上创建更好的索引——我只是数据的消费者。数据所在的服务器是 MS SQL Server 2008R2。

这就是上下文，问题呢？

Link to SQL Fiddle!

这是我当前的查询:

SELECT
  a.ID
  ,a.[GROUP]
  ,b.VALUE1
  ,b.VALUE2
  ,c.VALUE1
  ,c.VALUE2
FROM
  PRIMARY_TABLE a
  LEFT JOIN VALUE_TABLE b ON
    a.ID = b.ID
    AND a.[GROUP] = b.[GROUP]
    AND b.DEPTH = (
                 SELECT TOP 1 DEPTH 
                 FROM VALUE_TABLE
                 WHERE ID = a.ID AND [GROUP] = a.[GROUP]
                 ORDER BY DEPTH ASC
               )
  LEFT JOIN VALUE_TABLE c ON
    a.ID = c.ID
    AND a.[GROUP] = c.[GROUP]
    AND c.DEPTH = (
                 SELECT TOP 1 DEPTH 
                 FROM VALUE_TABLE
                 WHERE ID = a.ID AND [GROUP] = a.[GROUP]
                 ORDER BY DEPTH DESC
               )

稍微解释一下，我显然是想在 VALUE_TABLE 中获取 MIN 和 MAX DEPTHS 的关联值。 VALUE_TABLE 的大小适中，超过 100 万条记录，因此我使用 TOP 子查询来防止不必要的表扫描……我想知道我是否弊大于利!

必须进行两个左连接似乎很麻烦我目前想不出一种更干净或更快的方法...很想听听您的想法!

提前致谢。

PRIMARY 和 VALUE_TABLE 的一些示例数据:

Declare @PRIMARY_TABLE Table([GROUP] varchar(25), [ID] integer, [TYPE] varchar(25));
Insert @PRIMARY_TABLE([GROUP], [ID], [TYPE]) Values
('TEST',123456,'WIDGET')

Declare @VALUE_TABLE Table([GROUP] varchar(25), [ID] integer, [DEPTH] integer, [VALUE1] float, VALUE2 float);
Insert @VALUE_TABLE([GROUP], [ID], [DEPTH], [VALUE1], [VALUE2]) Values
('TEST',123456,2,-89.7,314),
('TEST',123456,3,-89.6,26),
('TEST',123456,4,-89.7,155),
('TEST',123456,5,-89.6,357),
('TEST',123456,6,-89.4,349),
('TEST',123456,7,-89.5,351),
('TEST',123456,8,-89.1,356),
('TEST',123456,9,-89.3,2),
('TEST',123456,10,-89.3,2),
('TEST',123456,11,-89.3,1),
('TEST',123456,12,-89.2,359),
('TEST',123456,13,-89.1,352),
('TEST',123456,14,-89.2,330),
('TEST',123456,15,-89,339),
('TEST',123456,16,-88.5,332),
('TEST',123456,17,-88.8,329),
('TEST',123456,18,-88.4,320),
('TEST',123456,19,-88.5,318),
('TEST',123456,20,-88.3,317),
('TEST',123456,21,-87.8,310),
('TEST',123456,22,-88,311),
('TEST',123456,23,-87.7,304),
('TEST',123456,24,-87.4,305),
('TEST',123456,25,-87.4,301),
('TEST',123456,26,-87.4,303),
('TEST',123456,27,-87.4,306),
('TEST',123456,28,-87.2,299),
('TEST',123456,29,-87.4,301),
('TEST',123456,30,-87.3,302),
('TEST',123456,31,-87.4,299),
('TEST',123456,32,-87.3,298),
('TEST',123456,33,-87,293),
('TEST',123456,34,-87.3,300),
('TEST',123456,35,-87.3,302),
('TEST',123456,36,-87.3,301),
('TEST',123456,37,-87.2,298),
('TEST',123456,38,-87.3,297),
('TEST',123456,39,-87.5,294),
('TEST',123456,40,-87.3,296),
('TEST',123456,41,-87.2,299),
('TEST',123456,42,-87.1,296),
('TEST',123456,43,-87.1,300),
('TEST',123456,44,-87.2,296),
('TEST',123456,45,-87.2,297),
('TEST',123456,46,-87.1,293),
('TEST',123456,47,-87,293),
('TEST',123456,48,-87,297),
('TEST',123456,49,-87.1,298),
('TEST',123456,50,-87.2,291),
('TEST',123456,51,-87.3,297),
('TEST',123456,52,-86.9,293),
('TEST',123456,53,-87.1,298),
('TEST',123456,54,-87,292),
('TEST',123456,55,-87,297),
('TEST',123456,56,-86.8,297),
('TEST',123456,57,-86.9,296),
('TEST',123456,58,-86.8,295),
('TEST',123456,59,-86.9,295),
('TEST',123456,60,-86.8,296),
('TEST',123456,61,-86.6,299),
('TEST',123456,62,-86.5,291),
('TEST',123456,63,-86.6,294),
('TEST',123456,64,-86.8,293),
('TEST',123456,65,-86.8,296),
('TEST',123456,66,-86.7,291),
('TEST',123456,67,-86.7,299),
('TEST',123456,68,-86.7,293),
('TEST',123456,69,-86.7,295),
('TEST',123456,70,-86.7,294),
('TEST',123456,71,-86.8,296),
('TEST',123456,72,-86.5,297),
('TEST',123456,73,-86.6,297),
('TEST',123456,74,-86.5,294),
('TEST',123456,75,-86.4,298),
('TEST',123456,76,-86.5,299),
('TEST',123456,77,-86.4,297),
('TEST',123456,78,-86.3,300),
('TEST',123456,79,-86.4,300),
('TEST',123456,80,-86.7,295),
('TEST',123456,81,-86.6,299),
('TEST',123456,82,-86.6,300),
('TEST',123456,83,-86.6,299),
('TEST',123456,84,-86.5,300),
('TEST',123456,85,-86.4,299),
('TEST',123456,86,-86.3,296),
('TEST',123456,87,-86.2,300),
('TEST',123456,88,-86.2,300),
('TEST',123456,89,-86.2,297),
('TEST',123456,90,-86.1,301),
('TEST',123456,91,-86.3,301),
('TEST',123456,92,-86.2,300),
('TEST',123456,93,-86,301),
('TEST',123456,94,-86.2,302),
('TEST',123456,95,-86.2,301),
('TEST',123456,96,-86.2,304),
('TEST',123456,97,-86.2,303),
('TEST',123456,98,-86,305),
('TEST',123456,99,-86.1,301),
('TEST',123456,100,-86.1,305),
('TEST',123456,101,-86.2,302),
('TEST',123456,102,-86,304),
('TEST',123456,103,-86,303),
('TEST',123456,104,-86.1,307),
('TEST',123456,105,-86.1,306),
('TEST',123456,106,-86.1,305),
('TEST',123456,107,-86.2,302),
('TEST',123456,108,-86.2,307),
('TEST',123456,109,-86.3,309),
('TEST',123456,110,-86.3,303),
('TEST',123456,111,-86.4,304),
('TEST',123456,112,-86.4,308),
('TEST',123456,113,-86.3,310),
('TEST',123456,114,-86.4,304),
('TEST',123456,115,-86.5,307),
('TEST',123456,116,-86.3,308),
('TEST',123456,117,-86.3,309),
('TEST',123456,118,-86.3,306),
('TEST',123456,119,-86.2,311),
('TEST',123456,120,-86,308),
('TEST',123456,121,-85.9,308),
('TEST',123456,122,-86.1,307),
('TEST',123456,123,-86.1,308),
('TEST',123456,124,-86,310),
('TEST',123456,125,-86.2,305),
('TEST',123456,126,-86.1,312),
('TEST',123456,127,-86.2,308),
('TEST',123456,128,-86.1,309),
('TEST',123456,129,-86.1,309),
('TEST',123456,130,-86.2,310),
('TEST',123456,131,-86.5,306),
('TEST',123456,132,-86.5,311),
('TEST',123456,133,-86.6,306),
('TEST',123456,134,-86.8,314),
('TEST',123456,135,-86.7,314),
('TEST',123456,136,-86.8,312),
('TEST',123456,137,-86.7,315),
('TEST',123456,138,-86.9,311),
('TEST',123456,139,-86.8,316),
('TEST',123456,140,-86.9,312),
('TEST',123456,141,-86.9,312),
('TEST',123456,142,-86.7,314),
('TEST',123456,143,-86.7,317),
('TEST',123456,144,-86.7,316),
('TEST',123456,145,-86.7,316),
('TEST',123456,146,-86.8,318),
('TEST',123456,147,-86.8,316),
('TEST',123456,148,-86.8,317),
('TEST',123456,149,-87,317),
('TEST',123456,150,-86.9,315),
('TEST',123456,151,-86.8,321),
('TEST',123456,152,-86.9,319),
('TEST',123456,153,-86.9,320),
('TEST',123456,154,-86.7,318),
('TEST',123456,155,-86.6,316),
('TEST',123456,156,-86.7,317),
('TEST',123456,157,-86.8,319),
('TEST',123456,158,-86.8,317),
('TEST',123456,159,-86.7,318),
('TEST',123456,160,-86.8,316),
('TEST',123456,161,-86.8,317),
('TEST',123456,162,-86.7,320),
('TEST',123456,163,-86.5,318),
('TEST',123456,164,-86.6,315),
('TEST',123456,165,-86.9,316),
('TEST',123456,166,-86.8,317),
('TEST',123456,167,-86.9,316),
('TEST',123456,168,-86.9,317),
('TEST',123456,169,-87,321),
('TEST',123456,170,-87,316),
('TEST',123456,171,-87.1,318),
('TEST',123456,172,-86.9,316),
('TEST',123456,173,-86.9,310),
('TEST',123456,174,-87,315),
('TEST',123456,175,-87,312),
('TEST',123456,176,-87.2,309),
('TEST',123456,177,-87.5,315),
('TEST',123456,178,-87.2,313),
('TEST',123456,179,-87.5,312),
('TEST',123456,180,-87.3,308),
('TEST',123456,181,-87.3,308),
('TEST',123456,182,-87.3,313)

以及预期的结果:

123456, TEST, 'WIDGET', -89.7, 314, -87.3, 313

Answer 1

尝试这样的事情......

;WITH C1 AS
 (
  SELECT VT.*
        ,ROW_NUMBER() OVER (ORDER BY VT.DEPTH ASC)  rn1
        ,ROW_NUMBER() OVER (ORDER BY VT.DEPTH DESC) rn2
  FROM VALUE_TABLE VT INNER JOIN PRIMARY_TABLE a
  ON VT.ID = a.ID AND VT.[GROUP] = a.[GROUP] 
 )
SELECT a.ID
      ,a.[GROUP]
      ,b.VALUE1
      ,b.VALUE2
      ,c.VALUE1
      ,c.VALUE2
FROM PRIMARY_TABLE a 
LEFT JOIN C1 b ON b.ID      = a.ID 
              AND b.[GROUP] = a.[GROUP]
              AND b.rn1     = 1
LEFT JOIN C1 c ON c.ID      = a.ID      
              AND c.[GROUP] = a.[GROUP]
              AND c.rn2     = 1

结果

╔════════╦═══════╦════════╦════════╦════════╦════════╗
║   ID   ║ GROUP ║ VALUE1 ║ VALUE2 ║ VALUE1 ║ VALUE2 ║
╠════════╬═══════╬════════╬════════╬════════╬════════╣
║ 123456 ║ TEST  ║ -89.7  ║    314 ║ -87.3  ║    313 ║
╚════════╩═══════╩════════╩════════╩════════╩════════╝

SQL FIDDLE