--le but de ce script est d'extraire des sorties globales démographiques et economiques et d'en calculer la moyenne et variance, afin d'avoir les données prêtes pour être injectées dans l'analyse de sensibilité declare @sim; --créer une table pour les moyennes des données démographiques CREATE TABLE "ASMeanDem" ( "NbIndividus015m" double precision, "NbIndividus015sigma" double precision, "NbIndividus015total" double precision, "NbIndividus1560m" double precision, "NbIndividus1560sigma" double precision, "NbIndividus1560total" double precision, "NbIndividus60+m" double precision, "NbIndividus60+sigma" double precision, "NbIndividus60+total" double precision, "NbFamiliesm" double precision, "NbFamiliessigma" double precision, "NbFamiliestotal" double precision, "NbMariablesm" double precision, "NbMariablessigma" double precision, "NbMariablestotal" double precision, "NbMarriagesm" double precision, "NbMarriagessigma" double precision, "NbMarriagestotal" double precision, "NbPersonsm" double precision, "NbPersonssigma" double precision, "NbPersonstotal" double precision, "Nbhouseholds" double precision, "NbHameaux" double precision, "TauxA" double precision, "NbBigman" double precision, "NbIndividus015diescat" double precision, "NbIndividus1560diescat" double precision, "NbIndividus60+diescat" double precision, "Nbtotalpersonscat" double precision, "NbhouseholdsImpact" double precision, "NbHameauxImpact" double precision, "khi2" double precision, "MeanR12" double precision, "StdR12" double precision, "MeanS" double precision, "StdS"double precision ) WITH ( OIDS=FALSE ); --créer une table pour les moyennes des données économiques CREATE TABLE "AsMeanEco" ( "Alimentationm" double precision, "Alimentationsig" double precision, "Alimentationtot" double precision, "Recoltebrutm" double precision, "Recoltebrutsig" double precision, "Recoltebruttot" double precision, "Recoltenetm" double precision, "Recoltenetsig" double precision, "Recoltenettot" double precision, "Semencem" double precision, "Semencesig" double precision, "Semence" double precision, "Surfacesautm" double precision, "Surfacesautsig" double precision, "Surfacesauttot" double precision, "Surfacessprm" double precision, "Surfacessprsig" double precision, "Surfacessprtot" double precision, "Nbovinsm" double precision, "Nbovinssig" double precision, "Nbovinstot" double precision, "NCaprinsm" double precision, "NCaprinssig" double precision, "NCaprinstot" double precision, "NPorcinsm" double precision, "NPorcinssig" double precision, "NPorcinstot" double precision, "Gbovinsm" double precision, "Gbovinssig" double precision, "Gbovinstot" double precision, "GCaprinsm" double precision, "GCaprinssig" double precision, "GCaprinstot" double precision, "GPorcinsm" double precision, "GPorcinssig" double precision, "GPorcinstot" double precision, "Chassem" double precision, "Chassesig" double precision, "Chassetot" double precision, "Cueilletteam" double precision, "Cueilletteasig" double precision, "Cueilletteatot" double precision, "Cueillettepm" double precision, "Cueillettepsig" double precision, "Cueilletteptot" double precision, "gainanim" double precision, "gainaniistd" double precision, "gainanitot" double precision ) WITH ( OIDS=FALSE ); --créer une table pour les variances des données démographiques CREATE TABLE "ASVarDem" ( "NbIndividus015m" double precision, "NbIndividus015sigma" double precision, "NbIndividus015total" double precision, "NbIndividus1560m" double precision, "NbIndividus1560sigma" double precision, "NbIndividus1560total" double precision, "NbIndividus60+m" double precision, "NbIndividus60+sigma" double precision, "NbIndividus60+total" double precision, "NbFamiliesm" double precision, "NbFamiliessigma" double precision, "NbFamiliestotal" double precision, "NbMariablesm" double precision, "NbMariablessigma" double precision, "NbMariablestotal" double precision, "NbMarriagesm" double precision, "NbMarriagessigma" double precision, "NbMarriagestotal" double precision, "NbPersonsm" double precision, "NbPersonssigma" double precision, "NbPersonstotal" double precision, "Nbhouseholds" double precision, "NbHameaux" double precision, "TauxA" double precision, "NbBigman" double precision, "NbIndividus015diescat" double precision, "NbIndividus1560diescat" double precision, "NbIndividus60+diescat" double precision, "Nbtotalpersonscat" double precision, "NbhouseholdsImpact" double precision, "NbHameauxImpact" double precision, "khi2" double precision, "MeanR12" double precision, "StdR12" double precision, "MeanS" double precision, "StdS"double precision ) WITH ( OIDS=FALSE ); --créer une table pour les variances des données économiques CREATE TABLE "AsVarEco" ( "Alimentationm" double precision, "Alimentationsig" double precision, "Alimentationtot" double precision, "Recoltebrutm" double precision, "Recoltebrutsig" double precision, "Recoltebruttot" double precision, "Recoltenetm" double precision, "Recoltenetsig" double precision, "Recoltenettot" double precision, "Semencem" double precision, "Semencesig" double precision, "Semence" double precision, "Surfacesautm" double precision, "Surfacesautsig" double precision, "Surfacesauttot" double precision, "Surfacessprm" double precision, "Surfacessprsig" double precision, "Surfacessprtot" double precision, "Nbovinsm" double precision, "Nbovinssig" double precision, "Nbovinstot" double precision, "NCaprinsm" double precision, "NCaprinssig" double precision, "NCaprinstot" double precision, "NPorcinsm" double precision, "NPorcinssig" double precision, "NPorcinstot" double precision, "Gbovinsm" double precision, "Gbovinssig" double precision, "Gbovinstot" double precision, "GCaprinsm" double precision, "GCaprinssig" double precision, "GCaprinstot" double precision, "GPorcinsm" double precision, "GPorcinssig" double precision, "GPorcinstot" double precision, "Chassem" double precision, "Chassesig" double precision, "Chassetot" double precision, "Cueilletteam" double precision, "Cueilletteasig" double precision, "Cueilletteatot" double precision, "Cueillettepm" double precision, "Cueillettepsig" double precision, "Cueilletteptot" double precision, "gainanim" double precision, "gainaniistd" double precision, "gainanitot" double precision ) WITH ( OIDS=FALSE ); --boucle pour chacune des simulations faites pour l'analyse de sensibilité (256 dans cette thèse) set @sim = 101; while @sim <= 356 --créer table temporaire pour les données démographiques begin CREATE TABLE DataOutputDem@sim ( "NbIndividus015m" double precision, "NbIndividus015sigma" double precision, "NbIndividus015total" double precision, "NbIndividus1560m" double precision, "NbIndividus1560sigma" double precision, "NbIndividus1560total" double precision, "NbIndividus60+m" double precision, "NbIndividus60+sigma" double precision, "NbIndividus60+total" double precision, "NbFamiliesm" double precision, "NbFamiliessigma" double precision, "NbFamiliestotal" double precision, "NbMariablesm" double precision, "NbMariablessigma" double precision, "NbMariablestotal" double precision, "NbMarriagesm" double precision, "NbMarriagessigma" double precision, "NbMarriagestotal" double precision, "NbPersonsm" double precision, "NbPersonssigma" double precision, "NbPersonstotal" double precision, "Nbhouseholds" double precision, "NbHameaux" double precision, "TauxA" double precision, "NbBigman" double precision, "NbIndividus015diescat" double precision, "NbIndividus1560diescat" double precision, "NbIndividus60+diescat" double precision, "Nbtotalpersonscat" double precision, "NbhouseholdsImpact" double precision, "NbHameauxImpact" double precision, "khi2" double precision, "MeanR12" double precision, "StdR12" double precision, "MeanS" double precision, "StdS"double precision ) WITH ( OIDS=FALSE ); --créer table temporaire pour les données économiques COPY DataOutputDem@sim FROM 'D:\obresocBEAN021015\Release\Simulation@sim\DataoutputDem@sim.txt' WITH DELIMITER ' ' CSV HEADER; CREATE TABLE DataOutputEco@sim ( "Alimentationm" double precision, "Alimentationsig" double precision, "Alimentationtot" double precision, "Recoltebrutm" double precision, "Recoltebrutsig" double precision, "Recoltebruttot" double precision, "Recoltenetm" double precision, "Recoltenetsig" double precision, "Recoltenettot" double precision, "Semencem" double precision, "Semencesig" double precision, "Semence" double precision, "Surfacesautm" double precision, "Surfacesautsig" double precision, "Surfacesauttot" double precision, "Surfacessprm" double precision, "Surfacessprsig" double precision, "Surfacessprtot" double precision, "Nbovinsm" double precision, "Nbovinssig" double precision, "Nbovinstot" double precision, "NCaprinsm" double precision, "NCaprinssig" double precision, "NCaprinstot" double precision, "NPorcinsm" double precision, "NPorcinssig" double precision, "NPorcinstot" double precision, "Gbovinsm" double precision, "Gbovinssig" double precision, "Gbovinstot" double precision, "GCaprinsm" double precision, "GCaprinssig" double precision, "GCaprinstot" double precision, "GPorcinsm" double precision, "GPorcinssig" double precision, "GPorcinstot" double precision, "Chassem" double precision, "Chassesig" double precision, "Chassetot" double precision, "Cueilletteam" double precision, "Cueilletteasig" double precision, "Cueilletteatot" double precision, "Cueillettepm" double precision, "Cueillettepsig" double precision, "Cueilletteptot" double precision, "gainanim" double precision, "gainaniistd" double precision, "gainanitot" double precision ) WITH ( OIDS=FALSE ); COPY DataOutputEco@sim FROM 'D:\obresocBEAN021015\Release\Simulation@sim\DataoutputEco@sim.txt' WITH DELIMITER ' ' CSV HEADER; --extraire les moyennes démographiques INSERT INTO "ASMeanDem" SELECT AVG(dataoutputdem@sim."NbIndividus015m"), AVG(dataoutputdem@sim."NbIndividus015sigma"), AVG(dataoutputdem@sim."NbIndividus015total"), AVG(dataoutputdem@sim."NbIndividus1560m"), AVG(dataoutputdem@sim."NbIndividus1560sigma"), AVG(dataoutputdem@sim."NbIndividus1560total"), AVG(dataoutputdem@sim."NbIndividus60+m"), AVG(dataoutputdem@sim."NbIndividus60+sigma"), AVG(dataoutputdem@sim."NbIndividus60+total"), AVG(dataoutputdem@sim."NbFamiliesm"), AVG(dataoutputdem@sim."NbFamiliessigma"), AVG(dataoutputdem@sim."NbFamiliestotal"), AVG(dataoutputdem@sim."NbMariablesm"), AVG(dataoutputdem@sim."NbMariablessigma"), AVG(dataoutputdem@sim."NbMariablestotal"), AVG(dataoutputdem@sim."NbMarriagesm"), AVG(dataoutputdem@sim."NbMarriagessigma"), AVG(dataoutputdem@sim."NbMarriagestotal"), AVG(dataoutputdem@sim."NbPersonsm"), AVG(dataoutputdem@sim."NbPersonssigma"), AVG(dataoutputdem@sim."NbPersonstotal"), AVG(dataoutputdem@sim."Nbhouseholds"), AVG(dataoutputdem@sim."NbHameaux"), AVG(dataoutputdem@sim."TauxA"), AVG(dataoutputdem@sim."NbBigman"), AVG(dataoutputdem@sim."NbIndividus015diescat"), AVG(dataoutputdem@sim."NbIndividus1560diescat"), AVG(dataoutputdem@sim."NbIndividus60+diescat"), AVG(dataoutputdem@sim."Nbtotalpersonscat"), AVG(dataoutputdem@sim."NbhouseholdsImpact"), AVG(dataoutputdem@sim."NbHameauxImpact"), AVG(dataoutputdem@sim."khi2"), AVG(dataoutputdem@sim."MeanR12"), AVG(dataoutputdem@sim."StdR12"), AVG(dataoutputdem@sim."MeanS"), AVG(dataoutputdem@sim."StdS") FROM public.dataoutputdem@sim ; --extraire les moyennes économiques INSERT INTO "AsMeanEco" SELECT avg(dataoutputeco@sim."Alimentationm"), avg(dataoutputeco@sim."Alimentationsig"), avg(dataoutputeco@sim."Alimentationtot"), avg(dataoutputeco@sim."Recoltebrutm"), avg(dataoutputeco@sim."Recoltebrutsig"), avg(dataoutputeco@sim."Recoltebruttot"), avg(dataoutputeco@sim."Recoltenetm"), avg(dataoutputeco@sim."Recoltenetsig"), avg(dataoutputeco@sim."Recoltenettot"), avg(dataoutputeco@sim."Semencem"), avg(dataoutputeco@sim."Semencesig"), avg(dataoutputeco@sim."Semence"), avg(dataoutputeco@sim."Surfacesautm"), avg(dataoutputeco@sim."Surfacesautsig"), avg(dataoutputeco@sim."Surfacesauttot"), avg(dataoutputeco@sim."Surfacessprm"), avg(dataoutputeco@sim."Surfacessprsig"), avg(dataoutputeco@sim."Surfacessprtot"), avg(dataoutputeco@sim."Nbovinsm"), avg(dataoutputeco@sim."Nbovinssig"), avg(dataoutputeco@sim."Nbovinstot"), avg(dataoutputeco@sim."NCaprinsm"), avg(dataoutputeco@sim."NCaprinssig"), avg(dataoutputeco@sim."NCaprinstot"), avg(dataoutputeco@sim."NPorcinsm"), avg(dataoutputeco@sim."NPorcinssig"), avg(dataoutputeco@sim."NPorcinstot"), avg(dataoutputeco@sim."Gbovinsm"), avg(dataoutputeco@sim."Gbovinssig"), avg(dataoutputeco@sim."Gbovinstot"), avg(dataoutputeco@sim."GCaprinsm"), avg(dataoutputeco@sim."GCaprinssig"), avg(dataoutputeco@sim."GCaprinstot"), avg(dataoutputeco@sim."GPorcinsm"), avg(dataoutputeco@sim."GPorcinssig"), avg(dataoutputeco@sim."GPorcinstot"), avg(dataoutputeco@sim."Chassem"), avg(dataoutputeco@sim."Chassesig"), avg(dataoutputeco@sim."Chassetot"), avg(dataoutputeco@sim."Cueilletteam"), avg(dataoutputeco@sim."Cueilletteasig"), avg(dataoutputeco@sim."Cueilletteatot"), avg(dataoutputeco@sim."Cueillettepm"), avg(dataoutputeco@sim."Cueillettepsig"), avg(dataoutputeco@sim."Cueilletteptot"), avg(dataoutputeco@sim."gainanim"), avg(dataoutputeco@sim."gainaniistd"), avg(dataoutputeco@sim."gainanitot") FROM public.dataoutputeco@sim ; --extraire les variances démographiques INSERT INTO "ASVarDem" SELECT variance(dataoutputdem@sim."NbIndividus015m"), variance(dataoutputdem@sim."NbIndividus015sigma"), variance(dataoutputdem@sim."NbIndividus015total"), variance(dataoutputdem@sim."NbIndividus1560m"), variance(dataoutputdem@sim."NbIndividus1560sigma"), variance(dataoutputdem@sim."NbIndividus1560total"), variance(dataoutputdem@sim."NbIndividus60+m"), variance(dataoutputdem@sim."NbIndividus60+sigma"), variance(dataoutputdem@sim."NbIndividus60+total"), variance(dataoutputdem@sim."NbFamiliesm"), variance(dataoutputdem@sim."NbFamiliessigma"), variance(dataoutputdem@sim."NbFamiliestotal"), variance(dataoutputdem@sim."NbMariablesm"), variance(dataoutputdem@sim."NbMariablessigma"), variance(dataoutputdem@sim."NbMariablestotal"), variance(dataoutputdem@sim."NbMarriagesm"), variance(dataoutputdem@sim."NbMarriagessigma"), variance(dataoutputdem@sim."NbMarriagestotal"), variance(dataoutputdem@sim."NbPersonsm"), variance(dataoutputdem@sim."NbPersonssigma"), variance(dataoutputdem@sim."NbPersonstotal"), variance(dataoutputdem@sim."Nbhouseholds"), variance(dataoutputdem@sim."NbHameaux"), variance(dataoutputdem@sim."TauxA"), variance(dataoutputdem@sim."NbBigman"), variance(dataoutputdem@sim."NbIndividus015diescat"), variance(dataoutputdem@sim."NbIndividus1560diescat"), variance(dataoutputdem@sim."NbIndividus60+diescat"), variance(dataoutputdem@sim."Nbtotalpersonscat"), variance(dataoutputdem@sim."NbhouseholdsImpact"), variance(dataoutputdem@sim."NbHameauxImpact"), variance(dataoutputdem@sim."khi2"), variance(dataoutputdem@sim."MeanR12"), variance(dataoutputdem@sim."StdR12"), variance(dataoutputdem@sim."MeanS"), variance(dataoutputdem@sim."StdS") FROM public.dataoutputdem@sim ; --extraire les variances économiques INSERT INTO "AsVarEco" SELECT variance(dataoutputeco@sim."Alimentationm"), variance(dataoutputeco@sim."Alimentationsig"), variance(dataoutputeco@sim."Alimentationtot"), variance(dataoutputeco@sim."Recoltebrutm"), variance(dataoutputeco@sim."Recoltebrutsig"), variance(dataoutputeco@sim."Recoltebruttot"), variance(dataoutputeco@sim."Recoltenetm"), variance(dataoutputeco@sim."Recoltenetsig"), variance(dataoutputeco@sim."Recoltenettot"), variance(dataoutputeco@sim."Semencem"), variance(dataoutputeco@sim."Semencesig"), variance(dataoutputeco@sim."Semence"), variance(dataoutputeco@sim."Surfacesautm"), variance(dataoutputeco@sim."Surfacesautsig"), variance(dataoutputeco@sim."Surfacesauttot"), variance(dataoutputeco@sim."Surfacessprm"), variance(dataoutputeco@sim."Surfacessprsig"), variance(dataoutputeco@sim."Surfacessprtot"), variance(dataoutputeco@sim."Nbovinsm"), variance(dataoutputeco@sim."Nbovinssig"), variance(dataoutputeco@sim."Nbovinstot"), variance(dataoutputeco@sim."NCaprinsm"), variance(dataoutputeco@sim."NCaprinssig"), variance(dataoutputeco@sim."NCaprinstot"), variance(dataoutputeco@sim."NPorcinsm"), variance(dataoutputeco@sim."NPorcinssig"), variance(dataoutputeco@sim."NPorcinstot"), variance(dataoutputeco@sim."Gbovinsm"), variance(dataoutputeco@sim."Gbovinssig"), variance(dataoutputeco@sim."Gbovinstot"), variance(dataoutputeco@sim."GCaprinsm"), variance(dataoutputeco@sim."GCaprinssig"), variance(dataoutputeco@sim."GCaprinstot"), variance(dataoutputeco@sim."GPorcinsm"), variance(dataoutputeco@sim."GPorcinssig"), variance(dataoutputeco@sim."GPorcinstot"), variance(dataoutputeco@sim."Chassem"), variance(dataoutputeco@sim."Chassesig"), variance(dataoutputeco@sim."Chassetot"), variance(dataoutputeco@sim."Cueilletteam"), variance(dataoutputeco@sim."Cueilletteasig"), variance(dataoutputeco@sim."Cueilletteatot"), variance(dataoutputeco@sim."Cueillettepm"), variance(dataoutputeco@sim."Cueillettepsig"), variance(dataoutputeco@sim."Cueilletteptot"), variance(dataoutputeco@sim."gainanim"), variance(dataoutputeco@sim."gainaniistd"), variance(dataoutputeco@sim."gainanitot") FROM public.dataoutputeco@sim ; SET @sim = @sim + 1; END --exporter les données en format CSV (ouvrable avec Excel) COPY (SELECT * FROM "ASMeanDem") TO 'D:/AS/BEAN2710/AsMeanDem.csv' DELIMITER ';' CSV HEADER; COPY (SELECT * FROM "ASVarDem") TO 'D:/AS/BEAN2710/AsVarDem.csv' DELIMITER ';' CSV HEADER; COPY (SELECT * FROM "AsMeanEco") TO 'D:/AS/BEAN2710/AsMeanEco.csv' DELIMITER ';' CSV HEADER; COPY (SELECT * FROM "AsVarEco") TO 'D:/AS/BEAN2710/AsVarEco.csv' DELIMITER ';' CSV HEADER;