Oracle 需要枢轴SQL的帮助来计算枢轴列的总数

askTom 2018-01-17

422

问题描述

嗨，

我需要帮助来获取表的每一行和每一列的总数。
我使用PIVOT来隔离数据，但无法弄清楚总数。
下面是脚本，当前输出和所需输出。请求帮助 ..谢谢 ..
Oracle数据库-11.2.0.4.0-64位

------------------------------
CREATE TABLE 
------------------------------

CREATE TABLE TEST_RES_TAB
(
  RESOURCE_ID        NUMBER              NOT NULL,
  RESOURCE_NAME      VARCHAR2(240)       NOT NULL,
  ROLE               VARCHAR2(50),
  TRACK              VARCHAR2(50),
  LOCATION           VARCHAR2(50),
  RESOURCE_TYPE      VARCHAR2(50),
  SITE_TYPE          VARCHAR2(50)
)


------------------------------
INSERT DATA
------------------------------

begin
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990001,'RES_26','PM','RTR','UK','TMP','OffSite',to_date('11-MAY-2015 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('30-JUN-2018 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990002,'RES_25','BSA','AMS','UK','TMP','OffSite',to_date('28-JUN-2016 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('06-OCT-2017 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990003,'RES_24','BSA','BI','US','TMP','OnSite',to_date('13-OCT-2015 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('31-DEC-2018 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990004,'RES_23','Manager','RTR','US','FTE','OnSite',to_date('09-SEP-2013 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('31-DEC-9999 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990005,'RES_22','QA','OPRN','UK','TMP','OffSite',to_date('01-NOV-2016 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('06-OCT-2017 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990006,'RES_21','BSA','CTO','US','TMP','OnSite',to_date('18-MAY-2014 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('20-JAN-2017 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990007,'RES_20','PM','PMO','US','TMP','OnSite',to_date('12-SEP-2016 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('19-MAY-2017 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990008,'RES_19','Developer','CTO','UK','TMP','OnSite',to_date('02-MAR-2016 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('15-JUN-2017 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990009,'RES_18','Developer','CTO','UK','TMP','OnSite',to_date('02-MAY-2016 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('30-JUN-2017 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990010,'RES_17','Manager','CTO','US','FTE','OnSite',to_date('30-AUG-2010 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('31-DEC-9999 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990011,'RES_16','BSA','PTP','US','FTE','OnSite',to_date('29-AUG-2011 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('31-DEC-9999 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990012,'RES_15','BSA','RTR','US','TMP','OnSite',to_date('26-JAN-2016 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('28-JUL-2017 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990013,'RES_14','Developer','BI','US','FTE','OnSite',to_date('14-APR-2014 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('31-DEC-9999 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990014,'RES_13','Leadership','MGMT','CAN','FTE','OnSite',to_date('12-AUG-2013 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('31-DEC-9999 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990015,'RES_12','Dev Lead','CTO','CAN','FTE','OnSite',to_date('25-MAR-2013 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('31-DEC-9999 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990016,'RES_11','Dev Lead','CTO','CAN','TMP','OnSite',to_date('21-AUG-2015 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('30-JUN-2017 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990017,'RES_10','PM','PMO','CAN','FTE','OnSite',to_date('06-JUN-2016 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('31-DEC-9999 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990018,'RES_9','DBA','DBA','CAN','TMP','OnSite',to_date('28-JUN-2016 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('31-MAR-2017 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990019,'RES_8','Leadership','MGMT','CAN','FTE','OnSite',to_date('15-APR-2013 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('31-DEC-9999 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990020,'RES_7','Developer','RTR','US','TMP','OnSite',to_date('08-MAR-2016 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('14-AUG-2017 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990021,'RES_6','Developer','CTO','UK','TMP','OffSite',to_date('31-MAY-2016 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('07-APR-2017 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990034,'RES_5','Dev Lead','AMS','US','TMP','OnSite',to_date('21-SEP-2016 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('30-JUN-2017 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990035,'RES_4','QA','OPRN','UK','TMP','OffSite',to_date('08-FEB-2017 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('06-OCT-2017 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990036,'RES_3','Manager','CTO','US','FTE','OnSite',to_date('07-DEC-2009 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('31-DEC-9999 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990046,'RES_2','Manager','CTO','US','FTE','OnSite',to_date('29-OCT-2012 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('31-DEC-9999 00:00:00','DD-MON-YYYY HH24:MI:SS'));
Insert into TEST_RES_TAB (RESOURCE_ID,RESOURCE_NAME,ROLE,TRACK,LOCATION,RESOURCE_TYPE,SITE_TYPE,START_DATE_ACTIVE,END_DATE_ACTIVE) values (990047,'RES_1','Developer','CTO','US','TMP','OnSite',to_date('31-MAY-2016 00:00:00','DD-MON-YYYY HH24:MI:SS'),to_date('30-MAY-2017 00:00:00','DD-MON-YYYY HH24:MI:SS'));
commit;
end;

------------------------------
MY QUERY
------------------------------

SELECT *
FROM   (  SELECT resource_type status,
         site_type on_off_site,
         location,
         COUNT (resource_name) res_cnt
    FROM TEST_RES_TAB
GROUP BY resource_type, site_type, location
)
PIVOT (
sum(res_cnt) FOR location IN ('US' US,  'UK' UK, 'CAN' CANADA)
)  

------------------------------
CURRENT OUTPUT
------------------------------

STATUS |    ON_OFF_SITE |        US |    UK  |  CANADA
FTE  |   OnSite  |             6 |  |   4
TMP  |   OnSite  |             7 |    2  |   2
TMP  |   OffSite |                       |    5  |


------------------------------
REQUIRED OUTPUT  -- feel free to remove PIVOT and use DECODE if that will make it simple.
------------------------------
STATUS |ON_OFF_SITE    |US     |UK     |CANADA |Total
FTE     |OnSite         |6      |       |4      |10
TMP     |OnSite         |7      |2      |2      |11
TMP     |OffSite        |       |       |5      |5
Total   |               |13     |7      |6      |26

任何帮助都非常感谢。
谢谢。

专家解答

所以，你想要生成的总计:

-资源类型，站点类型，位置
-资源类型，站点类型
-位置
-总计

您可以使用分组集 () 在子查询中生成这些。传递给这个你想要小计的列的所有组合:

select grouping_id (resource_type, site_type, location) grp,
         resource_type status,
         site_type on_off_site,
         location,
         count(*) c
  from   test_res_tab
  group by grouping sets(
    (resource_type, site_type, location), 
    (resource_type, site_type), 
    (location),
    ()
  );

GRP   STATUS   ON_OFF_SITE   LOCATION   C    
    0 FTE      OnSite        US            6 
    0 FTE      OnSite        CAN           4 
    0 TMP      OnSite        UK            2 
    0 TMP      OnSite        US            7 
    0 TMP      OnSite        CAN           2 
    0 TMP      OffSite       UK            5 
    1 FTE      OnSite                     10 
    1 TMP      OnSite                     11 
    1 TMP      OffSite                     5 
    6                        CAN           6 
    6                        UK            7 
    6                        US           13 
    7                                     26

grouping_id函数告诉您总数是用于哪个列的组合。使用它将小计映射到您想要的列中的值。

你想要的总数

-每个位置都是一行
-每个 (resource_type，site_type) 都是一列

因此，当它们具有适当的grouping_id时，将它们映射到 “总计” 值:

select case 
           when grouping_id (resource_type, site_type, location) in (6, 7) then  
             'TOTAL'
           else
             resource_type 
         end status,
         site_type on_off_site,
         case
           when grouping_id (resource_type, site_type, location) in (1, 7) then
             'TOTAL'
           else
             location
         end location,
         count(*) c
  FROM   test_res_tab
  group by grouping sets(
    (resource_type, site_type, location), 
    (resource_type, site_type), 
    (location),
    ()
  );

STATUS   ON_OFF_SITE   LOCATION   C    
FTE      OnSite        US            6 
FTE      OnSite        CAN           4 
TMP      OnSite        UK            2 
TMP      OnSite        US            7 
TMP      OnSite        CAN           2 
TMP      OffSite       UK            5 
FTE      OnSite        TOTAL        10 
TMP      OnSite        TOTAL        11 
TMP      OffSite       TOTAL         5 
TOTAL                  CAN           6 
TOTAL                  UK            7 
TOTAL                  US           13 
TOTAL                  TOTAL        26

您现在需要做的就是将total添加到您的位置数据透视列表中:

with tots as (
  select case 
           when grouping_id (resource_type, site_type, location) in (6, 7) then  
             'TOTAL'
           else
             resource_type 
         end status,
         site_type on_off_site,
         case
           when grouping_id (resource_type, site_type, location) in (1, 7) then
             'TOTAL'
           else
             location
         end location,
         count(*) c
  FROM   test_res_tab
  group by grouping sets(
    (resource_type, site_type, location), 
    (resource_type, site_type), 
    (location),
    ()
  )
)
  select * from tots
  pivot (
    sum(c) for location IN ('US' US,  'UK' UK, 'CAN' CANADA, 'TOTAL' TOTAL)
  );

STATUS   ON_OFF_SITE   US   UK   CANADA   TOTAL   
FTE      OnSite           6             4      10 
TMP      OnSite           7    2        2      11 
TMP      OffSite               5                5 
TOTAL                    13    7        6      26

oracle asktom

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

Oracle 需要枢轴SQL的帮助来计算枢轴列的总数

问题描述

专家解答

评论