Oracle view, break down values in 0.5 segments-CodePudding

I need to create an oracle view that will break down data in segments of 0.5 based on a length from a table.

Road ID	Road Miles
1	1.4

Above is an example of table. The Road Miles is 1.4 miles. I would like to break this down in to 3 rows in segments of 0.5 as shown below.

Road ID	Beg MP	End MP
1	0	0.5
1	0.5	1.0
1	1.0	1.4

The last row as shown should be the remainder. If the Road Miles was 1.6, then there will be 4 rows.

Is what I am asking possible? I have used the (trunc) function but it doesn't work in this case.

CodePudding user response：

You can use a hierarchical query such that

 CREATE OR REPLACE VIEW v_road_partition AS
 SELECT Road_ID, .5*(level-1) AS Beg_Mp, LEAST(.5*level,Road_Miles) AS End_Mp
   FROM t 
CONNECT BY level <= CEIL(Road_Miles/.5)   
    AND PRIOR sys_guid() IS NOT NULL
    AND PRIOR Road_ID = Road_ID

Demo

CodePudding user response：

You can use a recursive sub-query (which does not have the overhead of generating a GUID with each row):

CREATE VIEW view_name (road_id, beg_mp, end_mp) AS
WITH road_splits (road_id, beg_mp, end_mp) AS (
  SELECT road_id, FLOOR(road_miles * 2)/2, road_miles
  FROM   table_name
UNION ALL
  SELECT road_id, beg_mp - 0.5, beg_mp
  FROM   road_splits
  WHERE  beg_mp > 0
)
SEARCH DEPTH FIRST BY road_id SET order_id
SELECT road_id, beg_mp, end_mp
FROM   road_splits

Which, for the sample data:

CREATE TABLE table_name (Road_ID, Road_Miles) AS
SELECT 1, 1.4  FROM DUAL UNION ALL
SELECT 2, 0.49 FROM DUAL UNION ALL
SELECT 3, 2.01 FROM DUAL;

Then the view would output:

ROAD_ID BEG_MP END_MP

1 1 1.4

1 .5 1

1 0 .5

2 0 .49

3 2 2.01

3 1.5 2

3 1 1.5

3 .5 1

3 0 .5

ROAD_ID	BEG_MP	END_MP
1	1	1.4
1	.5	1
1	0	.5
2	0	.49
3	2	2.01
3	1.5	2
3	1	1.5
3	.5	1
3	0	.5

db<>fiddle here

CodePudding user response：

This will need some data to created/generated. We can do so using level.

One approach can be as follows -

with filler_cte (miles) as
(
select 0.5*level miles from dual connect by level<10
), data_cte(id,b_mp) as
(select 1,1.4 from dual union all
select 2,1.9 from dual)
select id as "Road ID",
miles-0.5 as "Beg MP",
case when ceil(miles) = (select ceil(b_mp)
from data_cte d where d.id = a.id) then b_mp
else miles
end as "End MP"
from data_cte a, filler_cte b
where ceil(a.b_mp) > b.miles
order by id, "Beg MP";

   Road ID     Beg MP     End MP
---------- ---------- ----------
         1          0         .5
         1         .5          1
         1          1        1.4
         2          0         .5
         2         .5          1
         2          1        1.9