Re: Tough SQL Problem

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Wed, 08 Feb 2006 19:23:20 -0700
Message-Id: <6.2.3.4.2.20060208191838.042ddab8@pop.centrexcc.com>

Looks extremely ugly and I have no idea how it performs on something with more than 30 rows, but it appears to give the correct answer, provided I coorectly understood what the question was:

SQL> select * from ethan order by seq

   2 /

PROCEDURE_ID SEQ
------------ ----------

            1          1
            2          2
            1          3
            1          4
            1          5
            1          6
            1          7
            2          8
            1          9
            1         10
            2         11
            1         12
            1         13
            2         14
            2         15
            2         16
            1         17
            1         18
            2         19
            2         20
            1         21
            1         22
            1         23
            2         24
            2         25
            1         26
            3         27
            3         28
            3         29
            3         30

30 rows selected.

SQL> select PROCEDURE_ID, max(cnt) "consecutive calls"

   2 from (
   3 select PROCEDURE_ID, c, count(0) cnt    4 from (
   5 select PROCEDURE_ID
   6 , row_number() over ( PARTITION BY PROCEDURE_ID ORDER BY consecutive )    7 - dense_rank() over ( PARTITION BY PROCEDURE_ID ORDER BY consecutive ) c    8 from (
   9 SELECT PROCEDURE_ID, seq
  10 , count(CASE WHEN /* seq is null or */ seq = prev_seq+1 THEN 1 ELSE NULL END )
  11 OVER ( PARTITION BY PROCEDURE_ID ORDER BY seq RANGE UNBOUNDED PRECEDING ) consecutive
  12 from (
  13 SELECT PROCEDURE_ID, seq
  14 , LAG (seq) OVER ( PARTITION BY PROCEDURE_ID ORDER BY seq) prev_seq   15 from ethan

  16  )
  17  )
  18  ) group by PROCEDURE_ID, c
  19  ) group by PROCEDURE_ID

  20 /

PROCEDURE_ID consecutive calls

------------ -----------------
            1                 5
            2                 3
            3                 4

3 rows selected.

Ethan, will you be in Dallas again next month?

At 10:46 AM 2/8/2006, Ethan Post wrote:
>I have a log table that is logging various PLSQL calls. Assume the
>table looks like this...
>
>
>Looking at this I can see that the max times 1 was called
>consecutivley is 5. I have no clue how I would do this in SQL without
>writing a function of some sort to keep a count and return the max
>count. Anyone know if this can be done using and built in functions?

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Feb 08 2006 - 20:23:20 CST