Group uninterrupted days for min Balance?

User_ZR3PE · Dec 3, 2022 2:07PM

I have a BalanceTable like below. I want to take min Balance but if the days in the uninterrupted holiday I want to take min Balance in holiday.

Why I want to to that?

Because, I will make calculation based on min Balance but holidays take into account different than other days. If 18.11.2022-19.11.2022-20.11.2022-21.11.2022 are holiday for me they ara not seperate for days instead they are like 1 super day lasted 96 hours and has power of the 4 days .

Oracle version :21c

CREATE TABLE BalanceTable(

Balance_Day DATE,

Balance NUMERIC(6, 2)

);

INSERT INTO BalanceTable (Balance_Day, Balance) VALUES (TO_DATE('29.11.2022','DD.MM.YYYY'),500);

INSERT INTO BalanceTable (Balance_Day, Balance) VALUES (TO_DATE('28.11.2022','DD.MM.YYYY'),400);

INSERT INTO BalanceTable (Balance_Day, Balance) VALUES (TO_DATE('27.11.2022','DD.MM.YYYY'),425);

INSERT INTO BalanceTable (Balance_Day, Balance) VALUES (TO_DATE('26.11.2022','DD.MM.YYYY'),670);

INSERT INTO BalanceTable (Balance_Day, Balance) VALUES (TO_DATE('25.11.2022','DD.MM.YYYY'),780);

INSERT INTO BalanceTable (Balance_Day, Balance) VALUES (TO_DATE('24.11.2022','DD.MM.YYYY'),355);

INSERT INTO BalanceTable (Balance_Day, Balance) VALUES (TO_DATE('23.11.2022','DD.MM.YYYY'),255);

INSERT INTO BalanceTable (Balance_Day, Balance) VALUES (TO_DATE('22.11.2022','DD.MM.YYYY'),873);

INSERT INTO BalanceTable (Balance_Day, Balance) VALUES (TO_DATE('21.11.2022','DD.MM.YYYY'),990);

INSERT INTO BalanceTable (Balance_Day, Balance) VALUES (TO_DATE('20.11.2022','DD.MM.YYYY'),1020);

INSERT INTO BalanceTable (Balance_Day, Balance) VALUES (TO_DATE('19.11.2022','DD.MM.YYYY'),200);

INSERT INTO BalanceTable (Balance_Day, Balance) VALUES (TO_DATE('18.11.2022','DD.MM.YYYY'),560);

INSERT INTO BalanceTable (Balance_Day, Balance) VALUES (TO_DATE('17.11.2022','DD.MM.YYYY'),1090);

INSERT INTO BalanceTable (Balance_Day, Balance) VALUES (TO_DATE('16.11.2022','DD.MM.YYYY'),129);

CREATE TABLE CalendarTable(

Calendar_Day DATE,

IsHoliday VARCHAR(3)

);

INSERT INTO CalendarTable (Calendar_Day, IsHoliday) VALUES (TO_DATE('29.11.2022','DD.MM.YYYY'),'no');

INSERT INTO CalendarTable (Calendar_Day, IsHoliday) VALUES (TO_DATE('28.11.2022','DD.MM.YYYY'),'yes');

INSERT INTO CalendarTable (Calendar_Day, IsHoliday) VALUES (TO_DATE('27.11.2022','DD.MM.YYYY'),'yes');

INSERT INTO CalendarTable (Calendar_Day, IsHoliday) VALUES (TO_DATE('26.11.2022','DD.MM.YYYY'),'no');

INSERT INTO CalendarTable (Calendar_Day, IsHoliday) VALUES (TO_DATE('25.11.2022','DD.MM.YYYY'),'no');

INSERT INTO CalendarTable (Calendar_Day, IsHoliday) VALUES (TO_DATE('24.11.2022','DD.MM.YYYY'),'yes');

INSERT INTO CalendarTable (Calendar_Day, IsHoliday) VALUES (TO_DATE('23.11.2022','DD.MM.YYYY'),'yes');

INSERT INTO CalendarTable (Calendar_Day, IsHoliday) VALUES (TO_DATE('22.11.2022','DD.MM.YYYY'),'no');

INSERT INTO CalendarTable (Calendar_Day, IsHoliday) VALUES (TO_DATE('21.11.2022','DD.MM.YYYY'),'yes');

INSERT INTO CalendarTable (Calendar_Day, IsHoliday) VALUES (TO_DATE('20.11.2022','DD.MM.YYYY'),'yes');

INSERT INTO CalendarTable (Calendar_Day, IsHoliday) VALUES (TO_DATE('19.11.2022','DD.MM.YYYY'),'yes');

INSERT INTO CalendarTable (Calendar_Day, IsHoliday) VALUES (TO_DATE('18.11.2022','DD.MM.YYYY'),'yes');

INSERT INTO CalendarTable (Calendar_Day, IsHoliday) VALUES (TO_DATE('17.11.2022','DD.MM.YYYY'),'no');

INSERT INTO CalendarTable (Calendar_Day, IsHoliday) VALUES (TO_DATE('16.11.2022','DD.MM.YYYY'),'no');

Frank Kulash · Dec 3, 2022 2:49PM

Hi, @User_ZR3PE

Thanks for posting the CREATE TABLE and INSERT statements. Don't forget to post your full database version (e.g. 18.4.0.0.0) as well.

Assuming CalendarTable has exactly one row per date (Calendar_day is always midnight) and BalanceTable has at least one row per day (Balance_Day is always midnight) then you can do it this way:

SELECT	  c.Min_Calendar_day	AS Balance_day
,	  MIN (b.balance)	AS Min_Balance
,	  CASE
	      WHEN  MIN (c.IsHoliday) = 'yes'
	      THEN  COUNT (*)
	  END			AS How_Many_Days_Holiday_Lasted
FROM	  CalendarTable
MATCH_RECOGNIZE
	  (
	      ORDER BY      calendar_day
	      MEASURES	    MIN (Calendar_Day)    AS Min_Calendar_Day
	      ,     	    COUNT (*)		  AS How_Many_Days
	      ALL ROWS PER MATCH
	      PATTERN       ( Frst Holiday* )
	      DEFINE	    Holiday	    AS   IsHoliday = FIRST (IsHoliday)
	      		    		    AND	 IsHoliday = 'yes'
	  )  		c
JOIN	  BalanceTable  b  ON  b.Balance_Day  = c.Calendar_Day
GROUP BY  c.Min_Calendar_Day
ORDER BY  c.Min_Calendar_Day  DESC
;

mathguy · Dec 3, 2022 2:59PM

Perfect application for MATCH_RECOGNIZE.

select balance_day, min_balance, how_many_days_holiday_lasted
from   (
         select balance_day, balance, isholiday
         from   balancetable join calendartable on balance_day = calendar_day
       )
match_recognize (
  order    by balance_day
  measures first(balance_day)                              as balance_day,
           min(balance)                                    as min_balance,
           case classifier() when 'YES' then count(*) end  as how_many_days_holiday_lasted
  pattern  ( YES+ | NO )
  define   YES as isholiday = 'yes', NO as isholiday = 'no'
)
order by balance_day desc
;


BALANCE_DAY MIN_BALANCE HOW_MANY_DAYS_HOLIDAY_LASTED
----------- ----------- ----------------------------
29.11.2022          500                             
27.11.2022          400                            2
26.11.2022          670                             
25.11.2022          780                             
23.11.2022          255                            2
22.11.2022          873                             
18.11.2022          200                            4
17.11.2022         1090                             
16.11.2022          129

Frank Kulash · Dec 3, 2022 2:49PM

Hi, @User_ZR3PE

Thanks for posting the CREATE TABLE and INSERT statements. Don't forget to post your full database version (e.g. 18.4.0.0.0) as well.

Assuming CalendarTable has exactly one row per date (Calendar_day is always midnight) and BalanceTable has at least one row per day (Balance_Day is always midnight) then you can do it this way:

SELECT	  c.Min_Calendar_day	AS Balance_day
,	  MIN (b.balance)	AS Min_Balance
,	  CASE
	      WHEN  MIN (c.IsHoliday) = 'yes'
	      THEN  COUNT (*)
	  END			AS How_Many_Days_Holiday_Lasted
FROM	  CalendarTable
MATCH_RECOGNIZE
	  (
	      ORDER BY      calendar_day
	      MEASURES	    MIN (Calendar_Day)    AS Min_Calendar_Day
	      ,     	    COUNT (*)		  AS How_Many_Days
	      ALL ROWS PER MATCH
	      PATTERN       ( Frst Holiday* )
	      DEFINE	    Holiday	    AS   IsHoliday = FIRST (IsHoliday)
	      		    		    AND	 IsHoliday = 'yes'
	  )  		c
JOIN	  BalanceTable  b  ON  b.Balance_Day  = c.Calendar_Day
GROUP BY  c.Min_Calendar_Day
ORDER BY  c.Min_Calendar_Day  DESC
;

mathguy · Dec 3, 2022 2:59PM

Perfect application for MATCH_RECOGNIZE.

select balance_day, min_balance, how_many_days_holiday_lasted
from   (
         select balance_day, balance, isholiday
         from   balancetable join calendartable on balance_day = calendar_day
       )
match_recognize (
  order    by balance_day
  measures first(balance_day)                              as balance_day,
           min(balance)                                    as min_balance,
           case classifier() when 'YES' then count(*) end  as how_many_days_holiday_lasted
  pattern  ( YES+ | NO )
  define   YES as isholiday = 'yes', NO as isholiday = 'no'
)
order by balance_day desc
;


BALANCE_DAY MIN_BALANCE HOW_MANY_DAYS_HOLIDAY_LASTED
----------- ----------- ----------------------------
29.11.2022          500                             
27.11.2022          400                            2
26.11.2022          670                             
25.11.2022          780                             
23.11.2022          255                            2
22.11.2022          873                             
18.11.2022          200                            4
17.11.2022         1090                             
16.11.2022          129

mathguy · Dec 3, 2022 3:22PM

Comparing the two MATCH_RECOGNIZE solutions: Both join the two tables on date, and both use MATCH_RECOGNIZE to identify the "super-days" (multi-day holidays).

The difference is in how they find the minimum balance. Mr. Kulash uses MATCH_RECOGNIZE on just the calendar table, to identify the holidays; then he joins to the balances table, and uses a further GROUP BY operation to get the minimum balances.

If we join first and only then apply MATCH_RECOGNIZE, we don't need to aggregate at the end. We can get the minimum balances in the same MATCH_RECOGNIZE pass where we identify the holidays. To be able to do that, though, we must join first - not after MATCH_RECOGNIZE, but before it.

User_ZR3PE · Dec 3, 2022 3:29PM

https://community.oracle.com/tech/developers/discussion/comment/16855540#Comment_16855540

Perfect 👏 thank you very much 👍️

User_ZR3PE · Dec 3, 2022 3:35PM

https://community.oracle.com/tech/developers/discussion/comment/16855543#Comment_16855543

Thanks for explanition 👍️

Rocky · Dec 12, 2022 6:27PM

Hi User,

I have also tried it.

select BALANCE_DAy,

BALANCE--,

-- ISHOLIDAY,

-- mn,

-- fltr

from (

select BALANCE_DAy,

BALANCE,

ISHOLIDAY,

(case when min(BALANCE_DAY) over (partition by GRP) = BALANCE_DAY then 'Y' end) fltr,

min(BALANCE_DAY) over (partition by GRP) mn From (

select BALANCE_DAy, BALANCE,ISHOLIDAY,

(case when rwnm is null then

lag(RWNM) ignore nulls over (order by BALANCE_DAy)

else

rwnm

end) grp

from (

select BALANCE_DAy, BALANCE,ISHOLIDAY,

(case when lag(ISHOLIDAY) over (order by BALANCE_DAy) = 'yes' AND ISHOLIDAY = 'yes' then

null

else

row_number() over (order by BALANCE_DAy)

end) rwnm

from (

select BALANCE_DAy, BALANCE,ISHOLIDAY

from BalanceTable b, CalendarTable c where c.CALENDAR_DAY = b.BALANCE_DAY

)

where fltr = 'Y';

BluShadow · Dec 13, 2022 8:51AM

Or using tabibitosan method (community document https://community.oracle.com/tech/developers/discussion/4417554/pl-sql-101-grouping-sequence-ranges-(tabibitosan-method))

SQL> with t as (
  2  select b.balance_day
  3        ,b.balance
  4        ,c.isholiday
  5        ,b.balance_day-row_number() over (partition by c.isholiday order by b.balance_day) as grp
  6  from   balancetable b
  7         join calendartable c on (c.calendar_day = b.balance_day)
  8  )
  9  select min(balance_day) as balance_day
 10        ,min(balance) as min_balance
 11        ,case when min(isholiday) = 'yes' then count(*) else null end as howmanydays
 12  from   t
 13  group by grp, case when isholiday = 'yes' then grp else balance_day end
 14  order by 1 desc
 15  /

BALANCE_DAY          MIN_BALANCE HOWMANYDAYS
-------------------- ----------- -----------
29-NOV-2022 00:00:00         500
27-NOV-2022 00:00:00         400           2
26-NOV-2022 00:00:00         670
25-NOV-2022 00:00:00         780
23-NOV-2022 00:00:00         255           2
22-NOV-2022 00:00:00         873
18-NOV-2022 00:00:00         200           4
17-NOV-2022 00:00:00        1090
16-NOV-2022 00:00:00         129

9 rows selected.

The subquery factoring determines 'groups' of date ranges based on whether the date is a holiday or not.

The main query then aggregates the groups, but keeps the non-holiday groups separate by grouping those on their own balance day too.

Forum Stats

Quick Links

Categories

Group uninterrupted days for min Balance?

Best Answers

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

Group uninterrupted days for min Balance?

Best Answers

Answers