Several ways to summarize the range of values in SQL

Several ways to summarize the range of values in SQL

In statistical work, we often encounter a group summary of a number of value ranges, such as
Assuming that the ID value is 1~20000, grouped according to the group distance of 5000, we have to find out 5000 of the following including 5000,5000 above 10000 including 10000,10000 and above 15000 including 15000 below, 15000 or more 20000 below includes 20000.
Can be obtained by using the built-in rounding 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       
         2       
         3       5000
         4      

but this method is not able to deal with unequal-distance groupings,
assuming that we have to find 500 of the following including 500,500 and above 1000 including 1000, 1000 above 5000 including 5000,5000 above 20000 including 20000 count, Ceil function is powerless.
At this point we can use the custom function,

create or replace FUNCTION G2 (v number) RETURN INT is
  TYPE it is TABLE of int;< Br>begin
    IF v>0 and v<= then
      RETURN 1;
    elsif v>500 and v<=, then
      RETURN 2;
    elsif v>1000 and v<=, 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 also implement the same conditional grouping without the help of the function, but the statement is verbose and the column name is fixed. is not conducive to reading and modification, and is not conducive to code reuse.
Select (case
When ID >0 and id<= 1
When ID >500 and id<= 2
When ID >1000 and id<= 3
When ID >5000 and id<= 20000 then 4
else 0
End) F,
COUNT (1) CNT from T1 GROUP by
(case
When ID >0 and id<= 1
When ID >500 and id<= 2
When ID >1000 and id<= 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

This article is from the "Java White Battlefield" blog, be sure to keep this source http://8023java.blog.51cto.com/10117207/1766721

Several ways to summarize the range of values in SQL