SQL grouping and summarization of value ranges

SQL grouping and summarization of value ranges

In the statistical work, we often encounter grouping and summarizing a quantity value range, such
Assume that the id value is 1 ~ 20000. grouping based on the group distance of 5000, we need to find the following values: 5000, 10000, 15000, 20000, 20000, and, respectively.
You can use the built-in entire function Ceil and Division operations.
Select Ceil (ID/5000) F, count (1) CNT from T1 group by Ceil (ID/5000) order by 1;

F CNT
--------------------
1 5000
2 5000
3 5000
4 5000

However, this method cannot process the case of unequal groups,
Suppose we want to find the ceil function, which is less than 500, including 500,500 and less than 1000, including 5000, and 20000 and less than 20000, including.
In this case, we can use user-defined functions,

Create or replace function G2 (V Number) return Int Is
Type it is table of int;
Begin
If v> 0 and v <= 500 then
Return 1;
Elsif v> 500 and v <= 1000 then
Return 2;
Elsif v> 1000 and v <= 5000 then
Return 3;
Elsif v> 5000 and v <= 20000 then
Return 4;
Else
Return 0;
End if;
End G2;
/

Select G2 (ID) F, count (1) CNT from T1 group by G2 (ID) order by 1;

F CNT
--------------------
1 500
2 500
3 4000
4 15000

Of course, we can use case when statements to implement the same conditional grouping without using functions, but the statements are lengthy and the column names are fixed. It is not conducive to reading and modifying, but also to code reuse.
Select (Case
When ID> 0 and ID <= 500 then 1
When ID> 500 and ID <= 1000 then 2
When ID> 1000 and ID <= 5000 then 3
When ID> 5000 and ID <= 20000 then 4
Else 0
End) F,
Count (1) CNT from T1 group
(Case
When ID> 0 and ID <= 500 then 1
When ID> 500 and ID <= 1000 then 2
When ID> 1000 and ID <= 5000 then 3
When ID> 5000 and ID <= 20000 then 4
Else 0
End)
Order by 1;

F CNT
--------------------
1 500
2 500
3 4000
4 15000