oracle聚合函数及行专列，pivot rollup cube

1、原始数据

?

--方法-：

--以单位分组，计算每类特殊情况的合计以及按照单位的小计数??????

with a as

?(SELECT b.szfz,

???????? case? when tsqk is not null then? tsqk? else? -1? end tsqk1,

???????? sum(tsqksl) tsqksl

??? FROM stat_tsqk_dw b

?? group by rollup(szfz,tsqk))

??

select * from a pivot(sum(tsqksl) for tsqk1 in(32,33,34,35,36,-1))

?

union?

--计数合计列，计算所有单位的各类特殊情况合计数?

select ‘‘ szfz,b.*

? from (select case? when tsqk is not null then? tsqk? else? -1? end tsqk1,

?????????????? sum(tsqksl) tsqksl

????????? from stat_tsqk_dw a

???????? group by cube(tsqk)) PIVOT(SUM(TSQKSL) FOR TSQK1 IN(32,

???????????????????????????????????????????????????????????? 33,

???????????????????????????????????????????????????????????? 34,

???????????????????????????????????????????????????????????? 35,

???????????????????????????????????????????????????????????? 36,

???????????????????????????????????????????????????????????? -1)) b

?

结果

?

数据表结构

-- Create table
create table STAT_TSQK_DW
(
  tjsj   VARCHAR2(20) not null,hjlx   NUMBER(4) not null,sglx   NUMBER(4) not null,sjlx   NUMBER(4) not null,szfz   VARCHAR2(16) not null,tsqk   NUMBER(4) not null,qxyy   NUMBER(4) not null,tsqksl NUMBER(9),qxyysl NUMBER(9)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64
    next 1
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table
comment on table STAT_TSQK_DW
  is ‘统计-出车特殊情况-单位/特殊情况‘;
-- Add comments to the columns
comment on column STAT_TSQK_DW.tjsj
  is ‘统计时间(粒度分钟)‘;
comment on column STAT_TSQK_DW.hjlx
  is ‘呼叫类型‘;
comment on column STAT_TSQK_DW.sglx
  is ‘事故类型‘;
comment on column STAT_TSQK_DW.sjlx
  is ‘事件类型‘;
comment on column STAT_TSQK_DW.szfz
  is ‘所在分站‘;
comment on column STAT_TSQK_DW.tsqk
  is ‘特殊情况‘;
comment on column STAT_TSQK_DW.qxyy
  is ‘取消原因‘;
comment on column STAT_TSQK_DW.tsqksl
  is ‘特殊情况数量‘;
comment on column STAT_TSQK_DW.qxyysl
  is ‘取消原因数量‘;
-- Create/Recreate primary,unique and foreign key constraints
alter table STAT_TSQK_DW
  add constraint PK_STAT_TSQK_DW primary key (TJSJ,HJLX,SGLX,SJLX,SZFZ,TSQK,QXYY)
  using index
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

?

数据：

?

prompt Importing table STAT_TSQK_DW...
set feedback off
set define off
insert into STAT_TSQK_DW (TJSJ,QXYY,TSQKSL,QXYYSL)
values (‘2018-09-18 04:01:00‘,10,‘3309000108‘,-9999,1,0);

insert into STAT_TSQK_DW (TJSJ,QXYYSL)
values (‘2018-09-17 23:26:00‘,‘3309000213‘,QXYYSL)
values (‘2018-09-18 11:39:00‘,100,1);

insert into STAT_TSQK_DW (TJSJ,QXYYSL)
values (‘2018-09-19 10:38:00‘,QXYYSL)
values (‘2018-09-18 11:17:00‘,50,QXYYSL)
values (‘2018-09-17 01:56:00‘,‘3309000307‘,QXYYSL)
values (‘2018-09-17 17:50:00‘,‘3309000101‘,QXYYSL)
values (‘2018-09-17 02:49:00‘,QXYYSL)
values (‘2018-09-18 10:12:00‘,QXYYSL)
values (‘2018-09-18 07:23:00‘,‘3309000309‘,QXYYSL)
values (‘2018-09-18 01:25:00‘,‘3309000106‘,QXYYSL)
values (‘2018-09-19 10:52:00‘,60,QXYYSL)
values (‘2018-09-17 14:44:00‘,QXYYSL)
values (‘2018-09-18 16:50:00‘,‘3309000302‘,QXYYSL)
values (‘2018-09-18 07:41:00‘,QXYYSL)
values (‘2018-09-17 17:04:00‘,QXYYSL)
values (‘2018-09-17 15:22:00‘,QXYYSL)
values (‘2018-09-18 18:24:00‘,1000,140,QXYYSL)
values (‘2018-09-17 02:16:00‘,QXYYSL)
values (‘2018-09-17 12:14:00‘,‘3309000111‘,QXYYSL)
values (‘2018-09-18 18:01:00‘,‘3309000312‘,QXYYSL)
values (‘2018-09-18 12:41:00‘,‘3309000310‘,QXYYSL)
values (‘2018-09-18 00:47:00‘,QXYYSL)
values (‘2018-09-17 02:00:00‘,QXYYSL)
values (‘2018-09-18 19:08:00‘,QXYYSL)
values (‘2018-09-17 03:04:00‘,QXYYSL)
values (‘2018-09-17 21:53:00‘,0);

prompt Done.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!