1 2 Previous Next 17 Replies Latest reply: Aug 16, 2010 3:47 PM by 783956 RSS

    Tabibitosan method tutorial by Aketi Jyuuzou

    Aketi Jyuuzou
      This thread explains Tabibitosan method B-)

      *****************************************************************
      1. What is Tabibitosan ? ?:|

      "Tabibitosan" is Japanese language.
      "Tabibitosan" is one of math problem.

      http://www.manabinoba.com/index.cfm/7,757,33,1,html
      http://yslibrary.cool.ne.jp/sansub1801.html

      I do not know what "Tabibitosan" is called in English.
      If you know, please teach me.

      In Japanese -> English dictionary,
      "Tabibito" means "pilgrim","viator" or "wayfaring man".

      Japanese "Tabibitosan" book
      http://www.amazon.co.jp/dp/4895243702

      *****************************************************************
      2. What is basic usage of "Tabibitosan method" ? ?:|

      "Tabibitosan method" is very effective when we want to collect Rows which are continued a lot of condition.

      **************************
      For exsample1 (collect Rows continued Number)
      create table Ex1 (NumVal primary key) as
      select  1 from dual union
      select  2 from dual union
      select  3 from dual union
      select  5 from dual union
      select  6 from dual union
      select  7 from dual union
      select 10 from dual union
      select 11 from dual union
      select 12 from dual union
      select 20 from dual union
      select 21 from dual;
      I will derive resultSet which collects Rows continued NumVal.
      select min(NumVal),max(NumVal),count(*)
      from (select NumVal,
            NumVal-Row_Number() over(order by NumVal)
            as disTance
            from Ex1)
      group by disTance
      order by min(NumVal);
      
      MIN(NUMVAL) MAX(NUMVAL)   COUNT(*)
      ----------- ----------- ----------
                1           3          3
                5           7          3
               10          12          3
               20          21          2
      In above solution,
      I imagined that there are 2 people called X and A.
      X always walks 1m. (Row_Number() over(order by NumVal))
      A always walks more than 1m. (NumVal)

      Then,I derived distance between X and A.
      Then, I made group using these distance. (group by disTance)

      **************************
      For exsample2 (collect Rows continued Days)
      create table Ex2 (DateVal primary key) as
      select date '2009-12-10' from dual union
      select date '2009-12-11' from dual union
      select date '2009-12-12' from dual union
      select date '2009-12-16' from dual union
      select date '2009-12-17' from dual union
      select date '2009-12-20' from dual;
      
      select min(DateVal),max(DateVal),count(*)
      from (select DateVal,
            DateVal-Row_Number() over(order by DateVal)
            as disTance
            from Ex2)
      group by disTance
      order by min(DateVal);
      
      MIN(DATE MAX(DATE   COUNT(*)
      -------- -------- ----------
      09-12-10 09-12-12          3
      09-12-16 09-12-17          2
      09-12-20 09-12-20          1
        • 1. Re: Tabibitosan method tutorial by Aketi Jyuuzou
          Aketi Jyuuzou
          **************************
          For exsample3 (collect Rows continued Months)
          create table Ex3 (DateVal primary key) as
          select date '2009-09-01' from dual union
          select date '2009-10-01' from dual union
          select date '2009-12-01' from dual union
          select date '2010-01-01' from dual union
          select date '2010-02-01' from dual union
          select date '2010-04-01' from dual;
          
          select min(DateVal),max(DateVal),count(*)
          from (select DateVal,
                 extract(year  from DateVal)*12
                +extract(month from DateVal)
                -Row_Number() over(order by DateVal)
                as disTance
                from Ex3)
          group by disTance
          order by min(DateVal);
          
          MIN(DATE MAX(DATE   COUNT(*)
          -------- -------- ----------
          09-09-01 09-10-01          2
          09-12-01 10-02-01          3
          10-04-01 10-04-01          1
          *****************************************************************
          3. What is complex usage of "Tabibitosan method" ? ?:|

          **************************
          For exsample4 (collect Rows continued and ID is equal)
          from Group by preserving the order
          create table Ex4 (ID,Val,SortKey) as
          select 1, 5, 1 from dual union all
          select 1,10, 2 from dual union all
          select 2, 2, 3 from dual union all
          select 2, 5, 4 from dual union all
          select 1,15, 5 from dual union all
          select 3,25, 6 from dual union all
          select 3,10, 7 from dual union all
          select 3, 5, 8 from dual union all
          select 3,15, 9 from dual union all
          select 4, 5,10 from dual;
          
          select ID,min(Val),max(Val),count(*)
          from (select ID,Val,SortKey,
                 Row_Number() over(order by SortKey)
                -Row_Number() over(partition by ID order by SortKey)
                as disTance
                from Ex4)
          group by ID,disTance
          order by min(SortKey);
          
          ID MIN(VAL) MAX(VAL) COUNT(*)
          -- -------- -------- --------
           1        5       10        2
           2        2        5        2
           1       15       15        1
           3        5       25        4
           4        5        5        1
          In above solution,
          I imagined that there are 5 people called X,A,B,C, and D.

          X always walks 1m. (Row_Number() over(order by SortKey))
          When ID = 1, A walks 1m. (Row_Number() over(partition by ID order by SortKey))
          When ID = 2, B walks 1m. (Row_Number() over(partition by ID order by SortKey))
          When ID = 3, C walks 1m. (Row_Number() over(partition by ID order by SortKey))
          When ID = 4, D walks 1m. (Row_Number() over(partition by ID order by SortKey))

          Then,
          I derived distance between X and A.
          I derived distance between X and B.
          I derived distance between X and C.
          I derived distance between X and D.

          Then, I made group using these distances. (group by ID,disTance)

          **************************
          For exsample5 (collect Rows continued and Val1 is equal and Val2 is equal)
          from analytic sql question
          create table mytable (sortKey,Val1,Val2) as
          select 1,'A','X' from dual union all
          select 2,'A','X' from dual union all
          select 3,'B','Y' from dual union all
          select 4,'B','Y' from dual union all
          select 5,'A','X' from dual union all
          select 5,'B','X' from dual union all
          select 6,'A','Y' from dual union all
          select 7,'B','Y' from dual union all
          select 7,'A','Y' from dual union all
          select 8,'A','Y' from dual;
          
          select Val1,Val2,min(sortKey) as sta,max(sortKey) as end
          from (select sortKey,Val1,Val2,
                 dense_rank() over(order by sortKey)
                -Row_Number() over(partition by Val1,Val2
                                   order by sortKey)
                as distance
                 from mytable)
          group by Val1,Val2,distance
          order by min(sortKey);
          
          Val1 Val2 sta end
          ---- ---- --- ---
          A    X      1   2
          B    Y      3   4
          B    X      5   5
          A    X      5   5
          A    Y      6   8
          B    Y      7   7
          In above solution,
          I imagined that there are 5 people called X,A,B,C, and D.

          X always walks 1m. (dense_rank() over(order by sortKey))
          When Val1 = 'A' and Val2 = 'X', A walks 1m. (Row_Number() over(partition by Val1,Val2 order by SortKey))
          When Val1 = 'A' and Val2 = 'Y', B walks 1m. (Row_Number() over(partition by Val1,Val2 order by SortKey))
          When Val1 = 'B' and Val2 = 'X', C walks 1m. (Row_Number() over(partition by Val1,Val2 order by SortKey))
          When Val1 = 'B' and Val2 = 'Y', D walks 1m. (Row_Number() over(partition by Val1,Val2 order by SortKey))

          Then,
          I derived distance between X and A.
          I derived distance between X and B.
          I derived distance between X and C.
          I derived distance between X and D.

          Then, I made group using these distances. (group by Val1,Val2,disTance)

          *****************************************************************
          4. What threads are using "Tabibitosan method" ?:|

          I will write that threads sorted by easy.

          basic usage
          Grouping Continuous Dates
          How to count continuous years

          complex usage
          window function
          Group by preserving the order
          SQL - Find Continuous Records
          analytic sql question

          *****************************************************************
          5. What URLS mention "Tabibitosan method" ?:|

          Boneist's Oracle blog
          http://boneist-oracle.livejournal.com/7389.html

          Alex Nuijten's Presentation and that PDF document.
          http://caat.odtug.com/odtug_caat/caat_abstract_reports.display_presenter_abstract?conference_id=81&presenter_id=117&abstract_id=186

          *****************************************************************
          OracleSQLPuzzle (MyHomePage)
          http://www.geocities.jp/oraclesqlpuzzle/

          sister thread
          Boolean algebra in some Rows tutorial by Aketi Jyuuzou Boolean algebra in some Rows tutorial by Aketi Jyuuzou
          • 2. Re: Tabibitosan method tutorial by Aketi Jyuuzou
            _Karthick_
            Thanks, Learned something new today. This kind of problem comes up quite often.

            Keep up the good work. Much Appreciated!!
            • 3. Re: Tabibitosan method tutorial by Aketi Jyuuzou
              743014
              here is another example using this with dates. It groups ids by consecutive weeks
              WITH A AS
              ( SELECT 1 ID,5 Val, to_date('2009-08-24','yyyy-mm-dd') StartWeek, to_date('2009-08-30','yyyy-mm-dd') EndWeek FROM dual UNION ALL 
                SELECT 1,10, to_date('2009-08-31','yyyy-mm-dd'), to_date('2009-09-06','yyyy-mm-dd') FROM dual UNION ALL
                SELECT 1,2, to_date('2009-09-07','yyyy-mm-dd'), to_date('2009-09-13','yyyy-mm-dd') FROM dual UNION ALL  
                SELECT 2,2, to_date('2009-09-07','yyyy-mm-dd'), to_date('2009-09-13','yyyy-mm-dd') FROM dual UNION ALL 
                SELECT 2,5, to_date('2009-09-14','yyyy-mm-dd'), to_date('2009-09-20','yyyy-mm-dd') FROM dual UNION ALL 
                SELECT 3,5, to_date('2009-09-14','yyyy-mm-dd'), to_date('2009-09-20','yyyy-mm-dd') FROM dual UNION ALL 
                SELECT 1,15, to_date('2009-09-21','yyyy-mm-dd'), to_date('2009-09-27','yyyy-mm-dd') FROM dual UNION ALL 
                SELECT 3,25, to_date('2009-09-28','yyyy-mm-dd'), to_date('2009-10-04','yyyy-mm-dd') FROM dual UNION ALL 
                SELECT 3,10, to_date('2009-10-05','yyyy-mm-dd'), to_date('2009-10-11','yyyy-mm-dd') FROM dual UNION ALL 
                SELECT 3,5, to_date('2009-10-12','yyyy-mm-dd'), to_date('2009-10-18','yyyy-mm-dd') FROM dual UNION ALL 
                SELECT 3,15, to_date('2009-10-19','yyyy-mm-dd'), to_date('2009-10-25','yyyy-mm-dd') FROM dual UNION ALL 
                SELECT 4,5, to_date('2009-10-26','yyyy-mm-dd'), to_date('2009-11-01','yyyy-mm-dd') FROM dual)
              ,
              B AS
              ( SELECT ID,        Val,        StartWeek,        EndWeek,
                       (Row_Number() over(ORDER BY StartWeek) - Row_Number() over(partition BY ID ORDER BY StartWeek)) AS makeGroup
               FROM A)
              SELECT ID,       SUM(Val) AS SumVal,       MIN(StartWeek),       MAX(EndWeek)
              FROM B
              GROUP BY ID,makeGroup
              ORDER BY MIN(StartWeek);
              
              ID            SumVAL        MIN(STARTWEEK)      MAX(ENDWEEK)
              1             17            8/24/2009           9/13/2009
              2             7             9/7/2009            9/20/2009
              3             5             9/14/2009             9/20/2009
              1             15            9/21/2009             9/27/2009
              3             55            9/28/2009             10/25/2009
              4             5             10/26/2009          11/1/2009
              • 4. Re: Tabibitosan method tutorial by Aketi Jyuuzou
                Frank Kulash
                Hi,
                Aketi Jyuuzou wrote:
                ...
                1. What is Tabibitosan ? ?:|

                "Tabibitosan" is Japanese language.
                "Tabibitosan" is one of math problem.
                What is the math problem? It sounds interesting.
                http://www.manabinoba.com/index.cfm/4,757,73,html?year=2002
                http://yslibrary.cool.ne.jp/sansub1801.html
                I love the graphics on the second link!
                • 5. Re: Tabibitosan method tutorial by Aketi Jyuuzou
                  AlanWms
                  Okay, I am a little dense, I was going to ask for an explanation, in very simple terms, then I figured it out. Basically it is subtracting (or adding if your list is descending) the row number from an ordered list of numbers (or dates) to find consecutive ranges of numbers (or dates). Pretty slick.

                  As an example, lets take some integers with gaps in the sequence:
                  Num      Row    Distance (Num-Row)
                  ===      ===    =============
                  2        1        1
                  3        2        1
                  4        3        1
                  5        4        1
                  6        5        1
                  7        6        1
                  12       7        5
                  13       8        5
                  14       9        5 
                  15       10       5
                  16       11       5
                  17       12       5
                  20       13       7
                  21       14       7
                  etc ...
                  Note that the "Distance" delineates consecutive groups, you then group by this etc. Maybe everyone else who viewed this thread got it immediately...

                  Thanks Aketi, this is a neat trick!
                  • 6. Re: Tabibitosan method tutorial by Aketi Jyuuzou
                    Solomon Yakobson
                    AlanWms wrote:
                    Okay, I am a little dense, I was going to ask for an explanation, in very simple terms, then I figured it out.
                    It looks like one of the simplest cases of grouping "loosely grouped" data.

                    SY.
                    • 7. Re: Tabibitosan method tutorial by Aketi Jyuuzou
                      730428
                      Hi, here is another way to solve example 1 using hierarchical queries:
                      SQL> select min(CONNECT_BY_ROOT NUMVAL) minval, NUMVAL maxval, max(NUMVAL-CONNECT_BY_ROOT NUMVAL+1)  countval
                        2  from ex1
                        3  where connect_by_isleaf=1
                        4  connect by numval=prior numval+1
                        5  group by numval
                        6  order by 1;
                      
                          MINVAL     MAXVAL   COUNTVAL
                      ---------- ---------- ----------
                               1          3          3
                               5          7          3
                              10         12          3
                              20         21          2
                      The other examples can be easily derived...

                      Max
                      [My Italian Oracle blog|http://oracleitalia.wordpress.com/2009/12/27/inviare-email-dal-db-utilizzando-utl_smtp/]
                      • 8. Re: Tabibitosan method tutorial by Aketi Jyuuzou
                        Aketi Jyuuzou
                        @Massimo Ruocchio

                        Nice one :-)
                        Hehe I have arranged your solution B-)
                        select min(CONNECT_BY_ROOT NUMVAL) minval,
                        NUMVAL maxval, max(Level) countval
                        from ex1
                        where connect_by_isleaf=1
                        connect by numval=prior numval+1
                        group by numval
                        order by minval;
                        
                        MINVAL  MAXVAL  COUNTVAL
                        ------  ------  --------
                             1       3         3
                             5       7         3
                            10      12         3
                            20      21         2
                        • 9. Re: Tabibitosan method tutorial by Aketi Jyuuzou
                          Aketi Jyuuzou
                          5. What is rival solution of Tabibitosan method ? ?:|

                          There is the rival solution.
                          Rival solution derives willSum using Lag function and case expression
                          And Then derive runSum of willSum using sum function.
                          select min(NumVal),max(NumVal),count(*)
                          from (select NumVal,
                                sum(willSum) over(order by NumVal) as GID
                                from (select NumVal,
                                      case when NumVal-1
                                         = Lag(NumVal) over(order by NumVal)
                                           then 0 else 1 end as willSum
                                      from Ex1))
                          group by GID
                          order by GID;
                          
                          MIN(NUMVAL)  MAX(NUMVAL)   COUNT(*)
                          -----------  -----------  ---------
                                    1            3          3
                                    5            7          3
                                   10           12          3
                                   20           21          2
                          But This rival solution needs 2 InLineView.
                          Altough TabibitoSan method needs only 1 InLineView.

                          Therefore if we can use TabibitoSan method,we should use TabibitoSan method.

                          for example
                          In below cases,we cannot use TabibitoSan method.
                          Query - Merging intervals
                          sql statement for click stream analyzing
                          • 10. Re: Tabibitosan method tutorial by Aketi Jyuuzou
                            Etbin
                            Hats off. A clear and concise treatment of a non elementary group of problems. I like the pilgrim interpretation. Never thought about using it when I was asked to explain a solution of this kind. Tabibitosan - a word to remember.

                            Regards

                            Etbin
                            • 11. Re: Tabibitosan method tutorial by Aketi Jyuuzou
                              user503699
                              Aketi Jyuuzou wrote:
                              **************************
                              For exsample3 (collect Rows continued Months)
                              create table Ex3 (DateVal primary key) as
                              select date '2009-09-01' from dual union
                              select date '2009-10-01' from dual union
                              select date '2009-12-01' from dual union
                              select date '2010-01-01' from dual union
                              select date '2010-02-01' from dual union
                              select date '2010-04-01' from dual;
                              
                              select min(DateVal),max(DateVal),count(*)
                              from (select DateVal,
                              extract(year  from DateVal)*12
                              +extract(month from DateVal)
                              -Row_Number() over(order by DateVal)
                              as disTance
                              from Ex3)
                              group by disTance
                              order by min(DateVal);
                              
                              MIN(DATE MAX(DATE   COUNT(*)
                              -------- -------- ----------
                              09-09-01 09-10-01          2
                              09-12-01 10-02-01          3
                              10-04-01 10-04-01          1
                              Aketi,

                              First of all, thanks a lot for this great thread. It is in my top bookmarks.
                              In above example, you have showed how this technique can be applied to group the rows with difference of a month.
                              I was wondering if this technique can be applied to group rows within a specific interval.
                              For e.g. is it possible to group the rows in the following resultset, within interval of 1 minute?
                              30-JULY-2010 05:10:00
                              30-JULY-2010 05:50:00
                              30-JULY-2010 06:55:00
                              30-JULY-2010 07:54:00
                              30-JULY-2010 08:58:00
                              The above data should be grouped as follows (group number is only an indicator and need not be continuous)
                              30-JULY-2010 05:10:00      1
                              30-JULY-2010 05:50:00      1
                              30-JULY-2010 06:55:00      2
                              30-JULY-2010 07:54:00      2
                              30-JULY-2010 08:58:00      3
                              Can you please help?
                              • 12. Re: Tabibitosan method tutorial by Aketi Jyuuzou
                                Aketi Jyuuzou
                                Nice question :-)
                                But this problem needs rival solution of "Tabibitosan method"
                                Like this thread sql statement for click stream analyzing
                                with t(Val) as(
                                select to_date('20100630 05:10:00','yyyymmdd hh24:mi:ss') from dual union
                                select to_date('20100630 05:50:00','yyyymmdd hh24:mi:ss') from dual union
                                select to_date('20100630 06:55:00','yyyymmdd hh24:mi:ss') from dual union
                                select to_date('20100630 07:54:00','yyyymmdd hh24:mi:ss') from dual union
                                select to_date('20100630 08:58:00','yyyymmdd hh24:mi:ss') from dual)
                                select to_char(Val,'yyyymmdd hh24:mi:ss') as Val,
                                sum(willSum) over(order by Val) as GID
                                from (select Val,
                                      case when Lag(Val) over(order by Val) + InterVal '1' hour > Val
                                           then 0 else 1 end as willSum
                                        from t)
                                order by Val;
                                
                                VAL                GID
                                -----------------  ---
                                20100630 05:10:00    1
                                20100630 05:50:00    1
                                20100630 06:55:00    2
                                20100630 07:54:00    2
                                20100630 08:58:00    3
                                • 13. Re: Tabibitosan method tutorial by Aketi Jyuuzou
                                  user503699
                                  Aketi Jyuuzou wrote:
                                  Nice question :-)
                                  But this problem needs rival solution of "Tabibitosan method"
                                  Like this thread sql statement for click stream analyzing
                                  Aketi,
                                  Many Thanks.
                                  My sincere apologies as I realised that my description of the problem and test data (in earlier post) were not consistent with each other.
                                  Please allow me to rephrase my question.
                                  With the following data, I would like to group the records within one minute interval and get the first record within the group.
                                  Here is the test case
                                  SQL> create table t1 (x date, y int, id int) ;
                                  
                                  Table created.
                                  
                                  SQL> insert into t1 values (to_date('10/06/2008 03:34:40 PM', 'MM/DD/YYYY HH12:MI:SS AM'), 10, 228)
                                    2  /
                                  
                                  1 row created.
                                  
                                  SQL> insert into t1 values (to_date('10/06/2008 03:34:30 PM', 'MM/DD/YYYY HH12:MI:SS AM'), 10, 228)
                                    2  /
                                  
                                  1 row created.
                                  
                                  SQL> insert into t1 values (to_date('10/06/2008 03:34:50 PM', 'MM/DD/YYYY HH12:MI:SS AM'), 10, 228)
                                    2  /
                                  
                                  1 row created.
                                  
                                  SQL> insert into t1 values (to_date('10/06/2008 04:28:30 PM', 'MM/DD/YYYY HH12:MI:SS AM'), 10, 228)
                                    2  /
                                  
                                  1 row created.
                                  
                                  SQL> insert into t1 values (to_date('10/06/2008 04:29:30 PM', 'MM/DD/YYYY HH12:MI:SS AM'), 10, 228)
                                    2  /
                                  
                                  1 row created.
                                  
                                  SQL> insert into t1 values (to_date('10/06/2008 04:30:30 PM', 'MM/DD/YYYY HH12:MI:SS AM'), 10, 228)
                                    2  /
                                  
                                  1 row created.
                                  
                                  SQL> insert into t1 values (to_date('10/06/2008 04:30:29 PM', 'MM/DD/YYYY HH12:MI:SS AM'), 10, 228)
                                    2  /
                                  
                                  1 row created.
                                  
                                  SQL> insert into t1 values (to_date('10/06/2008 04:27:35 PM', 'MM/DD/YYYY HH12:MI:SS AM'), 12, 228)
                                    2  /
                                  
                                  1 row created.
                                  
                                  SQL> insert into t1 values (to_date('10/06/2008 03:33:34 PM', 'MM/DD/YYYY HH12:MI:SS AM'), 21, 228)
                                    2  /
                                  
                                  1 row created.
                                  
                                  SQL> insert into t1 values (to_date('10/06/2008 03:34:15 PM', 'MM/DD/YYYY HH12:MI:SS AM'), 10, 228)
                                    2  /
                                  
                                  1 row created.
                                  
                                  SQL> insert into t1 values (to_date('10/07/2008 12:59:40 PM', 'MM/DD/YYYY HH12:MI:SS AM'), 10, 229)
                                    2  /
                                  
                                  1 row created.
                                  
                                  SQL> insert into t1 values (to_date('10/07/2008 12:59:50 PM', 'MM/DD/YYYY HH12:MI:SS AM'), 10, 229)
                                    2  /
                                  
                                  1 row created.
                                  
                                  SQL> insert into t1 values (to_date('10/07/2008 01:00:05 PM', 'MM/DD/YYYY HH12:MI:SS AM'), 10, 229)
                                    2  /
                                  
                                  1 row created.
                                  
                                  SQL> insert into t1 values (to_date('10/07/2008 01:00:30 PM', 'MM/DD/YYYY HH12:MI:SS AM'), 10, 229)
                                    2  /
                                  
                                  1 row created.
                                  
                                  SQL> insert into t1 values (to_date('10/07/2008 01:00:49 PM', 'MM/DD/YYYY HH12:MI:SS AM'), 10, 229)
                                    2  /
                                  
                                  1 row created.
                                  
                                  SQL> insert into t1 values (to_date('10/07/2008 01:00:55 PM', 'MM/DD/YYYY HH12:MI:SS AM'), 10, 229)
                                    2  /
                                  
                                  1 row created.
                                  
                                  SQL> commit ;
                                  
                                  Commit complete.
                                  
                                  SQL> select id, x, y, row_number() over (partition by id order by x) rn from t1 order by id, x
                                    2  /
                                  
                                          ID X                             Y         RN
                                  ---------- -------------------- ---------- ----------
                                         228 06-OCT-2008 15:33:34         21          1
                                         228 06-OCT-2008 15:34:15         10          2
                                         228 06-OCT-2008 15:34:30         10          3
                                         228 06-OCT-2008 15:34:40         10          4
                                         228 06-OCT-2008 15:34:50         10          5
                                         228 06-OCT-2008 16:27:35         12          6
                                         228 06-OCT-2008 16:28:30         10          7
                                         228 06-OCT-2008 16:29:30         10          8
                                         228 06-OCT-2008 16:30:29         10          9
                                         228 06-OCT-2008 16:30:30         10         10
                                         229 07-OCT-2008 12:59:40         10          1
                                         229 07-OCT-2008 12:59:50         10          2
                                         229 07-OCT-2008 13:00:05         10          3
                                         229 07-OCT-2008 13:00:30         10          4
                                         229 07-OCT-2008 13:00:49         10          5
                                         229 07-OCT-2008 13:00:55         10          6
                                  
                                  16 rows selected.
                                  Expected output
                                          ID              X                      Y
                                  ---------- --------------------  ----------
                                         228 06-OCT-2008 15:33:34         21
                                         228 06-OCT-2008 15:34:40         10
                                         228 06-OCT-2008 16:27:35         12
                                         228 06-OCT-2008 16:29:30         10
                                         228 06-OCT-2008 16:30:30         10
                                         229 07-OCT-2008 12:59:40         10
                                         229 07-OCT-2008 13:00:49         10
                                  • 14. Re: Tabibitosan method tutorial by Aketi Jyuuzou
                                    Aketi Jyuuzou
                                    About 2 years before,I solved it :D
                                    range query
                                    with t(Val) as(
                                    select to_date('10/06/2008 03:34:40', 'MM/DD/YYYY HH24:MI:SS') from dual union
                                    select to_date('10/06/2008 03:34:30', 'MM/DD/YYYY HH24:MI:SS') from dual union
                                    select to_date('10/06/2008 03:34:50', 'MM/DD/YYYY HH24:MI:SS') from dual union
                                    select to_date('10/06/2008 04:28:30', 'MM/DD/YYYY HH24:MI:SS') from dual union
                                    select to_date('10/06/2008 04:29:30', 'MM/DD/YYYY HH24:MI:SS') from dual union
                                    select to_date('10/06/2008 04:30:30', 'MM/DD/YYYY HH24:MI:SS') from dual union
                                    select to_date('10/06/2008 04:30:29', 'MM/DD/YYYY HH24:MI:SS') from dual union
                                    select to_date('10/06/2008 04:27:35', 'MM/DD/YYYY HH24:MI:SS') from dual union
                                    select to_date('10/06/2008 03:33:34', 'MM/DD/YYYY HH24:MI:SS') from dual union
                                    select to_date('10/06/2008 03:34:15', 'MM/DD/YYYY HH24:MI:SS') from dual union
                                    select to_date('10/07/2008 12:59:40', 'MM/DD/YYYY HH24:MI:SS') from dual union
                                    select to_date('10/07/2008 12:59:50', 'MM/DD/YYYY HH24:MI:SS') from dual union
                                    select to_date('10/07/2008 13:00:05', 'MM/DD/YYYY HH24:MI:SS') from dual union
                                    select to_date('10/07/2008 13:00:30', 'MM/DD/YYYY HH24:MI:SS') from dual union
                                    select to_date('10/07/2008 13:00:49', 'MM/DD/YYYY HH24:MI:SS') from dual union
                                    select to_date('10/07/2008 13:00:55', 'MM/DD/YYYY HH24:MI:SS') from dual)
                                    select to_char(Val,'yyyy-mm-dd hh24:mi:ss') as Val
                                    from (select Val,min(Val) over() as startVal,
                                          min(Val) over(order by Val
                                                        range between InterVal '1' minute following
                                                                  and unbounded following) as nextVal
                                          from t)
                                    start with Val = startVal
                                    connect by prior nextVal = Val;
                                    
                                    VAL
                                    -------------------
                                    2008-10-06 03:33:34
                                    2008-10-06 03:34:40
                                    2008-10-06 04:27:35
                                    2008-10-06 04:29:30
                                    2008-10-06 04:30:30
                                    2008-10-07 12:59:40
                                    2008-10-07 13:00:49
                                    1 2 Previous Next