I'm trying to create frequency buckets in sql, I'm able to calculate the number of bins and the bin width. I have a table like this:

I want to create a column or seperate table that creates the bin widths until the count is 7 (since their are only 7 bins) which would look like this, the math is taking the first rows min_px bin width and then take that value bin_width.

It unclear to me where to start, I know I need to use the LAG() function but unclear how to use the LAG to LAG that value

CodePudding user response：

Please check generate_series option in postgresql . since you didn't tag or mention any database system. Here is the ddl and sql statment.

create table stack_questions_100
(zip varchar(20),
Bin_Width   decimal(10,3),
Bin_Count int,
min_px decimal(10,3),
max_px decimal(10,3)
);


insert into stack_questions_100
values
('07302',289285.717,7,325000.00,2350000.00);

select * from stack_questions_100;



 select 
    zip , s.min_px ((id -1 ) * bin_width) as lower_bound,s.min_px (id * bin_width ) as upper_bound
    
    from stack_questions_100 s,
    generate_series(1,s.Bin_Count) as temp(id)

output

 zip  |lower_bound|upper_bound|
----- ----------- ----------- 
07302| 325000.000| 614285.717|
07302| 614285.717| 903571.434|
07302| 903571.434|1192857.151|
07302|1192857.151|1482142.868|
07302|1482142.868|1771428.585|
07302|1771428.585|2060714.302|
07302|2060714.302|2350000.019|