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:
- sum transactions per customer total per customer.
- 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
betweencustomer
,transaction
automatically excludes customers without transaction. assuming relational integrity here (fk constraint oncustomer_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 oncustomer_id
, redundant (and slower). if want additional columns customer in result, joincustomer
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
Post a Comment