Aquí es el DDL —

create table tbl1 (
   id number,
   value varchar2(50)
);

insert into tbl1 values (1, 'AA, UT, BT, SK, SX');
insert into tbl1 values (2, 'AA, UT, SX');
insert into tbl1 values (3, 'UT, SK, SX, ZF');

Aviso, aquí el valor es separados por comas cadena.

Pero, necesitamos resultado como el siguiente-

ID VALUE
-------------
1  AA
1  UT
1  BT
1  SK
1  SX
2  AA
2  UT
2  SX
3  UT
3  SK
3  SX
3  ZF

¿Cómo podemos escribir SQL para esto?

4 Comentarios

  1. 13

    Estoy de acuerdo en que este es un muy mal diseño.
    Intente esto si usted no puede cambiar eso de diseño:

    select distinct id, trim(regexp_substr(value,'[^,]+', 1, level) ) value, level
      from tbl1
       connect by regexp_substr(value, '[^,]+', 1, level) is not null
       order by id, level;

    SALIDA

    id value level
    1   AA  1
    1   UT  2
    1   BT  3
    1   SK  4
    1   SX  5
    2   AA  1
    2   UT  2
    2   SX  3
    3   UT  1
    3   SK  2
    3   SX  3
    3   ZF  4

    Créditos a este

    Para eliminar los duplicados en una forma más elegante y eficiente (créditos a @mathguy)

    select id, trim(regexp_substr(value,'[^,]+', 1, level) ) value, level
      from tbl1
       connect by regexp_substr(value, '[^,]+', 1, level) is not null
          and PRIOR id =  id 
          and PRIOR SYS_GUID() is not null  
       order by id, level;

    Si quieres un «ANSIer enfoque de» ir con una CTE:

    with t (id,res,val,lev) as (
               select id, trim(regexp_substr(value,'[^,]+', 1, 1 )) res, value as val, 1 as lev
                 from tbl1
                where regexp_substr(value, '[^,]+', 1, 1) is not null
                union all           
                select id, trim(regexp_substr(val,'[^,]+', 1, lev+1) ) res, val, lev+1 as lev
                  from t
                  where regexp_substr(val, '[^,]+', 1, lev+1) is not null
                  )
    select id, res,lev
      from t
    order by id, lev;

    SALIDA

    id  val lev
    1   AA  1
    1   UT  2
    1   BT  3
    1   SK  4
    1   SX  5
    2   AA  1
    2   UT  2
    2   SX  3
    3   UT  1
    3   SK  2
    3   SX  3
    3   ZF  4

    Otro recursiva enfoque por MT0 pero sin regex:

    WITH t ( id, value, start_pos, end_pos ) AS
      ( SELECT id, value, 1, INSTR( value, ',' ) FROM tbl1
      UNION ALL
      SELECT id,
        value,
        end_pos                    + 1,
        INSTR( value, ',', end_pos + 1 )
      FROM t
      WHERE end_pos > 0
      )
    SELECT id,
      SUBSTR( value, start_pos, DECODE( end_pos, 0, LENGTH( value ) + 1, end_pos ) - start_pos ) AS value
    FROM t
    ORDER BY id,
      start_pos;

    He intentado 3 enfoques con un 30000 filas del conjunto de datos y 118104 filas devueltas y obtuvo los siguientes resultados promedio:

    • Mi recursiva enfoque: 5 segundos
    • MT0 enfoque: 4 segundos
    • Mathguy enfoque: 16 segundos
    • MT0 recursiva enfoque no-regex: 3.45 segundos

    @Mathguy también se ha ensayado con un mayor conjunto de datos:

    En todos los casos la consulta recursiva (yo sólo he probado el uno con regular
    substr y instr) no mejor, por un factor de 2 a 5. Aquí están las
    combinaciones de # de cadenas /tokens de cada cadena y llamadas a la acción de ejecución
    veces para jerárquica vs recursiva, jerárquica primera. Todos los tiempos en
    segundos

    • 30,000 x 4: 5 /1.
    • 30,000 x de 10: 15 /3.
    • 30,000 x 25: 56 /37.
    • 5.000 x 50: 33 /14.
    • 5.000 x 100: 160 /81.
    • 10.000 x 200: 1,924 /772
    • Ver, por ejemplo, para saber cómo evitar duplicados (por lo que no tiene que select DISTINCT): community.oracle.com/thread/2526535
    • Una gran idea de @mathguy. Gracias.
    • El uso de CONNECT BY como este se cross-connect artículos con diferentes ids y crear un crecimiento exponencial de la lista de duplicados que usted tiene que quitar con DISTINCT y se hace cada vez más ineficiente, ya que el número de filas y de elementos por fila crece.
    • gracias, he añadido el hack que mathguy me señaló.
    • Acaba de ser conscientes de que es un hack. El uso de una tabla de correlación de la colección de expresión (como por mi respuesta o esta respuesta) no generar duplicados y no necesita esta argucia para evitar cíclico de conexiones en los datos.
    • Yo (ahora) no está de acuerdo con MT0 en este. No hay un «hack» en el uso de SYS_GUID() la manera en que lo hice. Por favor, véase el comentario a mi Respuesta.
    • MT0: hice un standar recursiva enfoque. No hay hacks implican. Voy a probar con un mayor número de registros y ver cómo se comporta.
    • Consultas jerárquicas son a menudo más rápido que recursiva queridos. La prueba es absolutamente la mejor manera de decidir. El recurrente enfoque tiene la ventaja de que, con un poco más de esfuerzo, puede utilizar el estándar de SUBSTR y INSTR y evitar las expresiones regulares, que son a veces (a menudo?) más intensivo de la CPU y puede retrasarlo un poco.
    • He probado con un conjunto de datos más grande y publicado los resultados. No fue una prueba completa, pero creo que es interesante
    • ¿Cuáles son los " unidades de los números, y es más grande o más pequeño mejor?
    • significa segundos. El menor de los mejor
    • Una versión recursiva sin expresiones regulares: WITH t ( id, value, start_pos, end_pos ) AS ( SELECT id, value, 1, INSTR( value, ',' ) FROM tbl1 UNION ALL SELECT id, value, end_pos + 1, INSTR( value, ',', end_pos + 1 ) FROM t WHERE end_pos > 0 ) SELECT id, SUBSTR( value, start_pos, DECODE( end_pos, 0, LENGTH( value ) + 1, end_pos ) - start_pos ) AS value FROM t ORDER BY id, start_pos;
    • genial, eso es un poco más rápido.
    • Que diferencia en el rendimiento sería mucho mejor razón para preferir el «colecciones» de la ruta (MT0) y su «recursivo» de la ruta. Voy a probar más y sospecho que con cadenas más largas (con más fichas por cadena de entrada) los resultados pueden ser diferentes. Si hay interés, probablemente debería abrir una cuestión separada/hilo para publicar los resultados de la prueba, etc. Por ahora, acabo de probar el jerárquica de la solución («mi» solución) con 30000 de las cadenas de entrada y 4 tokens de cada cadena, la producción total 120000 filas; en mi laptop y con la versión gratuita de Oracle 11.2, toma 5.1 segundos.
    • OK, acabo de probar con diferentes escenarios. En todos los casos la consulta recursiva (yo sólo he probado el uno con regular substr y instr) no mejor, por un factor de 2 a 5. Aquí están las combinaciones de # de cadenas / tokens de cada cadena y llamadas a la acción a los tiempos de ejecución para jerárquica vs recursiva, jerárquica primera. Todos los tiempos en segundos. 30,000 x 4: 5 / 1. 30,000 x de 10: 15 / 3. 30,000 x 25: 56 / 37. 5.000 x 50: 33 / 14. 5.000 x 100: 160 / 81. 10.000 x 200: 1,924 / 772

  2. 4

    Esta forma, se obtienen los valores sin necesidad de eliminar los duplicados o tener que usar un hack de incluir SYS_GUID() o DBMS_RANDOM.VALUE() en el CONNECT BY:

    SELECT t.id,
           v.COLUMN_VALUE AS value
    FROM   TBL1 t,
           TABLE(
             CAST(
               MULTISET(
                 SELECT TRIM( REGEXP_SUBSTR( t.value, '[^,]+', 1, LEVEL ) )
                 FROM   DUAL
                 CONNECT BY LEVEL <= REGEXP_COUNT( t.value, '[^,]+' )
               )
               AS SYS.ODCIVARCHAR2LIST
             )
           ) v

    Actualización:

    Devolver el índice del elemento en la lista:

    Opción 1 – Devolver un UDT:

    CREATE TYPE string_pair IS OBJECT( lvl INT, value VARCHAR2(4000) );
    /
    
    CREATE TYPE string_pair_table IS TABLE OF string_pair;
    /
    
    SELECT t.id,
           v.*
    FROM   TBL1 t,
           TABLE(
             CAST(
               MULTISET(
                 SELECT string_pair( level, TRIM( REGEXP_SUBSTR( t.value, '[^,]+', 1, LEVEL ) ) )
                 FROM   DUAL
                 CONNECT BY LEVEL <= REGEXP_COUNT( t.value, '[^,]+' )
               )
               AS string_pair_table
             )
           ) v;

    Opción 2 – El Uso De ROW_NUMBER():

    SELECT t.id,
           v.COLUMN_VALUE AS value,
           ROW_NUMBER() OVER ( PARTITION BY id ORDER BY ROWNUM ) AS lvl
    FROM   TBL1 t,
           TABLE(
             CAST(
               MULTISET(
                 SELECT TRIM( REGEXP_SUBSTR( t.value, '[^,]+', 1, LEVEL ) )
                 FROM   DUAL
                 CONNECT BY LEVEL <= REGEXP_COUNT( t.value, '[^,]+' )
               )
               AS SYS.ODCIVARCHAR2LIST
             )
           ) v;
    • No estoy de acuerdo; CONNECT BY con ninguna condición de uso de la PRIOR operador, como usted hace, es un hack (viola Oracle requisitos para consultas jerárquicas). No veo cuál es el hack es cuando se utiliza PRIOR SYS_GUID() para romper los ciclos; que es perfectamente legítimo el uso de consultas jerárquicas.
    • Tom Kyte ha declarado que este es un bug con la documentación y que «No, nunca ha necesitado PRIOR en una conectarse.» (enlace).
    • le falta un ‘ TRIM( REGEXP_SUBSTR( t.valor, ‘[^,]+
    • Gracias, fijo
    • Sólo leer acerca de la cast(multiset(....)) – si recuerdo correctamente (a partir de la lectura acerca de esto hace un par de meses), una tabla anidada en Oracle sólo puede tener una columna, a la derecha? O es posible extraer no sólo las monedas, sino también su «nivel» dentro de la cadena original? En algunos casos, es de suponer que el orden de las fichas, tiene un significado que puede tener que ser utilizado en el procesamiento.
    • Hay varias formas de resolver que: Usted puede devolver un tipo definido por el usuario como una tabla de un tipo de objeto con múltiples campos; o puede utilizar el ROW_NUMBER() analítica de la función. A solo 2 que inmediatamente vienen a la mente.
    • Voy a pasar algún tiempo en ambos. En cualquier caso, contrario a lo que me llevó a creer, el CTE recursiva (que siempre me ha gustado mucho mejor que el enfoque jerárquico) parece ser más rápido, y no sólo por un pequeño margen. Voy a volver a los antiguos hilo con una pregunta similar (desde hace un par de días), el OP pregunta allí si que nos puede ayudar a mejorar el rendimiento, ya que sus datos reales es mucho «más grande» que el de la muestra que había publicado. Voy a intentar con la consulta recursiva en el que el ejercicio, y el cast(multiset(...)) enfoque, así que si puedo entender (que creo que has publicado ya). Gracias!
    • Actualizado con ambos métodos.
    • Consiguió, gracias. Supongo que ROWNUM también puede ser utilizado directamente (no hay diferencia frente a usarlo para ordenar en ROW_NUMBER()).
    • No es fácil – ROW_NUMBER() funciona como usted puede partición por el id (asumiendo que son la única ni ROWID generado en una vista en línea si no). ROWNUM que usted necesita para poner la tabla de la colección de expresión en una vista en línea para generar a partir de 1 de cada fila original pero, a continuación, la tabla de la colección de expresión es demasiado anidados a la que hace referencia y no funciona.

  3. 1

    Vercelli publicado una respuesta correcta. Sin embargo, con más de una cadena de split, connect by generará un crecimiento exponencial de la cantidad de filas, con muchos, muchos duplicados. (Sólo trate de la consulta sin distinct.) Esto destruirá el rendimiento en los datos de la no-trivial tamaño.

    Una forma común para superar este problema es el uso de un prior condición y una comprobación adicional para evitar ciclos en la jerarquía. Así:

    select id, trim(regexp_substr(value,'[^,]+', 1, level) ) value, level
      from tbl1
       connect by regexp_substr(value, '[^,]+', 1, level) is not null
              and prior id = id
              and prior sys_guid() is not null
       order by id, level;

    Ver, por ejemplo, esta discusión sobre la OTN: https://community.oracle.com/thread/2526535

    • Sé que hemos hablado de esto antes, pero el uso de SYS_GUID() es un hack y yo creo que es mejor utilizar una tabla de correlación de la colección de expresión que nunca va a generar estos duplicados y así no tener que recurrir a soluciones para lidiar con ellos.
    • Nos hizo hablar de esto antes. Yo no sé nada (yo sólo empecé a aprender SQL y Oracle este mes de febrero), pero me parece que todos los gurús en la OTN, Tom Kyte, etc. todo el uso de la sys_guid() o dbms_random.value() truco. Vea el enlace que he proporcionado. Tenga en cuenta que connect by sin una condición mediante la PRIOR operador ya es un hack (viola Oracle requisitos para CONNECT BY – consulte la documentación: docs.oracle.com/cd/B28359_01/server.111/b28286/queries003.htm , vea el segundo punto de bala después de que el diagrama de sintaxis).
    • de hecho, he cambiado mi mente. CONNECT BY sin una condición de uso de PRIOR es un hack y no pueden ser compatibles en el futuro. La manera en que yo lo uso no es un hack porque yo uso el PRIOR operador en al menos una condición. SYS_GUID() está garantizado para producir diferentes valores para cada fila, que luego se traduce en que no haya ciclos en la jerarquía. No estoy de acuerdo que este es un hack. ¿Por qué es un hack?
    • Es un hack porque AND PRIOR SYS_GUID() IS NOT NULL siempre será verdad que la condición se reduce a AND TRUE y debe ser irrelevante – sin embargo, la eliminación de lo que va a obtener ORA-01436: CONNECT BY loop in user data.
    • Que es incorrecta. AND PRIOR SYS_GUID() IS NOT NULL hace dos las cosas, no una sola. Se evalúa a VERDADERO en todos los casos, sino que también añade un único bit de datos para cada uno de los recién generado fila. AND TRUE sólo hace el primer trabajo, no en la segunda.
  4. 1

    Un método alternativo es definir una sencilla función de PL/SQL:

    CREATE OR REPLACE FUNCTION split_String(
      i_str    IN  VARCHAR2,
      i_delim  IN  VARCHAR2 DEFAULT ','
    ) RETURN SYS.ODCIVARCHAR2LIST DETERMINISTIC
    AS
      p_result       SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
      p_start        NUMBER(5) := 1;
      p_end          NUMBER(5);
      c_len CONSTANT NUMBER(5) := LENGTH( i_str );
      c_ld  CONSTANT NUMBER(5) := LENGTH( i_delim );
    BEGIN
      IF c_len > 0 THEN
        p_end := INSTR( i_str, i_delim, p_start );
        WHILE p_end > 0 LOOP
          p_result.EXTEND;
          p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, p_end - p_start );
          p_start := p_end + c_ld;
          p_end := INSTR( i_str, i_delim, p_start );
        END LOOP;
        IF p_start <= c_len + 1 THEN
          p_result.EXTEND;
          p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, c_len - p_start + 1 );
        END IF;
      END IF;
      RETURN p_result;
    END;
    /

    A continuación, el SQL se vuelve muy sencillo:

    SELECT t.id,
           v.column_value AS value
    FROM   TBL1 t,
           TABLE( split_String( t.value ) ) v

Dejar respuesta

Please enter your comment!
Please enter your name here