0
412
Vistaspostgresql: ubica una fila aleatoria entre dos filas específicas que siguen un patrón
Rascarme la cabeza tratando de hacer que esto funcione. Tengo una tabla histórica que tiene varias filas (acciones) para una cuenta. Conozco el patrón antes y después de una acción específica y quisiera una consulta para ubicar la acción intermedia para todas las cuentas en esa tabla. El problema es que todo lo que he intentado puede ubicar las acciones que quiero usar como guía (antes y después), pero no la del medio.
Por ejemplo, aquí hay un ejemplo de tabla para ayudar a explicar el escenario:
|action id | comment | timestamp | |----------|-----------------|--------------| | 0110 | random comment | timestamp 1 | | 0117 | text pattern 1 | timestamp 2 | | 0129 | RANDOM COMMENT | timestamp 3 | | 0130 | text pattern 2 | timestamp 4 | | 0136 | random comment | timestamp 5 | | etc.. | | |Entonces, como puede ver, el único patrón consistente con el que tengo que trabajar es el patrón de texto antes y después de la fila de destino (id 129). Incluí las marcas de tiempo porque pensé que tal vez podría ser algo que tal vez podría usarse. Hay otras columnas en la tabla, pero son esencialmente aleatorias y no sirven de mucho para esta consulta.
¿Alguna idea sobre cómo puedo lograr esto?
Gracias de antemano por cualquier consejo, es muy apreciado.
Código que utilicé que no funcionó:
select distinct ht.accountid, ht.id, ht.comment from historic_table ht where ht.id > (select MAX(ht1.id) from historic_table ht1 where ht1.accountid = ht.accountid and ht1.comment ilike '%comment pattern 1%') and ht.id < (select MAX(ht2.id) from historic_table ht2 where ht2.accountid = ht.accountid and ht2.comment ilike '%comment pattern 2%') limit 10Y aquí hay un ejemplo de la salida que estoy buscando para seleccionar. En amarillo están las filas anteriores y posteriores con los distintos patrones de texto. En verde, he resaltado todas las filas que me gustaría ver en la salida. Incluí solo 2 ejemplos, uno con 1 fila de destino y otro con varias filas de destino. Espero que esto ayude a aclarar:
2 Respuestas
Responde la pregunta0
Esto no es perfecto, no es óptimo, pero parece funcionar. El CTE garantiza que la tabla de historial de acciones solo se analiza una vez, pero seguirá siendo una exploración secuencial, debido al ILIKE '% zzz%' .
Si el historial es relativamente estable, probablemente guardaría las identificaciones de los registros de inicio/detención en una tabla (temp) en lugar de un CTE.
NOTA : esta solución asume que los registros están ordenados correctamente (cada patrón de inicio tiene exactamente un patrón de parada coincidente y que no se anidan )
CREATE TABLE zaction( id INTEGER NOT NULL PRIMARY KEY , zcomment text , ztimestamp timestamp NOT NULL ); INSERT INTO zaction(id, zcomment, ztimestamp) VALUES ( 0110, 'random comment', '2017-04-27 12:00:00' ) ,( 0117, 'text pattern 1', '2017-04-27 12:10:00' ) ,( 0129, 'RANDOM COMMENT', '2017-04-27 12:20:00' ) ,( 0130, 'text pattern 2', '2017-04-27 12:30:00' ) ,( 0136, 'random comment', '2017-04-27 12:40:00' ) -- ,( 1110, 'random comment', '2017-04-27 12:00:00' ) ,( 1117, 'text pattern 1', '2017-04-27 12:10:00' ) ,( 1123, 'RANDOM COMMENT', '2017-04-27 12:20:00' ) ,( 1129, 'RANDOM CONTENT', '2017-04-27 12:20:00' ) ,( 1130, 'text pattern 2', '2017-04-27 12:30:00' ) ,( 1136, 'random comment', '2017-04-27 12:40:00' ) -- ; VACUUM ANALYZE zaction; -- SELECT * FROM zaction; EXPLAIN WITH z1 AS ( SELECT za.id , CASE WHEN zcomment ilike '%text pattern 1' THEN 1 WHEN zcomment ilike '%text pattern 2' THEN -1 ELSE 0 END AS dir FROM zaction za ) , z2 AS ( SELECT z1.id, z1.dir , SUM(z1.dir) OVER (ORDER BY z1.id) AS yesno FROM z1 ) SELECT za.* FROM zaction za JOIN z2 ON z2.id = za.id AND z2.yesno > 0 AND z2.dir = 0 ;Versión ligeramente modificada, que intenta mantener pequeños los CTE :
-- EXPLAIN WITH z1 AS ( SELECT za.id , CASE WHEN zcomment ilike '%text pattern 1' THEN 1 WHEN zcomment ilike '%text pattern 2' THEN -1 ELSE 0 END AS dir FROM zaction za WHERE zcomment ilike '%text pattern %' -- <<== preselection to keep the CTE small ) SELECT za.* FROM zaction za JOIN ( -- <<== Join with subquery to give the optimiser some freedom SELECT za.id, z1.dir , SUM(z1.dir) OVER (ORDER BY za.id) AS yesno FROM zaction za LEFT JOIN z1 ON z1.id = za.id -- <<== remerge with original set, whicha coulduse an index ) z2 ON z2.id = za.id AND z2.yesno > 0 AND z2.dir IS DISTINCT FROM 1 ;0
Logré obtener los resultados necesarios, aunque con un formato de salida comprometido al usar string_agg para incluir todos los ID de acción y comentarios entre los dos patrones de texto especificados para todas las cuentas. Lo publiqué a continuación en caso de que alguien más pudiera usarlo:
select ht.accountid ,string_agg(ht.id::text, ', ') as actions_ids ,string_agg(ht.comment, ', ') as comments from historic_table ht where ht.timestamp >= '2017-02-01' and ht.timestamp < '2017-03-01' and ht.id > (select max(ht1.id) from historic_table ht1 where ht1.accountid = ht.accountid and ht1.comment ilike '%comment pattern 1%') and ht.id < (select max(ht2.id) from historic_table ht2 where ht2.accountid = ht.accountid and ht2.comment ilike '%comment pattern 2%') group by ht.accountid order by 1Específicamente, tuve que eliminar el distintivo (que de todos modos no necesitaba) y este fue el culpable de causar el bloqueo cuando lo ejecuté antes (la tabla era tan grande con muchos comentarios personalizados que el distintivo estaba tratando de ordenar/unique el todo el conjunto de resultados y sobrecargó los datos almacenados en pg_temp). Al agregar un filtro de marca de tiempo y eliminar el distintivo, ahora se ejecuta bastante rápido.