#mysql #sql
Вопрос:
Я новичок в SQL, и я хотел бы извлечь среднее, максимальное, минимальное и sd-извлечение внутри 5-95 квантилей для переменных B2, B3, B4, B8, NDVI, SAVI, SIPI, SR, RGI, TVI, MSR, PRI, GNDVI, PSRI, GCI, агрегированных по ВОЗРАСТУ и переменным ESPAC в таблице CMPC:
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);
Я пытаюсь выполнить запрос:
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
Мой желаемый результат-что-то вроде:
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 ...
Пожалуйста, какая-нибудь помощь в построении этого запроса?
Заранее спасибо!
Комментарии:
1. Отредактируйте свой вопрос, и вы предоставите описание таблицы с некоторыми данными вставки и каков ваш ожидаемый результат на основе этих данных, создайте db-fiddle.com если это возможно
Ответ №1:
SELECT DISTINCT AGE, ESPAC
,PERCENTILE_DISC(B2, 0.05) WITHIN GROUP (ORDER BY B2) OVER (PARTITION BY AGE, ESPAC) AS P05_B2
,PERCENTILE_DISC(B2, 0.95) WITHIN GROUP (ORDER BY B2) OVER (PARTITION BY AGE, ESPAC) AS P95_B2
,MIN(B2) OVER (PARTITION BY AGE, ESPAC ORDER BY B2) AS MIN_B2
,AVG(B2) OVER (PARTITION BY AGE, ESPAC ORDER BY B2) AS AVG_B2
,MAX(B2) OVER (PARTITION BY AGE, ESPAC ORDER BY B2) AS MAX_B2
,STDDEV(B2) OVER (PARTITION BY AGE, ESPAC ORDER BY B2) AS StDev_B2
FROM CMPC
ORDER BY AGE, ESPAC