Geek asks and answers

Find the longest sequence of consecutive increasing numbers in SQL

In this example, they say that I have a table with two fields, AREA varchar(30)and OrderNumber INT.

The table has the following data

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

I would like to return

The results that I would like to return are, for each region, the largest length of increasing sequential values. For Fontana it is 3 (32, 42, 76).For RC it is 2 (8,9)

AREA    | LongestLength
Fontana |          3
RC      |          2

How do I do this on MS Sql 2005?

+5
source share
3 answers

One way is to use a recursive CTE that goes through each line. If the line meets the criteria (increasing the order number for the same area), you increase the chain length by one. If this is not the case, you start a new chain:

; 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 in 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

SQL Fiddle.

+8

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]

, , , , .

, , , , .

:... , .

0

, RC 1, Fontana 32. , 1 , : 32. Fontana 32, , , , , . , , , ( , 0 , , ).

, .. - , . script :

  • AREA eventtime, .

  • , .

  • ( 0). gaps-and-islands .

  • AREA , β„– 3, .

  • # 1 # 4. ( AREA).

  • , AREA, # 3 # 5, AREA.

:

WITH enumerated AS (
  SELECT
    *,
    row = ROW_NUMBER() OVER (PARTITION BY AREA ORDER BY eventtime)
  FROM atable
),
signed AS (
  SELECT
    this.eventtime,
    this.AREA,
    this.row,
    sgn = SIGN(this.OrderNumber - COALESCE(last.OrderNumber, 0))
  FROM      enumerated AS this
  LEFT JOIN enumerated AS last
    ON this.AREA = last.AREA
   AND this.row  = last.row + 1
),
partitioned AS (
  SELECT
    AREA,
    sgn,
    grp = row - ROW_NUMBER() OVER (PARTITION BY AREA, sgn ORDER BY eventtime)
  FROM signed
)
SELECT DISTINCT
  AREA,
  LongestIncSeq = MAX(COUNT(*)) OVER (PARTITION BY AREA)
FROM partitioned
WHERE sgn = 1
GROUP BY
  AREA,
  grp
;

SQL Fiddle .

0

More articles:


All Articles