Which statement to use based on its row and transaction in stay that way-CodePudding

I am getting stuck on this, hard to explain to write down here as well the subject heading hopefully by the data example and output result below that enough to give you an idea.

I am run out which Oracle SQL statement best to use. The over (partition) seem didn't work out for the below scenario.

screenshoot - before & after

DATA

customer_id	row_id	date	type
LB01	22	14/03/2022	CAR 1
LB01	21	11/03/2022	CAR 1
LB01	20	9/11/2001	CAR 1
LB01	19	16/10/2001	CAR 1
LB01	18	10/08/2001	CAR 2
LB01	17	29/05/2001	CAR 2
LB01	16	24/04/2001	CAR 2
LB01	15	13/03/2001	CAR 3
LB01	14	21/12/2000	CAR 3
LB01	13	13/11/2000	CAR 3
LB01	12	20/10/2000	CAR 3
LB01	11	14/03/2000	CAR 1
LB01	10	18/01/2000	CAR 1
LB01	9	24/12/1999	CAR 2
LB01	8	14/09/1999	CAR 2
LB01	7	30/03/1999	CAR 2
LB01	6	24/02/1999	CAR 2
LB01	5	19/02/1999	CAR 4
LB01	4	15/12/1998	CAR 4
LB01	3	15/12/1998	CAR 4
LB01	2	24/09/1998	CAR 4
LB01	1	06/08/1998	CAR 4

The output that I want result like this:

customer_id	type	min_date	max_date
LB01	Car 1	16/10/2001	14/03/2002
LB01	Car 2	24/04/2001	10/08/2001
LB01	Car 3	20/10/2000	13/03/2001
LB01	Car 1	18/01/2000	14/03/2000
LB01	Car 2	24/02/1999	24/12/1999
LB01	Car 4	6/08/1998	19/02/1999

I hope those makes sense for you and hopefully you can assist me on this.

CodePudding user response：

You have to apply Tabibitosan algorithm first:

select customer_id, type, rn, min(dat) as min_date, max(dat) as max_dat
from (
    select d.*, row_id 1-row_number() over(partition by customer_id, type order by row_id) as rn 
    from data d
)
group by customer_id, type, rn
order by customer_id, type
;

CUST TYPE          RN MIN_DATE MAX_DAT 
---- ----- ---------- -------- --------
LB01 CAR 1         10 18/01/00 14/03/00
LB01 CAR 1         17 16/10/01 14/03/22
LB01 CAR 2          6 24/02/99 24/12/99
LB01 CAR 2         12 24/04/01 10/08/01
LB01 CAR 3         12 20/10/00 13/03/01
LB01 CAR 4          1 06/08/98 19/02/99

CodePudding user response：

Just create your own grouping calculation out of already existing ROW_ID and use MIN_DATE for ordering:

SELECT  CUST_ID, A_TYPE, Min(A_DATE) "MIN_DATE", Max(A_DATE) "MAX_DATE" 
FROM    (SELECT     t.*,
                    Max(ROW_ID) OVER(Partition By CUST_ID || A_TYPE ORDER BY ROW_ID DESC Rows Between Unbounded Preceding And Current Row) -
                    (ROW_ID   Sum(1) OVER(PARTITION BY CUST_ID || A_TYPE ORDER BY ROW_ID DESC) - 1) "GRP"
        FROM        tbl t
        ORDER BY    ROW_ID DESC     )
GROUP BY    CUST_ID, A_TYPE, GRP
ORDER BY    CUST_ID, MIN(A_DATE) DESC

With your sample data:

WITH
    tbl (CUST_ID,   ROW_ID, A_DATE, A_TYPE) AS
        (   Select 'LB01',  22, To_Date('14/03/2022', 'dd/mm/yyyy'),    'CAR 1' From Dual Union All
            Select 'LB01',  21, To_Date('11/03/2022', 'dd/mm/yyyy'),    'CAR 1' From Dual Union All
            Select 'LB01',  20, To_Date('09/11/2001', 'dd/mm/yyyy'),    'CAR 1' From Dual Union All
            Select 'LB01',  19, To_Date('16/10/2001', 'dd/mm/yyyy'),    'CAR 1' From Dual Union All
            --
            Select 'LB01',  18, To_Date('10/08/2001', 'dd/mm/yyyy'),    'CAR 2' From Dual Union All
            Select 'LB01',  17, To_Date('29/05/2001', 'dd/mm/yyyy'),    'CAR 2' From Dual Union All
            Select 'LB01',  16, To_Date('24/04/2001', 'dd/mm/yyyy'),    'CAR 2' From Dual Union All
            --
            Select 'LB01',  15, To_Date('13/03/2001', 'dd/mm/yyyy'),    'CAR 3' From Dual Union All
            Select 'LB01',  14, To_Date('21/12/2000', 'dd/mm/yyyy'),    'CAR 3' From Dual Union All
            Select 'LB01',  13, To_Date('13/11/2000', 'dd/mm/yyyy'),    'CAR 3' From Dual Union All
            Select 'LB01',  12, To_Date('20/10/2000', 'dd/mm/yyyy'),    'CAR 3' From Dual Union All
            --
            Select 'LB01',  11, To_Date('14/03/2000', 'dd/mm/yyyy'),    'CAR 1' From Dual Union All
            Select 'LB01',  10, To_Date('18/01/2000', 'dd/mm/yyyy'),    'CAR 1' From Dual Union All
            --
            Select 'LB01',  9,  To_Date('24/12/1999', 'dd/mm/yyyy'),    'CAR 2' From Dual Union All
            Select 'LB01',  8,  To_Date('14/09/1999', 'dd/mm/yyyy'),    'CAR 2' From Dual Union All
            Select 'LB01',  7,  To_Date('30/03/1999', 'dd/mm/yyyy'),    'CAR 2' From Dual Union All
            Select 'LB01',  6,  To_Date('24/02/1999', 'dd/mm/yyyy'),    'CAR 2' From Dual Union All
            --
            Select 'LB01',  5,  To_Date('19/02/1999', 'dd/mm/yyyy'),    'CAR 4' From Dual Union All
            Select 'LB01',  4,  To_Date('15/12/1998', 'dd/mm/yyyy'),    'CAR 4' From Dual Union All
            Select 'LB01',  3,  To_Date('15/12/1998', 'dd/mm/yyyy'),    'CAR 4' From Dual Union All
            Select 'LB01',  2,  To_Date('24/09/1998', 'dd/mm/yyyy'),    'CAR 4' From Dual Union All
            Select 'LB01',  1,  To_Date('06/08/1998', 'dd/mm/yyyy'),    'CAR 4' From Dual
        )

R e s u l t :

CUST_ID	A_TYPE	MIN_DATE	MAX_DATE
LB01	CAR 1	16-OCT-01	14-MAR-22
LB01	CAR 2	24-APR-01	10-AUG-01
LB01	CAR 3	20-OCT-00	13-MAR-01
LB01	CAR 1	18-JAN-00	14-MAR-00
LB01	CAR 2	24-FEB-99	24-DEC-99
LB01	CAR 4	06-AUG-98	19-FEB-99

CodePudding user response：

PLEASE NOTE: For some reason this website can't tick both answers that the solution provided by @p3consulting and @d r are working on my data by creating a new column for a new row id in that way row numbers are in orderly

subquery before that;

row_number() over(partition by cust_id order by seq_id ) row_id

depend on how your data has numbers are not in orderly number

CodePudding user response：

refer to the above post made by @p3consulting regarding the Tabibitosan algorithm

here is the below script that you can copy and paste into your Oracle SQL to learn

for my question here still does not work for my data and still searching the right script

with
sample_data (customer_id, row_id, mock_date, product_type) as (
select 'LB01', '22', to_date ('03/14/22', 'mm/dd/rr'), 'CAR 1' from dual union all    
select 'LB01', '21', to_date ('03/11/22', 'mm/dd/rr'), 'CAR 1' from dual union all
select 'LB01', '20', to_date ('11/09/01', 'mm/dd/rr'), 'CAR 1' from dual union all
select 'LB01', '19', to_date ('10/16/01', 'mm/dd/rr'), 'CAR 1' from dual union all
select 'LB01', '18', to_date ('08/10/01', 'mm/dd/rr'), 'CAR 2' from dual union all
select 'LB01', '17', to_date ('05/29/01', 'mm/dd/rr'), 'CAR 2' from dual union all
select 'LB01', '16', to_date ('04/24/01', 'mm/dd/rr'), 'CAR 2' from dual union all
select 'LB01', '15', to_date ('03/13/01', 'mm/dd/rr'), 'CAR 3' from dual union all    
select 'LB01', '14', to_date ('12/21/00', 'mm/dd/rr'), 'CAR 3' from dual union all
select 'LB01', '13', to_date ('11/13/00', 'mm/dd/rr'), 'CAR 3' from dual union all
select 'LB01', '12', to_date ('10/20/00', 'mm/dd/rr'), 'CAR 3' from dual union all
select 'LB01', '11', to_date ('03/14/00', 'mm/dd/rr'), 'CAR 1' from dual union all
select 'LB01', '10', to_date ('01/18/00', 'mm/dd/rr'), 'CAR 1' from dual union all
select 'LB01', '09', to_date ('12/24/99', 'mm/dd/rr'), 'CAR 2' from dual union all
select 'LB01', '08', to_date ('09/14/99', 'mm/dd/rr'), 'CAR 2' from dual union all
select 'LB01', '07', to_date ('03/30/99', 'mm/dd/rr'), 'CAR 2' from dual union all
select 'LB01', '06', to_date ('02/24/99', 'mm/dd/rr'), 'CAR 2' from dual union all
select 'LB01', '05', to_date ('02/19/99', 'mm/dd/rr'), 'CAR 4' from dual union all    
select 'LB01', '04', to_date ('12/15/98', 'mm/dd/rr'), 'CAR 4' from dual union all    
select 'LB01', '03', to_date ('12/15/98', 'mm/dd/rr'), 'CAR 4' from dual union all
select 'LB01', '02', to_date ('09/24/98', 'mm/dd/rr'), 'CAR 4' from dual union all
select 'LB01', '01', to_date ('06/08/98', 'mm/dd/rr'), 'CAR 4' from dual 
)
 
select
customer_id ,
product_type ,
grp ,
min(mock_date) as min_date , 
max(mock_date) as max_date 
from (
select d.* ,
row_id   1 - row_number () over (partition by customer_id, product_type order by row_id) as grp
from sample_data d )
 
group by
customer_id ,
product_type ,
grp 
 
order by
grp desc