帮助 Oracle sql rollup

发布于 2024-07-30 06:10:21 字数 4819 浏览 4 评论 0 原文

我编写了一个查询来收集一些数据以显示在自动更新框和列表中。 Excel 中的晶须图。我想使用汇总为每种类型的 train_line （PF 和 MJ）创建汇总行以包含在 Excel 图表中。

我可以使用 Rollup 来做到这一点吗？

我已经尝试过了解 Rollup，但还没有走得太远。我尝试将它包裹在我的 group-by 中的随机事物中，但它没有达到我想要的效果。

以下是前几列结果的样子。

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

这是我的 SQL 查询。

 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

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

太阳男子 2024-08-06 06:10:21

您可以使用 ROLLUP 生成分层结构查询的小计，即：

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 (*)

ROLLUP 子句生成标记为 (*)

的行如果您只想获取一组小计而不是所有层次结构级别，则可以使用 GROUPING SETS 子句，即：

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

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

回复收藏 0 原文