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.