SQL Server 2008 R2 - 群岛和差距

I have a simple 2 column table. PctGain contains weekly percentage stock market gains. WSeqkey contains a contiguous integer value that increments each new week. There are approximately 3300 rows in the above table. Here is a sample.

我有一个简单的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

What would like help solving, and learning how to do along the way is...write/run a query that will tell me when the positive and negative sequences begin and end. Something like

想要帮助解决,并学习如何做的是...写/运行一个查询,告诉我什么时候正序和负序开始和结束。就像是

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

Thank you in advance for solving this and helping me learn along the way.

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

Frank

2 个解决方案

#1

Something like this should do the job SQL Fiddle

像这样的东西应该做SQL Fiddle的工作

It finds islands of sequential data with the same value for SIGN and alllocates them the same grouping value using Itzik Ben Gan's row number technique then groups them and aggregates them. The CROSS APPLY ... VALUES unpivots the MIN and MAX

它找到具有相同SIGN值的顺序数据岛,并使用Itzik Ben Gan的行号技术将它们分配相同的分组值,然后将它们分组并聚合它们。 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

#2

Thanks everyone I looked at the gaps/islands URL on MSDN and figured it out.

谢谢大家,我查看了MSDN上的gap / islands网址,并想出来了。

I dumped just the WSEQKEYs into a temp table (#gaps) while filtering on pctgain > 0 and then used the following sql:

我只是将WSEQKEYs转换为临时表(#gaps),同时在pctgain> 0上过滤,然后使用以下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

#1