Extraction of mean, max, min and sd extraction inside 5-95 quantiles

Question

Extraction of mean, max, min and sd extraction inside 5-95 quantiles

SSC Enthusiast

Points: 114

October 1, 2021 at 6:54 pm

I'd like to extraction the mean, max, min and sd extraction inside 5-95 quantiles for the variables B2, B3, B4, B8, NDVI, SAVI, SIPI, SR, RGI, TVI, MSR, PRI, GNDVI, PSRI, GCI aggregate by AGE and ESPAC variables inside CMPC table:

    CREATE TABLE CMPC(
       x          NUMERIC(8,4) NOT NULL 
      ,y          NUMERIC(8,4) NOT NULL
      ,stand      VARCHAR(11) NOT NULL
      ,date       DATE  NOT NULL
      ,B2         INTEGER  NOT NULL
      ,B3         INTEGER  NOT NULL
      ,B4         INTEGER  NOT NULL
      ,B8         INTEGER  NOT NULL
      ,NDVI       VARCHAR(17) NOT NULL
      ,SAVI       VARCHAR(16) NOT NULL
      ,SIPI       VARCHAR(17) NOT NULL
      ,SR         VARCHAR(16) NOT NULL
      ,RGI        VARCHAR(17) NOT NULL
      ,TVI        INTEGER  NOT NULL
      ,MSR        VARCHAR(16) NOT NULL
      ,PRI        VARCHAR(18) NOT NULL
      ,GNDVI      VARCHAR(17) NOT NULL
      ,PSRI       VARCHAR(19) NOT NULL
      ,GCI        VARCHAR(16) NOT NULL
      ,ID_PROJETO INTEGER  NOT NULL
      ,PROJETO    VARCHAR(7) NOT NULL
      ,CD_TALHAO  VARCHAR(4) NOT NULL
      ,DATA_PLANT DATE  NOT NULL
      ,ESPECIE    VARCHAR(7) NOT NULL
      ,ESPAC      VARCHAR(8) NOT NULL
      ,AGE_1      NUMERIC(4,1) NOT NULL
      ,AGE        INTEGER  NOT NULL
    );
    INSERT INTO CMPC(x,y,stand,date,B2,B3,B4,B8,NDVI,SAVI,SIPI,SR,RGI,TVI,MSR,PRI,GNDVI,PSRI,GCI,ID_PROJETO,PROJETO,CD_TALHAO,DATA_PLANT,ESPECIE,ESPAC,AGE_1,AGE) VALUES (-52.5502,-30.8295,'ABRANJO001A','2019-01-28',213,361,227,3033,'0.860736196319018','4549.37867450561','1.00498930862438','13.3612334801762','0.628808864265928',173720,'3.65530210518586','-0.257839721254355','0.787271655863288','-0.044180679195516','7.4016620498615',245,'ABRANJO','001A','2008-07-15','SALIGNA','3.5x2.14',10.5,11);
    INSERT INTO CMPC(x,y,stand,date,B2,B3,B4,B8,NDVI,SAVI,SIPI,SR,RGI,TVI,MSR,PRI,GNDVI,PSRI,GCI,ID_PROJETO,PROJETO,CD_TALHAO,DATA_PLANT,ESPECIE,ESPAC,AGE_1,AGE) VALUES (-52.5502,-30.8295,'ABRANJO001A','2019-02-22',154,276,145,3470,'0.919778699861687','5204.93459630131','0.997293233082707','23.9310344827586','0.52536231884058',204740,'4.89193565807632','-0.283720930232558','0.852642819006941','-0.0377521613832853','11.5724637681159',245,'ABRANJO','001A','2008-07-15','SALIGNA','3.5x2.14',10.6,11);
    INSERT INTO CMPC(x,y,stand,date,B2,B3,B4,B8,NDVI,SAVI,SIPI,SR,RGI,TVI,MSR,PRI,GNDVI,PSRI,GCI,ID_PROJETO,PROJETO,CD_TALHAO,DATA_PLANT,ESPECIE,ESPAC,AGE_1,AGE) VALUES (-52.5502,-30.8295,'ABRANJO001A','2019-03-24',178,312,167,3609,'0.911546610169492','5413.42723311547','0.996804183614178','21.6107784431138','0.53525641025641',212320,'4.64873944667947','-0.273469387755102','0.840856924254017','-0.0401773344416736','10.5673076923077',245,'ABRANJO','001A','2008-07-15','SALIGNA','3.5x2.14',10.7,11);
    INSERT INTO CMPC(x,y,stand,date,B2,B3,B4,B8,NDVI,SAVI,SIPI,SR,RGI,TVI,MSR,PRI,GNDVI,PSRI,GCI,ID_PROJETO,PROJETO,CD_TALHAO,DATA_PLANT,ESPECIE,ESPAC,AGE_1,AGE) VALUES (-52.5502,-30.8295,'ABRANJO001A','2019-05-18',77,230,131,3479,'0.927423822714681','5218.44131700762','1.01612903225806','26.5572519083969','0.569565217391304',204840,'5.15337286720037','-0.498371335504886','0.875977352386088','-0.0284564530037367','14.1260869565217',245,'ABRANJO','001A','2008-07-15','SALIGNA','3.5x2.14',10.8,11);
    INSERT INTO CMPC(x,y,stand,date,B2,B3,B4,B8,NDVI,SAVI,SIPI,SR,RGI,TVI,MSR,PRI,GNDVI,PSRI,GCI,ID_PROJETO,PROJETO,CD_TALHAO,DATA_PLANT,ESPECIE,ESPAC,AGE_1,AGE) VALUES (-52.5502,-30.8295,'ABRANJO001A','2019-06-27',127,292,181,4113,'0.915696320447136','6169.43095994914','1.01373346897253','22.7237569060773','0.61986301369863',240360,'4.76694418952827','-0.393794749403341','0.867423382519864','-0.0269876002917578','13.0856164383562',245,'ABRANJO','001A','2008-07-15','SALIGNA','3.5x2.14',11,11);
    INSERT INTO CMPC(x,y,stand,date,B2,B3,B4,B8,NDVI,SAVI,SIPI,SR,RGI,TVI,MSR,PRI,GNDVI,PSRI,GCI,ID_PROJETO,PROJETO,CD_TALHAO,DATA_PLANT,ESPECIE,ESPAC,AGE_1,AGE) VALUES (-52.5502,-30.8295,'ABRANJO001A','2019-07-07',97,266,142,3926,'0.930186823992134','5888.94371779627','1.01189217758985','27.6478873239437','0.533834586466165',232000,'5.25812583759115','-0.465564738292011','0.873091603053435','-0.0315843097300051','13.7593984962406',245,'ABRANJO','001A','2008-07-15','SALIGNA','3.5x2.14',11,11);
    INSERT INTO CMPC(x,y,stand,date,B2,B3,B4,B8,NDVI,SAVI,SIPI,SR,RGI,TVI,MSR,PRI,GNDVI,PSRI,GCI,ID_PROJETO,PROJETO,CD_TALHAO,DATA_PLANT,ESPECIE,ESPAC,AGE_1,AGE) VALUES (-52.5502,-30.8295,'ABRANJO001A','2019-08-06',129,279,183,3099,'0.888482632541133','4648.40588033602','1.01851851851852','16.9344262295082','0.655913978494624',178800,'4.11514595482447','-0.367647058823529','0.834813499111901','-0.0309777347531462','10.1075268817204',245,'ABRANJO','001A','2008-07-15','SALIGNA','3.5x2.14',11.1,11);
    INSERT INTO CMPC(x,y,stand,date,B2,B3,B4,B8,NDVI,SAVI,SIPI,SR,RGI,TVI,MSR,PRI,GNDVI,PSRI,GCI,ID_PROJETO,PROJETO,CD_TALHAO,DATA_PLANT,ESPECIE,ESPAC,AGE_1,AGE) VALUES (-52.5502,-30.8295,'ABRANJO001A','2019-12-24',123,281,147,3340,'0.915686836822484','5009.9309534993','1.00751644221735','22.7210884353741','0.523131672597865',196940,'4.76666428809227','-0.391089108910891','0.844794255730461','-0.0401197604790419','10.8861209964413',245,'ABRANJO','001A','2008-07-15','SALIGNA','3.5x2.14',11.4,11);
    INSERT INTO CMPC(x,y,stand,date,B2,B3,B4,B8,NDVI,SAVI,SIPI,SR,RGI,TVI,MSR,PRI,GNDVI,PSRI,GCI,ID_PROJETO,PROJETO,CD_TALHAO,DATA_PLANT,ESPECIE,ESPAC,AGE_1,AGE) VALUES (-52.5502,-30.8295,'ABRANJO001A','2020-01-08',211,343,201,3260,'0.883848598670904','4889.90145448603','0.996730957829356','16.2189054726368','0.58600583090379',189220,'4.02727022592684','-0.23826714801444','0.809603108520677','-0.043558282208589','8.50437317784256',245,'ABRANJO','001A','2008-07-15','SALIGNA','3.5x2.14',11.5,11);
    INSERT INTO CMPC(x,y,stand,date,B2,B3,B4,B8,NDVI,SAVI,SIPI,SR,RGI,TVI,MSR,PRI,GNDVI,PSRI,GCI,ID_PROJETO,PROJETO,CD_TALHAO,DATA_PLANT,ESPECIE,ESPAC,AGE_1,AGE) VALUES (-52.5502,-30.8295,'ABRANJO001A','2020-01-18',210,372,213,3965,'0.898037338439445','5947.41485676216','1.00079957356077','18.6150234741784','0.57258064516129',231480,'4.31451312133576','-0.278350515463918','0.8284528475905','-0.0401008827238335','9.65860215053763',245,'ABRANJO','001A','2008-07-15','SALIGNA','3.5x2.14',11.5,12);
    INSERT INTO CMPC(x,y,stand,date,B2,B3,B4,B8,NDVI,SAVI,SIPI,SR,RGI,TVI,MSR,PRI,GNDVI,PSRI,GCI,ID_PROJETO,PROJETO,CD_TALHAO,DATA_PLANT,ESPECIE,ESPAC,AGE_1,AGE) VALUES (-52.5502,-30.8295,'ABRANJO001A','2020-03-08',201,345,204,3552,'0.891373801916933','5327.90861579812','1.00089605734767','17.4117647058824','0.591304347826087',206520,'4.17274067081605','-0.263736263736264','0.822940723633564','-0.0396959459459459','9.29565217391304',245,'ABRANJO','001A','2008-07-15','SALIGNA','3.5x2.14',11.7,12);
    INSERT INTO CMPC(x,y,stand,date,B2,B3,B4,B8,NDVI,SAVI,SIPI,SR,RGI,TVI,MSR,PRI,GNDVI,PSRI,GCI,ID_PROJETO,PROJETO,CD_TALHAO,DATA_PLANT,ESPECIE,ESPAC,AGE_1,AGE) VALUES (-52.5502,-30.8295,'ABRANJO001A','2020-04-07',159,300,155,3915,'0.923832923832924','5872.43817311528','0.998936170212766','25.258064516129','0.516666666666667',231400,'5.02574019584469','-0.30718954248366','0.857651245551601','-0.037037037037037','12.05',245,'ABRANJO','001A','2008-07-15','SALIGNA','3.5x2.14',11.7,12);
    INSERT INTO CMPC(x,y,stand,date,B2,B3,B4,B8,NDVI,SAVI,SIPI,SR,RGI,TVI,MSR,PRI,GNDVI,PSRI,GCI,ID_PROJETO,PROJETO,CD_TALHAO,DATA_PLANT,ESPECIE,ESPAC,AGE_1,AGE) VALUES (-52.5502,-30.8295,'ABRANJO001A','2020-05-17',192,340,191,3946,'0.90766255740875','5918.92371188923','0.999733688415446','20.6596858638743','0.561764705882353',231260,'4.54529271487265','-0.278195488721804','0.841343910405973','-0.0377597567156614','10.6058823529412',245,'ABRANJO','001A','2008-07-15','SALIGNA','3.5x2.14',11.8,12);
    INSERT INTO CMPC(x,y,stand,date,B2,B3,B4,B8,NDVI,SAVI,SIPI,SR,RGI,TVI,MSR,PRI,GNDVI,PSRI,GCI,ID_PROJETO,PROJETO,CD_TALHAO,DATA_PLANT,ESPECIE,ESPAC,AGE_1,AGE) VALUES (-52.5502,-30.8295,'ABRANJO001A','2020-05-27',86,222,154,3906,'0.924137931034483','5858.93844103931','1.0181236673774','25.3636363636364','0.693693693693694',227840,'5.03623235798711','-0.441558441558442','0.892441860465116','-0.0174091141833077','16.5945945945946',245,'ABRANJO','001A','2008-07-15','SALIGNA','3.5x2.14',11.9,12);
    INSERT INTO CMPC(x,y,stand,date,B2,B3,B4,B8,NDVI,SAVI,SIPI,SR,RGI,TVI,MSR,PRI,GNDVI,PSRI,GCI,ID_PROJETO,PROJETO,CD_TALHAO,DATA_PLANT,ESPECIE,ESPAC,AGE_1,AGE) VALUES (-52.5502,-30.8295,'ABRANJO001A','2020-07-26',116,225,144,3591,'0.92289156626506','5386.43734590283','1.00812300551204','24.9375','0.64',210060,'4.99374608885955','-0.319648093841642','0.882075471698113','-0.0225563909774436','14.96',245,'ABRANJO','001A','2008-07-15','SALIGNA','3.5x2.14',12,12);
    INSERT INTO CMPC(x,y,stand,date,B2,B3,B4,B8,NDVI,SAVI,SIPI,SR,RGI,TVI,MSR,PRI,GNDVI,PSRI,GCI,ID_PROJETO,PROJETO,CD_TALHAO,DATA_PLANT,ESPECIE,ESPAC,AGE_1,AGE) VALUES (-52.5502,-30.8295,'ABRANJO001A','2020-08-10',103,278,167,3551,'0.910166756320603','5326.42598611316','1.01891252955083','21.2634730538922','0.600719424460432',207480,'4.61123335495963','-0.459317585301837','0.854792373987986','-0.031258800337933','11.773381294964',245,'ABRANJO','001A','2008-07-15','SALIGNA','3.5x2.14',12.1,12);
    INSERT INTO CMPC(x,y,stand,date,B2,B3,B4,B8,NDVI,SAVI,SIPI,SR,RGI,TVI,MSR,PRI,GNDVI,PSRI,GCI,ID_PROJETO,PROJETO,CD_TALHAO,DATA_PLANT,ESPECIE,ESPAC,AGE_1,AGE) VALUES (-52.5502,-30.8295,'ABRANJO001A','2020-10-09',156,274,186,2724,'0.872164948453608','4085.89009257435','1.01182033096927','14.6451612903226','0.678832116788321',155800,'3.82689969692473','-0.274418604651163','0.817211474316211','-0.0323054331864905','8.94160583941606',245,'ABRANJO','001A','2008-07-15','SALIGNA','3.5x2.14',12.2,12);
    INSERT INTO CMPC(x,y,stand,date,B2,B3,B4,B8,NDVI,SAVI,SIPI,SR,RGI,TVI,MSR,PRI,GNDVI,PSRI,GCI,ID_PROJETO,PROJETO,CD_TALHAO,DATA_PLANT,ESPECIE,ESPAC,AGE_1,AGE) VALUES (-52.5502,-30.8295,'ABRANJO001A','2020-10-19',159,287,199,2603,'0.857958600999286','3904.37585776669','1.01663893510815','13.0804020100503','0.693379790940767',147760,'3.61668384159443','-0.286995515695067','0.801384083044983','-0.0338071456012293','8.06968641114983',245,'ABRANJO','001A','2008-07-15','SALIGNA','3.5x2.14',12.3,12);
    INSERT INTO CMPC(x,y,stand,date,B2,B3,B4,B8,NDVI,SAVI,SIPI,SR,RGI,TVI,MSR,PRI,GNDVI,PSRI,GCI,ID_PROJETO,PROJETO,CD_TALHAO,DATA_PLANT,ESPECIE,ESPAC,AGE_1,AGE) VALUES (-52.5502,-30.8295,'ABRANJO001A','2020-12-23',149,251,169,2968,'0.892253745616831','4451.90944811574','1.00714540907467','17.5621301775148','0.673306772908366',171220,'4.19071952980807','-0.255','0.844050947499223','-0.0276280323450135','10.8247011952191',245,'ABRANJO','001A','2008-07-15','SALIGNA','3.5x2.14',12.4,12);
    INSERT INTO CMPC(x,y,stand,date,B2,B3,B4,B8,NDVI,SAVI,SIPI,SR,RGI,TVI,MSR,PRI,GNDVI,PSRI,GCI,ID_PROJETO,PROJETO,CD_TALHAO,DATA_PLANT,ESPECIE,ESPAC,AGE_1,AGE) VALUES (-52.5502,-30.8295,'ABRANJO001A','2021-05-02',188,302,194,3926,'0.905825242718447','5888.92203616879','1.0016077170418','20.2371134020619','0.642384105960265',228240,'4.4985679279146','-0.23265306122449','0.857142857142857','-0.0275089149261335','12',245,'ABRANJO','001A','2008-07-15','SALIGNA','3.5x2.14',12.8,13);
    INSERT INTO CMPC(x,y,stand,date,B2,B3,B4,B8,NDVI,SAVI,SIPI,SR,RGI,TVI,MSR,PRI,GNDVI,PSRI,GCI,ID_PROJETO,PROJETO,CD_TALHAO,DATA_PLANT,ESPECIE,ESPAC,AGE_1,AGE) VALUES (-52.5502,-30.8295,'ABRANJO001A','2021-05-07',100,254,154,3891,'0.923856613102596','5836.43819397993','1.01445009365801','25.2662337662338','0.606299212598425',228220,'5.02655287112687','-0.435028248587571','0.877442702050663','-0.0257003341043434','14.3188976377953',245,'ABRANJO','001A','2008-07-15','SALIGNA','3.5x2.14',12.8,13);
    INSERT INTO CMPC(x,y,stand,date,B2,B3,B4,B8,NDVI,SAVI,SIPI,SR,RGI,TVI,MSR,PRI,GNDVI,PSRI,GCI,ID_PROJETO,PROJETO,CD_TALHAO,DATA_PLANT,ESPECIE,ESPAC,AGE_1,AGE) VALUES (-52.5502,-30.8295,'ABRANJO001A','2021-05-12',139,279,146,4186,'0.932594644506002','6278.94579878728','1.00173267326733','28.6712328767123','0.523297491039427',247720,'5.35455253748736','-0.334928229665072','0.875027995520717','-0.0317725752508361','14.0035842293907',245,'ABRANJO','001A','2008-07-15','SALIGNA','3.5x2.14',12.8,13);
    INSERT INTO CMPC(x,y,stand,date,B2,B3,B4,B8,NDVI,SAVI,SIPI,SR,RGI,TVI,MSR,PRI,GNDVI,PSRI,GCI,ID_PROJETO,PROJETO,CD_TALHAO,DATA_PLANT,ESPECIE,ESPAC,AGE_1,AGE) VALUES (-52.5502,-30.8295,'ABRANJO001A','2021-05-27',126,317,152,4197,'0.930098873304208','6295.44364108268','1.00642768850433','27.6118421052632','0.479495268138801',249300,'5.25469714686424','-0.431151241534989','0.859548072662827','-0.039313795568263','12.2397476340694',245,'ABRANJO','001A','2008-07-15','SALIGNA','3.5x2.14',12.9,13);
    INSERT INTO CMPC(x,y,stand,date,B2,B3,B4,B8,NDVI,SAVI,SIPI,SR,RGI,TVI,MSR,PRI,GNDVI,PSRI,GCI,ID_PROJETO,PROJETO,CD_TALHAO,DATA_PLANT,ESPECIE,ESPAC,AGE_1,AGE) VALUES (-52.5502,-30.8295,'ABRANJO001A','2021-07-21',172,343,207,4221,'0.90650406504065','6331.42265537427','1.00871948181365','20.3913043478261','0.603498542274053',246280,'4.51567318877552','-0.332038834951456','0.849693251533742','-0.0322198531153755','11.3061224489796',245,'ABRANJO','001A','2008-07-15','SALIGNA','3.5x2.14',13,13);
    INSERT INTO CMPC(x,y,stand,date,B2,B3,B4,B8,NDVI,SAVI,SIPI,SR,RGI,TVI,MSR,PRI,GNDVI,PSRI,GCI,ID_PROJETO,PROJETO,CD_TALHAO,DATA_PLANT,ESPECIE,ESPAC,AGE_1,AGE) VALUES (-52.5501,-30.8297,'ABRANJO001A','2019-01-28',205,362,233,3307,'0.868361581920904','4960.38632297935','1.00910865322056','14.1931330472103','0.643646408839779',189600,'3.76737747607143','-0.27689594356261','0.802671027527937','-0.0390081644995464','8.1353591160221',245,'ABRANJO','001A','2008-07-15','SALIGNA','3.5x2.14',10.5,11);
    INSERT INTO CMPC(x,y,stand,date,B2,B3,B4,B8,NDVI,SAVI,SIPI,SR,RGI,TVI,MSR,PRI,GNDVI,PSRI,GCI,ID_PROJETO,PROJETO,CD_TALHAO,DATA_PLANT,ESPECIE,ESPAC,AGE_1,AGE) VALUES (-52.5501,-30.8297,'ABRANJO001A','2019-02-22',168,283,149,3893,'0.92627412172192','5839.44031245827','0.994925213675214','26.1275167785235','0.526501766784452',230000,'5.11150826845888','-0.254988913525499','0.864463601532567','-0.034420755201644','12.756183745583',245,'ABRANJO','001A','2008-07-15','SALIGNA','3.5x2.14',10.6,11);
    INSERT INTO CMPC(x,y,stand,date,B2,B3,B4,B8,NDVI,SAVI,SIPI,SR,RGI,TVI,MSR,PRI,GNDVI,PSRI,GCI,ID_PROJETO,PROJETO,CD_TALHAO,DATA_PLANT,ESPECIE,ESPAC,AGE_1,AGE) VALUES (-52.5501,-30.8297,'ABRANJO001A','2019-03-24',155,289,154,3827,'0.92263250439588','5740.43711719069','0.999727742989382','24.8506493506494','0.532871972318339',225780,'4.98504256257149','-0.301801801801802','0.859572400388727','-0.035275672850797','12.242214532872',245,'ABRANJO','001A','2008-07-15','SALIGNA','3.5x2.14',10.7,11);
    INSERT INTO CMPC(x,y,stand,date,B2,B3,B4,B8,NDVI,SAVI,SIPI,SR,RGI,TVI,MSR,PRI,GNDVI,PSRI,GCI,ID_PROJETO,PROJETO,CD_TALHAO,DATA_PLANT,ESPECIE,ESPAC,AGE_1,AGE) VALUES (-52.5501,-30.8297,'ABRANJO001A','2019-05-18',83,229,137,3797,'0.930350788002034','5695.4438601284','1.01475409836066','27.7153284671533','0.59825327510917',223280,'5.26453497159562','-0.467948717948718','0.886239443616493','-0.0242296549907822','15.5807860262009',245,'ABRANJO','001A','2008-07-15','SALIGNA','3.5x2.14',10.8,11);
    INSERT INTO CMPC(x,y,stand,date,B2,B3,B4,B8,NDVI,SAVI,SIPI,SR,RGI,TVI,MSR,PRI,GNDVI,PSRI,GCI,ID_PROJETO,PROJETO,CD_TALHAO,DATA_PLANT,ESPECIE,ESPAC,AGE_1,AGE) VALUES (-52.5501,-30.8297,'ABRANJO001A','2019-06-27',76,205,117,3868,'0.941279799247177','5801.95321871252','1.01093041855505','33.0598290598291','0.570731707317073',228580,'5.7497677396421','-0.459074733096085','0.89933709796219','-0.0227507755946225','17.8682926829268',245,'ABRANJO','001A','2008-07-15','SALIGNA','3.5x2.14',11,11);
    INSERT INTO CMPC(x,y,stand,date,B2,B3,B4,B8,NDVI,SAVI,SIPI,SR,RGI,TVI,MSR,PRI,GNDVI,PSRI,GCI,ID_PROJETO,PROJETO,CD_TALHAO,DATA_PLANT,ESPECIE,ESPAC,AGE_1,AGE) VALUES (-52.5501,-30.8297,'ABRANJO001A','2019-07-07',54,204,133,3812,'0.932572877059569','5717.94578067672','1.02147322642022','28.6616541353383','0.651960784313726',223580,'5.35365801441765','-0.581395348837209','0.898406374501992','-0.0186253934942288','17.6862745098039',245,'ABRANJO','001A','2008-07-15','SALIGNA','3.5x2.14',11,11);

I try to do the query:

       SELECT B2, B3, B4, B8, NDVI, SAVI, SIPI, SR, RGI, TVI, MSR, PRI, GNDVI, PSRI, GCI 
       GETPERCENTILE(B2, B3, B4, B8, NDVI, SAVI, SIPI, SR, RGI, TVI, MSR, PRI, GNDVI, PSRI, GCI, 0.05),
       GETPERCENTILE(B2, B3, B4, B8, NDVI, SAVI, SIPI, SR, RGI, TVI, MSR, PRI, GNDVI, PSRI, GCI, 0.95),
       AVG(B2, B3, B4, B8, NDVI, SAVI, SIPI, SR, RGI, TVI, MSR, PRI, GNDVI, PSRI, GCI),
       MAX(B2, B3, B4, B8, NDVI, SAVI, SIPI, SR, RGI, TVI, MSR, PRI, GNDVI, PSRI, GCI),
       MIN(B2, B3, B4, B8, NDVI, SAVI, SIPI, SR, RGI, TVI, MSR, PRI, GNDVI, PSRI, GCI),
       STDEV(B2, B3, B4, B8, NDVI, SAVI, SIPI, SR, RGI, TVI, MSR, PRI, GNDVI, PSRI, GCI)
       GROUP BY AGE, ESPAC
       FROM CMPC

My desirable output is something like:

 AGE ESPAC    B2_mean B2_max B2_min B2_sd B3_mean B3_max B3_min B3_sd B4_mean B4_max B4_min B4_sd B8_mean B8_max B8_min ...                                 
      0 4X1.85      419.   928.   71   274.     610.   1208   199  328.     730.   1668   109  535.    2933.   4069   2095 ...
      1 4X1.85      344.   683   129    83.4    510.    944   286   97.0    544.   1180   256  129.    2871.   3451   2115 ...
     11 3.5x2.14    137.   259    70    29.8    276.    467   199   38.4    160.    361   109   26.4   3665.   4069   2688 ...
     12 3.5x2.14    150.   298    67.5  23.6    267.    485   200   32.1    169.    421   109   26.2   3354.   4067   2293 ...
     13 3.5x2.14    130.   302    70    35.3    247.    482   200   30.0    144.    465   111   29.7   3833.   4069   3116 ...

Please, any help with this query construction?

Thanks in advance!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

murthyvs SSCommitted Points: 1799 More actions · Answer 1

Something like this should work ...

SELECT DISTINCT Age, ESPAC
 ,MIN(B2) OVER  (PARTITION BY Age, ESPAC ORDER BY B2) as MIN_B2
      ,PERCENTILE_CONT(0.05) WITHIN GROUP (ORDER BY B2) OVER (PARTITION BY Age, ESPAC) AS P05_B2
      ,PERCENTILE_DISC(0.50) WITHIN GROUP (ORDER BY B2) OVER (PARTITION BY Age, ESPAC) AS P50_Median_B2
 ,AVG(B2) OVER  (PARTITION BY Age, ESPAC ORDER BY B2) as AVG_B2
 ,PERCENTILE_DISC(0.95) WITHIN GROUP (ORDER BY B2) OVER (PARTITION BY Age, ESPAC) AS P95_B2
 ,MAX(B2) OVER  (PARTITION BY Age, ESPAC ORDER BY B2) as MAX_B2
 ,STDEV(B2) OVER  (PARTITION BY Age, ESPAC ORDER BY B2) as StDev_B2
from CMPC
order by Age, ESPAC

Repeat for other columns

Reshape the result set to your desired format.

Hope this helps.

Cheers!

This reply was modified 3 years, 1 month ago by murthyvs.

leprechault SSC Enthusiast Points: 114 More actions · Answer 2

Thank you very @murthyvs,

But when I try to use your query the output is:

Query Error: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(PARTITION BY Age, ESPAC ORDER BY B2) as MIN_B2 ,PERCENTILE_CONT(0.05) WIT' at line 1

I try to make the code test in: https://www.db-fiddle.com/#&togetherjs=LR2eHmyV2z

Cheers,

Alexandre

murthyvs SSCommitted Points: 1799 More actions · Answer 3

It looks like you aren't using SQL Server 2019 but MySQL. May be post it in MySQL forums?

leprechault SSC Enthusiast Points: 114 More actions · Answer 4

Thanks @murthyvs!! Works very well in SQL Server 2019, please do not consider my test in MySQL. !! Problem solved.

jschmidt 17654 SSCommitted Points: 1656 More actions · Answer 5

What do you mean by "extraction inside 5-95 quantiles?" I read that as limiting the mean/min/etc to a reduced set that excludes values below 5th and above 95th, but I'm not seeing that in the @murthyvs query. Am I reading something extra into this or missing something in the query?

Thanks.