data_analysis_wind_turbine/dataStorage_datang/sql/Script-datang.sql

select *
from info_curve_power_turbine icpt 
where 1=1
and icpt.standard_model = 'CCWE1500-82.DF'
-- and  icpt.standard_model ='CCWE1500-87.DF'
-- and icpt.wind_speed >=0.5 and icpt.wind_speed <3.5
and icpt.wind_speed >=18
order by icpt.wind_farm_id,icpt.description ,icpt.standard_model ,icpt.wind_speed 
;


select model,manufacturer
from info_model_turbine imt 
group by model,manufacturer
;

select dst.no_model_turbine 
from data_scada_turbine dst 
group by dst.no_model_turbine 
;

select dst.id_turbine 
from data_scada_turbine dst 
group by dst.id_turbine 
;

select count(1)
from data_scada_turbine dst 
;


select dst.id_farm,dst.name_farm,dst.id_turbine,dst.data_time,dst.*
from data_scada_turbine dst 
where 1=1
and dst.id_farm = 'Twr2SPNt'
;


select dst.id_farm,dst.name_farm,dst.id_turbine,dst.data_time,dst.no_model_turbine,dst.wtg_sts,dst.scada_wtg_sts,dst.hyd_station_prs , dst.wind_spd ,dst.wind_dir,dst.yaw_ang,dst.yaw_to_wind_ang  ,dst.impeller_spd ,dst.rotor_spd,dst.pitch_ang_act_1 ,dst.pitch_ang_act_2 ,dst.pitch_ang_act_3,dst.pitch_motor_cur_1  ,dst.pitch_motor_cur_2 ,dst.pitch_motor_cur_3 ,dst.gearbox_spd_1 ,dst.gearbox_spd_2  , dst.gen_spd ,dst.p_active 
from data_scada_turbine dst 
where 1=1
and dst.rotor_spd is not null
and (
dst.gearbox_spd_1 is not null 
or 
dst.gearbox_spd_2  is not null 
)
and dst.gen_spd is not null 
and dst.p_active is not null 
;



select *
from info_curve_power_turbine icpt 
where 1=1
and icpt.standard_model ='CCWE1500-82.DF'
;


select 
it.wind_farm_id ,it.model , 
imt.no_model ,
imt.rated_capacity ,imt.cut_in_wind_speed,imt.cut_out_wind_speed,imt.rotor_diameter,imt.hub_height ,
icpt.rated_wind_speed
from info_turbine it ,info_model_turbine imt ,info_curve_power_turbine icpt 
where 1=1
and it.model =imt.model
and it.cut_in_wind_speed =imt.cut_in_wind_speed
and it.cut_out_wind_speed =imt.cut_out_wind_speed
and it.rotor_diameter =imt.rotor_diameter
and it.hub_height =imt.hub_height 
and it.wind_farm_id =icpt.wind_farm_id
and it.model =icpt.standard_model
group by 
it.wind_farm_id ,it.model , 
imt.no_model ,
imt.rated_capacity ,imt.cut_in_wind_speed,imt.cut_out_wind_speed,imt.rotor_diameter,imt.hub_height ,
icpt.rated_wind_speed
;

SET SESSION tidb_mem_quota_query = 4 << 30; -- TiDB 对单条查询sql 增加最大内存
--
select 
it.wind_farm_id,it.turbine_id ,it.model,
icpt.rated_wind_speed,
imt.rated_capacity ,imt.cut_in_wind_speed,imt.cut_out_wind_speed,imt.rotor_diameter,imt.hub_height ,
dst.data_time,dst.wtg_sts,dst.scada_wtg_sts,dst.hyd_station_prs , dst.wind_spd ,dst.wind_dir,dst.yaw_ang,dst.yaw_to_wind_ang  ,dst.impeller_spd ,dst.rotor_spd,dst.pitch_ang_act_1 ,dst.pitch_ang_act_2 ,dst.pitch_ang_act_3,dst.pitch_motor_cur_1  ,dst.pitch_motor_cur_2 ,dst.pitch_motor_cur_3 ,dst.gearbox_spd_1 ,dst.gearbox_spd_2  , dst.gen_spd ,dst.p_active
from info_turbine it ,info_curve_power_turbine icpt,info_model_turbine imt ,data_scada_turbine dst  
where 1=1
and it.wind_farm_id =dst.id_farm
and it.turbine_id =dst.id_turbine
and it.model =dst.no_model_turbine
and it.model =imt.model
and it.cut_in_wind_speed =imt.cut_in_wind_speed
and it.cut_out_wind_speed =imt.cut_out_wind_speed
and it.rotor_diameter =imt.rotor_diameter
and it.hub_height =imt.hub_height 
and it.wind_farm_id =icpt.wind_farm_id
and it.model =icpt.standard_model
and dst.rotor_spd is not null
and (
dst.gearbox_spd_1 is not null 
or 
dst.gearbox_spd_2  is not null 
)
and dst.gen_spd is not null 
and dst.p_active is not null 
and it.model ='CCWE1500-82.DF'
;

-- 推算 机型的 额定叶轮转速、额定发电机转速、 传动比，获取数据sql 
select 
it.wind_farm_id,it.turbine_id ,
it.model,
icpt.rated_wind_speed,
imt.no_model,
imt.rated_capacity ,imt.cut_in_wind_speed,imt.cut_out_wind_speed,imt.rotor_diameter,imt.hub_height ,
dst.data_time,
dst.wind_spd ,dst.rotor_spd,dst.gen_spd ,dst.p_active

from info_turbine it ,info_curve_power_turbine icpt,info_model_turbine imt ,data_scada_turbine dst  
where 1=1
and it.wind_farm_id =dst.id_farm
and it.turbine_id =dst.id_turbine
and it.model =dst.no_model_turbine
and it.model =imt.model
and it.cut_in_wind_speed =imt.cut_in_wind_speed
and it.cut_out_wind_speed =imt.cut_out_wind_speed
and it.rotor_diameter =imt.rotor_diameter
and it.hub_height =imt.hub_height 
and it.wind_farm_id =icpt.wind_farm_id
and it.model =icpt.standard_model

and dst.wind_spd>=icpt.rated_wind_speed
and (dst.p_active>=imt.rated_capacity*0.95 and dst.p_active<=imt.rated_capacity*1.05)
;