sql - Top 10% of sum() Postgres -


i'm looking pull top 10% of summed value on postgres sever.

so i'm summing value sum(transaction.value) , i'd top 10% of value

from gather in comments, assume want to:

  1. sum transactions per customer total per customer.
  2. list top 10 % of customers have transactions , spent most.

with cte (    select t.customer_id, sum(t.value) sum_value      transaction t    group  1    ) select *, rank() on (order sum_value desc) sails_rank   cte order  sum_value desc limit (select count(*)/10 cte) 

major points

  • best use cte here, makes count cheaper.

  • the join between customer , transaction automatically excludes customers without transaction. assuming relational integrity here (fk constraint on customer_id).

  • dividing bigint / int truncates result (round down nearest integer). may interested in related question:
    postgresql equivalent top n ties: limit "with ties"?

  • i added sails_rank column didn't ask for, seems fit requirement.

  • as can see, didn't include table customer in query. assuming have foreign key constraint on customer_id, redundant (and slower). if want additional columns customer in result, join customer result of above query:

with cte (    select t.customer_id, sum(t.value) sum_value      transaction t    group  1    ) select c.customer_id, c.name, sub.sum_value, sub.sails_rank  (    select *, rank() on (order sum_value desc) sails_rank      cte    order  sum_value desc    limit (select count(*)/10 cte)    ) sub join  customer c using (customer_id); 

Comments

Popular posts from this blog

java.util.scanner - How to read and add only numbers to array from a text file -

rewrite - Trouble with Wordpress multiple custom querystrings -