sql - Postgres query for 10 steps in a range -

sql - Postgres query for 10 steps in a range -

we have rails app postgres database , have utilize case drawing graph on range of values. unfortunately range decimal, not able utilize generate_series function of postgres. need help in figuring out optimal way query rather splitting 10 different queries. here's sample data

we have table score | students given query set of score-student tuples, range(min(score), max(score)). illustration range(10.25, 16.80) we need break above range 10 steps interval of 0.655 (max-min)10 - 10.25,10.91,11.56,12.22,12.87 for each step above show number of students between score , previous value result array [(10.25,11232),(10.91,2434),....]

any way/thoughts in postgres in single query or less 10+ queries?

your result set (makes more sense me):

with base of operations ( select student, score tbl <some_condition> ) , border ( select min(score) min_score, max(score) max_score base of operations ) select lower_bound, ct ( select step , min_score + ((max_score - min_score) * (step-1)) / 10 lower_bound border, generate_series(1,10) step ) x left bring together ( select width_bucket(b.score, x.min_score, x.max_score, 10) step , count(*)::int ct border x, base of operations b grouping step ) y using (step) order step;

featuring 2 ctes, generate_series() (still useful) , overlooked function width_bucket().

to produce array of composite types, outlined in question, first create matching type (once):

create type my_type (bound numeric, ct int);

assuming numeric values lack of information. feed above query array constructor:

select array ( <query above> select (lower_bound, ct::int)::my_type -- difference <query above> );

sql ruby-on-rails postgresql aggregate-functions generate-series