counting the number of consecutive number in multiple rows.

[kyle]

Hi,

I want to count the number of consecutive int value spread through out multiple rows. For example.

Code: Select all

=> select * from kyle order by value;
 value
-------
     1
     2
     5
     6
     7
    10
    12
    13
(8 rows)

In the table, I want to count the number of consecutive value tuple
- (1,2) (5,6,7) (12,13)
with the final output as the number of consecutive int in a tuple - (1,2) and (12,13) has two int. (5,6,7) has 3 int.

Code: Select all

# of consecutive int | count
2                             | 2
3                             | 1

Can I do this without using UDF? thx.

[JimKnicely]

Here's a query that might help ...

Code: Select all

SELECT p2 as "# of consecutive int", count(p2)
  FROM (SELECT COUNT(p1) p2
          FROM (SELECT (CASE
                          WHEN a_group = 0 AND LEAD(a_group) OVER (ORDER BY value) = 1 THEN
                            conditional_change_event(a_group) OVER (ORDER BY value) + 1
                          ELSE conditional_change_event(a_group) OVER (ORDER BY value)
                        END) p1
                  FROM (SELECT value, DECODE(value - LAG(value) OVER (ORDER BY value), 1, 1, 0) a_group
                          FROM kyle) foo) foo
         WHERE p1 > 0
         GROUP BY p1
        HAVING COUNT(p1) > 1) foo
 GROUP BY p2
 ORDER BY p2;

Example:

Code: Select all

dbadmin=> SELECT * FROM kyle ORDER BY 1;
 value
-------
     1
     2
     5
     6
     7
    10
    12
    13
(8 rows)

dbadmin=> SELECT p2 as "# of consecutive int"
dbadmin->      , count(p2)
dbadmin->   FROM (SELECT COUNT(p1) p2
dbadmin(>           FROM (SELECT (CASE
dbadmin(>                           WHEN a_group = 0 AND LEAD(a_group) OVER (ORDER BY value) = 1 THEN
dbadmin(>                             conditional_change_event(a_group) OVER (ORDER BY value) + 1
dbadmin(>                           ELSE conditional_change_event(a_group) OVER (ORDER BY value)
dbadmin(>                         END) p1
dbadmin(>                   FROM (SELECT value
dbadmin(>                              , DECODE(value - LAG(value) OVER (ORDER BY value), 1, 1, 0) a_group
dbadmin(>                           FROM kyle) foo) foo
dbadmin(>          WHERE p1 > 0
dbadmin(>          GROUP BY p1
dbadmin(>         HAVING COUNT(p1) > 1) foo
dbadmin->  GROUP BY p2
dbadmin->  ORDER BY p2;
 # of consecutive int | count
----------------------+-------
                    2 |     2
                    3 |     1
(2 rows)

[id10t]

Hi!

[DELETED]

[kyle]

Hi sKwa and knicely87,

Both suggestions work like charm Thank you very much!!