月报SQL代码

一、建表统计社会充电量（原充电记录为当年累计充电量）

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