9 Replies Latest reply on Jul 13, 2016 6:42 AM by BluShadow

    Need recursive SQL code

    Raghvendra Deshpande

      Hi,

      I am using Oracle 11. I have data in following format:

      I have first two columns  and I need to get third column using SQL/Recursive SQL.

      The first two columns show mobile number and their call time to call center. I want to flag each call whether it is a repetitive call or not.

      If the call from the same number is within 48 hours of first non repeated call , then I would flag it as repeated.

      I have shown 17 rows below, in reality I have 3 millon rows to process. Could you please help.

       

      Mobile NumberCall TimeRepeat/Non Repeat
      A2016/01/01 06:15:530
      B2016/01/01 18:29:290
      B2016/01/01 18:33:451
      B2016/01/01 19:14:021
      B2016/01/01 20:28:381
      B2016/01/05 20:51:480
      B2016/01/05 20:53:191
      B2016/01/10 20:54:280
      C2016/01/01 22:45:410
      D2016/01/02 17:33:120
      E2016/01/02 00:15:080
      F2016/01/01 19:51:090
      F2016/01/01 20:06:061
      F2016/01/01 21:24:471
      F2016/01/02 09:21:081
      F2016/01/03 20:21:080
      F2016/01/05 09:21:081

       

       

      Here is script to create the above sample data:

      CREATE TABLE  TestTable
      (
      Mobile  VARCHAR(10),
      CallTime  TIMESTAMP
      )
      ;

      INSERT INTO TestTable VALUES ('A',TO_TIMESTAMP( '2016-01-01 06:15:53', 'YYYY-MM-DD HH24:MI:SS'));
      INSERT INTO TestTable VALUES ('B',TO_TIMESTAMP( '2016-01-01 18:29:29' , 'YYYY-MM-DD HH24:MI:SS'));
      INSERT INTO TestTable VALUES ('B',TO_TIMESTAMP( '2016-01-01 18:33:45'  , 'YYYY-MM-DD HH24:MI:SS'));
      INSERT INTO TestTable VALUES ('B',TO_TIMESTAMP( '2016-01-01 19:14:02' , 'YYYY-MM-DD HH24:MI:SS'));
      INSERT INTO TestTable VALUES ('B',TO_TIMESTAMP( '2016-01-01 20:28:38' , 'YYYY-MM-DD HH24:MI:SS'));
      INSERT INTO TestTable VALUES ('B',TO_TIMESTAMP( '2016-01-05 20:51:48' , 'YYYY-MM-DD HH24:MI:SS'));
      INSERT INTO TestTable VALUES ('B',TO_TIMESTAMP( '2016-01-05 20:53:19' , 'YYYY-MM-DD HH24:MI:SS'));
      INSERT INTO TestTable VALUES ('B',TO_TIMESTAMP( '2016-01-10 20:54:28' , 'YYYY-MM-DD HH24:MI:SS'));
      INSERT INTO TestTable VALUES ('C',TO_TIMESTAMP( '2016-01-01 22:45:41' , 'YYYY-MM-DD HH24:MI:SS'));
      INSERT INTO TestTable VALUES ('D',TO_TIMESTAMP( '2016-01-02 17:33:12' , 'YYYY-MM-DD HH24:MI:SS'));
      INSERT INTO TestTable VALUES ('E',TO_TIMESTAMP( '2016-01-02 00:15:08' , 'YYYY-MM-DD HH24:MI:SS'));
      INSERT INTO TestTable VALUES ('F',TO_TIMESTAMP( '2016-01-01 19:51:09' , 'YYYY-MM-DD HH24:MI:SS'));
      INSERT INTO TestTable VALUES ('F',TO_TIMESTAMP( '2016-01-01 20:06:06' , 'YYYY-MM-DD HH24:MI:SS'));
      INSERT INTO TestTable VALUES ('F',TO_TIMESTAMP( '2016-01-01 21:24:47' , 'YYYY-MM-DD HH24:MI:SS'));
      INSERT INTO TestTable VALUES ('F',TO_TIMESTAMP( '2016-01-02 09:21:08' , 'YYYY-MM-DD HH24:MI:SS'));
      INSERT INTO TestTable VALUES ('F',TO_TIMESTAMP( '2016-01-03 20:21:08' , 'YYYY-MM-DD HH24:MI:SS'));
      INSERT INTO TestTable VALUES ('F',TO_TIMESTAMP( '2016-01-05 09:21:08' , 'YYYY-MM-DD HH24:MI:SS'));

        • 1. Re: Need recursive SQL code
          BluShadow

          This perhaps?

           

          SQL> ed
          Wrote file afiedt.buf

            1  select mobile
            2        ,calltime
            3        ,case when calltime-lag(calltime) over (partition by mobile order by calltime) <= interval '2' day then 1 else 0 end as repeat
            4  from testtable
            5* order by mobile, calltime
          SQL> /

          MOBILE     CALLTIME                                                                        REPEAT
          ---------- --------------------------------------------------------------------------- ----------
          A          01-JAN-16 06.15.53.000000                                                            0
          B          01-JAN-16 18.29.29.000000                                                            0
          B          01-JAN-16 18.33.45.000000                                                            1
          B          01-JAN-16 19.14.02.000000                                                            1
          B          01-JAN-16 20.28.38.000000                                                            1
          B          05-JAN-16 20.51.48.000000                                                            0
          B          05-JAN-16 20.53.19.000000                                                            1
          B          10-JAN-16 20.54.28.000000                                                            0
          C          01-JAN-16 22.45.41.000000                                                            0
          D          02-JAN-16 17.33.12.000000                                                            0
          E          02-JAN-16 00.15.08.000000                                                            0
          F          01-JAN-16 19.51.09.000000                                                            0
          F          01-JAN-16 20.06.06.000000                                                            1
          F          01-JAN-16 21.24.47.000000                                                            1
          F          02-JAN-16 09.21.08.000000                                                            1
          F          03-JAN-16 20.21.08.000000                                                            1
          F          05-JAN-16 09.21.08.000000                                                            1

           

          17 rows selected.

           

          Not sure why you had a 0 for F on 3rd Jan, as it's within 48 hours of 2nd Jan.

          • 2. Re: Need recursive SQL code
            Paulzip

            Yes, I came up with the same approach, then just before posting realised the recursive element is in the fact if a non start call time is not within 48 hours, repeat becomes 0 and following call times are compared to that.  So you could end up with scenarios where repeat becomes 0 and that affects latter items, which in turn could be 0 and affect later ones.  It's a rippling which you wouldn't see in the OP's simple data example.

            • 3. Re: Need recursive SQL code
              BluShadow

              Paulzip wrote:

               

              Yes, I came up with the same approach, then just before posting realised the recursive element is in the fact if a non start call time is not within 48 hours, repeat becomes 0 and following call times are compared to that.  So you could end up with scenarios where repeat becomes 0 and that affects latter items, which in turn could be 0 and affect later ones.  It's a rippling which you wouldn't see in the OP's simple data example.

               

              I'll take your word for it. 

               

              The OP needs to show what they've tried or why they consider it recursive.

              • 4. Re: Need recursive SQL code
                odie_63
                with tmp (mobile, calltime, id) as (
                  select mobile, calltime, row_number() over(partition by mobile order by calltime)
                  from testtable
                ),
                getrows (mobile, calltime, id, is_repeat, last_nr_calltime) as (
                  select mobile, calltime, id, 0, calltime
                  from tmp
                  where id = 1
                  union all
                  select r.mobile
                       , t.calltime
                       , t.id
                       , case when t.calltime - r.last_nr_calltime <= interval '48' hour then 1 else 0 end
                       , case when t.calltime - r.last_nr_calltime <= interval '48' hour then r.last_nr_calltime else t.calltime end
                  from getrows r
                       join tmp t on t.mobile = r.mobile and t.id = r.id + 1
                )
                search depth first by id set order1
                select mobile, calltime, is_repeat
                from getrows;
                
                

                 

                Performance must be horrible on million of rows though...

                 

                The same approach is possible using MODEL.

                • 5. Re: Need recursive SQL code
                  James Su

                  some more interesting solutions:

                   

                  MODEL:

                  select mobile,calltime,case when last_non_repeat=calltime then 0 else 1 end as repeat

                  from (select mobile, calltime, row_number() over(partition by mobile order by calltime) rn from testtable)

                  model ignore nav

                    partition by (mobile)

                    dimension by (rn)

                    measures (calltime,calltime last_non_repeat)

                    rules (

                     last_non_repeat[any] order by rn = case

                                                        when cv(rn)=1 then calltime[cv()]

                                                        when last_non_repeat[cv()-1] + interval '48' hour > calltime[cv()]

                                                        then last_non_repeat[cv()-1]

                                                        else calltime[cv()]

                                                        end

                     )

                  ;

                   

                   

                   

                   

                  12C:

                  select mobile,calltime,decode(flag,'A',0,1) as repeat

                  from testtable

                  match_recognize (

                       partition by mobile

                       order by calltime

                       measures classifier() as flag

                       all rows per match

                       pattern ((A|B)+)

                       define

                          A as calltime>last(A.calltime,1)+interval '48' hour or prev(calltime) is null

                       )

                  ;

                   

                   

                   

                   

                  CONNECT BY:

                  with d as (

                  select mobile,calltime

                    from (select mobile, calltime

                                ,row_number() over(partition by mobile order by calltime) rn

                                ,count(*) over(partition by mobile order by calltime range between current row and interval '48' hour following) cnt

                           from testtable

                         )

                    start with rn = 1

                  connect by mobile=prior mobile and rn = prior cnt + prior rn

                  )

                  select t.mobile,t.calltime

                        ,case when d.mobile is null then 1 else 0 end as repeat

                    from testtable t left join d on t.mobile = d.mobile and t.calltime = d.calltime

                  order by 1,2

                  ;

                  • 6. Re: Need recursive SQL code
                    Frank Kulash

                    Hi,

                     

                    Raghvendra Deshpande wrote:

                     

                    Hi,

                    I am using Oracle 11. ...

                    Too bad!  Starting in Oracle 12.1, you could use MATCH_RECOGNIZE, like this:

                    SELECT    mobile, calltime

                    ,        CASE  ptrn_elmnt

                                  WHEN  'STARTER'  THEN  0

                                                  ELSE  1

                              END    AS repeat

                    FROM      testtable

                    MATCH_RECOGNIZE (

                                        PARTITION BY  mobile

                                        ORDER BY      calltime

                                        MEASURES      CLASSIFIER ()  AS ptrn_elmnt

                                        ALL ROWS PER MATCH

                                        PATTERN  ( starter

                                                   follower*

                                                )

                                        DEFINE  follower  AS follower.calltime < starter.calltime + INTERVAL '48' HOUR

                                    )

                    ORDER BY  mobile, calltime

                    ;

                    Output:

                    MOBILE    CALLTIME                        REPEAT

                    ---------- ------------------------------ --------

                    A          01-JAN-16 06.15.53.000000 AM          0

                    B          01-JAN-16 06.29.29.000000 PM          0

                    B          01-JAN-16 06.33.45.000000 PM          1

                    B          01-JAN-16 07.14.02.000000 PM          1

                    B          01-JAN-16 08.28.38.000000 PM          1

                    B          05-JAN-16 08.51.48.000000 PM          0

                    B          05-JAN-16 08.53.19.000000 PM          1

                    B          10-JAN-16 08.54.28.000000 PM          0

                    C          01-JAN-16 10.45.41.000000 PM          0

                    D          02-JAN-16 05.33.12.000000 PM          0

                    E          02-JAN-16 12.15.08.000000 AM          0

                    F          01-JAN-16 07.51.09.000000 PM          0

                    F          01-JAN-16 08.06.06.000000 PM          1

                    F          01-JAN-16 09.24.47.000000 PM          1

                    F          02-JAN-16 09.21.08.000000 AM          1

                    F          03-JAN-16 08.21.08.000000 PM          0

                    F          05-JAN-16 09.21.08.000000 AM          1

                    By the way, are you using fractions of a second in your real data?  If not, consider making calltime a DATE rather than a TIMESTAMP.  There are more functions available for manipulating DATEs.

                    • 7. Re: Need recursive SQL code

                      BluShadow wrote:

                      Not sure why you had a 0 for F on 3rd Jan, as it's within 48 hours of 2nd Jan.

                      Do you see it after reading PaulZip's explanation?

                       

                      The 0 for F on 3rd Jan is because it is NOT withing 48 hours of the previous 0 which is for 2016/01/01 19:51:09

                       

                      The repeated calls are ignored as if they don't exist.

                      • 8. Re: Need recursive SQL code
                        BluShadow

                        Yeah, I see it now, but it wasn't clear from the original question.