月报数据表准备

表1 国网（地市、月份、类别、充电量、充电次数、平均充电量、用户数、平均每用户充电量）
create table monthly_stste_city as
select substr(t.trade_company,3,2) city，to_char(t.ending_time,'yyyy-mm') month,t.high_speed ||t.special_or_public  station_type,
round（sum（t.trade_electricity）,0）charging ,count(t.trade_electricity) times_charging,round（sum（t.trade_electricity）/count(t.trade_electricity),0）ave_charging,
count(distinct t.card_id) charging_user,round(sum（t.trade_electricity）/ count(distinct t.card_id)) ave_user_charging
from RECHARGER_REPORT t
where t.ending_time>=to_date('2017-01','yyyy-mm')
group by t.trade_company,to_char(t.ending_time,'yyyy-mm'),t.special_or_public,t.high_speed
order by to_char(t.ending_time,'yyyy-mm'),t.trade_company,t.special_or_public,t.high_speed;


表2 全省会（地市、月份、类别、充电量）
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月份社会充电量
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;


--计算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;


update  MON_ELE_ANALTSIS t  set t.charging=(
select s.charging from SOCIAL_CHARGE_DATA_MONTH s where t.city=s.city and t.month=s.month and t.station_type=s.station_type and s.month<>'2018-01'
and (s.station_type like '公共站'or s.station_type like '公交站'or s.station_type like '居民')
)where t.month<>'2018-01' and (t.station_type like '公共站'or t.station_type like '公交站'or t.station_type like '居民')


update  MON_ELE_ANALTSIS t  set t.charging=(
select s.charging from monthly_stste_city s where t.city=s.city and t.month=s.month and t.station_type=s.station_type 
and s.month<>'2018-01'  and (s.station_type like '城市专用'or s.station_type like '高速公用'or s.station_type like '城市专用')
)where t.month<>'2018-01'  and  (t.station_type like '城市专用'or t.station_type like '高速公用'or t.station_type like '城市专用');


update MON_ELE_ANALTSIS t set t.charging=round(t.charging,0);


update MON_ELE_ANALTSIS  m set m.charging='0' where m.charging is null;


表3 运营商（运营商、月份、类别、充电量）
create  table mon_ele_so_company as
select c.account_name,substr(c.city,10,2) city,c.month,(case when c.station_type='公交站' then '城市专用' when c.station_type='公共站' then '城市公用' ELSE c.station_type end) station_type
         ,sum(nvl(c.charging,0)) charging from social_charge_data c
         where c.station_attribute='社会报装' and (c.station_type='公交站' or c.station_type='公共站')
         group by  c.account_name,c.city,c.month,c.station_type
  union
  select c.account_name,substr(c.city,10,2) city,c.month,c.station_type,sum(c.charging) from social_charge_data c where
  c.station_attribute='社会报装' and c.station_type='居民' group by c.account_name,c.city,c.month,c.station_type
  ;




create table  mon_ele_company as 
  select t.account_name,t.city,'2017-01' month,t.station_type,round(t.charging/59*31,0) charging from mon_ele_so_company  t where t.month='2017-02' union
  select t.account_name,t.city,'2017-02',t.station_type,round(t.charging/59*28,0) from mon_ele_so_company  t where t.month='2017-02' union  
  select s.account_name,s.city,s.month,s.station_type,s.charging-e.charging as charging from (
         select t.account_name,t.city,t.month,t.station_type,t.charging from  mon_ele_so_company t ) s,
         (select t.account_name,t.city,t.month,t.station_type,t.charging from  mon_ele_so_company t ) e
         where s.city=e.city and add_months(to_date(s.month,'yyyy-mm'),-1)= to_date(e.month,'yyyy-mm') 
               and s.account_name=e.account_name and s.station_type=e.station_type and s.month<>'2018-01' union
  select  t.account_name,t.city,'2018-01',t.station_type,t.charging from mon_ele_so_company t where t.month='2018-01' union
  select '国网',substr(r.trade_company,3,2) city,to_char(r.ending_time,'yyyy-mm'),r.high_speed||r.special_or_public,sum(r.trade_electricity)
 from recharger_report r group by substr(r.trade_company,3,2),to_char(r.ending_time,'yyyy-mm'),r.high_speed||r.special_or_public 
 


表4 某月份工作日充电情况（地市、星期、类别、充电量、充电次数、平均充电量、用户数、平均每用户充电量）
create table mon_state_city_week as
select substr(r.trade_company,3,2) city,r.high_speed||r.special_or_public station_type,to_char(r.ending_time,'d') week,round(sum(r.trade_electricity),0) trade_electricity,
       count(r.trade_electricity) times_charging,round（sum（r.trade_electricity）/count(r.trade_electricity),0）ave_charging,
       count(distinct r.card_id) charging_user,round(sum（r.trade_electricity）/ count(distinct r.card_id)) ave_user_charging
       from recharger_report r where r.ending_time>=to_date('2018-01','yyyy-mm')
       group by to_char(r.ending_time,'d'),r.trade_company,r.high_speed||r.special_or_public;




表5 某月份各时间段充电情况（地市、时间段、类别、充电量、充电次数、平均充电量、用户数、平均每用户充电量）
create table mon_state_city_timegap as
select substr(r.trade_company,3,2) city,r.high_speed||r.special_or_public station_type,r.trade_timegap timegap,round(sum(r.trade_electricity),0) trade_electricity,
       count(r.trade_electricity) times_charging,round（sum（r.trade_electricity）/count(r.trade_electricity),0）ave_charging,
       count(distinct r.card_id) charging_user,round(sum（r.trade_electricity）/ count(distinct r.card_id)) ave_user_charging
       from recharger_report r where r.ending_time>=to_date('2018-01','yyyy-mm')
       group by r.trade_timegap ,r.trade_company,r.high_speed||r.special_or_public;




表6 西湖区西溪天堂充电站和江干区香樟街充电站某月充电情况
create table mon_XZJ_and_XX as
select r.station_name station_name,r.high_speed||r.special_or_public station_type,to_char(r.ending_time,'dd-mm') date_charging,round(sum(r.trade_electricity),0) trade_electricity,
       count(r.trade_electricity) times_charging,round（sum（r.trade_electricity）/count(r.trade_electricity),0）ave_charging,
       count(distinct r.card_id) charging_user,round(sum（r.trade_electricity）/ count(distinct r.card_id)) ave_user_charging
       from recharger_report r 
       where r.ending_time>=to_date('2017-06','yyyy-mm') and （r.station_name like '%西溪天堂充电站%' or r.station_name like '%香樟街充电站%' ）
       group by to_char(r.ending_time,'dd-mm'),r.station_name,r.high_speed||r.special_or_public
       order by r.station_name ,to_char(r.ending_time,'dd-mm');




表7 西湖区西溪天堂充电站和江干区香樟街充电站某月充电结束原因分析
create table mon_XZJXX_ending_reason as
select r.station_name station_name,to_char(r.ending_time,'dd-mm') date_charging,r.ending_reason,round(sum(r.trade_electricity),0) trade_electricity,
       count(r.trade_electricity) times_charging,round（sum（r.trade_electricity）/count(r.trade_electricity),0）ave_charging,
       count(distinct r.card_id) charging_user,round(sum（r.trade_electricity）/ count(distinct r.card_id)) ave_user_charging
       from recharger_report r 
       where r.ending_time>=to_date('2018-01','yyyy-mm') and （r.station_name like '%西湖区西溪天堂充电站%' or r.station_name like '%江干区香樟街充电站%' ）
       group by r.station_name,to_char(r.ending_time,'dd-mm') ,r.ending_reason
       order by r.station_name ,to_char(r.ending_time,'dd-mm');


表8 充电桩设备供应商的设备故障分析
create table mon_manufactor as 
select p.manufactruer,to_char(r.ending_time,'dd-mm') date_charging,r.ending_reason,count(r.trade_electricity) times_charging,
       count(distinct r.card_id) charging_user from recharger_report r ,piles p
       where   r.ending_time>=to_date('2018-01','yyyy-mm')
       group by to_char(r.ending_time,'dd-mm'),p.manufactruer,r.ending_reason
       order by p.manufactruer,to_char(r.ending_time,'dd-mm'),r.ending_reason