挑战Oracle /SQL-将一列分配给另一列的排列

Question

输入是G级数和P级数的两个数据集，它们是实际数据中的字符串。

X = 3,

G series
G1
G2
G3

Y = 2,

P series
P1
P2

G级数的X数总是>=P级数的Y数。

我想返回所有可能匹配的可用P值到G值，除了所有相同的P值分配给G。在每个组中，G值的序列是固定的，总是G1，G2，G3。

预期结果之一：

    1   G1  P1
    1   G2  P1
    1   G3  P2
---------------
    2   G1  P2
    2   G2  P2
    2   G3  P1
---------------
    3   G1  P1
    3   G2  P2
    3   G3  P1
---------------
    4   G1  P2
    4   G2  P1
    4   G3  P2
---------------
    5   G1  P1
    5   G2  P2
    5   G3  P2
---------------
    6   G1  P2
    6   G2  P1
    6   G3  P1

正如你所看到的，对于X= 3，Y= 2，我想要6组或X*Y组的可能的配对排列：

   | Group 1 | Group 2 | Group 3 | Group 4 | Group 5 | Group 6 |
    -----------------------------------------------------------
   | G1 P1   | G1   P2 | G1 P1   | G1  P2  | G1 P1   | G1 P2   |
   | G2 P1   | G2   P2 | G2 P2   | G2  P1  | G2 P2   | G2 P1   |
   | G3 P2   | G3   P1 | G3 P1   | G3  P2  | G3 P2   | G3 P1   |

P值到G1 -G3的模式:换句话说，需要P的组合，如112,221,121,212,122,211 .但不需要111,222的P值。只要排列都是列出来的，任何分类方法都是开放的。

由于所有相同的Ps都分配给G在同一组中，所以不需要组合如下：

 1   G1 P1
 1   G2 P1
 1   G3 P1
-----------
 2   G1 P2
 2   G2 P2
 2   G3 P2

我的计划是:先做两个数据集的笛卡尔积：

1   G1  P1
2   G1  P2
3   G2  P1
4   G2  P2
5   G3  P1
6   G3  P2

然后插入笛卡尔乘积的X数，希望创建一个笛卡尔乘积组合的序列，以得到一个预期的结果，但我无法从中找到一个模式。

1   G1  P1
2   G2  P1
3   G3  P2
4   G1  P2
5   G2  P2
6   G3  P1
7   G1  P1
8   G2  P2
9   G3  P1
10  G1  P2
11  G2  P1
12  G3  P2
13  G1  P1
14  G2  P2
15  G3  P2
16  G1  P2
17  G2  P1
18  G3  P1

另外，您可能注意到有X* (Y^X )行的可能结果，在上述情况下，当X=3和Y=2时，为3*( 2 ^3-2) = 18行和6个组或(Y^X )。

此过程创建不需要的序列：

Create or replace Procedure Permutation ( X in Number, Y in Number )                                                                        
AS j Number:=1;
Begin                                                                                                                               
For k in 1 .. X
   Loop
         For i in 1 .. X*Y                                                          
      Loop                                                          
                CASE 
          WHEN i = 1  THEN
            Insert into Table_1 ("INJECTION") values ( j );                                                                 
            Commit;
          WHEN mod(i,2)= 0 Then
            j :=  j + X ;                                                       
            Insert into Table_1 ("INJECTION") values ( j );                                                     
            Commit;
          WHEN mod(i,2)<>0 then
            j :=  j - Y ;                                                       
            Insert into Table_1 ("INJECTION") values ( j );                                                     
            Commit; 
          End CASE;
            End Loop;                                                           
         j := j + 1;                                                                
    Commit;                                                             
    End Loop;                                                                   
EXCEPTION                                                                       
   WHEN OTHERS                                                                      
   THEN                                                                     
      null;                                                                     
End;

结果:1，4，2，5，3，6，7，10，8，11，9，13，16，14，17，15，18

1   G1  P1  1
2   G1  P2  4
3   G2  P1  2
4   G2  P2  5
5   G3  P1  3
6   G3  P2  6
7   G1  P1  7
8   G1  P2  10
9   G2  P1  8
10  G2  P2  11
11  G3  P1  9
12  G3  P2  12
13  G1  P1  13
14  G1  P2  16
15  G2  P1  14
16  G2  P2  17
17  G3  P1  15
18  G3  P2  18

做一种您有以下数字的数字，这正是不想要的结果：

1   G1  P1  1
3   G2  P1  2
5   G3  P1  3
2   G1  P2  4
4   G2  P2  5
6   G3  P2  6
7   G1  P1  7
9   G2  P1  8
11  G3  P1  9
8   G1  P2  10
10  G2  P2  11
12  G3  P2  12
13  G1  P1  13
15  G2  P1  14
17  G3  P1  15
14  G1  P2  16
16  G2  P2  17
18  G3  P2  18

我想知道是否有更好的方法来解决这个问题？阵列？水平？

非常感谢您的投入，非常感谢。

更新：

我在考虑算法，本质上，它是从一个P数组[P1,P2,P3,..PY]中选择G数组[G1,G2,G3...GX]

Python 3.5.2, 

Create When X = 3, Y = 2, p = [1,2]

   import itertools
   for product in itertools.product([1,2],repeat=3):
        print (product)

(1, 1, 1)
(1, 1, 2)
(1, 2, 1)
(1, 2, 2)
(2, 1, 1)
(2, 1, 2)
(2, 2, 1)
(2, 2, 2)

除所有重复项目(1,1,1)和(2，2，2).

Answer 1

我建议建造一种分枝树。每次您添加一个"G"，您实际上添加了一个级别到您的树。

您希望以有序的方式得到的结果是到树的叶子的路径，除了外部的路径(如下所示)。

              G1  G2  G3
        p1    ****************NO (p1 p1 p1)
       /
     p1
    /   \
   /     p2   (p1 p1 p2)
p1 
   \    p1    (p1 p2 p1)
    \  /
     p2 
      \ 
        p2    (p1 p2 p2)

        p1    (p2 p1 p1)
       /
     p1
    /   \
   /     p2   (p2 p1 p2)
p2       
   \    p1    (p2 p2 p1)
    \  /
     p2 
      \ 
        p2   **************NO (p2 p2 p1)

每次添加一个级别时，都要复制每个P的元组，并将每个P添加到相同的P中。下面是在这样一个数组(l_ttab)中构建它的一种方法，您必须删除第一个和最后一个元素(+交叉每个G的乘积)来满足您的需要。

类型

CREATE OR REPLACE TYPE t_list AS TABLE OF VARCHAR2(100);
CREATE OR REPLACE TYPE tt_list AS TABLE OF t_list;

复制函数cpy_node

CREATE OR REPLACE FUNCTION cpy_node(p_list in t_list)
  RETURN t_list
AS
  l_tab t_list := t_list();
BEGIN
  for i in 1..p_list.count loop
    l_tab.extend;
    l_tab(i):=p_list(i);
  end loop;
  RETURN l_tab;
END cpy_node;

从"P“函数cto_table的输入列表生成表

CREATE OR REPLACE FUNCTION cto_table(p_sep in Varchar2, p_list IN VARCHAR2)
  RETURN t_list
AS
  l_string VARCHAR2(32767) := p_list || p_sep;
  l_sep_index PLS_INTEGER;
  l_index PLS_INTEGER := 1;
  l_tab t_list := t_list();
BEGIN
  LOOP
    l_sep_index := INSTR(l_string, p_sep, l_index);
    EXIT
  WHEN l_sep_index = 0;
    l_tab.EXTEND;
    l_tab(l_tab.COUNT) := TRIM(SUBSTR(l_string,l_index,l_sep_index - l_index));
    l_index            := l_sep_index + 1;
  END LOOP;
  RETURN l_tab;
END cto_table;

创建*有序节点列表：

declare
  l_ttab tt_list :=tt_list();
  l_ttab_next tt_list :=tt_list();
  l_tab_p t_list;    
  l_tab t_list;    
  p_nb_lvl pls_integer:=2;
begin
  l_tab_p := cto_table(',', 'p1, p2');
  -- initiate table with single nodes
  for i in 1..l_tab_p.count loop
    l_ttab.extend;
    l_tab :=t_list();
    l_tab.extend;
    l_tab(l_tab.count):=l_tab_p(i);
    l_ttab(l_ttab.count):=l_tab;
  end loop;
  -- ( p1 ) ( p2 )

  for k in 1..p_nb_lvl-1 loop
    l_ttab_next := tt_list();
    for j in 1..l_ttab.count loop
      for i in 1..l_tab_p.count loop
        -- copy from current list
        l_ttab_next.extend;
        l_ttab_next(l_ttab_next.count):=cpy_node(l_ttab(j));
        -- add node at the end
        l_ttab_next(l_ttab_next.count).extend;
        l_ttab_next(l_ttab_next.count)(k+1):=l_tab_p(i);
      end loop;
    end loop;  
    l_ttab := l_ttab_next;
  end loop;    

  -- display result
  for i in 1..l_ttab.count loop
    for j in 1..l_ttab(i).count loop
        dbms_output.put(l_ttab(i)(j)||' ');
    end loop;
    dbms_output.put_line(chr(13)||'--');
  end loop;
  -- e.g. if p_nb_lvl=2:
  --(p1 p1) (p1 p2) (p2 p1) (p2 p2) (p1 p1) (p1 p2) ( p2 p1) ( p2 p2)

end;

*我并不是说以上这些都是最理想的，因为我每次都会把树重新翻一遍，但这样对我来说更容易。