月报--充电量报表

一、计算各地市各月份社会月充电量

1.新建表并计算1月份社会充电量
create table SOCIAL_CHARGE_DATA_month(city,month,station_type,charging) as
select t.city,'2017-01',t.station_type,(sum(t.charging)/59)*31 from SOCIAL_CHARGE_DATA  t where t.station_attribute='社会报装' 
and t.month='2017-02' 

group by t.city,t.station_type order by city,station_type;

2.计算2月份社会充电量

insert into SOCIAL_CHARGE_DATA_month(city,month,station_type,charging)
select t.city,'2017-02',t.station_type,(sum(t.charging)/59)*28 from SOCIAL_CHARGE_DATA  t where t.station_attribute='社会报装' and t.month='2017-02' group by t.city,t.station_type

order by city,station_type;

3.计算2017年3—12月社会充电量
insert into SOCIAL_CHARGE_DATA_month(city,month,station_type,charging)
select s.city,s.month,s.station_type,s.charging-e.charging as charging from (
select t.city,t.month,t.station_type,sum(t.charging) charging from  SOCIAL_CHARGE_DATA t where t.station_attribute='社会报装' group by t.city,t.month,t.station_type) s,
(select t.city,t.month,t.station_type,sum(t.charging) charging from  SOCIAL_CHARGE_DATA t where t.station_attribute='社会报装' group by t.city,t.month,t.station_type) e
where s.city=e.city and add_months(to_date(s.month,'yyyy-mm'),-1)= to_date(e.month,'yyyy-mm') and s.station_type=e.station_type
order by s.month,s.city,s.station_type;

4.计算2018年1月份社会充电量

insert into SOCIAL_CHARGE_DATA_month(city,month,station_type,charging)
select t.city,'2018-01',t.station_type,sum(t.charging) from SOCIAL_CHARGE_DATA t where t.station_attribute='社会报装' and t.month='2018-01' group by t.city,t.station_type
order by city,station_type;

5.新建表将个类型充电量按字段排列
create table ReCHARGE_DATA_month_report as select t.city,t.month,t.charging as resident_charging,0 as pub_charging,0 as bus_charging
from SOCIAL_CHARGE_DATA_month t where t.station_type='居民';

update ReCHARGE_DATA_month_report t set t.bus_charging=(select s.charging from SOCIAL_CHARGE_DATA_month s where s.city=t.city and s.month=t.month and s.station_type='公交站');

update ReCHARGE_DATA_month_report t set t.pub_charging=(select s.charging from SOCIAL_CHARGE_DATA_month s where s.city=t.city and s.month=t.month and s.station_type='公共站');

update ReCHARGE_DATA_month_report t set t.bus_charging=0 where bus_charging is null ;
update ReCHARGE_DATA_month_report t.pub_charging=0 where pub_charging is null ;


二、新建表计算国网充电量
1.建表计算国网城市公用充电量
create table st_pub_city as 

select substr(g.trade_company,3,2) city,

to_char(g.ending_time,'yyyy-mm') month,round(sum(g.trade_electricity),0)ST__PUB_CHARGING,count(g.trade_electricity) ST__PUB_TIME ,

round(sum(g.trade_electricity)/count(g.trade_electricity),0) ST__PUB_PERCHARGING,count(distinct g.card_id) ST__PUB_USERNUMBER,
round(sum(g.trade_electricity)/ count(distinct g.card_id)) ST__PUB_PER_USERCHARGING
from RECHARGER_REPORT g
where g.ending_time>=to_date('2017-01','yyyy-mm') and g.special_or_public='公用'and g.high_speed='城市'
group by g.trade_company,to_char(g.ending_time,'yyyy-mm'),g.special_or_public,g.high_speed;


2.建表计算国网公交专用充电量
create table st_bus as 
select substr(g.trade_company,3,2) city,to_char(g.ending_time,'yyyy-mm') month,round(sum(g.trade_electricity),0)ST__bus_CHARGING,count(g.trade_electricity) ST__bus_TIME,round(sum(g.trade_electricity)/count(g.trade_electricity),0)ST__bus_PERCHARGING,
count(distinct g.card_id) ST__bus_USERNUMBER ,round(sum(g.trade_electricity)/ count(distinct g.card_id),0) ST__bus_PER_USERCHARGING
from RECHARGER_REPORT g
where g.ending_time>=to_date('2017-01','yyyy-mm') and g.special_or_public='专用'and g.high_speed='城市'
group by g.trade_company,to_char(g.ending_time,'yyyy-mm'),g.special_or_public,g.high_speed


3.建表计算国网高速充电量
create table st_highSpeed as 
select substr(g.trade_company,3,2) city,to_char(g.ending_time,'yyyy-mm') month,round（sum（g.trade_electricity）,0）ST_HIGH_CHARGING,count(g.trade_electricity) ST__HIGH_TIME,round（sum（g.trade_electricity）/count(g.trade_electricity),0）ST__HIGH_PERCHARGING,
count(distinct g.card_id) ST__HIGH_USERNUMBER,round(sum（g.trade_electricity）/ count(distinct g.card_id),0) ST__HIGH_PER_USERCHARGING
from RECHARGER_REPORT g
where g.ending_time>=to_date('2017-01','yyyy-mm') and g.special_or_public='公用'and g.high_speed='高速'
group by g.trade_company,to_char(g.ending_time,'yyyy-mm'),g.special_or_public,g.high_speed


4.新建视图将各类型充电量按字段排列
create or replace view v_tmp as 
select a.city,a.month,
a.ST__PUB_CHARGING,a.ST__PUB_TIME,a.ST__PUB_PERCHARGING,a.ST__PUB_USERNUMBER,a.ST__PUB_PER_USERCHARGING,
b.ST__bus_CHARGING,b.ST__bus_TIME,b.ST__bus_PERCHARGING,b.ST__bus_USERNUMBER,b.ST__bus_PER_USERCHARGING,
c.ST_HIGH_CHARGING,c.ST__HIGH_TIME,c.ST__HIGH_PERCHARGING,c.ST__HIGH_USERNUMBER,c.ST__HIGH_PER_USERCHARGING
from 
st_pub_city a,
st_bus b,
st_highSpeed c
where a.city=b.city(+) and a.city=c.city and a.month=b.month(+) and a.month=c.month;

三、建社会和国网充电量宽表

1.将国网充电量插入社会充电量的表中

update ReCHARGE_DATA_month_report m set (m.ST__PUB_CHARGING,m.ST__PUB_TIME,m.ST__PUB_PERCHARGING,m.ST__PUB_USERNUMBER,m.ST__PUB_PER_USERCHARGING,
m.ST_bus_CHARGING,m.ST_bus_TIME,m.ST_bus_PERCHARGING,m.ST_bus_USERNUMBER,m.ST_bus_PER_USERCHARGING,
m.ST_HIGH_CHARGING,m.ST_HIGH_TIME,m.ST_HIGH_PERCHARGING,m.ST_HIGH_USERNUMBER,m.ST_HIGH_PER_USERCHARGING)=
(select 
a.ST__PUB_CHARGING,a.ST__PUB_TIME,a.ST__PUB_PERCHARGING,a.ST__PUB_USERNUMBER,a.ST__PUB_PER_USERCHARGING,
b.ST__bus_CHARGING,b.ST__bus_TIME,b.ST__bus_PERCHARGING,b.ST__bus_USERNUMBER,b.ST__bus_PER_USERCHARGING,
c.ST_HIGH_CHARGING,c.ST__HIGH_TIME,c.ST__HIGH_PERCHARGING,c.ST__HIGH_USERNUMBER,c.ST__HIGH_PER_USERCHARGING
from 
st_pub_city a,
st_bus b,
st_highSpeed c
where a.city=b.city(+) and a.city=c.city and a.month=b.month(+) and a.month=c.month and m.city like '%'||a.city||'%' and m.month=a.month );---左关联

2.查询社会充电量的表中是否包含国网充电量的统计表所有数据
select * from v_tmp v where not exists (
select 1 from RECHARGE_DATA_MONTH_REPORT t where t.city=v.city and t.month=v.month)

3.将不含的数据插入社会统计表，形成各地市各月份充电量宽表。
insert into ReCHARGE_DATA_month_report(city,month,ST__PUB_CHARGING,ST__PUB_TIME,ST__PUB_PERCHARGING,ST__PUB_USERNUMBER,ST__PUB_PER_USERCHARGING,
ST_bus_CHARGING,ST_bus_TIME,ST_bus_PERCHARGING,ST_bus_USERNUMBER,ST_bus_PER_USERCHARGING,
ST_HIGH_CHARGING,ST_HIGH_TIME,ST_HIGH_PERCHARGING,ST_HIGH_USERNUMBER,ST_HIGH_PER_USERCHARGING)
select city,month,ST__PUB_CHARGING,ST__PUB_TIME,ST__PUB_PERCHARGING,ST__PUB_USERNUMBER,ST__PUB_PER_USERCHARGING,
ST__bus_CHARGING,ST__bus_TIME,ST__bus_PERCHARGING,ST__bus_USERNUMBER,ST__bus_PER_USERCHARGING,
ST_HIGH_CHARGING,ST__HIGH_TIME,ST__HIGH_PERCHARGING,ST__HIGH_USERNUMBER,ST__HIGH_PER_USERCHARGING from v_tmp v where not exists (
select 1 from RECHARGE_DATA_MONTH_REPORT t where t.city=v.city and t.month=v.month)