Coincidencia de patrones (MATCH_RECOGNIZE) en Oracle Database 12c versión 1 (12.1)

Base de datos, Funciones Analíticas, Oracle, SQL

Introducidas en Oracle 8i, las funciones analíticas, también conocidas como funciones de ventanas, permiten a los desarrolladores realizar tareas en SQL que antes se limitaban a los lenguajes de procedimiento. Oracle 12c ha agregado la cláusula MATCH_RECOGNIZE en la sintaxis de la función analítica para simplificar la comparación de patrones de SQL. Este artículo ofrece una idea de lo que se puede hacer con la cláusula MATCH_RECOGNIZE, pero deberá consultar la documentación oficial de ORACLE para comprender el verdadero nivel de complejidad posible.

  • Sintaxis Simplificada
  • Preparar
  • Ejemplo

Sintaxis Simplificada

La sintaxis de coincidencia de patrones incluye muchas opciones, lo que la hace bastante desalentadora al principio. Esta sección describe una vista muy simplista de la sintaxis, permitiéndole dar sus primeros pasos. Para obtener una descripción detallada de la sintaxis, para mas detalle consulte la documentación oficial de ORACLE.
Los datos deben procesarse correctamente y de forma determinista. Las cláusulas PARTITION BY y ORDER BY de todas las funciones analíticas se utilizan para dividir los datos en grupos y asegurarse de que estén ordenados correctamente dentro de cada grupo, de modo que las funciones analíticas sensibles al orden funcionen como se espera. Esto se explica aquí. Si no se definen particiones, se asume que todo el conjunto de resultados es una gran partición.

PARTITION BY product
ORDER BY tstamp

La cláusula MEASURES define la salida de la columna que se producirá para cada coincidencia.

MEASURES  STRT.tstamp AS start_tstamp,
          LAST(UP.tstamp) AS peak_tstamp,
          LAST(DOWN.tstamp) AS end_tstamp

Junto con las MEASURES, debe decidir si desea presentar todas las filas que representan la coincidencia o solo información resumida.

[ONE ROW | ALL ROWS] PER MATCH

El patrón que representa una coincidencia se define utilizando variables de patrón, por lo que tiene sentido mirarlas primero. Las variables de patrón pueden usar cualquier palabra no reservada asociada con una expresión. Hay dos ejemplos abajo.

DEFINE
  UP AS UP.units_sold > PREV(UP.units_sold),
  FLAT AS FLAT.units_sold = PREV(FLAT.units_sold),
  DOWN AS DOWN.units_sold < PREV(DOWN.units_sold)

DEFINE
  TWINKIES AS TWINKIES.product='TWINKIES',
  DINGDONGS AS DINGDONG.product='DINGDONGS',
  HOHOS AS HOHOS.product='HOHOS'

A continuación, el patrón se define mediante expresiones regulares que incorporan las variables del patrón. A continuación se ofrecen algunos ejemplos, pero en la documentación se encuentra disponible una lista completa de las posibilidades.

-- 1-Many increases, followed by 1-Many decreases in a value. A "V" shaped spike.
PATTERN (STRT UP+ DOWN+)

-- 1-Many increases, followed by a single decrease, then 1-Many increases. A single dip, during the rise.
PATTERN (STRT UP+ DOWN{1} UP+)

-- 1-5 Twinkies, followed by 1 DingDong, followed by 2 HoHos.
PATTERN(STRT TWINKIES{1,5} DINGDONGS{1} HOHOS{2})

La cláusula AFTER MATCH SKIP define desde dónde se reinicia la búsqueda. Las opciones disponibles incluyen las siguientes.

  • AFTER MATCH SKIP TO NEXT ROW : La búsqueda continúa en la fila que sigue al inicio del patrón emparejado.
  • AFTER MATCH SKIP PAST LAST ROW : (Predeterminado) La búsqueda continúa en la fila que sigue al final del patrón coincidente.
  • AFTER MATCH SKIP TO FIRST pattern_variable : La búsqueda continúa desde la primera fila relacionada con el patrón definido por la variable de patrón especificada.
  • AFTER MATCH SKIP TO LAST pattern_variable : La búsqueda continúa desde la última fila relacionada con el patrón definido por la variable de patrón especificada.
  • AFTER MATCH SKIP TO pattern_variable : Equivalente a «AFTER MATCH SKIP TO LAST pattern_variable».

Hay una serie de funciones que proporcionan información adicional sobre la salida mostrada.

  • MATCH_NUMBER() : Numeración secuencial de coincidencias 1-N, que indica qué filas de salida se relacionan con qué coincidencia.
  • CLASSIFIER() : La variable de patrón que se aplica a la fila de salida. Esto solo tiene sentido cuando se muestran todas las filas.

La navegación por las filas en un patrón es posible utilizando las funciones PREV, NEXT, FIRST y LAST.

  • PREV(UP.units_sold) — Valor de las unidades vendidas de la fila anterior.
  • PREV(UP.units_sold, 2) — Valor de las unidades vendidas de la fila antes de la fila anterior (offset of 2 rows).
  • NEXT(UP.units_sold) — Valor de las unidades vendidas de la siguiente fila.
  • NEXT(UP.units_sold, 2) — Valor de las unidades vendidas de la fila después de la siguiente fila (offset of 2 rows).
  • FIRST(UP.units_sold) — Primera fila del patrón.
  • FIRST(UP.units_sold, 1) — Fila siguiente a la primera fila (offset of 1 row).
  • LAST(UP.units_sold) — Última fila del patrón.
  • LAST(UP.units_sold, 1) — Fila que precede a la última fila (offset of 1 row).

La navegación de patrones, junto con las funciones agregadas, se puede calificar con las palabras clave de semántica FINAL y RUNNING. Éstos son efectivamente una cláusula de ventana dentro del patrón, que define si la acción se relaciona con el patrón completo, o desde el inicio del patrón hasta la fila actual.

Preparar

Los ejemplos de este artículo requieren las dos tablas siguientes. La primera tabla define las ventas totales de cada producto por día. En este caso, solo hay datos para un solo producto.

DROP TABLE sales_history PURGE;

CREATE TABLE sales_history (
  id            NUMBER,
  product       VARCHAR2(20),
  tstamp        TIMESTAMP,
  units_sold    NUMBER,
  CONSTRAINT sales_history_pk PRIMARY KEY (id)
);

ALTER SESSION SET nls_timestamp_format = 'DD-MON-YYYY';

INSERT INTO sales_history VALUES ( 1, 'TWINKIES', '01-OCT-2014', 17);
INSERT INTO sales_history VALUES ( 2, 'TWINKIES', '02-OCT-2014', 19);
INSERT INTO sales_history VALUES ( 3, 'TWINKIES', '03-OCT-2014', 23);
INSERT INTO sales_history VALUES ( 4, 'TWINKIES', '04-OCT-2014', 23);
INSERT INTO sales_history VALUES ( 5, 'TWINKIES', '05-OCT-2014', 16);
INSERT INTO sales_history VALUES ( 6, 'TWINKIES', '06-OCT-2014', 10);
INSERT INTO sales_history VALUES ( 7, 'TWINKIES', '07-OCT-2014', 14);
INSERT INTO sales_history VALUES ( 8, 'TWINKIES', '08-OCT-2014', 16);
INSERT INTO sales_history VALUES ( 9, 'TWINKIES', '09-OCT-2014', 15);
INSERT INTO sales_history VALUES (10, 'TWINKIES', '10-OCT-2014', 17);
INSERT INTO sales_history VALUES (11, 'TWINKIES', '11-OCT-2014', 23);
INSERT INTO sales_history VALUES (12, 'TWINKIES', '12-OCT-2014', 30);
INSERT INTO sales_history VALUES (13, 'TWINKIES', '13-OCT-2014', 31);
INSERT INTO sales_history VALUES (14, 'TWINKIES', '14-OCT-2014', 29);
INSERT INTO sales_history VALUES (15, 'TWINKIES', '15-OCT-2014', 25);
INSERT INTO sales_history VALUES (16, 'TWINKIES', '16-OCT-2014', 21);
INSERT INTO sales_history VALUES (17, 'TWINKIES', '17-OCT-2014', 35);
INSERT INTO sales_history VALUES (18, 'TWINKIES', '18-OCT-2014', 46);
INSERT INTO sales_history VALUES (19, 'TWINKIES', '19-OCT-2014', 45);
INSERT INTO sales_history VALUES (20, 'TWINKIES', '20-OCT-2014', 30);
COMMIT;

La siguiente consulta muestra el patrón de los datos, al que nos referiremos más adelante.

ALTER SESSION SET nls_timestamp_format = 'DD-MON-YYYY';

SET PAGESIZE 50
COLUMN product    FORMAT A10
COLUMN tstamp     FORMAT A11
COLUMN graph      FORMAT A50

SELECT id,
       product,
       tstamp,
       units_sold,
       RPAD('#', units_sold, '#') AS graph
FROM   sales_history
ORDER BY id;

        ID PRODUCT    TSTAMP      UNITS_SOLD GRAPH
---------- ---------- ----------- ---------- --------------------------------------------------
         1 TWINKIES   01-OCT-2014         17 #################
         2 TWINKIES   02-OCT-2014         19 ###################
         3 TWINKIES   03-OCT-2014         23 #######################
         4 TWINKIES   04-OCT-2014         23 #######################
         5 TWINKIES   05-OCT-2014         16 ################
         6 TWINKIES   06-OCT-2014         10 ##########
         7 TWINKIES   07-OCT-2014         14 ##############
         8 TWINKIES   08-OCT-2014         16 ################
         9 TWINKIES   09-OCT-2014         15 ###############
        10 TWINKIES   10-OCT-2014         17 #################
        11 TWINKIES   11-OCT-2014         23 #######################
        12 TWINKIES   12-OCT-2014         30 ##############################
        13 TWINKIES   13-OCT-2014         31 ###############################
        14 TWINKIES   14-OCT-2014         29 #############################
        15 TWINKIES   15-OCT-2014         25 #########################
        16 TWINKIES   16-OCT-2014         21 #####################
        17 TWINKIES   17-OCT-2014         35 ###################################
        18 TWINKIES   18-OCT-2014         46 ##############################################
        19 TWINKIES   19-OCT-2014         45 #############################################
        20 TWINKIES   20-OCT-2014         30 ##############################

20 rows selected.

SQL>

La siguiente tabla define un seguimiento de auditoría de todas las ventas a medida que ocurren.

DROP TABLE sales_audit PURGE;

CREATE TABLE sales_audit (
  id            NUMBER,
  product       VARCHAR2(20),
  tstamp        TIMESTAMP,
  CONSTRAINT sales_audit_pk PRIMARY KEY (id)
);

ALTER SESSION SET nls_timestamp_format = 'DD-MON-YYYY HH24:MI:SS';

INSERT INTO sales_audit VALUES ( 1, 'TWINKIES', '01-OCT-2014 12:00:01');
INSERT INTO sales_audit VALUES ( 2, 'TWINKIES', '01-OCT-2014 12:00:02');
INSERT INTO sales_audit VALUES ( 3, 'DINGDONGS', '01-OCT-2014 12:00:03');
INSERT INTO sales_audit VALUES ( 4, 'HOHOS', '01-OCT-2014 12:00:04');
INSERT INTO sales_audit VALUES ( 5, 'HOHOS', '01-OCT-2014 12:00:05');
INSERT INTO sales_audit VALUES ( 6, 'TWINKIES', '01-OCT-2014 12:00:06');
INSERT INTO sales_audit VALUES ( 7, 'TWINKIES', '01-OCT-2014 12:00:07');
INSERT INTO sales_audit VALUES ( 8, 'DINGDONGS', '01-OCT-2014 12:00:08');
INSERT INTO sales_audit VALUES ( 9, 'DINGDONGS', '01-OCT-2014 12:00:09');
INSERT INTO sales_audit VALUES (10, 'HOHOS', '01-OCT-2014 12:00:10');
INSERT INTO sales_audit VALUES (11, 'HOHOS', '01-OCT-2014 12:00:11');
INSERT INTO sales_audit VALUES (12, 'TWINKIES', '01-OCT-2014 12:00:12');
INSERT INTO sales_audit VALUES (13, 'TWINKIES', '01-OCT-2014 12:00:13');
INSERT INTO sales_audit VALUES (14, 'DINGDONGS', '01-OCT-2014 12:00:14');
INSERT INTO sales_audit VALUES (15, 'DINGDONGS', '01-OCT-2014 12:00:15');
INSERT INTO sales_audit VALUES (16, 'HOHOS', '01-OCT-2014 12:00:16');
INSERT INTO sales_audit VALUES (17, 'TWINKIES', '01-OCT-2014 12:00:17');
INSERT INTO sales_audit VALUES (18, 'TWINKIES', '01-OCT-2014 12:00:18');
INSERT INTO sales_audit VALUES (19, 'TWINKIES', '01-OCT-2014 12:00:19');
INSERT INTO sales_audit VALUES (20, 'TWINKIES', '01-OCT-2014 12:00:20');
COMMIT;

La siguiente consulta muestra el orden de venta del producto para un período de tiempo específico, al que nos referiremos más adelante.

COLUMN tstamp FORMAT A20

SELECT *
FROM   sales_audit
ORDER BY tstamp;

        ID PRODUCT    TSTAMP
---------- ---------- --------------------
         1 TWINKIES   01-OCT-2014 12:00:01
         2 TWINKIES   01-OCT-2014 12:00:02
         3 DINGDONG   01-OCT-2014 12:00:03
         4 HOHOS      01-OCT-2014 12:00:04
         5 HOHOS      01-OCT-2014 12:00:05
         6 TWINKIES   01-OCT-2014 12:00:06
         7 TWINKIES   01-OCT-2014 12:00:07
         8 DINGDONGS  01-OCT-2014 12:00:08
         9 DINGDONGS  01-OCT-2014 12:00:09
        10 HOHOS      01-OCT-2014 12:00:10
        11 HOHOS      01-OCT-2014 12:00:11
        12 TWINKIES   01-OCT-2014 12:00:12
        13 TWINKIES   01-OCT-2014 12:00:13
        14 DINDONGS   01-OCT-2014 12:00:14
        15 HOHOS      01-OCT-2014 12:00:15
        16 TWINKIES   01-OCT-2014 12:00:16
        17 TWINKIES   01-OCT-2014 12:00:17
        18 TWINKIES   01-OCT-2014 12:00:18
        19 TWINKIES   01-OCT-2014 12:00:19
        20 TWINKIES   01-OCT-2014 12:00:20

20 rows selected.

SQL>

Ejemplos

Busque picos / picos en las ventas, donde las ventas suben y luego bajan. Observe que las variables de patrón «UP», «FLAT» and «DOWN» están definidas para mostrar un aumento, ningún cambio y una disminución en el valor respectivamente. El patrón que estamos buscando es 1-Many UPs, nivelando opcionalmente, seguido de 1-Many Downs. Las medidas que se muestran son el inicio del patrón (STRT.tstamp), la parte superior del pico (LAST (UP.tstamp)) y la parte inferior de la gota (LAST (DOWN.tstamp)), con una sola fila para cada coincidencia. También mostramos MATCH_NUMBER ().

ALTER SESSION SET nls_timestamp_format = 'DD-MON-YYYY';

COLUMN start_tstamp FORMAT A11
COLUMN peak_tstamp  FORMAT A11
COLUMN end_tstamp   FORMAT A11

SELECT *
FROM   sales_history MATCH_RECOGNIZE (
         PARTITION BY product
         ORDER BY tstamp
         MEASURES  STRT.tstamp AS start_tstamp,
                   LAST(UP.tstamp) AS peak_tstamp,
                   LAST(DOWN.tstamp) AS end_tstamp,
                   MATCH_NUMBER() AS mno
         ONE ROW PER MATCH
         AFTER MATCH SKIP TO LAST DOWN
         PATTERN (STRT UP+ FLAT* DOWN+)
         DEFINE
           UP AS UP.units_sold > PREV(UP.units_sold),
           FLAT AS FLAT.units_sold = PREV(FLAT.units_sold),
           DOWN AS DOWN.units_sold < PREV(DOWN.units_sold) ) MR ORDER BY MR.product, MR.start_tstamp; PRODUCT START_TSTAM PEAK_TSTAMP END_TSTAMP MNO ---------- ----------- ----------- ----------- ---------- TWINKIES 01-OCT-2014 03-OCT-2014 06-OCT-2014 1 TWINKIES 06-OCT-2014 08-OCT-2014 09-OCT-2014 2 TWINKIES 09-OCT-2014 13-OCT-2014 16-OCT-2014 3 TWINKIES 16-OCT-2014 18-OCT-2014 20-OCT-2014 4 4 rows selected. SQL>

La salida nos dice que hubo 4 picos / picos distintos en las ventas, lo que nos da la ubicación del inicio, el pico y el final del patrón.
La siguiente consulta es similar, pero muestra todas las filas para la coincidencia e incluye la función CLASSIFIER () para indicar qué variable de patrón es relevante para cada fila.

SET LINESIZE 110
ALTER SESSION SET nls_timestamp_format = 'DD-MON-YYYY';

COLUMN start_tstamp FORMAT A11
COLUMN peak_tstamp  FORMAT A11
COLUMN end_tstamp   FORMAT A11
COLUMN cls          FORMAT A5

SELECT *
FROM   sales_history MATCH_RECOGNIZE (
         PARTITION BY product
         ORDER BY tstamp
         MEASURES  STRT.tstamp AS start_tstamp,
                   FINAL LAST(UP.tstamp) AS peak_tstamp,
                   FINAL LAST(DOWN.tstamp) AS end_tstamp,
                   MATCH_NUMBER() AS mno,
                   CLASSIFIER() AS cls
         ALL ROWS PER MATCH
         AFTER MATCH SKIP TO LAST DOWN
         PATTERN (STRT UP+ FLAT* DOWN+)
         DEFINE
           UP AS UP.units_sold > PREV(UP.units_sold),
           DOWN AS DOWN.units_sold < PREV(DOWN.units_sold), FLAT AS FLAT.units_sold = PREV(FLAT.units_sold) ) MR ORDER BY MR.product, MR.mno, MR.tstamp; PRODUCT TSTAMP START_TSTAM PEAK_TSTAMP END_TSTAMP MNO CLS ID UNITS_SOLD ---------- ----------- ----------- ----------- ----------- ---------- ----- ---------- ---------- TWINKIES 01-OCT-2014 01-OCT-2014 03-OCT-2014 06-OCT-2014 1 STRT 1 17 TWINKIES 02-OCT-2014 01-OCT-2014 03-OCT-2014 06-OCT-2014 1 UP 2 19 TWINKIES 03-OCT-2014 01-OCT-2014 03-OCT-2014 06-OCT-2014 1 UP 3 23 TWINKIES 04-OCT-2014 01-OCT-2014 03-OCT-2014 06-OCT-2014 1 FLAT 4 23 TWINKIES 05-OCT-2014 01-OCT-2014 03-OCT-2014 06-OCT-2014 1 DOWN 5 16 TWINKIES 06-OCT-2014 01-OCT-2014 03-OCT-2014 06-OCT-2014 1 DOWN 6 10 TWINKIES 06-OCT-2014 06-OCT-2014 08-OCT-2014 09-OCT-2014 2 STRT 6 10 TWINKIES 07-OCT-2014 06-OCT-2014 08-OCT-2014 09-OCT-2014 2 UP 7 14 TWINKIES 08-OCT-2014 06-OCT-2014 08-OCT-2014 09-OCT-2014 2 UP 8 16 TWINKIES 09-OCT-2014 06-OCT-2014 08-OCT-2014 09-OCT-2014 2 DOWN 9 15 TWINKIES 09-OCT-2014 09-OCT-2014 13-OCT-2014 16-OCT-2014 3 STRT 9 15 TWINKIES 10-OCT-2014 09-OCT-2014 13-OCT-2014 16-OCT-2014 3 UP 10 17 TWINKIES 11-OCT-2014 09-OCT-2014 13-OCT-2014 16-OCT-2014 3 UP 11 23 TWINKIES 12-OCT-2014 09-OCT-2014 13-OCT-2014 16-OCT-2014 3 UP 12 30 TWINKIES 13-OCT-2014 09-OCT-2014 13-OCT-2014 16-OCT-2014 3 UP 13 31 TWINKIES 14-OCT-2014 09-OCT-2014 13-OCT-2014 16-OCT-2014 3 DOWN 14 29 TWINKIES 15-OCT-2014 09-OCT-2014 13-OCT-2014 16-OCT-2014 3 DOWN 15 25 TWINKIES 16-OCT-2014 09-OCT-2014 13-OCT-2014 16-OCT-2014 3 DOWN 16 21 TWINKIES 16-OCT-2014 16-OCT-2014 18-OCT-2014 20-OCT-2014 4 STRT 16 21 TWINKIES 17-OCT-2014 16-OCT-2014 18-OCT-2014 20-OCT-2014 4 UP 17 35 TWINKIES 18-OCT-2014 16-OCT-2014 18-OCT-2014 20-OCT-2014 4 UP 18 46 TWINKIES 19-OCT-2014 16-OCT-2014 18-OCT-2014 20-OCT-2014 4 DOWN 19 45 TWINKIES 20-OCT-2014 16-OCT-2014 18-OCT-2014 20-OCT-2014 4 DOWN 20 30 23 rows selected. SQL>

Observe cómo se duplican algunas filas, ya que representan el final de un patrón y el comienzo del siguiente.
El siguiente ejemplo identificó la única ocurrencia de un aumento general en los valores, que contiene un solo valor de caída.

SELECT *
FROM   sales_history MATCH_RECOGNIZE (
         PARTITION BY product
         ORDER BY tstamp
         MEASURES  STRT.tstamp AS start_tstamp,
                   FINAL LAST(UP.tstamp) AS peak_tstamp,
                   MATCH_NUMBER() AS mno,
                   CLASSIFIER() AS cls
         ALL ROWS PER MATCH
         AFTER MATCH SKIP TO LAST DOWN
         PATTERN (STRT UP+ DOWN{1} UP+)
         DEFINE
           UP AS UP.units_sold > PREV(UP.units_sold),
           DOWN AS DOWN.units_sold < PREV(DOWN.units_sold) ) MR ORDER BY MR.product, MR.tstamp; PRODUCT TSTAMP START_TSTAM PEAK_TSTAMP MNO CLS ID UNITS_SOLD ---------- ----------- ----------- ----------- ---------- ----- ---------- ---------- TWINKIES 06-OCT-2014 06-OCT-2014 13-OCT-2014 1 STRT 6 10 TWINKIES 07-OCT-2014 06-OCT-2014 13-OCT-2014 1 UP 7 14 TWINKIES 08-OCT-2014 06-OCT-2014 13-OCT-2014 1 UP 8 16 TWINKIES 09-OCT-2014 06-OCT-2014 13-OCT-2014 1 DOWN 9 15 TWINKIES 10-OCT-2014 06-OCT-2014 13-OCT-2014 1 UP 10 17 TWINKIES 11-OCT-2014 06-OCT-2014 13-OCT-2014 1 UP 11 23 TWINKIES 12-OCT-2014 06-OCT-2014 13-OCT-2014 1 UP 12 30 TWINKIES 13-OCT-2014 06-OCT-2014 13-OCT-2014 1 UP 13 31 8 rows selected. SQL>

Pasando a la tabla de auditoría, podemos buscar patrones específicos de ventas. En este caso, no queremos dividir el conjunto de resultados ya que el patrón abarca varios productos. Buscamos ventas de 2-Many TWINKIES, seguidas de 2 DINGDONGS y 1 HOHOS, seguidas de 3-Many TWINKIES.

ALTER SESSION SET nls_timestamp_format = 'DD-MON-YYYY HH24:MI:SS';

COLUMN tstamp       FORMAT A20
COLUMN start_tstamp FORMAT A20
COLUMN end_tstamp   FORMAT A20
COLUMN cls          FORMAT A10

SELECT *
FROM   sales_audit MATCH_RECOGNIZE (
         --PARTITION BY product
         ORDER BY tstamp
         MEASURES  FIRST(TWINKIES.tstamp) AS start_tstamp,
                   FINAL LAST(TWINKIES.tstamp) AS end_tstamp,
                   MATCH_NUMBER() AS mno,
                   CLASSIFIER() AS cls
         ALL ROWS PER MATCH
         AFTER MATCH SKIP TO LAST TWINKIES
         PATTERN(TWINKIES{2,} DINGDONGS{2} HOHOS{1} TWINKIES{3,})
         DEFINE
           TWINKIES AS TWINKIES.product='TWINKIES',
           DINGDONGS AS DINGDONGS.product='DINGDONGS',
           HOHOS AS HOHOS.product='HOHOS'
       ) MR
ORDER BY MR.mno, MR.tstamp;

TSTAMP               START_TSTAMP         END_TSTAMP                  MNO CLS                ID PRODUCT
-------------------- -------------------- -------------------- ---------- ---------- ---------- ----------
01-OCT-2014 12:00:12 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20          1 TWINKIES           12 TWINKIES
01-OCT-2014 12:00:13 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20          1 TWINKIES           13 TWINKIES
01-OCT-2014 12:00:14 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20          1 DINGDONGS          14 DINGDONGS
01-OCT-2014 12:00:15 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20          1 DINGDONGS          15 DINGDONGS
01-OCT-2014 12:00:16 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20          1 HOHOS              16 HOHOS
01-OCT-2014 12:00:17 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20          1 TWINKIES           17 TWINKIES
01-OCT-2014 12:00:18 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20          1 TWINKIES           18 TWINKIES
01-OCT-2014 12:00:19 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20          1 TWINKIES           19 TWINKIES
01-OCT-2014 12:00:20 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20          1 TWINKIES           20 TWINKIES

9 rows selected.

SQL>

Podemos ver que solo hay una coincidencia para ese patrón en los datos.
A continuación, verificamos una serie de ventas de TWINKIES separadas por exactamente tres ventas que coincidan con cualquier combinación de DINGDONGS y / o HOHOS.

SELECT *
FROM   sales_audit MATCH_RECOGNIZE (
         --PARTITION BY product
         ORDER BY tstamp
         MEASURES  FIRST(TWINKIES.tstamp) AS start_tstamp,
                   FINAL LAST(TWINKIES.tstamp) AS end_tstamp,
                   MATCH_NUMBER() AS mno,
                   CLASSIFIER() AS cls
         ALL ROWS PER MATCH
         AFTER MATCH SKIP TO LAST TWINKIES
         PATTERN(TWINKIES+ (DINGDONGS | HOHOS){3} TWINKIES+)
         DEFINE
           TWINKIES AS TWINKIES.product='TWINKIES',
           DINGDONGS AS DINGDONGS.product='DINGDONGS',
           HOHOS AS HOHOS.product='HOHOS'
       ) MR
ORDER BY MR.mno, MR.tstamp;

TSTAMP               START_TSTAMP         END_TSTAMP                  MNO CLS                ID PRODUCT
-------------------- -------------------- -------------------- ---------- ---------- ---------- ----------
01-OCT-2014 12:00:01 01-OCT-2014 12:00:01 01-OCT-2014 12:00:07          1 TWINKIES            1 TWINKIES
01-OCT-2014 12:00:02 01-OCT-2014 12:00:01 01-OCT-2014 12:00:07          1 TWINKIES            2 TWINKIES
01-OCT-2014 12:00:03 01-OCT-2014 12:00:01 01-OCT-2014 12:00:07          1 DINGDONGS           3 DINGDONGS
01-OCT-2014 12:00:04 01-OCT-2014 12:00:01 01-OCT-2014 12:00:07          1 HOHOS               4 HOHOS
01-OCT-2014 12:00:05 01-OCT-2014 12:00:01 01-OCT-2014 12:00:07          1 HOHOS               5 HOHOS
01-OCT-2014 12:00:06 01-OCT-2014 12:00:01 01-OCT-2014 12:00:07          1 TWINKIES            6 TWINKIES
01-OCT-2014 12:00:07 01-OCT-2014 12:00:01 01-OCT-2014 12:00:07          1 TWINKIES            7 TWINKIES
01-OCT-2014 12:00:12 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20          2 TWINKIES           12 TWINKIES
01-OCT-2014 12:00:13 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20          2 TWINKIES           13 TWINKIES
01-OCT-2014 12:00:14 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20          2 DINGDONGS          14 DINGDONGS
01-OCT-2014 12:00:15 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20          2 DINGDONGS          15 DINGDONGS
01-OCT-2014 12:00:16 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20          2 HOHOS              16 HOHOS
01-OCT-2014 12:00:17 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20          2 TWINKIES           17 TWINKIES
01-OCT-2014 12:00:18 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20          2 TWINKIES           18 TWINKIES
01-OCT-2014 12:00:19 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20          2 TWINKIES           19 TWINKIES
01-OCT-2014 12:00:20 01-OCT-2014 12:00:12 01-OCT-2014 12:00:20          2 TWINKIES           20 TWINKIES

16 rows selected.

SQL>

Podemos ver que hay dos coincidencias con este patrón.

Una vez que tenga una idea de la sintaxis de coincidencia de patrones, debería poder comenzar a construir sus propios ejemplos de patrones. También puede consultar la documentación oficial de ORACLE para obtener más ejemplos.

 

Quizás también te puedas interesar:

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *