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, HJLX, SGLX, SJLX, SZFZ, TSQK, QXYY, TSQKSL, QXYYSL)
values ('2018-09-18 04:01:00', 10, 10, 10, '3309000108', 33, -9999, 1, 0); insert into STAT_TSQK_DW (TJSJ, HJLX, SGLX, SJLX, SZFZ, TSQK, QXYY, TSQKSL, QXYYSL)
values ('2018-09-17 23:26:00', 10, 10, 10, '3309000213', 33, -9999, 1, 0); insert into STAT_TSQK_DW (TJSJ, HJLX, SGLX, SJLX, SZFZ, TSQK, QXYY, TSQKSL, QXYYSL)
values ('2018-09-18 11:39:00', 10, 10, 10, '3309000108', -9999, 100, 0, 1); insert into STAT_TSQK_DW (TJSJ, HJLX, SGLX, SJLX, SZFZ, TSQK, QXYY, TSQKSL, QXYYSL)
values ('2018-09-19 10:38:00', 10, 10, 10, '3309000108', -9999, 100, 0, 1); insert into STAT_TSQK_DW (TJSJ, HJLX, SGLX, SJLX, SZFZ, TSQK, QXYY, TSQKSL, QXYYSL)
values ('2018-09-18 11:17:00', 50, 10, 10, '3309000108', 33, -9999, 1, 0); insert into STAT_TSQK_DW (TJSJ, HJLX, SGLX, SJLX, SZFZ, TSQK, QXYY, TSQKSL, QXYYSL)
values ('2018-09-17 01:56:00', 10, 10, 10, '3309000307', 36, -9999, 1, 0); insert into STAT_TSQK_DW (TJSJ, HJLX, SGLX, SJLX, SZFZ, TSQK, QXYY, TSQKSL, QXYYSL)
values ('2018-09-17 17:50:00', 10, 10, 10, '3309000101', 34, -9999, 1, 0); insert into STAT_TSQK_DW (TJSJ, HJLX, SGLX, SJLX, SZFZ, TSQK, QXYY, TSQKSL, QXYYSL)
values ('2018-09-17 02:49:00', 10, 10, 10, '3309000213', 33, -9999, 1, 0); insert into STAT_TSQK_DW (TJSJ, HJLX, SGLX, SJLX, SZFZ, TSQK, QXYY, TSQKSL, QXYYSL)
values ('2018-09-18 10:12:00', 10, 10, 10, '3309000213', 33, -9999, 1, 0); insert into STAT_TSQK_DW (TJSJ, HJLX, SGLX, SJLX, SZFZ, TSQK, QXYY, TSQKSL, QXYYSL)
values ('2018-09-18 07:23:00', 10, 10, 10, '3309000309', 33, -9999, 1, 0); insert into STAT_TSQK_DW (TJSJ, HJLX, SGLX, SJLX, SZFZ, TSQK, QXYY, TSQKSL, QXYYSL)
values ('2018-09-18 01:25:00', 10, 10, 10, '3309000106', 33, 0, 1, 0); insert into STAT_TSQK_DW (TJSJ, HJLX, SGLX, SJLX, SZFZ, TSQK, QXYY, TSQKSL, QXYYSL)
values ('2018-09-19 10:52:00', 60, 10, 10, '3309000108', 33, -9999, 1, 0); insert into STAT_TSQK_DW (TJSJ, HJLX, SGLX, SJLX, SZFZ, TSQK, QXYY, TSQKSL, QXYYSL)
values ('2018-09-17 14:44:00', 10, 10, 10, '3309000309', 33, -9999, 1, 0); insert into STAT_TSQK_DW (TJSJ, HJLX, SGLX, SJLX, SZFZ, TSQK, QXYY, TSQKSL, QXYYSL)
values ('2018-09-18 16:50:00', 10, 10, 10, '3309000302', 33, -9999, 1, 0); insert into STAT_TSQK_DW (TJSJ, HJLX, SGLX, SJLX, SZFZ, TSQK, QXYY, TSQKSL, QXYYSL)
values ('2018-09-18 07:41:00', 10, 10, 10, '3309000106', 33, -9999, 1, 0); insert into STAT_TSQK_DW (TJSJ, HJLX, SGLX, SJLX, SZFZ, TSQK, QXYY, TSQKSL, QXYYSL)
values ('2018-09-17 17:04:00', 10, 10, 10, '3309000302', 35, -9999, 1, 0); insert into STAT_TSQK_DW (TJSJ, HJLX, SGLX, SJLX, SZFZ, TSQK, QXYY, TSQKSL, QXYYSL)
values ('2018-09-17 15:22:00', 10, 10, 10, '3309000101', 33, -9999, 1, 0); insert into STAT_TSQK_DW (TJSJ, HJLX, SGLX, SJLX, SZFZ, TSQK, QXYY, TSQKSL, QXYYSL)
values ('2018-09-18 18:24:00', 10, 10, 10, '3309000108', 1000, 140, 1, 1); insert into STAT_TSQK_DW (TJSJ, HJLX, SGLX, SJLX, SZFZ, TSQK, QXYY, TSQKSL, QXYYSL)
values ('2018-09-17 02:16:00', 10, 10, 10, '3309000108', 33, -9999, 1, 0); insert into STAT_TSQK_DW (TJSJ, HJLX, SGLX, SJLX, SZFZ, TSQK, QXYY, TSQKSL, QXYYSL)
values ('2018-09-17 12:14:00', 10, 10, 10, '3309000111', 33, -9999, 1, 0); insert into STAT_TSQK_DW (TJSJ, HJLX, SGLX, SJLX, SZFZ, TSQK, QXYY, TSQKSL, QXYYSL)
values ('2018-09-18 18:01:00', 10, 10, 10, '3309000312', 33, -9999, 1, 0); insert into STAT_TSQK_DW (TJSJ, HJLX, SGLX, SJLX, SZFZ, TSQK, QXYY, TSQKSL, QXYYSL)
values ('2018-09-18 12:41:00', 10, 10, 10, '3309000310', 33, -9999, 1, 0); insert into STAT_TSQK_DW (TJSJ, HJLX, SGLX, SJLX, SZFZ, TSQK, QXYY, TSQKSL, QXYYSL)
values ('2018-09-18 00:47:00', 10, 10, 10, '3309000213', 33, -9999, 1, 0); insert into STAT_TSQK_DW (TJSJ, HJLX, SGLX, SJLX, SZFZ, TSQK, QXYY, TSQKSL, QXYYSL)
values ('2018-09-17 02:00:00', 10, 10, 10, '3309000309', 33, -9999, 1, 0); insert into STAT_TSQK_DW (TJSJ, HJLX, SGLX, SJLX, SZFZ, TSQK, QXYY, TSQKSL, QXYYSL)
values ('2018-09-18 19:08:00', 60, 10, 10, '3309000302', 35, 0, 1, 0); insert into STAT_TSQK_DW (TJSJ, HJLX, SGLX, SJLX, SZFZ, TSQK, QXYY, TSQKSL, QXYYSL)
values ('2018-09-17 03:04:00', 10, 10, 10, '3309000101', 33, -9999, 1, 0); insert into STAT_TSQK_DW (TJSJ, HJLX, SGLX, SJLX, SZFZ, TSQK, QXYY, TSQKSL, QXYYSL)
values ('2018-09-18 19:08:00', 60, 10, 10, '3309000307', 35, 0, 1, 0); insert into STAT_TSQK_DW (TJSJ, HJLX, SGLX, SJLX, SZFZ, TSQK, QXYY, TSQKSL, QXYYSL)
values ('2018-09-17 21:53:00', 10, 10, 10, '3309000302', 35, -9999, 1, 0); prompt Done.