select branch_type,
use_range_type,
branch_type_name,
sum(value_sum),
sum(if(year_mouth = '2022-01', value_sum, 0)) '01',
sum(if(year_mouth = '2022-02', value_sum, 0)) '02',
sum(if(year_mouth = '2022-03', value_sum, 0)) '03',
sum(if(year_mouth = '2022-04', value_sum, 0)) '04',
sum(if(year_mouth = '2022-05', value_sum, 0)) '05',
sum(if(year_mouth = '2022-06', value_sum, 0)) '06',
sum(if(year_mouth = '2022-07', value_sum, 0)) '07',
sum(if(year_mouth = '2022-08', value_sum, 0)) '08',
sum(if(year_mouth = '2022-09', value_sum, 0)) '09',
sum(if(year_mouth = '2022-10', value_sum, 0)) '10',
sum(if(year_mouth = '2022-11', value_sum, 0)) '11',
sum(if(year_mouth = '2022-12', value_sum, 0)) '12'
from ( select building, func_id, parent_code, branch_type,
branch_type_name, use_range_type, value_sum,
current_hour, substr(current_hour, 12) hour_,
current_year, year_mouth, week_of_year, dt
from app_energy_15_min ) t1
group by branch_type, use_range_type, branch_type_name;
-- 夜间能耗
select branch_type,
use_range_type,
concat(branch_type, use_range_type) `branch_type_`,
branch_type_name,
sum(value_sum) '2022至今',
sum(if(year_mouth = '2022-01', value_sum, 0)) '01',
sum(if(year_mouth = '2022-02', value_sum, 0)) '02',
sum(if(year_mouth = '2022-03', value_sum, 0)) '03',
sum(if(year_mouth = '2022-04', value_sum, 0)) '04',
sum(if(year_mouth = '2022-05', value_sum, 0)) '05',
sum(if(year_mouth = '2022-06', value_sum, 0)) '06',
sum(if(year_mouth = '2022-07', value_sum, 0)) '07',
sum(if(year_mouth = '2022-08', value_sum, 0)) '08',
sum(if(year_mouth = '2022-09', value_sum, 0)) '09',
sum(if(year_mouth = '2022-10', value_sum, 0)) '10',
sum(if(year_mouth = '2022-11', value_sum, 0)) '11',
sum(if(year_mouth = '2022-12', value_sum, 0)) '12'
from (
select building, func_id, parent_code, branch_type, branch_type_name, use_range_type, value_sum, current_hour, substr(current_hour, 12) hour_,current_year, year_mouth, week_of_year, dt
from app_energy_15_min ) t1
where hour_ in ('21', '22', '23', '00', '01', '02', '03', '04', '05', '06')
group by branch_type, use_range_type, branch_type_name;
成都办公室1101080255 \ 西安办公室6101122004 \ 北京办公室1101080259
修改 192.168.100.142 的目录 /opt/app/python_tmp 下的配置文件 config.json \ 将 building 替换成都与西安的建筑id \
执行脚本
python3 /opt/app/python_tmp/start_mysql.py 10101 --start_time 20221001000000 --end_time 20221031000000
-- 成都西安夜间能耗
select building, func_id, meter, sum(data_value)
from (select building, func_id, meter, data_time, data_value, substr(data_time, 12, 2) hour_
from energy_15_min_ot) t1
where hour_ in ('21', '22', '23', '00', '01', '02', '03', '04', '05', '06')
group by building, func_id, meter;