En nuestro dw basado en BigQuery, manejamos volúmenes bastante grandes de datos de sensores que ingresan. De estos, creamos diferentes mercados de datos para un uso específico.

Hacemos actualizaciones incrementales de algunos de los mercados. Uno de estos marts debe tener el valor de la fila anterior agregado a la fila actual. Eso es fácil de hacer para una actualización completa, pero ¿cómo se puede hacer mejor para las actualizaciones incrementales?

Considere el siguiente ejemplo donde el incremental se basa en la fecha:

WITH sampledata AS
 (SELECT 1 as id, 1 as value, TIMESTAMP '2016-10-17 2:51:45' as dt
  UNION ALL SELECT 1,2, TIMESTAMP '2016-10-18 2:54:11'
  UNION ALL SELECT 1,3, TIMESTAMP '2016-10-18 2:59:01'
  UNION ALL SELECT 1,2, TIMESTAMP '2016-10-18 3:01:17'
  UNION ALL SELECT 1,4, TIMESTAMP '2016-10-18 3:05:42'
  UNION ALL SELECT 1,2, TIMESTAMP '2016-10-18 3:06:24'
  UNION ALL SELECT 1,4, TIMESTAMP '2016-10-18 3:06:36'
  UNION ALL SELECT 1,3, TIMESTAMP '2016-10-18 3:07:41'
  UNION ALL SELECT 1,4, TIMESTAMP '2016-10-18 3:08:58'
  UNION ALL SELECT 1,1, TIMESTAMP '2016-10-18 3:10:14')

select 
  id,
  value,
  LAG(value ) OVER(PARTITION BY id ORDER BY dt ASC) as prev_value,
  dt
from sampledata
where DATE(dt) > "2016-10-17"

Resultados: | id | valor | prev_value | dt | | - | ----- | ---------- | - | | 1 | 2 | nulo | 2016-10-18 02:54:11 UTC | | 1 | 3 | 2 | 2016-10-18 02:59:01 UTC | | 1 | 2 | 3 | 2016-10-18 03:01:17 UTC | ...etcétera..

Verá que la primera fila se vuelve nula como su prev_value porque si el factor de fecha límite en la cláusula where (necesario para las actualizaciones incrementales). Hemos intentado unir la tabla por sí solos / unirlos de forma cruzada, lo que funciona, pero es demasiado lento para la cantidad de datos que tenemos.

¿Alguien tiene algún consejo sobre lo que se puede probar?

0
gixen 4 mar. 2021 a las 01:58

1 respuesta

La mejor respuesta

Prueba a continuación

select * from (
  select 
    id,
    value,
    LAG(value ) OVER(PARTITION BY id ORDER BY dt ASC) as prev_value,
    dt
  from sampledata
)
where DATE(dt) > "2016-10-17" 

Con salida

enter image description here

0
Mikhail Berlyant 3 mar. 2021 a las 23:03