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!
October 2, 2021 at 5:16 am
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!
October 2, 2021 at 3:28 pm
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
October 2, 2021 at 7:20 pm
It looks like you aren't using SQL Server 2019 but MySQL. May be post it in MySQL forums?
October 11, 2021 at 1:22 pm
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply