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 Go to next message
Amine
Messages: 336
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'));

I want to group dates for ids and motifs to get summarized results. The desired output is :

       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
I did some gymnastic but in vain. Here is my try :

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
As you can see, the grp column isn't enough to group the data so we can get the desired output.

Any help would be appreciated.

Thanks in advance,

Amine
Re: Need help on Tabibitosan (start of group) [message #683926 is a reply to message #683925] Fri, 05 March 2021 14:46 Go to previous messageGo to next message
Michel Cadot
Messages: 67815
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You have to explain why 21-28 are grouped but not 10-11 and 14-15, what is the rule about this?

Re: Need help on Tabibitosan (start of group) [message #683927 is a reply to message #683926] Fri, 05 March 2021 14:51 Go to previous messageGo to next message
Amine
Messages: 336
Registered: March 2010
Senior Member

Actually, here is the business logic :
motif is a code for the reason of absence of Id.
So id was absent on 10 (for some reason, here the reason code is 80) then on 11, on 14, on 15, and then for the period between 21 and 28.

Hope it's clear

[Updated on: Fri, 05 March 2021 14:54]

Report message to a moderator

Re: Need help on Tabibitosan (start of group) [message #683928 is a reply to message #683927] Fri, 05 March 2021 15:02 Go to previous messageGo to next message
Michel Cadot
Messages: 67815
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why 10 AND 11 is not period between 10 and 11?

Re: Need help on Tabibitosan (start of group) [message #683929 is a reply to message #683928] Fri, 05 March 2021 15:09 Go to previous messageGo to next message
Amine
Messages: 336
Registered: March 2010
Senior Member

You're right Michel. I am tired sorry, spent a lot of time on it.
The desired output is

       ID     MOTIF DAT_START  DAT_END
--------- --------- ---------- ----------
      100        80 10/02/2021 11/02/2021
      100        80 14/02/2021 15/02/2021
      100        80 21/02/2021 28/02/2021
Re: Need help on Tabibitosan (start of group) [message #683930 is a reply to message #683929] Fri, 05 March 2021 15:14 Go to previous messageGo to next message
Michel Cadot
Messages: 67815
Registered: March 2007
Location: Nanterre, France, http://...
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 #683931 is a reply to message #683930] Fri, 05 March 2021 15:18 Go to previous messageGo to next message
Amine
Messages: 336
Registered: March 2010
Senior Member

Thanks Michel.

Could you explain the grp column please ?
Re: Need help on Tabibitosan (start of group) [message #683932 is a reply to message #683931] Fri, 05 March 2021 15:19 Go to previous messageGo to next message
Amine
Messages: 336
Registered: March 2010
Senior Member

Why you did dat_do - date '2021-01-01' ?
Re: Need help on Tabibitosan (start of group) [message #683933 is a reply to message #683932] Fri, 05 March 2021 15:29 Go to previous messageGo to next message
Michel Cadot
Messages: 67815
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3047
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>
SY.
Re: Need help on Tabibitosan (start of group) [message #683935 is a reply to message #683934] Fri, 05 March 2021 15:34 Go to previous messageGo to next message
Michel Cadot
Messages: 67815
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Unfortunately MATCH_RECONGNIZE is not available in 11.2.0.3. Smile

Re: Need help on Tabibitosan (start of group) [message #683936 is a reply to message #683935] Fri, 05 March 2021 16:11 Go to previous messageGo to next message
Amine
Messages: 336
Registered: March 2010
Senior Member

Many thanks to Michel and SY.

I think that there's a need to create a topic like the Row Generator, about those methods that are very useful to solve real problems.

[Updated on: Fri, 05 March 2021 16:11]

Report message to a moderator

Re: Need help on Tabibitosan (start of group) [message #683937 is a reply to message #683935] Fri, 05 March 2021 16:30 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3047
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. Smile

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>
SY.
Re: Need help on Tabibitosan (start of group) [message #683938 is a reply to message #683937] Fri, 05 March 2021 16:35 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3047
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>
SY.
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.
Re: Need help on Tabibitosan (start of group) [message #683943 is a reply to message #683938] Sat, 06 March 2021 07:55 Go to previous messageGo to next message
Amine
Messages: 336
Registered: March 2010
Senior Member

Many thanks @Solomon Yakobson it works perfectly !

What about the idea of entry I mentioned above ?
Re: Need help on Tabibitosan (start of group) [message #683944 is a reply to message #683943] Sat, 06 March 2021 10:33 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3047
Registered: January 2010
Location: Connecticut, USA
Senior Member
What entry? Be specific.

SY.
Re: Need help on Tabibitosan (start of group) [message #683946 is a reply to message #683936] Sat, 06 March 2021 16:49 Go to previous message
Amine
Messages: 336
Registered: March 2010
Senior Member

Amine wrote on Fri, 05 March 2021 23:11
Many thanks to Michel and SY.

I think that there's a need to create a topic like the Row Generator, about those methods that are very useful to solve real problems.

About this one
Previous Topic: Setting up Email service on Oracle 9i
Next Topic: Parsing XML multiple CDATA with & and withoutn &
Goto Forum:
  


Current Time: Thu Apr 22 11:52:39 CDT 2021