Skip to content

Instantly share code, notes, and snippets.

@logworthy
Created August 19, 2020 01:06
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save logworthy/5803085d57d486eeacae17124e5bd2af to your computer and use it in GitHub Desktop.
Save logworthy/5803085d57d486eeacae17124e5bd2af to your computer and use it in GitHub Desktop.
Using width_bucket function to bin in postgres
with example as (
select 399 as num, width_bucket(399, 400, 990, 59) as buk
union all
select 400 as num, width_bucket(400, 400, 990, 59) as buk
union all
select 401 as num, width_bucket(401, 400, 990, 59) as buk
union all
select 409 as num, width_bucket(409, 400, 990, 59) as buk
union all
select 410 as num, width_bucket(410, 400, 990, 59) as buk
union all
select 411 as num, width_bucket(411, 400, 990, 59) as buk
union all
select 989 as num, width_bucket(989, 400, 990, 59) as buk
union all
select 990 as num, width_bucket(990, 400, 990, 59) as buk
union all
select 991 as num, width_bucket(991, 400, 990, 59) as buk
union all
select 999 as num, width_bucket(999, 400, 990, 59) as buk
)
select
num,
case when buk = 0 then '[0-400)'
when buk >= 60 then '[990-999]'
else '[' || (400 + (10 * buk-1)) || '-' || (400 + (10 * buk-1)) || ')'
end
from
example;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment