1 2 Previous Next 16 Replies Latest reply: Jan 7, 2013 9:41 AM by BluShadow RSS

    analytical function on multiple columns

    user10403630
      HI,

      I have data like below.

      with t as
      (select 'A' as PROMO_ID , to_date('9/15/2012','MM/DD/YYYY') promo_start_dt,to_date('9/22/2012','MM/DD/YYYY') promo_end_dt , 10 as qty from dual
      UNION ALL
      select 'A' as PROMO_ID , to_date('9/16/2012','MM/DD/YYYY') promo_start_dt,to_date('9/18/2012','MM/DD/YYYY') promo_end_dt, 20 as qty from dual
      UNION ALL
      select 'A' as PROMO_ID , to_date('9/24/2012','MM/DD/YYYY') promo_start_dt,to_date('9/28/2012','MM/DD/YYYY') promo_end_dt, 30 as qty from dual
      )
      select * from t

      Now I need to aggregate qty group by startdate and enddate something like below. I need to aggregate by start and end date ranges.
      If we see, for first record promo start date is 9/15/2012 and end date is 9/22/2012 and for the second record promo start date is 9/16/2012 and promo end date is 9/18/2012.
      date range 9/16/2012 to 9/18/2012 will fall in between 9/15/2012 and 9/22/2012. So I need to aggregate the qty for these two.
      And the third record start and end dates will be after the above mentioned dates.. so it should be different record.


      PROMO_ID PROMO_START_DT PROMO_END_DT QTY
      -------------------------------------------------------------------------------------------------
      A 9/15/2012 9/22/2012 30 -- (10+20)
      A 9/24/2012 9/28/2012 30


      Please help.

      Thanks

      Edited by: user10403630 on Jan 7, 2013 1:25 AM

      Edited by: user10403630 on Jan 7, 2013 1:25 AM

      Edited by: user10403630 on Jan 7, 2013 1:32 AM
        • 1. Re: analytical function on multiple columns
          Manik
          Can you explain in detail how did u combine first two rows.. based on what criteria..? How did u come to those dates.?

          Cheers,
          Manik.
          • 2. Re: analytical function on multiple columns
            BluShadow
            user10403630 wrote:
            HI,

            I have data like below.

            with t as
            (select 'A' as PROMO_ID , to_date('9/15/2012','MM/DD/YYYY') promo_start_dt,to_date('9/22/2012','MM/DD/YYYY') promo_end_dt , 10 as qty from dual
            UNION ALL
            select 'A' as PROMO_ID , to_date('9/16/2012','MM/DD/YYYY') promo_start_dt,to_date('9/18/2012','MM/DD/YYYY') promo_end_dt, 20 as qty from dual
            UNION ALL
            select 'A' as PROMO_ID , to_date('9/24/2012','MM/DD/YYYY') promo_start_dt,to_date('9/28/2012','MM/DD/YYYY') promo_end_dt, 30 as qty from dual
            )
            select * from t

            Now I need to aggregate qty group by startdate and enddate something like below. I need to aggregate by start and end date ranges.

            PROMO_ID PROMO_START_DT PROMO_END_DT QTY
            -------------------------------------------------------------------------------------------------
            A 9/15/2012 9/12/2012 30 -- (10+20)
            A 9/24/2012 9/28/2012 30


            Please help.

            Thanks,
            Ravi.
            I assume the PROMO_END_DT on the first row of the results should actually be 9/22/2012 rather than 9/12/2012?

            You need to explain the logic more clearly as there are many ways the data could be aggregated.
            Do you want it aggregated where a who date range of one row exists within another date range of another row? Or do you want it where they overlap in any way? Or where they overlap or are contiguous e.g. where the start date of one row follows on (1 day after) the end date of another row?

            Be clear in your requirements so that people can help you properly.

            Also, make sure you post your code and date using {noformat}
            {noformat} tags, so people can read it properly.
            
            See: {message:id=9360002}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
            • 3. Re: analytical function on multiple columns
              user10403630
              If we see, for first record promo start date is 9/15/2012 and end date is 9/22/2012 and for the second record promo start date is 9/16/2012 and promo end date is 9/18/2012.
              date range 9/16/2012 to 9/18/2012 will fall in between 9/15/2012 and 9/22/2012. So I need to aggregate the qty for these two.
              And the third record start and end dates will be after the above mentioned dates.. so it should be different record.

              Thanks
              • 4. Re: analytical function on multiple columns
                user10403630
                Yes, you are right it should be 9/22/2012. Also I have updated the criteria for aggregation.

                Thanks.
                • 5. Re: analytical function on multiple columns
                  BluShadow
                  Perhaps you don't really need analytical functions for this?
                  SQL> ed
                  Wrote file afiedt.buf
                  
                    1  with t as (select 'A' as PROMO_ID , to_date('9/15/2012','MM/DD/YYYY') promo_start_dt,to_date('9/22/2012','MM/DD/YYYY') promo_end_dt , 10 as qty from dual UNION ALL
                    2             select 'A' as PROMO_ID , to_date('9/16/2012','MM/DD/YYYY') promo_start_dt,to_date('9/18/2012','MM/DD/YYYY') promo_end_dt, 20 as qty from dual UNION ALL
                    3             select 'A' as PROMO_ID , to_date('9/24/2012','MM/DD/YYYY') promo_start_dt,to_date('9/28/2012','MM/DD/YYYY') promo_end_dt, 30 as qty from dual
                    4            )
                    5  select distinct promo_id
                    6        ,promo_start_dt
                    7        ,promo_end_dt
                    8        ,qty
                    9  from (
                   10        select root_start_dt
                   11              ,promo_id
                   12              ,min(promo_start_dt) as promo_start_dt
                   13              ,max(promo_end_dt) as promo_end_dt
                   14              ,sum(qty) as qty
                   15        from (
                   16              select connect_by_root(promo_start_dt) as root_start_dt
                   17                    ,promo_id
                   18                    ,promo_start_dt
                   19                    ,promo_end_dt
                   20                    ,qty
                   21              from t
                   22              connect by nocycle promo_id = prior promo_id
                   23                     and promo_start_dt <= prior promo_end_dt + 1
                   24                     and promo_end_dt >= prior promo_start_dt - 1
                   25             )
                   26        group by root_start_dt, promo_id
                   27       )
                   28* order by promo_id, promo_start_dt
                  SQL> /
                  
                  P PROMO_START_DT       PROMO_END_DT                QTY
                  - -------------------- -------------------- ----------
                  A 15-SEP-2012 00:00:00 22-SEP-2012 00:00:00         30
                  A 24-SEP-2012 00:00:00 28-SEP-2012 00:00:00         30
                  
                  SQL>
                  • 6. Re: analytical function on multiple columns
                    Manik
                    Another way:

                    Check this :
                    /* Formatted on 1/7/2013 5:48:39 PM (QP5 v5.163.1008.3004) */
                    with t as (select 'A' as PROMO_ID , to_date('9/15/2012','MM/DD/YYYY') promo_start_dt,to_date('9/22/2012','MM/DD/YYYY') promo_end_dt , 10 as qty from dual UNION ALL
                                   select 'A' as PROMO_ID , to_date('9/16/2012','MM/DD/YYYY') promo_start_dt,to_date('9/18/2012','MM/DD/YYYY') promo_end_dt, 20 as qty from dual UNION ALL
                                   select 'A' as PROMO_ID , to_date('9/24/2012','MM/DD/YYYY') promo_start_dt,to_date('9/28/2012','MM/DD/YYYY') promo_end_dt, 30 as qty from dual
                                  ),
                         t1 AS
                            (SELECT PROMO_ID,
                                    CASE
                                       WHEN LAG (
                                               promo_start_dt,
                                               1,
                                               NULL)
                                            OVER (PARTITION BY promo_id
                                                  ORDER BY promo_start_dt ASC) < promo_start_dt
                                            AND LAG (promo_end_dt, 1, NULL)
                                                   OVER (PARTITION BY promo_id ORDER BY NULL) >
                                                   promo_end_dt THEN
                                          LAG (
                                             promo_start_dt,
                                             1,
                                             NULL)
                                          OVER (PARTITION BY promo_id
                                                ORDER BY promo_start_dt ASC)
                                       ELSE
                                          promo_start_dt
                                    END
                                       AS PROMO_START_DT,
                                    CASE
                                       WHEN LAG (
                                               promo_start_dt,
                                               1,
                                               NULL)
                                            OVER (PARTITION BY promo_id
                                                  ORDER BY promo_start_dt ASC) < promo_start_dt
                                            AND LAG (promo_end_dt, 1, NULL)
                                                   OVER (PARTITION BY promo_id ORDER BY NULL) >
                                                   promo_end_dt THEN
                                          LAG (promo_end_dt, 1, NULL)
                                             OVER (PARTITION BY promo_id ORDER BY NULL)
                                       ELSE
                                          promo_end_dt
                                    END
                                       AS PROMO_END_DT,
                                    qty
                               FROM t)
                    SELECT DISTINCT
                           PROMO_ID,
                           PROMO_START_DT,
                           PROMO_END_DT,
                           SUM (QTY) OVER (PARTITION BY promo_id, promo_start_dt, promo_end_dt)
                              qty
                      FROM t1;
                    output:
                    PROMO_ID     PROMO_START_DT     PROMO_END_DT     QTY
                    A     9/15/2012     9/22/2012     30
                    A     9/24/2012     9/28/2012     30
                    Cheers,
                    Manik.
                    • 7. Re: analytical function on multiple columns
                      BluShadow
                      Or another way using analytical functions... (I added some more data for demonstration purposes)...
                      SQL> ed
                      Wrote file afiedt.buf
                      
                        1  with t as (select 'A' as PROMO_ID , to_date('9/15/2012','MM/DD/YYYY') promo_start_dt,to_date('9/22/2012','MM/DD/YYYY') promo_end_dt , 10 as qty from dual UNION ALL
                        2             select 'A' as PROMO_ID , to_date('9/16/2012','MM/DD/YYYY') promo_start_dt,to_date('9/18/2012','MM/DD/YYYY') promo_end_dt, 20 as qty from dual UNION ALL
                        3             select 'A' as PROMO_ID , to_date('9/24/2012','MM/DD/YYYY') promo_start_dt,to_date('9/28/2012','MM/DD/YYYY') promo_end_dt, 30 as qty from dual union all
                        4             select 'A' as PROMO_ID , to_date('10/21/2012','MM/DD/YYYY') promo_start_dt,to_date('10/28/2012','MM/DD/YYYY') promo_end_dt, 10 as qty from dual union all
                        5             select 'B' as PROMO_ID , to_date('9/24/2012','MM/DD/YYYY') promo_start_dt,to_date('10/28/2012','MM/DD/YYYY') promo_end_dt, 15 as qty from dual union all
                        6             select 'A' as PROMO_ID , to_date('11/10/2012','MM/DD/YYYY') promo_start_dt,to_date('11/15/2012','MM/DD/YYYY') promo_end_dt, 20 as qty from dual union all
                        7             select 'A' as PROMO_ID , to_date('11/16/2012','MM/DD/YYYY') promo_start_dt,to_date('11/20/2012','MM/DD/YYYY') promo_end_dt, 25 as qty from dual
                        8            )
                        9  --
                       10  select promo_id
                       11        ,min(promo_start_dt) as promo_start_dt
                       12        ,max(promo_end_dt) as promo_end_dt
                       13        ,sum(qty) as qty
                       14  from (
                       15        select promo_id
                       16              ,promo_start_dt
                       17              ,promo_end_dt
                       18              ,qty
                       19              ,sum(follows) over (partition by promo_id order by promo_start_dt) as grp
                       20        from (
                       21              select promo_id
                       22                    ,promo_start_dt
                       23                    ,promo_end_dt
                       24                    ,qty
                       25                    ,case when promo_start_dt <= lag(promo_end_dt+1) over (partition by promo_id order by promo_start_dt)
                       26                          then 0
                       27                          else 1
                       28                     end as follows
                       29              from t
                       30             )
                       31       )
                       32  group by promo_id, grp
                       33* order by 1, 2
                      SQL> /
                      
                      P PROMO_START_DT       PROMO_END_DT                QTY
                      - -------------------- -------------------- ----------
                      A 15-SEP-2012 00:00:00 22-SEP-2012 00:00:00         30
                      A 24-SEP-2012 00:00:00 28-SEP-2012 00:00:00         30
                      A 21-OCT-2012 00:00:00 28-OCT-2012 00:00:00         10
                      A 10-NOV-2012 00:00:00 20-NOV-2012 00:00:00         45
                      B 24-SEP-2012 00:00:00 28-OCT-2012 00:00:00         15
                      
                      SQL>
                      • 8. Re: analytical function on multiple columns
                        838196
                        Hi,
                        what about a promo from 9/19/2012 til 9/21/2012 and another from 9/26/2012 til 9/30/2012?
                        How should these records be included into the aggregation?
                        • 9. Re: analytical function on multiple columns
                          BluShadow
                          OlafGerike wrote:
                          Hi,
                          what about a promo from 9/19/2012 til 9/21/2012 and another from 9/26/2012 til 9/30/2012?
                          How should these records be included into the aggregation?
                          I've already asked that of the OP.

                          In my solutions, I've assumed that if one promotion follows another such that the start date of one is 1 day after the end date of another, then they are also aggregated as the same promotion.
                          That can be easily changed however if that's not the case.
                          • 10. Re: analytical function on multiple columns
                            838196
                            >
                            BluShadow wrote:
                            In my solutions, I've assumed that if one promotion follows another such that the start date of one is 1 day after the end date of another, then they are also aggregated as the same promotion
                            >
                            There is a gap in your solution. It depends on the fact, that only the next or previous promo is included into the aggregation. But this is unknown. They can stand alone but both be included into a third promo.
                            Maybe a hierachical query is needed like your first suggestion, but this could be very slow, because of the missing root.
                            I guess, the answer to this question is:
                            Please review your requirements.
                            • 11. Re: analytical function on multiple columns
                              chris227
                              If you are looking for some weekly grouping consider:
                              with data as (
                              select 'A' as PROMO_ID , to_date('9/15/2012','MM/DD/YYYY') promo_start_dt,to_date('9/22/2012','MM/DD/YYYY') promo_end_dt,10 as qty from dual UNION ALL
                              select 'A' as PROMO_ID , to_date('9/16/2012','MM/DD/YYYY') promo_start_dt,to_date('9/18/2012','MM/DD/YYYY') promo_end_dt, 20 as qty from dual UNION ALL
                              select 'A' as PROMO_ID , to_date('9/24/2012','MM/DD/YYYY') promo_start_dt,to_date('9/28/2012','MM/DD/YYYY') promo_end_dt, 30 as qty from dual
                              )
                              
                              select
                               PROMO_ID
                              ,trunc(promo_start_dt + 2, 'IW') - 2 PROMO_START_DT
                              ,trunc(promo_start_dt + 2, 'IW') + 4 PROMO_END_DT
                              ,sum(qty) qty
                              from data
                              group by 
                               promo_id
                              ,trunc(promo_start_dt + 2, 'IW')
                              
                              PROMO_ID PROMO_START_DT PROMO_END_DT QTY 
                              A 09/15/2012 09/21/2012 30 
                              A 09/22/2012 09/28/2012 30
                              Notice: week here starts at saturday
                              • 12. Re: analytical function on multiple columns
                                BluShadow
                                OlafGerike wrote:
                                >
                                BluShadow wrote:
                                In my solutions, I've assumed that if one promotion follows another such that the start date of one is 1 day after the end date of another, then they are also aggregated as the same promotion
                                >
                                There is a gap in your solution. It depends on the fact, that only the next or previous promo is included into the aggregation. But this is unknown. They can stand alone but both be included into a third promo.
                                Please demonstrate what you mean. It doesn't just cater for the next or previous, but groups things regardless of how many promo's overlap (or are contiguous).
                                SQL> ed
                                Wrote file afiedt.buf
                                
                                  1  with t as (select 'A' as PROMO_ID , to_date('9/15/2012','MM/DD/YYYY') promo_start_dt,to_date('9/22/2012','MM/DD/YYYY') promo_end_dt , 10 as qty from dual UNION ALL
                                  2             select 'A' as PROMO_ID , to_date('9/16/2012','MM/DD/YYYY') promo_start_dt,to_date('9/18/2012','MM/DD/YYYY') promo_end_dt, 20 as qty from dual UNION ALL
                                  3             select 'A' as PROMO_ID , to_date('9/17/2012','MM/DD/YYYY') promo_start_dt,to_date('9/21/2012','MM/DD/YYYY') promo_end_dt, 20 as qty from dual UNION ALL
                                  4             select 'A' as PROMO_ID , to_date('9/24/2012','MM/DD/YYYY') promo_start_dt,to_date('9/28/2012','MM/DD/YYYY') promo_end_dt, 30 as qty from dual union all
                                  5             select 'A' as PROMO_ID , to_date('10/21/2012','MM/DD/YYYY') promo_start_dt,to_date('10/28/2012','MM/DD/YYYY') promo_end_dt, 10 as qty from dual union all
                                  6             select 'B' as PROMO_ID , to_date('9/24/2012','MM/DD/YYYY') promo_start_dt,to_date('10/28/2012','MM/DD/YYYY') promo_end_dt, 15 as qty from dual union all
                                  7             select 'C' as PROMO_ID , to_date('8/01/2012','MM/DD/YYYY') promo_start_dt,to_date('8/10/2012','MM/DD/YYYY') promo_end_dt, 1 as qty from dual union all
                                  8             select 'C' as PROMO_ID , to_date('8/11/2012','MM/DD/YYYY') promo_start_dt,to_date('8/15/2012','MM/DD/YYYY') promo_end_dt, 2 as qty from dual union all
                                  9             select 'C' as PROMO_ID , to_date('8/14/2012','MM/DD/YYYY') promo_start_dt,to_date('8/15/2012','MM/DD/YYYY') promo_end_dt, 3 as qty from dual union all
                                 10             select 'C' as PROMO_ID , to_date('8/13/2012','MM/DD/YYYY') promo_start_dt,to_date('8/20/2012','MM/DD/YYYY') promo_end_dt, 4 as qty from dual union all
                                 11             select 'C' as PROMO_ID , to_date('9/24/2012','MM/DD/YYYY') promo_start_dt,to_date('10/28/2012','MM/DD/YYYY') promo_end_dt, 5 as qty from dual union all
                                 12             select 'A' as PROMO_ID , to_date('11/10/2012','MM/DD/YYYY') promo_start_dt,to_date('11/15/2012','MM/DD/YYYY') promo_end_dt, 20 as qty from dual union all
                                 13             select 'A' as PROMO_ID , to_date('11/16/2012','MM/DD/YYYY') promo_start_dt,to_date('11/20/2012','MM/DD/YYYY') promo_end_dt, 25 as qty from dual
                                 14            )
                                 15  --
                                 16  select promo_id
                                 17        ,min(promo_start_dt) as promo_start_dt
                                 18        ,max(promo_end_dt) as promo_end_dt
                                 19        ,sum(qty) as qty
                                 20  from (
                                 21        select promo_id
                                 22              ,promo_start_dt
                                 23              ,promo_end_dt
                                 24              ,qty
                                 25              ,sum(follows) over (partition by promo_id order by promo_start_dt) as grp
                                 26        from (
                                 27              select promo_id
                                 28                    ,promo_start_dt
                                 29                    ,promo_end_dt
                                 30                    ,qty
                                 31                    ,case when promo_start_dt <= lag(promo_end_dt+1) over (partition by promo_id order by promo_start_dt)
                                 32                          then 0
                                 33                          else 1
                                 34                     end as follows
                                 35              from t
                                 36             )
                                 37       )
                                 38  group by promo_id, grp
                                 39* order by 1, 2
                                SQL> /
                                
                                P PROMO_START_DT       PROMO_END_DT                QTY
                                - -------------------- -------------------- ----------
                                A 15-SEP-2012 00:00:00 22-SEP-2012 00:00:00         50
                                A 24-SEP-2012 00:00:00 28-SEP-2012 00:00:00         30
                                A 21-OCT-2012 00:00:00 28-OCT-2012 00:00:00         10
                                A 10-NOV-2012 00:00:00 20-NOV-2012 00:00:00         45
                                B 24-SEP-2012 00:00:00 28-OCT-2012 00:00:00         15
                                C 01-AUG-2012 00:00:00 20-AUG-2012 00:00:00         10
                                C 24-SEP-2012 00:00:00 28-OCT-2012 00:00:00          5
                                I guess, the answer to this question is:
                                Please review your requirements.
                                Absolutely. Until the OP clarifies the requirements we won't know for sure.

                                Edited by: BluShadow on 07-Jan-2013 13:39
                                • 13. Re: analytical function on multiple columns
                                  838196
                                  >
                                  Please demonstrate what you mean. It doesn't just cater for the next or previous, but groups things regardless of how many promo's overlap (or are contiguous).
                                  >
                                  SQL> r
                                    1   with t as (select 'A' as PROMO_ID , to_date('9/15/2012','MM/DD/YYYY') promo_start_dt,to_date('9/22/2012','MM/DD/YYYY') promo_end_dt , 10 as qty from dual UNION ALL
                                    2              select 'A' as promo_id , to_date('9/16/2012','MM/DD/YYYY') promo_start_dt,to_date('9/17/2012','MM/DD/YYYY') promo_end_dt, 20 as qty from dual union all
                                    3              select 'A' as PROMO_ID , to_date('9/19/2012','MM/DD/YYYY') promo_start_dt,to_date('9/21/2012','MM/DD/YYYY') promo_end_dt, 20 as qty from dual
                                    4             )
                                    5   --
                                    6   select promo_id
                                    7         ,min(promo_start_dt) as promo_start_dt
                                    8         ,max(promo_end_dt) as promo_end_dt
                                    9         ,sum(qty) as qty
                                   10   from (
                                   11         select promo_id
                                   12               ,promo_start_dt
                                   13               ,promo_end_dt
                                   14               ,qty
                                   15               ,sum(follows) over (partition by promo_id order by promo_start_dt) as grp
                                   16         from (
                                   17               select promo_id
                                   18                     ,promo_start_dt
                                   19                     ,promo_end_dt
                                   20                     ,qty
                                   21                     ,case when promo_start_dt <= lag(promo_end_dt+1) over (partition by promo_id order by promo_start_dt)
                                   22                           then 0
                                   23                           else 1
                                   24                      end as follows
                                   25               from t
                                   26              )
                                   27        )
                                   28   group by promo_id, grp
                                   29   order by 1, 2
                                   30*
                                  
                                  P PROMO_STAR PROMO_END_        QTY
                                  - ---------- ---------- ----------
                                  A 15.09.2012 22.09.2012         30
                                  A 19.09.2012 21.09.2012         20
                                  • 14. Re: analytical function on multiple columns
                                    BluShadow
                                    Ah, ok, I see what you mean now.
                                    1 2 Previous Next