This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,961 Users
  • 2,269,775 Discussions
  • 7,916,823 Comments

Discussions

Group uninterrupted days for min Balance?

User_ZR3PE
User_ZR3PE Member Posts: 26 Green Ribbon

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');

Tagged:

Best Answers

  • Frank Kulash
    Frank Kulash Boston, USAMember, Moderator Posts: 43,002 Red Diamond
    Answer ✓

    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
    ;
    
    User_ZR3PE
  • mathguy
    mathguy Member Posts: 11,041 Black Diamond
    edited Dec 3, 2022 3:14PM Answer ✓

    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  
    
    User_ZR3PE

Answers

  • Frank Kulash
    Frank Kulash Boston, USAMember, Moderator Posts: 43,002 Red Diamond
    Answer ✓

    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
    ;
    
    User_ZR3PE
  • mathguy
    mathguy Member Posts: 11,041 Black Diamond
    edited Dec 3, 2022 3:14PM Answer ✓

    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  
    
    User_ZR3PE
  • mathguy
    mathguy Member Posts: 11,041 Black Diamond

    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
  • Rocky
    Rocky Member Posts: 197 Bronze Badge

    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
    BluShadow Mr UKMember, Moderator Posts: 42,706 Red Diamond

    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.