This discussion is archived
6 Replies Latest reply: Feb 20, 2013 6:51 AM by petez RSS

Aggregation query

petez Newbie
Currently Being Moderated
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 Guru Moderator
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    odie_63 wrote:
    s partition by (prod_id)
    That looks nice .. :)
  • 6. Re: Aggregation query
    petez Newbie
    Currently Being Moderated
    Thanks Guys,

    It looks like Frank's version is ok.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points