Amazon Redshift rank group

debugcn Published at Dev

warrior_z

I'm trying to do a ranking on the 1st 10, and then group the remaining in groups of 1000 (based on volume)

Below is the desired results, whats the easiest way to do this?

desired results

I can get the ranking on volume all the way down using the following, but would like to group anything more than a ranking of 10

DENSE_RANK() over (PARTITION BY date ORDER BY count (DISTINCT volume_key)  DESC)as rnk_loc_Vol

AlexYes

You can try to rank with regular function first (raw rank), then get tenth record's volume next to every row and produce another column (final rank) which is 1 to 10 or 11+integer division of the delta between tenth record volume and row's volume by 1000.

with 
ranked_entries as (
    select *
    ,dense_rank() over (partition by date order by volume desc) as raw_rnk
    from tbl
)
,tenth_entry as (
    select *
    ,min(case when raw_rnk<11 then volume end) over (partition by date) as tenth_record_volume
    from tbl
)
select *
,case 
    when raw_rnk<11 then raw_rnk 
    else 11+(tenth_record_volume-volume)/1000 
 end as final_rnk
from tenth_entry

(haven't tested though)

Collected from the Internet

Please contact [email protected] to delete if infringement.