## 博锐尚格能源数据 ### 总部 ```sql 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 \ ![img.png](img.png) \ 执行脚本 ```shell python3 /opt/app/python_tmp/start_mysql.py 10101 --start_time 20221001000000 --end_time 20221031000000 ``` ```sql -- 成都西安夜间能耗 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; ```