Is there a smarter method to create series with different intervalls for count within a query?-CodePudding

I want to create different intervalls:

0 to 10 steps 1
10 to 100 steps 10
100 to 1.000 steps 100
1.000 to 10.000 steps 1.000

to query a table for count the items.

with "series" as (
                    (SELECT generate_series(0, 10, 1) AS r_from)
                        union  
                    (select generate_series(10, 90, 10) as r_from)
                        union 
                    (select generate_series(100, 900, 100) as r_from)
                        union 
                    (select generate_series(1000, 9000, 1000) as r_from)
                        order by r_from
                )
, "range" as    ( select r_from
                    ,   case 
                            when r_from < 10    then r_from   1 
                            when r_from < 100   then r_from   10
                            when r_from < 1000  then r_from   100
                                                else r_from   1000 
                        end as r_to
                from series)
select r_from, r_to,(SELECT count(*) FROM "my_table" WHERE "my_value" BETWEEN r_from AND r_to) as "Anz."
FROM "range";

CodePudding user response：

I think generate_series is the right way, there is another way, we can use simple math to calculate the numbers.

SELECT 0 as r_from,1 as r_to
UNION ALL
SELECT power(10, steps ) * v ,
       power(10, steps ) * v   power(10, steps ) 
FROM generate_series(1, 9, 1) v
CROSS JOIN generate_series(0, 3, 1) steps

so that might as below

with "range" as    
( 
  SELECT 0 as r_from,1 as r_to
  UNION ALL
  SELECT power(10, steps) * v ,
         power(10, steps) * v   power(10, steps) 
  FROM generate_series(1, 9, 1) v
  CROSS JOIN generate_series(0, 3, 1) steps
)
select r_from, r_to,(SELECT count(*) FROM "my_table" WHERE "my_value" BETWEEN r_from AND r_to) as "Anz."
FROM "range";

sqlifddle

CodePudding user response：

Rather than generate_series you could create defined integer range types (int4range), then test whether your value is included within the range (see Range/Multirange Functions and Operators. So

with ranges (range_set) as 
     ( values ( int4range(0,10,'[)') )
            , ( int4range(10,100,'[)') )
            , ( int4range(100,1000,'[)') )
            , ( int4range(1000,10000,'[)') )
     ) --select * from ranges; 
select lower(range_set)     range_start
     , upper(range_set) - 1 range_end
     , count(my_value)      cnt
from ranges        r
left join my_table mt
  on (mt.my_value <@  r.range_set) 
group by r.range_set
order by lower(r.range_set);

Note the 3rd parameter in creating the ranges.

Creating a CTE as above is good if your ranges are static, however if dynamic ranges are required you can put the ranges into a table. Changes ranges then becomes a matter to managing the table. Not simple but does not require code updates. The query then reduces to just the Main part of the above:

select lower(range_set)     range_start
     , upper(range_set) - 1 range_end
     , count(my_value)      cnt
from range_tab     r
left join my_table mt
  on (mt.my_value <@  r.range_set) 
group by r.range_set
order by lower(r.range_set);

See demo for both here.