6 Replies Latest reply: Feb 20, 2013 8:51 AM by petez RSS

    Aggregation query

    petez
      Hi Guys,

      I need your help with aggregation query.

      Sample input query is:
      SELECT * FROM 
      (
      SELECT DATE'2013-02-10' AS date_day , 1 AS prod_id , 2 AS prod_val FROM DUAL UNION
      SELECT DATE'2013-02-11' AS date_day , 1 AS prod_id , 4 AS prod_val FROM DUAL UNION
      SELECT DATE'2013-02-12' AS date_day , 1 AS prod_id , 6 AS prod_val FROM DUAL UNION
      SELECT DATE'2013-02-15' AS date_day , 1 AS prod_id , 12 AS prod_val FROM DUAL UNION
      SELECT DATE'2013-02-16' AS date_day , 1 AS prod_id , 14 AS prod_val FROM DUAL UNION
      SELECT DATE'2013-02-17' AS date_day , 1 AS prod_id , 16 AS prod_val FROM DUAL UNION
      SELECT DATE'2013-02-18' AS date_day , 1 AS prod_id , 18 AS prod_val FROM DUAL UNION
      SELECT DATE'2013-02-19' AS date_day , 1 AS prod_id , 20 AS prod_val FROM DUAL UNION
      SELECT DATE'2013-02-20' AS date_day , 1 AS prod_id , 22 AS prod_val FROM DUAL UNION
      SELECT DATE'2013-02-14' AS date_day , 2 AS prod_id , 1 AS prod_val FROM DUAL UNION
      SELECT DATE'2013-02-15' AS date_day , 2 AS prod_id , 2 AS prod_val FROM DUAL UNION
      SELECT DATE'2013-02-16' AS date_day , 2 AS prod_id , 3 AS prod_val FROM DUAL UNION
      SELECT DATE'2013-02-19' AS date_day , 2 AS prod_id , 6 AS prod_val FROM DUAL UNION
      SELECT DATE'2013-02-20' AS date_day , 2 AS prod_id , 12 AS prod_val FROM DUAL 
      ) input_set 
      ORDER BY date_day, prod_id
      When I run aggr query for date 2/18/2013 I expect:
      SELECT DATE'2013-02-18' AS date_day , 21 AS prod_val FROM DUAL UNION
      SELECT DATE'2013-02-19' AS date_day , 26 AS prod_val FROM DUAL UNION
      SELECT DATE'2013-02-20' AS date_day , 34 AS prod_val FROM DUAL 
      When I run aggr query for date 2/12/2013 I expect:
      SELECT DATE'2013-02-12' AS date_day , 6  AS prod_val FROM DUAL UNION
      SELECT DATE'2013-02-13' AS date_day , 6  AS prod_val FROM DUAL UNION
      SELECT DATE'2013-02-14' AS date_day , 7  AS prod_val FROM DUAL UNION
      SELECT DATE'2013-02-15' AS date_day , 14 AS prod_val FROM DUAL UNION
      SELECT DATE'2013-02-16' AS date_day , 17 AS prod_val FROM DUAL UNION
      SELECT DATE'2013-02-17' AS date_day , 19 AS prod_val FROM DUAL UNION
      SELECT DATE'2013-02-18' AS date_day , 21 AS prod_val FROM DUAL UNION
      SELECT DATE'2013-02-19' AS date_day , 26 AS prod_val FROM DUAL UNION
      SELECT DATE'2013-02-20' AS date_day , 34 AS prod_val FROM DUAL
      My proposition is :
      WITH report_dates AS
      (
           SELECT     DATE'2013-02-10' AS min_date,
                     DATE'2013-02-20' AS max_date
           FROM     dual
      ),
      execution_data AS
      (
           SELECT DATE'2013-02-10' AS date_day , 1 AS prod_id , 2 AS prod_val FROM DUAL UNION
           SELECT DATE'2013-02-11' AS date_day , 1 AS prod_id , 4 AS prod_val FROM DUAL UNION
           SELECT DATE'2013-02-12' AS date_day , 1 AS prod_id , 6 AS prod_val FROM DUAL UNION
           SELECT DATE'2013-02-15' AS date_day , 1 AS prod_id , 12 AS prod_val FROM DUAL UNION
           SELECT DATE'2013-02-16' AS date_day , 1 AS prod_id , 14 AS prod_val FROM DUAL UNION
           SELECT DATE'2013-02-17' AS date_day , 1 AS prod_id , 16 AS prod_val FROM DUAL UNION
           SELECT DATE'2013-02-18' AS date_day , 1 AS prod_id , 18 AS prod_val FROM DUAL UNION
           SELECT DATE'2013-02-19' AS date_day , 1 AS prod_id , 20 AS prod_val FROM DUAL UNION
           SELECT DATE'2013-02-20' AS date_day , 1 AS prod_id , 22 AS prod_val FROM DUAL UNION
           SELECT DATE'2013-02-14' AS date_day , 2 AS prod_id , 1 AS prod_val FROM DUAL UNION
           SELECT DATE'2013-02-15' AS date_day , 2 AS prod_id , 2 AS prod_val FROM DUAL UNION
           SELECT DATE'2013-02-16' AS date_day , 2 AS prod_id , 3 AS prod_val FROM DUAL UNION
           SELECT DATE'2013-02-19' AS date_day , 2 AS prod_id , 6 AS prod_val FROM DUAL UNION
           SELECT DATE'2013-02-20' AS date_day , 2 AS prod_id , 12 AS prod_val FROM DUAL         
      ),
      execution_data_range AS
      (
           SELECT     MIN(exdata.date_day)     AS min_date,
                     MAX(exdata.date_day)     AS max_date
           FROM     execution_data exdata
      ),          
      per_group_date_range AS
      (
           SELECT     DISTINCT prod_id
           FROM     execution_data exdt
      ),
      day_time_frame AS
      (
           SELECT     td.date_day,
                     grp.prod_id
           FROM     (SELECT dx AS date_day FROM ( SELECT trunc(SYSDATE - LEVEL + 1) AS dx FROM dual CONNECT BY LEVEL <= 11  ) ORDER BY dx) td,
                     per_group_date_range      grp,
                     execution_data_range     exdates,
                     report_dates               repdates
           WHERE     td.date_day BETWEEN LEAST( COALESCE( exdates.min_date, repdates.min_date), repdates.min_date ) AND repdates.max_date
      ),
      no_gaps_execution_data AS
      (
           SELECT      dtf.date_day,
                     dtf.prod_id,
                     LAST_VALUE(ex.prod_val IGNORE NULLS)
                     OVER (PARTITION BY dtf.prod_id  ORDER BY dtf.date_day) AS prod_val
           FROM      day_time_frame      dtf,
                     execution_data      ex
           WHERE      dtf.date_day      = ex.date_day     (+)
           AND          dtf.prod_id          = ex.prod_id    (+)
      ),
      aggregated_data AS
      (
           SELECT     aggrdt.date_day,
                     SUM(aggrdt.prod_val) AS prod_val
           FROM     no_gaps_execution_data aggrdt
           GROUP BY aggrdt.date_day
      )
      -- main query
      SELECT     aggr.date_day AS day_date,
                aggr.prod_val
      FROM     aggregated_data          aggr,
                report_dates          repdates
      WHERE     aggr.date_day BETWEEN repdates.min_date AND repdates.max_date 
      ORDER BY aggr.date_day
      It looks for me too complicated.
      Any ideas ?

      Thanks,
      Piotr
        • 1. Re: Aggregation query
          BluShadow
          petez wrote:
          Hi Guys,

          I need your help with aggregation query.
          When I run aggr query for date 2/18/2013 I expect:
          SELECT DATE'2013-02-18' AS date_day , 21 AS prod_val FROM DUAL UNION
          SELECT DATE'2013-02-19' AS date_day , 26 AS prod_val FROM DUAL UNION
          SELECT DATE'2013-02-20' AS date_day , 34 AS prod_val FROM DUAL 
          When I run aggr query for date 2/12/2013 I expect:
          SELECT DATE'2013-02-12' AS date_day , 6  AS prod_val FROM DUAL UNION
          SELECT DATE'2013-02-13' AS date_day , 6  AS prod_val FROM DUAL UNION
          SELECT DATE'2013-02-14' AS date_day , 7  AS prod_val FROM DUAL UNION
          SELECT DATE'2013-02-15' AS date_day , 14 AS prod_val FROM DUAL UNION
          SELECT DATE'2013-02-16' AS date_day , 17 AS prod_val FROM DUAL UNION
          SELECT DATE'2013-02-17' AS date_day , 19 AS prod_val FROM DUAL UNION
          SELECT DATE'2013-02-18' AS date_day , 21 AS prod_val FROM DUAL UNION
          SELECT DATE'2013-02-19' AS date_day , 26 AS prod_val FROM DUAL UNION
          SELECT DATE'2013-02-20' AS date_day , 34 AS prod_val FROM DUAL
          Why? what's the logic you want to implement.
          • 2. Re: Aggregation query
            jeneesh
            Since you need to SUM up the values based on the presence of the product_id for that particular day - The requirement seems complicated, so is your query..

            It looks like, what you did is fine..
            • 3. Re: Aggregation query
              odie_63
              If I got the logic right, this seems to be more simple :
              SQL> with date_range as (
                2    select DATE '2013-02-10' + level - 1 as dt
                3    from dual
                4    connect by level <= DATE '2013-02-20' - DATE '2013-02-10' + 1
                5  )
                6  select dt
                7       , sum(prod_val) as sum_prod_val
                8  from (
                9    select r.dt
               10         , coalesce(
               11             s.prod_val
               12           , last_value(s.prod_val ignore nulls)
               13                  over(partition by s.prod_id order by r.dt)
               14           , 0
               15           ) as prod_val
               16    from date_range r
               17         left outer join input_set s partition by (prod_id)
               18                      on s.date_day = r.dt
               19  )
               20  group by dt
               21  order by dt ;
               
              DT          SUM_PROD_VAL
              ----------- ------------
              10/02/2013             2
              11/02/2013             4
              12/02/2013             6
              13/02/2013             6
              14/02/2013             7
              15/02/2013            14
              16/02/2013            17
              17/02/2013            19
              18/02/2013            21
              19/02/2013            26
              20/02/2013            34
               
              11 rows selected
               
              • 4. Re: Aggregation query
                Frank Kulash
                Hi,

                Here's one way
                WITH     date_range     AS
                (
                     SELECT     MIN (date_day)     AS first_day
                     ,     MAX (date_day)     AS last_day
                     FROM     input_set
                )
                ,     all_days     AS
                (
                     SELECT     first_day + LEVEL - 1     AS a_day
                     FROM     date_range
                     CONNECT BY  LEVEL <= 1 + last_day
                                          - first_day
                )
                ,     got_current_val          AS
                (
                     SELECT     a.a_day
                     ,     i.prod_id
                     ,     LAST_VALUE (prod_val IGNORE NULLS) 
                              OVER ( PARTITION BY  i.prod_id
                                        ORDER BY          a.a_day
                                )      AS current_val
                     FROM          all_days   a
                     LEFT OUTER JOIN  input_set  i  PARTITION BY (i.prod_id)
                                                    ON  i.date_day  = a.a_day
                )
                SELECT       a_day
                ,       SUM (current_val)     AS total_val
                FROM       got_current_val
                WHERE       a_day          >= DATE '2013-02-12'     -- first date to show
                GROUP BY  a_day
                ORDER BY  a_day
                ;
                • 5. Re: Aggregation query
                  jeneesh
                  odie_63 wrote:
                  s partition by (prod_id)
                  That looks nice .. :)
                  • 6. Re: Aggregation query
                    petez
                    Thanks Guys,

                    It looks like Frank's version is ok.