Oracle视图，将值分解为0.5段

Question

我需要创建一个oracle视图，该视图将根据表的长度将数据细分为0.5段。

<>道路英里/th>

您可以使用hierarchical query这样

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

您可以使用递归子查询(它没有为每一行生成GUID的开销):

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

对于样本数据:

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;

则视图将输出:

END_MP1.450。0报31.550。

这将需要创建/生成一些数据。我们可以使用level.

一种方法如下-

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