Forum Stats

  • 3,836,734 Users
  • 2,262,175 Discussions
  • 7,900,088 Comments

Discussions

Tabibitosan method tutorial by Aketi Jyuuzou

2»

Comments

  • user503699
    user503699 Member Posts: 2,098
    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?
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited Aug 2, 2010 8:05AM
    Nice question :-)
    But this problem needs rival solution of "Tabibitosan method"
    Like this thread 956106
    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
  • user503699
    user503699 Member Posts: 2,098
    Aketi Jyuuzou wrote:
    Nice question :-)
    But this problem needs rival solution of "Tabibitosan method"
    Like this thread 956106
    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
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    About 2 years before,I solved it :D
    703724
    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
  • user503699
    user503699 Member Posts: 2,098
    edited Aug 2, 2010 9:24AM
    Aketi Jyuuzou wrote:
    About 2 years before,I solved it :D
    703724
    Aketi,

    Many thanks for your response.
    Is this the only way (using SQL) to solve this problem?
    The main reason for asking for alternative solution is this solution requires accessing and sorting the table twice.
    SQL> select * from table(dbms_xplan.display_cursor) ;
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    SQL_ID  9skznz4u401t1, child number 0
    -------------------------------------
    select id,x,y from (select id,x,y,min(x) over (partition by id) startx,
    min(x) over (partition by id order by x range between interval '1' minute
    following and unbounded following) nextval from t1) start with x = startx
    connect by prior nextval = x
    
    Plan hash value: 775368986
    
    ----------------------------------------------------------------------------------
    | Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT          |      |       |       |     4 (100)|          |
    |*  1 |  CONNECT BY WITH FILTERING|      |       |       |            |          |
    |*  2 |   VIEW                    |      |    16 |  1136 |     4  (25)| 00:00:01 |
    |   3 |    WINDOW SORT            |      |    16 |   240 |     4  (25)| 00:00:01 |
    |   4 |     TABLE ACCESS FULL     | T1   |    16 |   240 |     3   (0)| 00:00:01 |
    |*  5 |   HASH JOIN               |      |       |       |            |          |
    |   6 |    CONNECT BY PUMP        |      |       |       |            |          |
    |   7 |    VIEW                   |      |    16 |   704 |     4  (25)| 00:00:01 |
    |   8 |     WINDOW SORT           |      |    16 |   240 |     4  (25)| 00:00:01 |
    |   9 |      TABLE ACCESS FULL    | T1   |    16 |   240 |     3   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - access("X"=PRIOR NULL)
       2 - filter("X"="STARTX")
       5 - access("X"=PRIOR NULL)
    p.s. Please don't suggest MODEL clause ;)

    Edited by: user503699 on Aug 2, 2010 6:52 PM
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    user503699 wrote:
    Is this the only way (using SQL) to solve this problem?
    The main reason for asking for alternative solution is this solution requires accessing and sorting the table twice.
    Ummm

    I think that there are 2 solutions.
    One is recursive with clause insted of above HierarchicalQuery.
    And The other is PipeLineTableFunction.
  • 783956
    783956 Member Posts: 1,161
    Hi Alan,

    >
    I am a little dense,...
    >

    No, you're not... I had to think about it too ;)

    Indeed, it is very slick. A known monotonous sequence (the row numbers) applied to a sparse sequence of monotonous values (the other sequence) reveals the sparse groups in the sequence because their difference is constant in each group.

    Very nice and elegant! :)

    Thank you Aketi !!

    John.
This discussion has been closed.