SQL Server 2008 R2-孤岛和空白 [英] SQL Server 2008 R2 - Islands and Gaps

问题描述

我有一个简单的2列表. PctGain包含每周股市收益百分比. WSeqkey包含一个连续的整数值，该整数值每隔一周增加一次.上表中大约有3300行.这是一个示例.

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.

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.

坦率

推荐答案

类似的东西应该可以完成

Something like this should do the job SQL Fiddle

它为SIGN查找具有相同值的顺序数据岛，并使用Itzik Ben Gan的行号技术将它们分配给相同的分组值，然后对其进行分组和汇总. CROSS APPLY ... VALUES取消显示MIN和MAX

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

;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 

这篇关于SQL Server 2008 R2-孤岛和空白的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！