--202.98.194.182:1521/ocp11g datasource/datasource

--1.统计各单位人数
select
fsocial_uuid,
fsocial_name,
count(FSocPerson_UUID) as Fscial_numOfPeople
from View_BD_SocialPerson
group by fsocial_uuid,fsocial_name
order by Fscial_numOfPeople desc
;

--2.统计各单位参加培训人数;
select
fsocial_uuid,
fsocial_name,
count(FPerson_UUID) as TrainedPeople
from View_BS_PersonTrainTaskDtl
group by fsocial_uuid,fsocial_name
order by TrainedPeople desc
;

--3.统计各单位培训通过率
select
fsocial_uuid,
fsocial_name,
round(count(case when Fis_Finish=‘1‘ then 1 else null end)/count(Fis_Finish), 4)*100||‘%‘ as passRate
from View_BS_PersonTrainTaskDtl
group by fsocial_uuid, fsocial_name
order by passRate desc
;

--4.统计各单位物联网设备总数
select
fsocial_uuid,
fsocial_name,
count(Fdevice_UUID) as deviceNum
from View_BD_DeviceInfo
group by fsocial_uuid,fsocial_name
order by deviceNum desc
;

--5.统计各单位物联网损坏数
select
fsocial_uuid,
fsocial_name,
count(Fdevice_UUID) as deviceFailureNum
from View_BD_DeviceInfo
where FStatus=‘02‘
group by fsocial_uuid,fsocial_name
order by deviceFailureNum desc
;

--select fsocial_uuid,fsocial_name from View_BD_DeviceInfo where FStatus=‘02‘ group by fsocial_uuid,fsocial_name;

--6.统计各单位设备故障率
select
fsocial_uuid,
fsocial_name,
round(COUNT(case when FStatus=‘02‘ then 1 else null end)/COUNT(c.FDEVICE_UUID), 4)*100||‘%‘ as deviceFailureRate
from View_BD_DeviceInfo c
group by fsocial_uuid,fsocial_name
order by deviceFailureRate desc
;

select
fsocial_uuid,
fsocial_name,
to_char(round(COUNT(case when FStatus=‘02‘ then 1 else null end)/COUNT(c.FDEVICE_UUID), 4)*100,‘fm990.00‘)||‘%‘ as deviceFailureRate
from View_BD_DeviceInfo c
group by fsocial_uuid,fsocial_name
order by deviceFailureRate desc
;

--遇到的问题:请问sql显示百分比小数点的问题 比如我select round(a1/a2*100,2)||‘%‘ from table的时候 如果a1/a2=0.005的话,就会显示 .5%
--解决办法
select to_char(round(a1/a2*100,2),‘fm999990.00‘)||‘%‘ from table
--其中fm表示去掉空格, 999990.00分别代表整数位数和小数位数是几位

--7.统计各单位设备启用率
select
fsocial_uuid,
fsocial_name,
round(COUNT(case when Fis_Active=‘1‘ then 1 else null end)/COUNT(c.Fis_Active), 4)*100||‘%‘ as deviceUsedNum
from View_BD_DeviceInfo c
group by fsocial_uuid,fsocial_name
order by deviceUsedNum desc
;

--8.统计各单位设备平均报警数
select
fsocial_uuid,
fsocial_name,
round(count(case when Fis_Alarm=‘1‘ then 1 else null end)/count(c.Fis_Alarm), 4)*100||‘%‘ as alrmPerDevice
from View_MN_DeviceMonitorRec c
group by fsocial_uuid, fsocial_name
order by alrmPerDevice desc
;

--9.统计各单位巡查数量
select
fsocial_uuid,
fsocial_name,
count(FPatrolBusDtl_UUID) as numOfPatrol
from View_bs_PatrolBusDtl
group by fsocial_uuid,fsocial_name
order by numOfPatrol desc
;

--10.View_BD_DeviceInfo和View_MN_DeviceMonitorRec inner join , 求交集
--fsocial_uuid fsocial_name deviceNum deviceFailureNum deviceFailureRate deviceUsedNum alrmPerDevice
--select * from A inner join B on A.field1=B.field2;
select
a.fsocial_uuid,
a.fsocial_name,
count(a.Fdevice_UUID) as deviceNum,
count(case when a.FStatus=‘02‘ then 1 else null end) as deviceFailureNum,
round(COUNT(case when a.FStatus=‘02‘ then 1 else null end)/COUNT(a.FDEVICE_UUID), 4)*100||‘%‘ as deviceFailureRate,
round(COUNT(case when a.Fis_Active=‘1‘ then 1 else null end)/COUNT(a.Fis_Active), 4)*100||‘%‘ as deviceUsedNum,
round(count(case when b.Fis_Alarm=‘1‘ then 1 ELSE null end)/count(b.Fis_Alarm), 4)*100||‘%‘ as alrmPerDevice
from View_BD_DeviceInfo a
inner join View_MN_DeviceMonitorRec b on a.fsocial_uuid=b.fsocial_uuid
GROUP BY a.fsocial_uuid, a.fsocial_name
;

--11.View_BD_DeviceInfo和View_MN_DeviceMonitorRec View_BD_SocialPerson View_BS_PersonTrainTaskDtl 生成报表
select
a.fsocial_uuid,
a.fsocial_name,
count(c.FSocPerson_UUID) as Fscial_numOfPeople,
count(d.FPerson_UUID) as TrainedPeople,
round(count(case when d.Fis_Finish=‘1‘ then 1 else null end)/count(d.Fis_Finish), 4)*100||‘%‘ as passRate,
count(a.Fdevice_UUID) as deviceNum,
count(case when a.FStatus=‘02‘ then 1 else null end) as deviceFailureNum,
round(COUNT(case when a.FStatus=‘02‘ then 1 else null end)/COUNT(a.FDEVICE_UUID), 4)*100||‘%‘ as deviceFailureRate,
round(COUNT(case when a.Fis_Active=‘1‘ then 1 else null end)/COUNT(a.Fis_Active), 4)*100||‘%‘ as deviceUsedNum,
round(count(case when b.Fis_Alarm=‘1‘ then 1 ELSE null end)/count(b.Fis_Alarm), 4)*100||‘%‘ as alrmPerDevice,
count(e.FPatrolBusDtl_UUID) as numOfPatrol
from View_BD_DeviceInfo a
inner join View_MN_DeviceMonitorRec b on a.fsocial_uuid=b.fsocial_uuid
inner join View_BD_SocialPerson c on a.fsocial_uuid=c.fsocial_uuid
inner join View_BS_PersonTrainTaskDtl d on a.fsocial_uuid=d.fsocial_uuid
inner join View_bs_PatrolBusDtl e on a.fsocial_uuid=e.fsocial_uuid
GROUP BY a.fsocial_uuid, a.fsocial_name
;

--12.建表 建筑画像数据 ArchitecturePortraitData
create table ArchitecturePortraitData(
Fsocial_UUID varchar2(32) null,
Fsocial_Name varchar2(100) null,
Fscial_numOfPeople int null,
TrainedPeople int null,
passRate float(4) null,
deviceNum int null,
deviceFailureNum int null,
deviceFailureRate float(4) null,
deviceUsedNum float(4) null,
alrmPerDevice float(4) null,
numOfPatrol int null
)
;
comment on table ArchitecturePortraitData
is ‘建筑画像数据‘;
comment on column ArchitecturePortraitData.Fsocial_UUID
is ‘社会单位标识‘;
comment on column ArchitecturePortraitData.Fsocial_Name
is ‘社会单位名称‘;
comment on column ArchitecturePortraitData.Fscial_numOfPeople
is ‘各单位人数‘;
comment on column ArchitecturePortraitData.TrainedPeople
is ‘各单位参加培训人数‘;
comment on column ArchitecturePortraitData.passRate
is ‘各单位培训通过率‘;
comment on column ArchitecturePortraitData.deviceNum
is ‘各单位物联网设备总数‘;
comment on column ArchitecturePortraitData.deviceFailureNum
is ‘各单位物联网损坏数‘;
comment on column ArchitecturePortraitData.deviceFailureRate
is ‘各单位设备故障率‘;
comment on column ArchitecturePortraitData.deviceUsedNum
is ‘各单位设备启用率‘;
comment on column ArchitecturePortraitData.alrmPerDevice
is ‘各单位设备平均报警数‘;
comment on column ArchitecturePortraitData.numOfPatrol
is ‘各单位巡查数量‘;

alter table ArchitecturePortraitData add constraint pk_apd_fsocial_uuid primary key(Fsocial_UUID);

建筑数据预处理脚本