查找SQL中连续递增数字的最长序列

问题描述：

对于本示例，假设我有一个包含两个字段的表，即AREA varchar(30)和OrderNumber INT。查找SQL中连续递增数字的最长序列

该表具有如下的数据

AREA  | OrderNumber 
Fontana |  32 
Fontana |  42 
Fontana |  76 
Fontana |  12 
Fontana |  3 
Fontana |  99 
RC  |  32 
RC  |  1 
RC  |  8 
RC  |  9 
RC  |  4

我想返回

我想返回的结果是为每个区域增加连续值的最长长度。对于Fontana it is 3 (32, 42, 76)。 For RC it is 2 (8,9)

AREA | LongestLength 
Fontana |   3 
RC  |   2

我该怎么做MS Sql 2005？

增加什么时候点什么？ SQL中没有行的固有顺序，您向我们显示的两列中都没有适合的候选项。 – 2013-03-12 18:08:31

对不起，我忘了添加eventtime列，这是一个日期时间，这将是我们的订单。如果我通过AREA订购OrderNumber，那么产量会如此。然后，我想找到连续增加OrderNumbers – 2013-03-12 18:12:30

@Pat里克艾伦最长的块 - 请根据您的上述澄清更新您的文章。 – 2013-03-12 18:23:31

答

一种方法是使用跨每行的递归CTE。如果该行符合条件（相同区域的递增顺序号），则将链长增加1。如果没有，你开始一个新的链：

; with numbered as 
     (
     select row_number() over (order by area, eventtime) rn 
     ,  * 
     from Table1 
     ) 
,  recurse as 
     (
     select rn 
     ,  area 
     ,  OrderNumber 
     ,  1 as ChainLength 
     from numbered 
     where rn = 1 
     union all 
     select cur.rn 
     ,  cur.area 
     ,  cur.OrderNumber 
     ,  case 
       when cur.area = prev.area 
        and cur.OrderNumber > prev.OrderNumber 
        then prev.ChainLength + 1 
       else 1 
       end 
     from recurse prev 
     join numbered cur 
     on  prev.rn + 1 = cur.rn 
     ) 
select area 
,  max(ChainLength) 
from recurse 
group by 
     area

Live example at SQL Fiddle.

的另一种方法是使用查询来查找“休息”，也就是说，为了结束越来越多的顺序排为同一地区。断点之间的行数是长度。

; with numbered as 
     (
     select row_number() over (order by area, eventtime) rn 
     ,  * 
     from Table1 t1 
     ) 
     -- Select rows that break an increasing chain 
,  breaks as 
     (
     select row_number() over (order by cur.rn) rn2 
     ,  cur.rn 
     ,  cur.Area 
     from numbered cur 
     left join 
       numbered prev 
     on  cur.rn = prev.rn + 1 
     where cur.OrderNumber <= prev.OrderNumber 
       or cur.Area <> prev.Area 
       or prev.Area is null 
     ) 
     -- Add a final break after the last row 
,  breaks2 as 
     (
     select * 
     from breaks 
     union all 
     select count(*) + 1 
     ,  max(rn) + 1 
     ,  null 
     from breaks 
     ) 
select series_start.area 
,  max(series_end.rn - series_start.rn) 
from breaks2 series_start 
join breaks2 series_end 
on  series_end.rn2 = series_start.rn2 + 1 
group by 
     series_start.area

Live example at SQL Fiddle.

这工作！非常感谢。 – 2013-03-12 19:27:30

我正在尝试在真实数据上实现第二个解决方案，但真正的数据有很多行，然后出现错误“在语句完成之前，最大递归100已耗尽”。 *我打算尝试第一个解决方案，但我的真实数据实际上包含这些行Eventtime，PublisherID，SubID，CampaignID，Userip，Userip是原始示例中的订单号。如何编辑第一个解决方案以添加更多“区域”字段。 – 2013-03-12 19:48:39

在查询的末尾添加'option（maxrecursion 0）' – Andomar 2013-03-12 19:50:43

答

您可以通过ROW_NUMBER()做一些数学来找出你有连续的项。

下面的代码示例：

;WITH rownums AS 
(
    SELECT [area], 
     ROW_NUMBER() OVER(PARTITION BY [area] ORDER BY [ordernumber]) AS rid1, 
     ROW_NUMBER() OVER(PARTITION BY [area] ORDER BY [eventtime]) AS rid2 
    FROM SomeTable 
), 
    differences AS 
(
    SELECT [area], 
     [calc] = rid1 - rid2 
    FROM rownums 
), 
    summation AS 
(
    SELECT [area], [calc], COUNT(*) AS lengths 
    FROM differences 
    GROUP BY [area], [calc] 
) 
SELECT [area], MAX(lengths) AS LongestLength 
FROM differences 
JOIN summation 
    ON differences.[calc] = summation.[calc] 
    AND differences.area = calc.area 
GROUP BY [area]

所以，如果我做一组由我的订单号码和我的事件时另一套行号的命令行号，这两个数字之间的差异将永远是同样，只要他们的订单是一样的。

然后，您可以得到一个按这些差异分组的计数，然后拉出最大的数量来获得您所需要的数量。

编辑：... 忽略第一次编辑，我得到冲。

答

你不解释为什么RC的最长序列不包括1，而Fontana的包括32.我认为，1被排除，因为它是一个减少：它在32之后。然而，Fontana的32是第一个这个小组中有一件事，我有两个想法如何解释为什么它被认为是增加。这要么是因为它是该组的第一个项目，或者因为它是也是正数（即好像在0之后，因此增加）。

为了这个答案的目的，我假设后者，即一组的第一项是增加，如果它是积极的。下面的脚本实现了以下想法：

枚举行每AREA组在eventtime列，你几乎忘了提及的顺序。
将枚举集合加入其自身，以将每一行与其前任进行链接。
获得该行与其上一个值之间的差异（将后者默认为0）的符号。此时问题变成了gaps-and-islands之一。
按照＃3中确定的符号对每个AREA组进行分区并枚举每个子组的行。
找到＃1与＃4中找到的行号之间的区别。这将是识别单个条纹的标准（连同AREA）。
最后，将结果分组为AREA，来自＃3的符号和来自＃5的结果对行进行计数并获得每AREA的最大计数。

我实现了上面这样的：

一个SQL小提琴演示可以发现here。

查找SQL中连续递增数字的最长序列

相关推荐