mysql - Calculating percentile rank on subset of values

Question

I have 3 years of fire danger observations in a redshift database and am trying to calculate the percentile rank of one of the indices as compared to a subset of the values. So, for 36 months of observations, I want to calculate the percentile rank for each day, but only for the summer months. I have the following code that almost does what I want. However, because of the WHERE date clause, I only get the rank calculated for the summer months. What I want to be able to do is put that where date clause in the window function, but can't figure out a way to do so. So, is it possible to get the rank of ec for every day of data but ranked according to ec values in the summer months?

SELECT  
    sta_id, 
    sta_nm, 
    latitude, 
    longitude, 
    nfdr_dt, 
    nfdr_tm, 
    nfdr_type, 
    msgc, 
    ec,  
    percent_rank() OVER (PARTITION by sta_id ORDER BY ec) as rank, 
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ec) OVER (PARTITION by sta_id) as percentile_50, 
    PERCENTILE_CONT(0.6) WITHIN GROUP (ORDER BY ec) OVER (PARTITION by sta_id) as percentile_60, 
    PERCENTILE_CONT(0.7) WITHIN GROUP (ORDER BY ec) OVER (PARTITION by sta_id) as percentile_70,  
    PERCENTILE_CONT(0.8) WITHIN GROUP (ORDER BY ec) OVER (PARTITION by sta_id) as percentile_80,  
    PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY ec) OVER (PARTITION by sta_id) as percentile_90, 
    PERCENTILE_CONT(0.97) WITHIN GROUP (ORDER BY ec) OVER (PARTITION by sta_id) as percentile_97,  
    PERCENTILE_CONT(1) WITHIN GROUP (ORDER BY ec) OVER (PARTITION by sta_id) as percentile_100 
FROM public.allwims 
WHERE 
    msgc LIKE '16Y%'
    AND sta_id = 470302 
    AND ec >=0 
    AND (DATE_PART(mon, nfdr_dt) >= 4 
         AND 
         DATE_PART(mon, nfdr_dt) <= 11)
ORDER BY nfdr_dt ASC

question from:https://stackoverflow.com/questions/66056040/calculating-percentile-rank-on-subset-of-values

Answer 1

Waitting for answers