Home » SQL & PL/SQL » SQL & PL/SQL » Need help on Tabibitosan (start of group) (11.2.0.3)
| Need help on Tabibitosan (start of group) [message #683925] |
Fri, 05 March 2021 14:28  |
Amine
Messages: 376
Registered: March 2010
|
Senior Member |
|
|
Hi everyone,
I need some help about the Tabibitosan method, also known start of group method.
drop table test;
create table test
(
id number ,
motif number ,
dat_do date
)
;
insert into test values (100,80, to_date('10/02/2021', 'dd/mm/yyyy'));
insert into test values (100,80, to_date('11/02/2021', 'dd/mm/yyyy'));
insert into test values (100,80, to_date('14/02/2021', 'dd/mm/yyyy'));
insert into test values (100,80, to_date('15/02/2021', 'dd/mm/yyyy'));
insert into test values (100,80, to_date('21/02/2021', 'dd/mm/yyyy'));
insert into test values (100,80, to_date('22/02/2021', 'dd/mm/yyyy'));
insert into test values (100,80, to_date('23/02/2021', 'dd/mm/yyyy'));
insert into test values (100,80, to_date('24/02/2021', 'dd/mm/yyyy'));
insert into test values (100,80, to_date('25/02/2021', 'dd/mm/yyyy'));
insert into test values (100,80, to_date('26/02/2021', 'dd/mm/yyyy'));
insert into test values (100,80, to_date('27/02/2021', 'dd/mm/yyyy'));
insert into test values (100,80, to_date('28/02/2021', 'dd/mm/yyyy'));
ID MOTIF DAT_START DAT_END
--------- --------- ---------- ----------
100 80 10/02/2021 10/02/2021
100 80 11/02/2021 11/02/2021
100 80 14/02/2021 14/02/2021
100 80 15/02/2021 15/02/2021
100 80 21/02/2021 28/02/2021
create or replace view v1 as
select *
from
(
with v00 as
(
select
test.*
--, row_number() over (order by dat_do) x
, row_number() over (partition by id, motif order by dat_do) x
-- ---
, lead(dat_do, 1, dat_do + 1) over (partition by id order by dat_do) dat_do_next
, lead(motif, 1, motif) over (partition by id order by dat_do) motif_next
from test
), v01 as
(
select v00.id
, motif
, dat_do
, x
, case
when dat_do_next - dat_do = 1 and motif_next = motif then 0
else 1
end start_of_group
from v00
where 1 = 1
)
select v01.*
, sum(start_of_group) over (partition by id, motif order by dat_do) grp
from v01
)
;
select * from v1;
ID MOTIF DAT_DO X START_OF_GROUP GRP
--------- --------- ---------- --------- -------------- ---------
100 80 10/02/2021 1 0 0
100 80 11/02/2021 2 1 1
100 80 14/02/2021 3 0 1
100 80 15/02/2021 4 1 2
100 80 21/02/2021 5 0 2
100 80 22/02/2021 6 0 2
100 80 23/02/2021 7 0 2
100 80 24/02/2021 8 0 2
100 80 25/02/2021 9 0 2
100 80 26/02/2021 10 0 2
100 80 27/02/2021 11 0 2
100 80 28/02/2021 12 0 2
12 ligne(s) sélectionnée(s).
create or replace view v2 as
select
id
, motif
, min(dat_do) dat_start
, max(dat_do) dat_end
from v1
group by
id
, motif
, grp
;
select *
from v2
order by dat_start
;
ID MOTIF DAT_START DAT_END
--------- --------- ---------- ----------
100 80 10/02/2021 10/02/2021
100 80 11/02/2021 14/02/2021
100 80 15/02/2021 28/02/2021
Any help would be appreciated.
Thanks in advance,
Amine
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Need help on Tabibitosan (start of group) [message #683930 is a reply to message #683929] |
Fri, 05 March 2021 15:14  |
 |
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
SQL> with
2 data as (
3 select id, motif, dat_do,
4 dat_do - date '2021-01-01'
5 - row_number() over (partition by id, motif order by dat_do) grp
6 from test
7 )
8 select id, motif,
9 min(dat_do) dat_start, max(dat_do) dat_end, count(*) nb_days
10 from data
11 group by id, motif, grp
12 order by id, motif, grp
13 /
ID MOTIF DAT_START DAT_END NB_DAYS
---------- ---------- ----------- ----------- ----------
100 80 10-FEB-2021 11-FEB-2021 2
100 80 14-FEB-2021 15-FEB-2021 2
100 80 21-FEB-2021 28-FEB-2021 8
3 rows selected. |
|
|
|
|
|
|
|
| Re: Need help on Tabibitosan (start of group) [message #683933 is a reply to message #683932] |
Fri, 05 March 2021 15:29 |
|
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Quote:Why you did dat_do - date '2021-01-01' ?
To convert the date to a number to compute a group number.
Quote:Could you explain the grp column please ?
It assigns a same number to rows in consecutive dates:
SQL> select id, motif, dat_do,
2 dat_do - date '2021-01-01' new_dat,
3 row_number() over (partition by id, motif order by dat_do) row_nb_in_part,
4 dat_do - date '2021-01-01'
5 - row_number() over (partition by id, motif order by dat_do) grp
6 from test
7 order by id, motif, dat_do
8 /
ID MOTIF DAT_DO NEW_DAT ROW_NB_IN_PART GRP
---------- ---------- ----------- ---------- -------------- ----------
100 80 10-FEB-2021 40 1 39
100 80 11-FEB-2021 41 2 39
100 80 14-FEB-2021 44 3 41
100 80 15-FEB-2021 45 4 41
100 80 21-FEB-2021 51 5 46
100 80 22-FEB-2021 52 6 46
100 80 23-FEB-2021 53 7 46
100 80 24-FEB-2021 54 8 46
100 80 25-FEB-2021 55 9 46
100 80 26-FEB-2021 56 10 46
100 80 27-FEB-2021 57 11 46
100 80 28-FEB-2021 58 12 46[Updated on: Fri, 05 March 2021 15:29] Report message to a moderator |
|
|
|
| Re: Need help on Tabibitosan (start of group) [message #683934 is a reply to message #683925] |
Fri, 05 March 2021 15:30 |
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
First of all Tabibitosan and start of group are two different methods. Tabibitosan is possible only if "distance" between all rows in a group is same. STart of group is generic method wher you flag beginning of each group. And you do need to answer Michel's question why 10 & 11 are not grouped together and same way why 14 & aren't while 21, 22, 23, 24, 25 , 26 , 27, 28 are? I'll assume group is 3 or more consecutive days. Then:
SELECT ID,
MOTIF,
DAT_START,
DAT_END
FROM TEST
MATCH_RECOGNIZE(
PARTITION BY ID,
MOTIF
ORDER BY DAT_DO
MEASURES
FIRST(DAT_DO) DAT_START,
LAST(DAT_DO) DAT_END
ONE ROW PER MATCH
PATTERN(STRT (GRP{2,})*)
DEFINE GRP AS DAT_DO = PREV(DAT_DO) + 1
)
/
ID MOTIF DAT_START DAT_END
---------- ---------- ------------------- -------------------
100 80 02/10/2021 00:00:00 02/10/2021 00:00:00
100 80 02/11/2021 00:00:00 02/11/2021 00:00:00
100 80 02/14/2021 00:00:00 02/14/2021 00:00:00
100 80 02/15/2021 00:00:00 02/15/2021 00:00:00
100 80 02/21/2021 00:00:00 02/28/2021 00:00:00
SQL>
|
|
|
|
|
|
|
|
| Re: Need help on Tabibitosan (start of group) [message #683937 is a reply to message #683935] |
Fri, 05 March 2021 16:30 |
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Michel Cadot wrote on Fri, 05 March 2021 16:34
Unfortunately MATCH_RECONGNIZE is not available in 11.2.0.3. 
Ah, I missed that. Then:
WITH T1 AS (
SELECT ID,
MOTIF,
DAT_DO,
DAT_DO - RANK() OVER(PARTITION BY ID,MOTIF ORDER BY DAT_DO) + 1 GRP
FROM TEST
),
T2 AS (
SELECT ID,
MOTIF,
DAT_DO,
GRP,
CASE
WHEN COUNT(DAT_DO) OVER(PARTITION BY ID,MOTIF,GRP) > 2 THEN 0
ELSE ROWNUM
END GRP_SLICER
FROM T1
)
SELECT ID,
MOTIF,
MIN(DAT_DO) DAT_START,
MAX(DAT_DO) DAT_END
FROM T2
GROUP BY ID,
MOTIF,
GRP,
GRP_SLICER
ORDER BY ID,
MOTIF,
GRP,
GRP_SLICER
/
ID MOTIF DAT_START DAT_END
---------- ---------- ------------------- -------------------
100 80 02/10/2021 00:00:00 02/10/2021 00:00:00
100 80 02/11/2021 00:00:00 02/11/2021 00:00:00
100 80 02/14/2021 00:00:00 02/14/2021 00:00:00
100 80 02/15/2021 00:00:00 02/15/2021 00:00:00
100 80 02/21/2021 00:00:00 02/28/2021 00:00:00
SQL>
|
|
|
|
| Re: Need help on Tabibitosan (start of group) [message #683938 is a reply to message #683937] |
Fri, 05 March 2021 16:35 |
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
And now I missed "You're right Michel" which makes it way simpler:
WITH T AS (
SELECT ID,
MOTIF,
DAT_DO,
DAT_DO - RANK() OVER(PARTITION BY ID,MOTIF ORDER BY DAT_DO) GRP
FROM TEST
)
SELECT ID,
MOTIF,
MIN(DAT_DO) DAT_START,
MAX(DAT_DO) DAT_END
FROM T
GROUP BY ID,
MOTIF,
GRP
ORDER BY ID,
MOTIF,
GRP
/
ID MOTIF DAT_START DAT_END
---------- ---------- ------------------- -------------------
100 80 02/10/2021 00:00:00 02/11/2021 00:00:00
100 80 02/14/2021 00:00:00 02/15/2021 00:00:00
100 80 02/21/2021 00:00:00 02/28/2021 00:00:00
SQL>
P.S. I assume DAT_DO dates are unique within each ID, MOTIF combination. If not, use DENSE_RANK instead of RANK. Or better change it to DENSE_RANK regardless to be "prepared" for possible future changes.
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 25 08:37:53 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
