是否有一个更聪明的方法可以在查询中创建具有不同间隔的系列？

Question

我想创建不同的间隔：

• 0到10步1
• 10至100步10
• 100至1.000步骤100
• 1.000至10.000步骤1.000

以查询表格计数。

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";

Answer 1

我认为generate_series是正确的方法，还有另一种方法，我们可以使用简单的数学来计算数字。

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  

因此，可能如下

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";

Answer 2

而不是generate_series您可以创建定义的整数（int4range），然后测试您的值是否包含在该范围内（请参见 range/Multirange功能和运算符，

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);

范围的第三参数。

请注意创建 范围放入表中。

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); 

将 ：//dbfiddle.uk/？rdbms = Postgres_14＆amp; fiddle = 1C8E3564D2475A547599E81989A0E08C“ rel =“ nofollow noreferrer”>在这里。