Cómo agregar una partición condicional en una función de ventana con Trino o Impala?
Por ejemplo, tengo un conjunto de datos como el siguiente:
tiempo | acción |
---|---|
03:00:00 | bloquear |
04:00:00 | desbloquear |
05:00:00 | bloquear |
06:00:00 | desbloquear |
07:00:00 | desbloquear |
08:00:00 | bloquear |
Ahora, para cada fila, quiero obtener el último tiempo en el que la columna acción
sea igual a “bloquear” antes del tiempo de la fila actual. Por ejemplo, para la quinta fila cuyo tiempo
es “07:00:00” y acción
es “desbloquear”, el último tiempo antes de ese cuando acción
sea igual a “bloquear” debería ser la tercera fila, y el tiempo esperado es “05:00:00”.
Mi resultado final esperado sería:
tiempo | acción | último_tiempo |
---|---|---|
03:00:00 | bloquear | 03:00:00 |
04:00:00 | desbloquear | 03:00:00 |
05:00:00 | bloquear | 05:00:00 |
06:00:00 | desbloquear | 05:00:00 |
07:00:00 | desbloquear | 05:00:00 |
08:00:00 | bloquear | 08:00:00 |
¿Cómo puedo obtener el resultado anterior usando una función de ventana sin unir por sí misma?
(p.d. si no se puede obtener el resultado anterior, también está bien el siguiente resultado:
tiempo | acción | último_tiempo |
---|---|---|
03:00:00 | bloquear | NULL |
04:00:00 | desbloquear | 03:00:00 |
05:00:00 | bloquear | 03:00:00 |
06:00:00 | desbloquear | 05:00:00 |
07:00:00 | desbloquear | 05:00:00 |
08:00:00 | bloquear | 05:00:00 |
davy.ai
Para obtener la última vez en que la columna
action
es igual a “block” antes de la hora de la fila actual sin unirse a sí misma, podemos utilizar una función de ventana de la siguiente manera:Explicación:
MAX
con una declaraciónCASE
en su interior para obtener el tiempo máximo cuandoaction
es igual a “block” antes de la fila actual.OVER
se utiliza para aplicar la funciónMAX
como una función de ventana.ORDER BY
en la cláusulaOVER
se utiliza para ordenar las filas portime
.ROWS
en la cláusulaOVER
define el marco de ventana que será considerado por la función de ventana. En este caso, se establece que sean todas las filas desde el inicio de la partición hasta una fila antes de la fila actual.Nota:
action
es “unblock”, el resultado de la primera fila será NULL en lugar de la hora de la primera fila.