how to calc ranges in oracle
I have a table defining ranges, e.g.:
START | END | MAP
1 | 10 | A
11 | 15 | B
...
how do I query into that table so the result will be
ID | MAP
1 | A
2 | A
3 | A
4 | A
5 | A
6 | A
7 | A
8 | A
9 | A
10 | A
11 | B
12 | B
13 | B
14 | B
15 | B
...
I bet its a easy one... Thanks for t开发者_StackOverflow社区he help
f.
select * from Table, (Select Level as Id from dual connect by Level <= (Select Max(End) from Table)) t
Where t.Id between rr.Start and rr.End
Order by Map, Start, Id
This solution at first glance it looks complicated, but generally resolves any range. Solve problem with VALUEs which may be interfered with any other range.
Firstly create sample table and insert data:
create table test_table (col_START NUMBER, col_END NUMBER, col_MAP CHAR(1));
insert into test_table(col_START, col_END, col_MAP) values(1,10,'A');
insert into test_table(col_START, col_END, col_MAP) values(11,15,'B');
insert into test_table(col_START, col_END, col_MAP) values(5,12,'C');
Now data look like this:
START | END | MAP
1 | 10 | A
11 | 15 | B
5 | 12 | C
Now create object type:
CREATE TYPE SampleType AS OBJECT
(
id number,
map_string varchar2(2000)
)
/
CREATE TYPE SampleTypeSet AS TABLE OF SampleType
/
And also create PIPELINED FUNCTION:
CREATE OR REPLACE FUNCTION GET_DATA RETURN SampleTypeSet
PIPELINED
IS
l_one_row SampleType := SampleType(NULL, NULL);
BEGIN
FOR cur_data IN (select col_START, col_END, col_MAP from test_table) LOOP
FOR i IN cur_data.col_START..cur_data.col_END LOOP
l_one_row.id := i;
l_one_row.map_string := cur_data.col_MAP;
PIPE ROW(l_one_row);
END LOOP;
END LOOP;
RETURN;
END GET_DATA;
/
Finally you can use simple query:
SELECT * FROM TABLE(GET_DATA());
Or create and select it from view (if you want hide OBJECT implementation):
CREATE VIEW VIEW_ALL_DATA AS SELECT * FROM TABLE(GET_DATA());
SELECT * FROM VIEW_ALL_DATA;
Based on this my article:
http://martin-mares.cz/2010/08/oracle-db-pipelined-function/
I can give you a dirty solution. But please don't laugh at me :(
- Prepare a dummy table, say table DUMMY which contains only one field (DUMMY_ID) which its values are 1..n where n is big enough to your problem. Let's take n = 100 for example.
Join these two tables, your actual table and the DUMMY table. Just like this :
SELECT
DUMMY_ID
,MAP
FROMDUMMY
, (SELECTSTART
,END
,MAP
FROMACTUAL
) ASACTUAL
WHEREDUMMY_ID
BETWEENSTART
ANDEND
Note that given query above is MySQL. I didn't use Oracle for long time but sure you got the point.
WITH r AS
(
SELECT MAX(end - start) + 1 AS mr
FROM ranges
),
series AS
(
SELECT level - 1 AS l
FROM dual
CONNECT BY
level <=
(
SELECT mr
FROM r
)
)
SELECT start + l, map
FROM ranges
JOIN series
ON l <= end - start
In PostgreSQL
, you could just do:
SELECT map, generate_series(start, end)
FROM ranges
Update:
Tested on your sample data:
WITH ranges AS
(
SELECT 1 AS f_start, 10 AS f_end, 'A' AS map
FROM dual
UNION ALL
SELECT 11 AS f_start, 15 AS f_end, 'B' AS map
FROM dual
),
r AS
(
SELECT MAX(f_end - f_start) + 1 AS mr
FROM ranges
),
series AS
(
SELECT level - 1 AS l
FROM dual
CONNECT BY
level <=
(
SELECT mr
FROM r
)
)
SELECT f_start + l, map
FROM ranges
JOIN series
ON l <= f_end - f_start
ORDER BY
2, 1
精彩评论