Cómo calcular diferencias de valor entre dos filas

Pongamos que tenemos una aplicación como coingecko y estamos rastreando los exchanges para obtener un listado de los precios de cada símbolo. Para ello nos basamos en una tabla sencilla de Postgres (o incluso timescale). Obviamente nos hemos preocupado de optimizar en todo momento los particionamientos de las tablas, índices, etc.

De repente, en un proceso de auditoría o, simplemente al comparar los precios de dos exchanges para llevar a cabo un proceso de arbitraje, nos damos cuenta que, de algunos exanges, nos faltan datos. Es decir, no tenemos algunos precios y queremos poner solución a este problema.

La tabla en la que almacenamos los datos la hemos llamado ticks y tiene la siguiente estructura:

Esta tabla almacena todas las velas o klines agrupadas por minuto.

Como nos hemos dado cuenta durante este proceso de auditoría de que nos faltan datos de algunos minutos, queremos hacer una select que itere en los más de 2.000.000 de registros y nos indique qué velas nos faltan para poder poner solución.

Mucha gente abordaría este problema mediante un bucle e iterando en el lenguaje de programación final o bien mediante un procedimiento almacenado. Sin embargo, existe una forma muy interesante de hacer esto con un único comando en PostgreSQL.

Supongamos que tenemos un subconjunto de datos como el siguiente:

Pues bien, si queremos obtener la diferencia entre el valor de una fila y el valor de la anterior deberemos hacer uso de la función LAG(). Esta diferencia de valor entre filas es crucial para poder realizar la tarea que nos proponemos.

LAG(expression [,offset [,default_value]]) 
OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

Lag

Esta función nos permitirá acceder a un dato de una fila distinta a la actual mediante los siguientes valores:

a) Expresión

Expresión que se evalúa en la fila que aparece en la fila indicada por el offset. Puede ser una columna, una expresión o una subquery.

b) Offset

Entero positivo que indica cuántas filas antes tiene que aplicar la expresión. El valor por defecto es 1.

c) Default_value

Valor por defecto en caso de que el offset no se pueda aplicar. El valor por defecto es NULL.

Over

Indica las características del conjunto de datos sobre el que iterará.

a) Partition by

Permite dividir las filas en particiones sobre las que se aplicará la función LAG() se aplica. Por defecto se usará una única partición.

b) Order by

Permite especificar el orden de las filas sobre las que aplicaremos la función LAG().

Resolución del problema

Sabiendo la estructura de la expresión, vamos a ir paso a paso:

Primero vamos a obtener todas las entradas para un símbolo en concreto:

SELECT * 
FROM ticks 
WHERE symbol = 'ETHBTC';

Ahora queremos mostrar además de la fecha de cierre de la fila actual, la de la fila anterior:

select close_time, lag(close_time) over (order by close_time), *
	from ticks 
	where symbol = 'ETHBTC'

Como se puede observar en esta imagen, en la primera fila, la columna lag tiene el valor NULL ya que no existe ninguna fila previa. Sin embargo, el valor de lag de la fila N tendrá el valor de la columna close_time de la fila N-1.

Ahora nos interesa , en lugar de obtener valores anteriores, vamos a a calcular el intervalo entre estas dos filas.

select close_time - lag(close_time) over (order by close_time) as "interval", *
	from ticks 
	where symbol = 'ETHBTC'

Ahora queremos poder filtrar por el nuevo campo. Por desgracia no se puede filtrar por el campo «interval». Por ello, deberemos hacer uso de una subselect para aplicar el filtro que deseemos u ordenar los datos finales en función de ese campo. En este caso, vamos a ver qué velas de 1 minuto nos faltan buscando intervalos superiores a 00:01:00.

select * from (
select close_time - lag(close_time) over (order by close_time) as increase, close_time, lag(close_time) over (order by close_time), *
	from ticks 
	where symbol = 'ETHBTC'
	) as tmp where increase > '00:01:00'

Obteniendo el siguiente resultado:

Ni que decir tiene, la función LAG no es gratuita en recursos y por ello hay que ejecutarla con cuidado y sólo cuando sea estrictamente necesario.