SQL Server 2008 R2-孤岛和差距

Question

我有一个简单的2列表。PctGain包含每周股市涨幅的百分比。WSeqkey包含一个连续的整数值，该整数值每隔一周递增一次。上表中大约有3300行。这是一个样品。

PctGain WSeqKey
0.12%   4407
0.31%   4406
0.68%   4405
1.14%   4404
0.95%   4403
0.38%   4402
4.57%   4401
-1.94%  4400
1.17%   4399
-0.32%  4398

想要帮助解决问题并学习如何解决问题的方法是…编写/运行一个查询，告诉我正序列和负序列何时开始和结束。类似的东西

Negative Beg 4398
Negative End 4398
Positive Beg 4399
Positive End 4399
Negative Beg 4400
Negative End 4400
Positive Beg 4401
Positive End 4407

提前感谢你解决了这个问题，并帮助我一路学习。

Frank

Answer 1

像这样的东西应该可以完成SQL Fiddle 的工作

它使用Itzik-Ben-Gan的行号技术找到具有相同SIGN值的序列数据岛，并将它们全部定位为相同的分组值，然后对它们进行分组和聚合。CROSS APPLY ... VALUES对MIN和MAX 进行解锁

;WITH T1
     AS (SELECT *,
                ROW_NUMBER() OVER (PARTITION BY SIGN(PctGain) 
                                       ORDER BY WSeqKey) - WSeqKey AS Grp
         FROM   YourTable),
     T2
     AS (SELECT MIN(WSeqKey)  AS BeginSeq,
                MAX(WSeqKey)  AS EndSeq,
                SIGN(PctGain) AS Sign
         FROM   T1
         GROUP  BY Grp,
                   SIGN(PctGain))
SELECT CASE Sign
         WHEN -1 THEN 'Negative'
         WHEN 0 THEN 'Equal'
         WHEN 1 THEN 'Positive'
       END AS [Sign],
       Descriptor,
       SeqKey
FROM   T2
       CROSS APPLY (VALUES('Begin', BeginSeq),
                          ('End',   EndSeq)) V(Descriptor, SeqKey)
ORDER  BY SeqKey 

Answer 2

感谢大家，我查看了MSDN上的gaps/lislands URL并找到了答案。

在pctgain>0上进行筛选时，我只将WSEQKEY转储到一个临时表（#gaps）中，然后使用以下sql:

SELECT t1.gapID as startOfGroup, MIN(t2.gapID) 
as endOfGroup FROM (SELECT gapID FROM #gaps tbl1 
  WHERE NOT EXISTS(SELECT * FROM #gaps tbl2 
    WHERE tbl1.gapID - tbl2.gapID = 1)) t1
  INNER JOIN (SELECT gapID FROM #gaps tbl1 
  WHERE NOT EXISTS(SELECT * FROM #gaps tbl2 
    WHERE tbl2.gapID - tbl1.gapID = 1)) t2
  ON t1.gapID <= t2.gapID   GROUP BY t1.gapID
  order by t1.gapID desc