Several methods of grouping and summarizing the range of values in SQL

Several methods of grouping and summarizing the range of values in SQL

In the statistical work, we often encounter the situation of grouping and summarizing the value range of a quantity, such as

Assuming that the value of id is 100020000, and grouped according to the group distance 5000, we need to find 5000 below 5000, including 5000 and above 10000, including 10,000 and above 15000, including 15000 and 15000, and below 20000, including 20000.

Can be obtained by using the built-in rounding function ceil and division.

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

But we can't deal with the unequal distance grouping with this method.

Suppose we want to find the count of less than 500500, including more than 1000, including 1000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000and 20000, including 20000, then the ceil function is powerless.

At this point, we can use custom functions.

Create or replace FUNCTION G2 (v NUMBER) RETURN INT IS

TYPE it IS TABLE OF INT

BEGIN

IF v > 0 AND v500 AND v1000 AND v5000 AND v0 AND id500 AND id1000 AND id5000 AND id0 AND id500 AND id1000 AND id5000 AND id