Oracle 用x轴和y轴添加地图中缺少的行

地图为
(
从双并集中选择1作为id_obj，0作为y，2作为x，'Y' 作为bin_
从双并集中选择1作为id_obj，0作为y，4作为x，'Y' 作为bin_
从双并集中选择1作为id_obj，0作为y，6作为x，'Y' 作为bin_
从双并集中选择1作为id_obj，0作为y，8作为x，'Y' 作为bin_
从双并集中选择1作为id_obj，2作为y，0作为x，'Y' 作为bin_
从双并集中选择1作为id_obj，2作为y，2作为x，'Y' 作为bin_
从双并集中选择1作为id_obj，2作为y，4作为x，'Y' 作为bin_
从双并集中选择1作为id_obj，4作为y，0作为x，'Y' 作为bin_
从双并集中选择1作为id_obj，4作为y，2作为x，'Y' 作为bin_

从双并集中选择2作为id_obj，0作为y，1作为x，'Y' 作为bin_
从双并集中选择2作为id_obj，0作为y，2作为x，'Y' 作为bin_
从双并集中选择2作为id_obj，0作为y，3作为x，'Y' 作为bin_
从双并集中选择2作为id_obj，0作为y，4作为x，'Y' 作为bin_
从双并集中选择2作为id_obj，1作为y，1作为x，'Y' 作为bin_
从双并集中选择2作为id_obj，1作为y，2作为x，'Y' 作为bin_
从双并集中选择2作为id_obj，1作为y，3作为x，'Y' 作为bin_
从双并集中选择2作为id_obj，1作为y，4作为x，'Y' 作为bin_
从对偶中选择2作为id_obj，2作为y，1作为x，'Y' 作为bin_

)
从地图中选择 *

这是我的查询。它给了我x轴和y轴的地图。

ID_OBJ Y X BIN _
-
1 0 2年
1 0 4年
1 0 6年
1 0 8年
1 2 0年
1 2 2 Y
1 2 4年
1 4 0年
1 4 2年
2 0 1年
2 0 2年
2 0 3年
2 0 4年
2 1 1年
2 1 2年
2 1 3年
2 1 4年
2 2 1年


我正在寻找添加丢失的x轴的方法
所以输出就像

ID_OBJ Y X BIN _
-
1 0 2年
1 0 1空 _ 值 ** 添加行
1 0 4年
1 0 6年
1 0 8年
1 1 Null Null_VALUE



我正在尝试执行此领先和滞后分析函数以查找缺失值。对于x轴，我可以得到它，但是对于y轴，我无法形成公式。

我可以在PLSQL块中执行此操作，但是尝试检查是否可以在单个SQL本身中执行此操作

您可以通过生成X范围内的所有可能值来包括缺少的行。然后外部将这个生成的表连接到您的表:

with map as
 ( select 1 as id_obj , 0 as y , 2 as x , 'Y' as bin_ from dual
 union all
 select 1 as id_obj , 0 as y , 4 as x , 'Y' as bin_ from dual
 union all
 select 1 as id_obj , 0 as y , 6 as x , 'Y' as bin_ from dual
 union all
 select 1 as id_obj , 0 as y , 8 as x , 'Y' as bin_ from dual
 union all
 select 1 as id_obj , 2 as y , 0 as x , 'Y' as bin_ from dual
 union all
 select 1 as id_obj , 2 as y , 2 as x , 'Y' as bin_ from dual
 union all
 select 1 as id_obj , 2 as y , 4 as x , 'Y' as bin_ from dual
 union all
 select 1 as id_obj , 4 as y , 0 as x , 'Y' as bin_ from dual
 union all
 select 1 as id_obj , 4 as y , 2 as x , 'Y' as bin_ from dual
 union all
 select 2 as id_obj , 0 as y , 1 as x , 'Y' as bin_ from dual
 union all
 select 2 as id_obj , 0 as y , 2 as x , 'Y' as bin_ from dual
 union all
 select 2 as id_obj , 0 as y , 3 as x , 'Y' as bin_ from dual
 union all
 select 2 as id_obj , 0 as y , 4 as x , 'Y' as bin_ from dual
 union all
 select 2 as id_obj , 1 as y , 1 as x , 'Y' as bin_ from dual
 union all
 select 2 as id_obj , 1 as y , 2 as x , 'Y' as bin_ from dual
 union all
 select 2 as id_obj , 1 as y , 3 as x , 'Y' as bin_ from dual
 union all
 select 2 as id_obj , 1 as y , 4 as x , 'Y' as bin_ from dual
 union all
 select 2 as id_obj , 2 as y , 1 as x , 'Y' as bin_ from dual
 ) , rws as
 ( select level r from dual connect by level <= 8
 )
select id_obj, y, x, r
from rws
left join map 
on r    = x
order by id_obj, y, r ;

ID_OBJ  Y  X  R  
1       0  2  2  
1       0  4  4  
1       0  6  6  
1       0  8  8  
1       2  2  2  
1       2  4  4  
1       4  2  2  
2       0  1  1  
2       0  2  2  
2       0  3  3  
2       0  4  4  
2       1  1  1  
2       1  2  2  
2       1  3  3  
2       1  4  4  
2       2  1  1  
              5  
              7 

但这不是你想要的!你只得到两个额外的行，它们没有id_obj或y的任何值...

幸运的是有一个简单的方法来做到这一点:

分区的外部连接

这将 “填充” 要为其生成额外行的列。在这里，您需要为缺少X的每个 (id_obj，y) 对一行。所以把这些放在你的分区by子句，你得到:

with map as
  ( select 1 as id_obj , 0 as y , 2 as x , 'Y' as bin_ from dual
  union all
  select 1 as id_obj , 0 as y , 4 as x , 'Y' as bin_ from dual
  union all
  select 1 as id_obj , 0 as y , 6 as x , 'Y' as bin_ from dual
  union all
  select 1 as id_obj , 0 as y , 8 as x , 'Y' as bin_ from dual
  union all
  select 1 as id_obj , 2 as y , 0 as x , 'Y' as bin_ from dual
  union all
  select 1 as id_obj , 2 as y , 2 as x , 'Y' as bin_ from dual
  union all
  select 1 as id_obj , 2 as y , 4 as x , 'Y' as bin_ from dual
  union all
  select 1 as id_obj , 4 as y , 0 as x , 'Y' as bin_ from dual
  union all
  select 1 as id_obj , 4 as y , 2 as x , 'Y' as bin_ from dual
  union all
  select 2 as id_obj , 0 as y , 1 as x , 'Y' as bin_ from dual
  union all
  select 2 as id_obj , 0 as y , 2 as x , 'Y' as bin_ from dual
  union all
  select 2 as id_obj , 0 as y , 3 as x , 'Y' as bin_ from dual
  union all
  select 2 as id_obj , 0 as y , 4 as x , 'Y' as bin_ from dual
  union all
  select 2 as id_obj , 1 as y , 1 as x , 'Y' as bin_ from dual
  union all
  select 2 as id_obj , 1 as y , 2 as x , 'Y' as bin_ from dual
  union all
  select 2 as id_obj , 1 as y , 3 as x , 'Y' as bin_ from dual
  union all
  select 2 as id_obj , 1 as y , 4 as x , 'Y' as bin_ from dual
  union all
  select 2 as id_obj , 2 as y , 1 as x , 'Y' as bin_ from dual
  ) , rws as
  ( select level r from dual connect by level <= 8
  )
select id_obj, y, x, r
from rws
left join map partition by ( id_obj, y )
on r    = x
where y < 2
order by id_obj, y, r ;

ID_OBJ  Y  X  R  
1       0     1  
1       0  2  2  
1       0     3  
1       0  4  4  
1       0     5  
1       0  6  6  
1       0     7  
1       0  8  8  
2       0  1  1  
2       0  2  2  
2       0  3  3  
2       0  4  4  
2       0     5  
2       0     6  
2       0     7  
2       0     8  
2       1  1  1  
2       1  2  2  
2       1  3  3  
2       1  4  4  
2       1     5  
2       1     6  
2       1     7  
2       1     8