Using CASE_N and RANGE_N in SELECT Requests
You can also use the CASE_N and RANGE_N functions in a SELECT request. For example, you might use them to help determine the distribution of rows among partitions for a proposed partitioning expression. For example, before deciding to partition on division_number, you might want to check the resulting distribution with a request like the following.
SELECT RANGE_N(division_number BETWEEN 1
AND 4
EACH 1) AS p,
COUNT(*) AS c,
FROM sales
GROUP BY p
ORDER BY p;
Another use of RANGE_N is to determine the number of ranges defined, for example, as follows.
SELECT RANGE_N(DATE '2007-12-31' BETWEEN DATE '2001-01-01'
AND DATE '2007-12-31'
EACH INTERVAL '30' DAY);
This query returns the value 86 because the last range is less than 30 days.
The following query over the same data returns the value 84 because the number of days per range varies between 28 and 31, depending on the month and year.
SELECT RANGE_N(DATE '2007-12-31' BETWEEN DATE '2001-01-01'
AND DATE '2007-12-31'
EACH INTERVAL '1' MONTH);
The final example returns the value 13 because the last range is only one day in length.
SELECT RANGE_N(DATE '2002-01-01' BETWEEN DATE '2001-01-01'
AND DATE '2002-01-01'
EACH INTERVAL '1' MONTH);
See “Considerations for Basing the Partitioning Expression on a RANGE_N Function” on page 299 for additional information on this topic.