## Forum Stats

• 3,836,734 Users
• 2,262,175 Discussions

Discussions

# Tabibitosan method tutorial by Aketi Jyuuzou

2»

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

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
• 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.
• Member Posts: 1,161
Hi Alan,

>
I am a little dense,...
>