views:

486

answers:

2

Hi folks,

I've written a query to collect some data to display in an auto-updating box & whisker graph in excel. I'd like to use rollup to create summary rows for each type of train_line (PF and MJ) to include in the excel chart.

Can I do that using Rollup?

I've tried to get my head around Rollup, but I'm not getting far. I've tried just wrapping it around random things in my group-by but it hasn't done what I've wanted.

Here's what the first few columns of results look like.

DUMP_YEAR   DUMP_WEEK  LINE MINE PRODUCT CODE
2009             30        MJ    MJ C      MJ-C
2009             30        PF    BR F      BR-F
2009             30        PF    BR L      BR-L
2009             30        PF    HD F      HD-F
2009             30        PF    HD L      HD-L
2009             30        PF    MA F      MA-F
2009             30        PF    MA L      MA-L
2009             30        PF    NM F      NM-F
2009             30        PF    NM L      NM-L
2009             30        PF    PA F      PA-F
2009             30        PF      PA L      PA-L
2009             30        PF    TP F      TP-F
2009             30        PF    TP L      TP-L
2009             30        PF      WA F      WA-F
2009             30        PF    WA L      WA-L
2009             30        PF    YA F      YA-F

And here's my SQL query.

 select t.dump_year,
       t.dump_week,
       (case when t.product = 'L' or t.product = 'F' then 'PF'
          when t.product = 'C' then 'MJ'
       else null
     end) as train_line,    
       t.mine_id,
       t.product,
       t.mine_id||'-'||t.product as code,
       count(distinct t.tpps_train_id) as trains,
       count(1) as wagons,
       count(CASE WHEN w.tonnes >= 1121 THEN w.tonnes END) as overload,
       round(count(CASE WHEN w.tonnes >= 1121 THEN w.tonnes END)/count(1)*100,1) as pct_ol,
       min(t.dump_date) as first_train,
       max(t.dump_date) as last_train,     
       119 as u_limit,
       100 as target,    

       round(avg(w.tonnes),2) as average,
       round(stddev(w.tonnes),2) as deviation,
       round(min(w.tonnes),2) as minimum,
       round(max(w.tonnes),2) as maximum,
      round(percentile_disc(0.99) within group (order by (w.tonnes) desc),2) as pct_1st,
      round((percentile_disc(0.75) within group (order by (w.tonnes) desc)),2)-round((percentile_disc(0.99) within group (order by (w.tonnes) desc)),2) as whisker1,
      round(percentile_disc(0.75) within group (order by (w.tonnes) desc),2) as pct_25th,
      round((percentile_disc(0.50) within group (order by (w.tonnes) desc)),2)-round((percentile_disc(0.75) within group (order by (w.tonnes) desc)),2) as box50,
      round((percentile_disc(0.25) within group (order by (w.tonnes) desc)),2)-round(percentile_disc(0.50) within group (order by (w.tonnes) desc),2) as box75,
      round((percentile_disc(0.01) within group (order by (w.tonnes) desc)),2)-round((percentile_disc(0.25) within group (order by (w.tonnes) desc)),2) as whisker99,
      round(percentile_disc(0.50) within group (order by (w.tonnes) desc),2) as pct_50th,
      round(percentile_disc(0.25) within group (order by (w.tonnes) desc),2) as pct_75th,
      round(percentile_disc(0.01) within group (order by (w.tonnes) desc),2) as pct_99th

   from 

    (
        select trn.mine_code as mine_id,
               substr(trn.train_control_id,2,1) as port,
               trn.train_tpps_id as tpps_train_id,      
               con.weight_total-con.empty_weight_total as tonnes     
        from  widsys.train trn
                  INNER JOIN widsys.consist con
                      USING (train_record_id)

        where trn.direction = 'N'
              and (con.weight_total-con.empty_weight_total) > 10
              and trn.num_cars > 10 
       ) w,

        (
         select td.datetime_act_comp_dump as dump_date,
                to_char(td.datetime_act_comp_dump-7/24, 'IYYY') as dump_year,
                to_char(td.datetime_act_comp_dump-7/24, 'IW') as dump_week,
                td.mine_code as mine_id,
                td.train_id as tpps_train_id,
                pt.product_type_code as product
         from tpps.train_details td
              inner join tpps.ore_products op
              using (ore_product_key)
              inner join tpps.product_types pt
              using (product_type_key)
         where to_char(td.datetime_act_comp_dump-7/24, 'IYYY') = 2009
               and to_char(td.datetime_act_comp_dump-7/24, 'IW') = 30
         order by td.datetime_act_comp_dump asc
    ) t 
   where w.mine_id = t.mine_id
      and w.tpps_train_id = t.tpps_train_id

 --having t.product is not null or t.mine_id is null 
   group by 
         t.dump_year,
         t.dump_week, 
       (case when t.product = 'L' or t.product = 'F' then 'PF'when t.product = 'C' then 'MJ'else null end),       
         t.mine_id,
         t.product


order by train_line asc
A: 

There is 'cube', 'rollup' and ' grouping sets': http://download.oracle.com/docs/cd/B28359_01/server.111/b28313/aggreg.htm#i1007462

Theo
+4  A: 

Hi Thomas,

You would use ROLLUP to generate hierarchical subtotals for your query, i-e:

SQL> WITH DATA AS (
  2     SELECT 'i' || MOD(ROWNUM, 1) dim1,
  3            'j' || MOD(ROWNUM, 2) dim2,
  4            'k' || MOD(ROWNUM, 3) dim3,
  5            ROWNUM qty
  6       FROM dual
  7     CONNECT BY LEVEL <= 100
  8  )
  9  SELECT dim1, dim2, dim3, SUM(qty) tot
 10    FROM DATA
 11   GROUP BY dim1, rollup(dim2,dim3)
 12   ORDER BY 1, 2, 3;

DIM1  DIM2  DIM3         TOT
----- ----- ----- ----------
i0    j0    k0           816
i0    j0    k1           884
i0    j0    k2           850
i0    j0                2550 (*)
i0    j1    k0           867
i0    j1    k1           833
i0    j1    k2           800
i0    j1                2500 (*)
i0                      5050 (*)

The ROLLUP clause generated the lines marked (*)

If you only want to get a set of subtotals and not all the hierarchical levels, you can use the GROUPING SETS clause, i-e:

SQL> WITH DATA AS (
  2     SELECT 'i' || MOD(ROWNUM, 1) dim1,
  3            'j' || MOD(ROWNUM, 2) dim2,
  4            'k' || MOD(ROWNUM, 3) dim3,
  5            ROWNUM qty
  6       FROM dual
  7     CONNECT BY LEVEL <= 100
  8  )
  9  SELECT dim1, dim2, dim3, SUM(qty) tot
 10    FROM DATA
 11   GROUP BY GROUPING SETS (
 12     (dim1, dim2, dim3), -- detail
 13     (dim1) -- total
 14   )
 15   ORDER BY 1, 2, 3;

DIM1  DIM2  DIM3         TOT
----- ----- ----- ----------
i0    j0    k0           816
i0    j0    k1           884
i0    j0    k2           850
i0    j1    k0           867
i0    j1    k1           833
i0    j1    k2           800
i0                      5050
Vincent Malgrat
+1, and the latter grouping sets notation can be rewritten to "GROUP BY dim1, ROLLUP((dim2,dim3))
Rob van Wijk
Thanks Rob, I didn't know about this synthax :>
Vincent Malgrat