暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / Oracle 在选择所有列时根据组生成总计

Oracle 在选择所有列时根据组生成总计

askTom 2018-03-03
327

问题描述

你好-

我需要创建一个按帐号和位置细分的输出。

这是我的示例表:
Create table sample_dat
(
POSITION_NO number (5), 
NAME varchar2(50),      
TITLE varchar2(50),
ACCOUNT number(15),
GAAP_ACNO number(15),
GAAP_DESC varchar2(50),
FUND varchar2(2),
BUDLOC varchar2(2),
TOT_SAL number(6),
FTE number(4),
STATUS varchar2(15),
LOC varchar2(50)
);


这里是我的示例数据集:

insert into sample_dat values(4985,'NAME','TITLE 1',1234,1234,'OTHER SALARIES','11','00',44074,1,'FILLED','01 GEORGE'):
insert into sample_dat values(4982,'NAME','TITLE 1',1234,1234,'OTHER SALARIES','11','00',49253,1,'FILLED','01 GEORGE'):
insert into sample_dat values(4543,'NAME','TITLE 1',1234,1234,'OTHER SALARIES','11','00',44074,1,'FILLED','01 GEORGE'):
insert into sample_dat values(5000,'NAME','TITLE 1',1234,1234,'OTHER SALARIES','11','00',42307,1,'FILLED','01 GEORGE'):
insert into sample_dat values(3113,'NAME ','TITLE 2',12341,12341,'TECH','11','00',0,1,'VACANT','01 GEORGE'):
insert into sample_dat values(1136,'NAME','TITLE 3',12341,12341,'TECH','11','01',9494,1,'FILLED','01 GEORGE'):
insert into sample_dat values(3839,'NAME','TITLE 5',12341,12341,'TECH','11','01',0,1,'VACANT','01 GEORGE'):
insert into sample_dat values(2688,'NAME','TITLE X',2345,2345,'INSTRUCTION','11','00',0,1,'VACANT','02 SCOTT'):
insert into sample_dat values(2724,'NAME','TITLE P',23451,23451,'PP','11','00',0,1,'VACANT','02 SCOTT'):
insert into sample_dat values(4245,'NAME','TITLE Q',23451,23451,'PP','11','00',0,1,'VACANT','02 SCOTT'):
insert into sample_dat values(3362,'NAME','TITLE 1',23451,23451,'PP','11','02',0,1,'FILLED','02 SCOTT'):
insert into sample_dat values(3295,'NAME','TITLE M',23452,23452,'SPORTS','11','02',57479,1,'FILLED','02 SCOTT'):
insert into sample_dat values(2443,'NAME','TITLE M',23452,23452,'SPORTS','11','02',56112,1,'FILLED','02 SCOTT'):
insert into sample_dat values(4144,'NAME','TITLE M',23452,23452,'SPORTS','11','02',56112,1,'FILLED','02 SCOTT'):


预期产出如下所示:
POSITION_NO NAME TITLE ACCOUNT GAAP_ACNO GAAP_DESC FUND BUDLOC TOT_SAL FTE STATUS LOC
4985 NAME TITLE 1 1234 1234 OTHER SALARIES 11 00 44074 1 FILLED 01 GEORGE
4982 NAME TITLE 1 1234 1234 OTHER SALARIES 11 00 49253 1 FILLED 01 GEORGE
4543 NAME TITLE 1 1234 1234 OTHER SALARIES 11 00 44074 1 FILLED 01 GEORGE
5000 NAME TITLE 1 1234 1234 OTHER SALARIES 11 00 42307 1 FILLED 01 GEORGE
   Account Total     179708 4  
3113 NAME  TITLE 2 12341 12341 TECH 11 00 0 1 VACANT 01 GEORGE
1136 NAME TITLE 3 12341 12341 TECH 11 01 9494 1 FILLED 01 GEORGE
3839 NAME TITLE 5 12341 12341 TECH 11 01 0 1 VACANT 01 GEORGE
   Account Total     9494 3  
   Location Total     189202 7  
2688 NAME TITLE X 2345 2345 INSTRUCTION 11 00 0 1 VACANT 02 SCOTT
   Account Total     0 1  
2724 NAME TITLE P 23451 23451 PP 11 00 0 1 VACANT 02 SCOTT
4245 NAME TITLE Q 23451 23451 PP 11 00 0 1 VACANT 02 SCOTT
3362 NAME TITLE 1 23451 23451 PP 11 02 0 1 FILLED 02 SCOTT
   Account Total     0 3  
3295 NAME TITLE M 23452 23452 SPORTS 11 02 57479 1 FILLED 02 SCOTT
2443 NAME TITLE M 23452 23452 SPORTS 11 02 56112 1 FILLED 02 SCOTT
4144 NAME TITLE M 23452 23452 SPORTS 11 02 56112 1 FILLED 02 SCOTT
   Account Total     169703 3  
   Location Total     169703 6  
   District Total (Total of All Locations)     358905 13


我认为这可以使用rollup/cube函数来实现。但是; 不确定如何使用这些功能。任何
我们将不胜感激!

谢谢
文卡特

专家解答

选择中的每个未汇总列都必须出现在group by中。

但是这样做，您就可以在最细粒度的水平上进行聚合。如果将所有内容都放在汇总或多维数据集中，则会获得所有列的小计。您只想要 (帐户，loc) 的这些汇总。

你可以计算所有的小计。然后排除所有你不想要的。grouping_id (...) 函数告诉你哪一列你有小计:

select POSITION_NO, NAME , TITLE , GAAP_ACNO , GAAP_DESC ,FUND ,BUDLOC , FTE ,STATUS,
       ACCOUNT , LOC, 
       sum(TOT_SAL), count(*),
       grouping_id (
         loc, account, POSITION_NO, NAME , TITLE , GAAP_ACNO , GAAP_DESC ,FUND ,BUDLOC , FTE ,STATUS
       ) gid
from   sample_dat
group  by rollup(
  loc, account, POSITION_NO, NAME , TITLE , GAAP_ACNO , GAAP_DESC ,FUND ,BUDLOC , FTE ,STATUS
);

POSITION_NO   NAME     TITLE     GAAP_ACNO   GAAP_DESC        FUND     BUDLOC   FTE      STATUS   ACCOUNT   LOC         SUM(TOT_SAL)   COUNT(*)   GID    
         2688 NAME     TITLE X          2345 INSTRUCTION      11       00              1 VACANT        2345 02 SCOTT                 0          1      0 
         2688 NAME     TITLE X          2345 INSTRUCTION      11       00              1         2345 02 SCOTT                 0          1      1 
         2688 NAME     TITLE X          2345 INSTRUCTION      11       00                  2345 02 SCOTT                 0          1      3 
         2688 NAME     TITLE X          2345 INSTRUCTION      11                     2345 02 SCOTT                 0          1      7 
         2688 NAME     TITLE X          2345 INSTRUCTION                       2345 02 SCOTT                 0          1     15 
         2688 NAME     TITLE X          2345                             2345 02 SCOTT                 0          1     31 
         2688 NAME     TITLE X                                     2345 02 SCOTT                 0          1     63
......


但这正在处理比你需要的更多的数据。

幸运的是，有一种方法可以获得特定的子组:

分组集!

看来你想要四个级别:

-所有列
-(账户、地点) 小计
-位置小计
-总计

最后三个是以下内容的汇总:

汇总 (位置、账户)

因此，将其粘贴在分组集中，以及每个未聚合列的另一个表达式中。然后使用grouping_id设置一个 “总名称” 列:

with grps as (
  select case grouping_id (account, loc, POSITION_NO) 
           when 1 then 'Account total'
           when 5 then 'Location total'
           when 7 then 'District total'
         end grp,
         POSITION_NO, NAME , TITLE , GAAP_ACNO , GAAP_DESC ,FUND ,BUDLOC , FTE ,STATUS,
         ACCOUNT , LOC, 
         sum(TOT_SAL), count(*),
         grouping_id (account, loc, POSITION_NO) gid
  from   sample_dat
  group  by grouping sets (
    (POSITION_NO, NAME , TITLE , GAAP_ACNO , GAAP_DESC ,FUND ,BUDLOC , FTE ,STATUS, account, loc),
    rollup(loc, account)
  )
)
  select * from grps
  order  by loc, account, gid;

GRP              POSITION_NO   NAME     TITLE     GAAP_ACNO   GAAP_DESC        FUND     BUDLOC   FTE      STATUS   ACCOUNT   LOC         SUM(TOT_SAL)   COUNT(*)   GID   
                    5000 NAME     TITLE 1          1234 OTHER SALARIES   11       00              1 FILLED        1234 01 GEORGE            42307          1     0 
                    4985 NAME     TITLE 1          1234 OTHER SALARIES   11       00              1 FILLED        1234 01 GEORGE            44074          1     0 
                    4543 NAME     TITLE 1          1234 OTHER SALARIES   11       00              1 FILLED        1234 01 GEORGE            44074          1     0 
                    4982 NAME     TITLE 1          1234 OTHER SALARIES   11       00              1 FILLED        1234 01 GEORGE            49253          1     0 
Account total                                                     1234 01 GEORGE           179708          4     1 
                    3113 NAME     TITLE 2         12341 TECH             11       00              1 VACANT       12341 01 GEORGE                0          1     0 
                    3839 NAME     TITLE 5         12341 TECH             11       01              1 VACANT       12341 01 GEORGE                0          1     0 
                    1136 NAME     TITLE 3         12341 TECH             11       01              1 FILLED       12341 01 GEORGE             9494          1     0 
Account total                                                    12341 01 GEORGE             9494          3     1 
Location total                                                   01 GEORGE           189202          7     5 
                    2688 NAME     TITLE X          2345 INSTRUCTION      11       00              1 VACANT        2345 02 SCOTT                 0          1     0 
Account total                                                     2345 02 SCOTT                 0          1     1 
                    2724 NAME     TITLE P         23451 PP               11       00              1 VACANT       23451 02 SCOTT                 0          1     0 
                    3362 NAME     TITLE 1         23451 PP               11       02              1 FILLED       23451 02 SCOTT                 0          1     0 
                    4245 NAME     TITLE Q         23451 PP               11       00              1 VACANT       23451 02 SCOTT                 0          1     0 
Account total                                                    23451 02 SCOTT                 0          3     1 
                    3295 NAME     TITLE M         23452 SPORTS           11       02              1 FILLED       23452 02 SCOTT             57479          1     0 
                    4144 NAME     TITLE M         23452 SPORTS           11       02              1 FILLED       23452 02 SCOTT             56112          1     0 
                    2443 NAME     TITLE M         23452 SPORTS           11       02              1 FILLED       23452 02 SCOTT             56112          1     0 
Account total                                                    23452 02 SCOTT            169703          3     1 
Location total                                                   02 SCOTT            169703          7     5 
District total                                                                 358905         14     7

oracleasktom
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论