Análise de dados de séries temporais¶
Você pode analisar dados de séries temporais no Snowflake, usando uma funcionalidade projetada especificamente para essa finalidade. Administradores de banco de dados, cientistas de dados e desenvolvedores de aplicativos precisam garantir que as séries temporais sejam armazenadas e carregadas de forma eficiente e, em muitos casos, resumidas em um formato completo e consistente, antes de disponibilizar os dados para analistas de negócios e outros consumidores.
Introdução: O que é uma série temporal?¶
Uma série temporal consiste em observações sequenciais que capturam como sistemas, processos e comportamentos mudam ao longo de um período. Dados de séries temporais são coletados de uma ampla gama de dispositivos em uma ampla gama de setores. Exemplos comuns incluem dados de negociação de ações coletados para aplicações financeiras, observações meteorológicas, leituras de temperatura coletadas de sensores em fábricas inteligentes e logs de cliques de usuários em publicidade digital.
Um único registro em uma série temporal normalmente tem os seguintes componentes:
Uma data, hora ou carimbo de data/hora que tenha um nível consistente de granularidade (milissegundos, segundos, minutos, horas etc.).
Uma ou mais medições ou métricas de algum tipo, geralmente numéricas (fatos que podem revelar tendências ou anomalias nos dados).
Dimensões de interesse associadas à medição, como um local para uma leitura de temperatura ou um símbolo de ação para uma determinada negociação.
Por exemplo, a seguinte observação meteorológica tem carimbos de data/hora de início e término, uma medição de precipitação (0.32) e informações de localização:
EVENTID | TYPE | SEVERITY | START_TIME | END_TIME | PRECIP | TIME_ZONE | CITY | COUNTY | STATE | ZIP
W100 | Rain | Moderate | 2020-12-20 16:35:00.000 | 2020-12-20 17:15:00.000 | 0.32 | US/Eastern | Southport | Brunswick | NC | 28461
Os seguintes dados coletados de um dispositivo de fábrica têm um namespace (IOT), uma ID de tag ou ID de sensor (3000), um carimbo de data/hora para a leitura da temperatura no dispositivo, a própria leitura da temperatura (21.1673) e um “carimbo de data/hora do corretor”, que é quando os dados chegaram posteriormente ao corretor de dados. Por exemplo, o corretor de dados pode ser um servidor Kafka que ingere dados em uma tabela Snowflake.
DEVICE | LINE | DEVICE_TIMESTAMP | TEMP | BROKER_TIMESTAMP
IOT | 3000 | 2023-01-01 00:01:00.000 | 21.1673 | 2023-01-01 00:01:32.000
Uma série temporal pode revelar picos quando as leituras mudam drasticamente por algum motivo. Por exemplo, a imagem a seguir mostra uma sequência de leituras de temperatura feitas em intervalos de 15 segundos, com valores atingindo o pico acima de 40 °C após permanecerem estáveis na faixa de 35 °C no dia anterior.
As seções a seguir mostram como analisar e visualizar grandes volumes desse tipo de dados com funções SQL e junções que fornecem resultados rápidos e precisos.
Como armazenar dados de séries temporais¶
Os seguintes tipos de dados datetime têm suporte:
DATE
TIME
TIMESTAMP (e variações, incluindo TIMESTAMP_TZ)
Para obter mais informações sobre como carregar, gerenciar e consultar dados que usam esses tipos de dados, consulte Como trabalhar com valores de data e hora.
Uma série de funções SQL comumente usadas estão disponíveis para ajudar tanto no armazenamento quanto na consulta de dados de séries temporais. Por exemplo, você pode usar CONVERT_TIMEZONE para converter carimbos de data/hora de um fuso horário para outro, e você pode usar funções como EXTRACT e TIMEADD para manipular dados baseados em tempo conforme necessário.
Nota
Para dados TIMESTAMP_TZ, o Snowflake armazena o deslocamento de um determinado fuso horário, e não o fuso horário real, no momento da criação de um valor específico.
Para otimizar o desempenho da consulta, as tabelas usadas para análises de séries temporais geralmente são clusterizadas por tempo (e às vezes também por ID de sensor ou uma dimensão similar). Consulte Chaves de clustering e tabelas clusterizadas.
Agregação de dados de séries temporais¶
O gerenciamento de dados de séries temporais pode exigir a agregação de grandes volumes de registros detalhados em um formato mais resumido (um processo às vezes chamado de “redução de amostragem”). Dado um grande conjunto de registros com uma granularidade específica baseada em tempo (milissegundos, segundos, minutos etc.), você pode agrupar esses registros para uma granularidade menos precisa, produzindo efetivamente uma amostra menor.
A redução de amostragem é valiosa porque diminui o tamanho de um conjunto de dados e seus requisitos de armazenamento. Um nível menos preciso de granularidade também reduz os requisitos de recursos de computação durante a execução da consulta. Outro motivo importante para a redução de amostragem é que um grande número de registros em uma série temporal pode ser redundante do ponto de vista de um analista. Por exemplo, se um sensor emite um novo valor uma vez a cada segundo, mas essa medição raramente muda dentro de intervalos de 60 segundos, os dados podem ser acumulados até o nível de minuto para análise.
Outro caso de redução de amostragem ocorre quando dois conjuntos de dados diferentes precisam ser analisados como um, mas eles têm granularidades de tempo diferentes. Por exemplo, o sensor A em uma fábrica coleta dados a cada 15 segundos, mas o sensor B coleta dados relacionados a cada 30 segundos. Nesse caso, agregar os registros em intervalos de 1 minuto pode ser uma boa solução. Os IDs e as dimensões em cada conjunto de dados são mantidos como estão, mas as medições numéricas são somadas ou calculadas em média por um intervalo de tempo comum.
Exemplos de redução de amostragem¶
Você pode reduzir a amostragem de um conjunto de dados armazenado em uma tabela usando a função TIME_SLICE. Essa função calcula os horários de início e término de “buckets” de largura fixa para que registros individuais possam ser agrupados e resumidos, usando funções de agregação padrão, como SUM e AVG.
Da mesma forma, a função DATE_TRUNC trunca parte de uma série de valores de data ou carimbo de data/hora, reduzindo sua granularidade. As seções a seguir mostram exemplos de cada função.
Redução de amostragem com TIME_SLICE¶
O exemplo a seguir reduz a amostragem de uma tabela nomeada sensor_data_ts, que contém leituras de dois sensores de fábrica e contém 5,3 milhões de linhas. Essas leituras foram ingeridas por segundo, então 5,3 milhões de linhas representam apenas um mês de dados, com pouco mais de 2,5 milhões de linhas por sensor. Você pode usar a função TIME_SLICE para agregar até uma única linha por minuto, por hora ou por dia, por exemplo.
Para executar este exemplo, primeiro crie e carregue a tabela sensor_data_ts; consulte Criação da tabela sensor_data_ts. Aqui está uma pequena amostra dos dados na tabela:
+-----------+-------------------------+-------------+-----------+-----------+
| DEVICE_ID | TIMESTAMP | TEMPERATURE | VIBRATION | MOTOR_RPM |
|-----------+-------------------------+-------------+-----------+-----------|
| DEVICE1 | 2024-03-01 00:00:00.000 | 32.6908 | 0.3158 | 1492 |
| DEVICE2 | 2024-03-01 00:00:00.000 | 35.2086 | 0.3232 | 1461 |
| DEVICE1 | 2024-03-01 00:00:01.000 | 35.9578 | 0.3302 | 1452 |
| DEVICE2 | 2024-03-01 00:00:01.000 | 26.2468 | 0.3029 | 1455 |
+-----------+-------------------------+-------------+-----------+-----------+
A tabela contém 60 leituras como essas por minuto para cada dispositivo, conforme mostrado por esta consulta:
SELECT device_id, count(*) FROM sensor_data_ts
WHERE TIMESTAMP >= ('2024-03-01 00:01:00')
AND TIMESTAMP < ('2024-03-01 00:02:00')
GROUP BY device_id;
+-----------+----------+
| DEVICE_ID | COUNT(*) |
|-----------+----------|
| DEVICE2 | 60 |
| DEVICE1 | 60 |
+-----------+----------+
Nessa consulta de redução de amostragem, a função TIME_SLICE define intervalos de um minuto e retorna o horário de início de cada intervalo. A função AVG calcula a temperatura média para cada bucket por dispositivo. A função COUNT (*) é incluída para referência, apenas para mostrar quantas linhas caem em cada intervalo de tempo.
As colunas vibration e motor_rpm não estão inclusas, mas podem ser agregadas da mesma forma que a coluna temperature ou usando diferentes funções de agregação.
Importante
Se você executar este exemplo sozinho, sua saída não corresponderá exatamente porque a tabela sensor_data_ts é carregada com valores gerados aleatoriamente.
SELECT
TIME_SLICE(TO_TIMESTAMP_NTZ(timestamp), 1, 'MINUTE') minute_slice,
device_id,
COUNT(*),
AVG(temperature) avg_temp
FROM sensor_data_ts
WHERE TIMESTAMP >= ('2024-03-01 00:01:00')
AND TIMESTAMP < ('2024-03-01 00:02:00')
GROUP BY 1,2
ORDER BY 1,2;
+-------------------------+-----------+----------+---------------+
| MINUTE_SLICE | DEVICE_ID | COUNT(*) | AVG_TEMP |
|-------------------------+-----------+----------+---------------|
| 2024-03-01 00:01:00.000 | DEVICE1 | 60 | 32.4315466667 |
| 2024-03-01 00:01:00.000 | DEVICE2 | 60 | 30.4967783333 |
+-------------------------+-----------+----------+---------------+
Ao usar a função TIME_SLICE, você pode criar tabelas menores e agregadas para fins de análise e pode aplicar o processo de redução de amostragem em diferentes níveis (hora, dia, semana e assim por diante).
Redução de amostragem com DATE_TRUNC¶
O exemplo a seguir seleciona dados de uma tabela nomeada order_header no esquema raw.pos do Banco de dados de amostra Tasty Bytes. Esta tabela contém 248 milhões de linhas.
A tabela order_header tem uma coluna TIMESTAMP nomeada order_ts. A consulta cria uma série temporal agregada usando esta coluna como o segundo argumento para a função DATE_TRUNC. O primeiro argumento especifica um intervalo de day. Isso significa que os registros individuais, que têm uma granularidade de horas/minutos/segundos, são agrupados por dia.
A consulta agrupa os registros por duas dimensões: truck_id e location_id. A coluna avg_amount retorna o preço médio por pedido, por food truck e por local para cada dia útil registrado.
A consulta mostrada aqui limita os resultados às primeiras 25 linhas de 1º de janeiro de 2022. Se você remover este filtro de data e a cláusula LIMIT, a consulta reduz a amostragem das 248 milhões de linhas originais para cerca de 500 mil linhas.
SELECT DATE_TRUNC('day', order_ts)::date sliced_ts, truck_id, location_id, AVG(order_amount)::NUMBER(4,2) as avg_amount
FROM order_header
WHERE EXTRACT(YEAR FROM order_ts)='2022'
GROUP BY date_trunc('day', order_ts), truck_id, location_id
ORDER BY 1, 2, 3 LIMIT 25;
+------------+----------+-------------+------------+
| SLICED_TS | TRUCK_ID | LOCATION_ID | AVG_AMOUNT |
|------------+----------+-------------+------------|
| 2022-01-01 | 1 | 3223 | 19.23 |
| 2022-01-01 | 1 | 3869 | 20.15 |
| 2022-01-01 | 2 | 2401 | 39.29 |
| 2022-01-01 | 2 | 4199 | 34.29 |
| 2022-01-01 | 3 | 2883 | 35.01 |
| 2022-01-01 | 3 | 2961 | 39.15 |
| 2022-01-01 | 4 | 2614 | 35.95 |
| 2022-01-01 | 4 | 2899 | 40.29 |
| 2022-01-01 | 6 | 1946 | 26.58 |
| 2022-01-01 | 6 | 14960 | 18.59 |
| 2022-01-01 | 7 | 1427 | 26.91 |
| 2022-01-01 | 7 | 3224 | 28.88 |
| 2022-01-01 | 9 | 1557 | 35.52 |
| 2022-01-01 | 9 | 2612 | 43.80 |
| 2022-01-01 | 10 | 2217 | 32.35 |
| 2022-01-01 | 10 | 2694 | 32.23 |
| 2022-01-01 | 11 | 2656 | 44.23 |
| 2022-01-01 | 11 | 3327 | 52.00 |
| 2022-01-01 | 12 | 3181 | 52.84 |
| 2022-01-01 | 12 | 3622 | 49.59 |
| 2022-01-01 | 13 | 2516 | 31.13 |
| 2022-01-01 | 13 | 3876 | 28.13 |
| 2022-01-01 | 14 | 1359 | 72.04 |
| 2022-01-01 | 14 | 2505 | 68.75 |
| 2022-01-01 | 15 | 2901 | 41.90 |
+------------+----------+-------------+------------+
Usar agregações em janela para cálculos contínuos¶
Ao usar funções de agregação em janela para observar como uma métrica muda ao longo do tempo, você pode analisar uma série temporal em busca de tendências. Agregações em janelas são úteis para analisar dados dentro de subconjuntos definidos (“janelas”) de um conjunto de dados maior. Você pode fazer cálculos contínuos (como médias e somas móveis) para cada linha em um conjunto de dados, levando em consideração um grupo de linhas antes, depois ou ao redor da linha atual. Esse tipo de análise contrasta com agregações regulares, que resumem todo o conjunto de dados.
Ao usar quadros de janela baseados em intervalo com offsets explícitos, você pode aplicar uma abordagem muito flexível ao cálculo dessas agregações contínuas. O quadro de janela RANGE BETWEEN, ordenado por carimbos de data/hora ou números, não é interrompido por lacunas que podem ocorrer em dados de séries temporais. Por exemplo, na ilustração a seguir, o fato de faltarem dados do Day 4 na série de registros não afeta o cálculo de funções agregadas em uma janela móvel de três dias. Em particular, os quadros 3, 4 e 5 são calculados corretamente, levando em conta que os dados do Day 4 são desconhecidos.
O exemplo a seguir calcula uma soma móvel sobre dados meteorológicos que registram leituras de precipitação por hora em diferentes cidades e municípios. Você pode executar esse tipo de consulta para avaliar tendências em vários conjuntos de dados de séries temporais, como sensores e outros dispositivos IoT, especialmente quando esses conjuntos de dados são previstos ou conhecidos por ter lacunas.
A função de janela inclui em seu quadro a leitura atual de precipitação e todas as leituras que ocorrem dentro do intervalo de tempo especificado antes da leitura atual. O cálculo contínuo é baseado nesse intervalo flexível e lógico de linhas, em vez de um número exato de linhas. A primeira linha para cada cidade tem valores precip e moving_sum_precip correspondentes. Depois disso, a soma é recalculada para cada linha subsequente no quadro. Os valores brutos flutuam significativamente, mas as somas móveis têm um forte efeito de suavização.
Para executar esse exemplo, siga estas instruções primeiro: Criação e carregamento da tabela heavy_weather. Essa tabela muito pequena contém observações meteorológicas esporádicas de hora em hora, com muitas lacunas, incluindo um dia ausente. A consulta retorna a soma móvel dos valores de precipitação ordenados pela coluna start_time. O quadro de janela define um intervalo entre 12 horas antes da linha atual e a linha atual. Portanto, o quadro consiste na linha atual mais apenas aquelas linhas que têm registros de data e hora até 12 horas antes do carimbo de data/hora ORDER BY da linha atual.
SELECT city, start_time, precip,
SUM(precip) OVER(
PARTITION BY city
ORDER BY start_time
RANGE BETWEEN INTERVAL '12 hours' PRECEDING AND CURRENT ROW) moving_sum_precip
FROM heavy_weather
WHERE city IN('South Lake Tahoe','Big Bear City')
GROUP BY city, precip, start_time
ORDER BY city;
+------------------+-------------------------+--------+-------------------+
| CITY | START_TIME | PRECIP | MOVING_SUM_PRECIP |
|------------------+-------------------------+--------+-------------------|
| Big Bear City | 2021-12-24 05:35:00.000 | 0.42 | 0.42 |
| Big Bear City | 2021-12-24 16:55:00.000 | 0.09 | 0.51 |
| Big Bear City | 2021-12-26 09:55:00.000 | 0.07 | 0.07 |
| South Lake Tahoe | 2021-12-23 16:23:00.000 | 0.56 | 0.56 |
| South Lake Tahoe | 2021-12-23 17:24:00.000 | 0.38 | 0.94 |
| South Lake Tahoe | 2021-12-23 18:30:00.000 | 0.28 | 1.22 |
| South Lake Tahoe | 2021-12-23 19:36:00.000 | 0.80 | 2.02 |
| South Lake Tahoe | 2021-12-24 06:49:00.000 | 0.17 | 0.97 |
| South Lake Tahoe | 2021-12-24 15:53:00.000 | 0.07 | 0.24 |
| South Lake Tahoe | 2021-12-26 05:43:00.000 | 0.16 | 0.16 |
| South Lake Tahoe | 2021-12-27 14:53:00.000 | 0.07 | 0.07 |
| South Lake Tahoe | 2021-12-27 17:53:00.000 | 0.07 | 0.14 |
+------------------+-------------------------+--------+-------------------+
Os três valores moving_sum_precip para Big Bear City são calculados da seguinte forma:
0,42 = 0,42 (sem linhas anteriores)
0,42 + 0,09 = 0,51 (as duas primeiras linhas estão dentro da janela de 12 horas)
0,07 = 0,07 (nenhuma linha anterior está dentro da janela de 12 horas)
As linhas de South Lake Tahoe incluem esses cálculos, por exemplo:
0,56 + 0,38 + 0,28 + 0,80 = 2,02 (todas as quatro linhas para 2024-12-23 estão dentro de 12 horas uma da outra)
0,80 + 0,17 = 0,97 (uma linha anterior está dentro da janela de 12 horas)
Outras funções de janela, como as funções de classificação LEAD e LAG também são comumente usadas em análises de séries temporais. Use função de janela LEAD para encontrar o próximo ponto de dados na série temporal, em relação ao ponto de dados atual, e a função LAG para encontrar o ponto de dados anterior.
Visualizar resultados de consulta no Snowsight¶
Você pode usar Snowsight para visualizar os resultados de consultas de agregação e ter uma noção melhor do efeito de suavização dos cálculos com quadros de janela deslizantes. Na planilha de consulta, clique no botão Chart ao lado de Results.
Por exemplo, a linha amarela no gráfico de barras a seguir mostra uma tendência muito mais suave para a temperatura média em comparação com a linha azul para a temperatura bruta. A consulta em si é semelhante a:
SELECT device_id, timestamp, temperature, AVG(temperature)
OVER (PARTITION BY device_id ORDER BY timestamp
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_temp
FROM sensor_data_ts
WHERE timestamp BETWEEN '2024-03-15 00:00:59.000' AND '2024-03-15 00:01:10.000'
ORDER BY 1, 2;
Usando funções de agregação MIN_BY e MAX_BY¶
A capacidade de selecionar uma coluna com base no valor mínimo ou máximo de outra coluna na mesma linha é um requisito comum para desenvolvedores SQL que trabalham com dados de séries temporais. MIN_BY e MAX_BY são funções de conveniência que retornam os valores inicial e final (ou maior e menor, ou primeiro e último) em uma tabela, quando os dados são classificados por alguma outra coluna, como um carimbo de data/hora.
O primeiro exemplo simplesmente encontra o último (mais recente) valor precip em toda a tabela. A função MAX_BY classifica todas as linhas por seu valor start_time e retorna o valor precip para o tempo de início “máximo”.
Para criar e carregar a tabela usada nos exemplos a seguir, consulte Criação da tabela heavy_weather.
SELECT MAX_BY(precip, start_time) most_recent_precip
FROM heavy_weather;
+--------------------+
| MOST_RECENT_PRECIP |
|--------------------|
| 0.07 |
+--------------------+
Você pode verificar esse resultado (e obter mais informações sobre ele) executando esta consulta:
SELECT * FROM heavy_weather WHERE start_time=
(SELECT MAX(start_time) FROM heavy_weather);
+-------------------------+--------+-------+-------------+
| START_TIME | PRECIP | CITY | COUNTY |
|-------------------------+--------+-------+-------------|
| 2021-12-30 20:53:00.000 | 0.07 | Lebec | Los Angeles |
+-------------------------+--------+-------+-------------+
Você pode adicionar uma cláusula GROUP BY para fazer perguntas mais interessantes sobre esses dados. Por exemplo, a consulta a seguir encontra o último valor de precipitação observado para cada cidade na Califórnia, ordenado pelos valores precip (do maior para o menor). Os resultados são agrupados por city para retornar o último valor precip para cada cidade diferente.
SELECT city, MAX_BY(precip, start_time) most_recent_precip
FROM heavy_weather
GROUP BY city
ORDER BY 2 DESC;
+------------------+--------------------+
| CITY | MOST_RECENT_PRECIP |
|------------------+--------------------|
| Alta | 0.89 |
| Bishop | 0.75 |
| Mammoth Lakes | 0.37 |
| Alturas | 0.23 |
| Mount Shasta | 0.09 |
| South Lake Tahoe | 0.07 |
| Big Bear City | 0.07 |
| Montague | 0.07 |
| Lebec | 0.07 |
+------------------+--------------------+
A última vez que uma observação foi feita para a cidade de Alta, o valor precip era 0.89, e a última vez que uma observação foi feita para as cidades de South Lake Tahoe, Big Bear City, Montague e Lebec, o valor precip era 0.07 para os quatro locais. (Observe que a consulta não informa quando essas observações foram feitas.)
Você pode retornar o conjunto de resultados “oposto” (o registro precip mais antigo versus o mais recente) usando a função MIN_BY.
SELECT city, MIN_BY(precip, start_time) oldest_precip
FROM heavy_weather
GROUP BY city
ORDER BY 2 DESC;
+------------------+---------------+
| CITY | OLDEST_PRECIP |
|------------------+---------------|
| South Lake Tahoe | 0.56 |
| Big Bear City | 0.42 |
| Mammoth Lakes | 0.37 |
| Alta | 0.25 |
| Alturas | 0.23 |
| Bishop | 0.08 |
| Lebec | 0.08 |
| Mount Shasta | 0.08 |
| Montague | 0.07 |
+------------------+---------------+
Junção de dados de série temporal¶
Você pode usar o construtor ASOF JOIN para unir tabelas que contêm dados de série temporal. Embora consultas ASOF JOIN possam ser emuladas através do uso de SQL complexo, outros tipos de junções e funções de janela, essas consultas são mais fáceis de escrever (e otimizadas) se você usar a sintaxe ASOF JOIN.
Um uso comum para junções ASOF é a análise de dados de negociação financeira. A análise dos custos de transação, por exemplo, requer cálculos de “slippage”, que medem a diferença entre o preço cotado no momento da decisão de comprar ações e o preço efetivamente pago quando a negociação foi executada e registrada. O ASOF JOIN pode agilizar esse tipo de análise. Dado que a principal capacidade deste método de junção é a análise de uma série temporal em relação a outra, ASOF JOIN pode ser útil para analisar qualquer conjunto de dados de natureza histórica. Em muitos desses casos de uso, ASOF JOIN pode ser usado para associar dados quando leituras de diferentes dispositivos possuem carimbos de data/hora que não são exatamente iguais.
A suposição é que os dados de série temporal que você precisa analisar existem em duas tabelas e há um carimbo de data/hora para cada linha em cada tabela. Este carimbo de data/hora representa a data e hora “a partir de” de um evento registrado. Para cada linha na primeira tabela (ou à esquerda), a junção usa uma “condição de correspondência” com um operador de comparação especificado para localizar uma única linha na segunda tabela (ou à direita) em que o valor do carimbo de data/hora é um dos seguintes:
Menor ou igual ao valor do carimbo de data/hora na tabela à esquerda.
Maior ou igual ao valor do carimbo de data/hora na tabela à esquerda.
Menor que o valor do carimbo de data/hora na tabela à esquerda.
Maior que o valor do carimbo de data/hora na tabela à esquerda.
A linha de qualificação no lado direito é a correspondência mais próxima, que pode ser igual no tempo, mais cedo ou mais tarde, dependendo do operador de comparação especificado.
A cardinalidade do resultado de ASOF JOIN é sempre igual à cardinalidade da tabela esquerda. Se a tabela esquerda contiver 40 milhões de linhas, ASOF JOIN retornará 40 milhões de linhas. Portanto, a tabela à esquerda pode ser considerada a tabela “de preservação” e a tabela à direita como a tabela “referenciada”.
União de duas tabelas na correspondência mais próxima (alinhamento)¶
Por exemplo, em um aplicativo financeiro, você pode ter uma tabela chamada quotes e uma tabela chamada trades. Uma tabela registra o histórico de ofertas de compra de ações e a outra registra o histórico de negociações reais. Uma oferta de compra de ações acontece antes da negociação (ou possivelmente no “mesmo” momento, dependendo da granularidade do tempo registrado). Ambas as tabelas possuem carimbos de data/hora e outras colunas de interesse que você pode querer comparar. Uma simples consulta ASOF JOIN retornará a cotação mais próxima (no tempo) antes de cada negociação. Em outras palavras, a consulta pergunta: qual era o preço de uma determinada ação no momento em que fiz a negociação?
Suponha que a tabela trades contenha três linhas e a tabela quotes contenha sete linhas. A cor de fundo das células mostra quais três linhas de quotes se qualificarão para ASOF JOIN quando as linhas forem unidas em símbolos de ações correspondentes e suas colunas de carimbo de data/hora forem comparadas.
Tabela TRADES (tabela esquerda ou “de preservação”)
Tabela QUOTES (tabela direita ou “referenciada”)
Este exemplo conceitual é fácil de transformar em uma consulta ASOF JOIN específica:
SELECT t.stock_symbol, t.trade_time, t.quantity, q.quote_time, q.price
FROM trades t ASOF JOIN quotes q
MATCH_CONDITION(t.trade_time >= quote_time)
ON t.stock_symbol=q.stock_symbol
ORDER BY t.stock_symbol;
+--------------+-------------------------+----------+-------------------------+--------------+
| STOCK_SYMBOL | TRADE_TIME | QUANTITY | QUOTE_TIME | PRICE |
|--------------+-------------------------+----------+-------------------------+--------------|
| AAPL | 2023-10-01 09:00:05.000 | 2000 | 2023-10-01 09:00:03.000 | 139.00000000 |
| SNOW | 2023-10-01 09:00:05.000 | 1000 | 2023-10-01 09:00:02.000 | 163.00000000 |
| SNOW | 2023-10-01 09:00:10.000 | 1500 | 2023-10-01 09:00:08.000 | 165.00000000 |
+--------------+-------------------------+----------+-------------------------+--------------+
A condição ON agrupa as linhas correspondentes por seus símbolos de ações.
Para executar este exemplo, crie e carregue as tabelas da seguinte forma:
CREATE OR REPLACE TABLE trades (
stock_symbol VARCHAR(4),
trade_time TIMESTAMP_NTZ(9),
quantity NUMBER(38,0)
);
CREATE OR REPLACE TABLE quotes (
stock_symbol VARCHAR(4),
quote_time TIMESTAMP_NTZ(9),
price NUMBER(12,8)
);
INSERT INTO trades VALUES
('SNOW','2023-10-01 09:00:05.000', 1000),
('AAPL','2023-10-01 09:00:05.000', 2000),
('SNOW','2023-10-01 09:00:10.000', 1500);
INSERT INTO quotes VALUES
('SNOW','2023-10-01 09:00:01.000', 166.00),
('SNOW','2023-10-01 09:00:02.000', 163.00),
('SNOW','2023-10-01 09:00:07.000', 166.00),
('SNOW','2023-10-01 09:00:08.000', 165.00),
('AAPL','2023-10-01 09:00:03.000', 139.00),
('AAPL','2023-10-01 09:00:07.000', 142.00),
('AAPL','2023-10-01 09:00:11.000', 142.00);
Para mais exemplos de consultas ASOF JOIN, consulte Exemplos.
Preenchimento de lacunas em dados com ASOF JOIN¶
Além de alinhar os dados em duas tabelas por meio de correspondências não exatas em colunas baseadas em tempo, ASOF JOIN é útil para preencher lacunas em uma série temporal quando sua tabela de dados brutos não tem linhas para datas ou carimbos de data/hora específicos. Esse processo é conhecido como “preenchimento de lacunas” ou “interpolação”. Quando as linhas estão ausentes porque o equipamento tem um defeito ou uma falha de energia resulta em leituras de sensor ignoradas, você pode usar ASOF JOIN para interpolar valores de uma série temporal gerada na tabela. As linhas ausentes são preenchidas com o último valor conhecido para as leituras ausentes. Este valor também é conhecido como “última observação transferida” (LOCF). A consulta ASOF JOIN retorna um conjunto completo de linhas em ordem cronológica e contíguas.
Para usar ASOF JOIN para interpolação, siga estes passos:
Identifique as lacunas na sua tabela executando uma consulta simples.
Gere uma série temporal completa, com a granularidade apropriada, para o período que você precisa cobrir. Por exemplo, sua série temporal pode ser uma sequência simples de datas para um ano específico ou uma sequência muito mais granular de carimbos de data/hora por segundo para um determinado número de dias. Você pode usar SQL ou um aplicativo de planilha para gerar a lista de valores.
A série temporal também precisará de uma dimensão ou ID significativo para cada linha que você irá especificar posteriormente na condição ASOF JOIN ON.
Escreva uma consulta ASOF JOIN que interpole valores nas linhas ausentes. A série temporal gerada será a tabela de preservação e a tabela de dados brutos será a tabela referenciada.
O exemplo a seguir requer a tabela sensor_data_ts. Se você ainda não a criou e carregou, consulte Criação da tabela sensor_data_ts. Para simular a necessidade de uma operação de preenchimento de lacunas, exclua algumas linhas da tabela da seguinte forma:
DELETE FROM sensor_data_ts
WHERE device_id='DEVICE2'
AND TIMESTAMP > ('2024-03-07 00:01:15')
AND TIMESTAMP <= ('2024-03-07 00:01:20');
O resultado é uma tabela em que faltam cinco linhas para DEVICE2 em 7 de março (1h16 à 1h20).
+------------------------+
| number of rows deleted |
|------------------------|
| 5 |
+------------------------+
Agora siga estes passos para completar o exercício de preenchimento de lacunas.
Nota
Se você executar este exemplo sozinho, sua saída não corresponderá exatamente porque a tabela sensor_data_ts é carregada com valores gerados aleatoriamente.
Etapa 1: Verifique se a tabela tem lacunas¶
Execute a seguinte consulta para identificar as lacunas:
SELECT * FROM sensor_data_ts
WHERE device_id='DEVICE2'
AND TIMESTAMP >= ('2024-03-07 00:01:15')
AND TIMESTAMP <= ('2024-03-07 00:01:21')
ORDER BY TIMESTAMP;
+-----------+-------------------------+-------------+-----------+-----------+
| DEVICE_ID | TIMESTAMP | TEMPERATURE | VIBRATION | MOTOR_RPM |
|-----------+-------------------------+-------------+-----------+-----------|
| DEVICE2 | 2024-03-07 00:01:15.000 | 30.1088 | 0.2960 | 1457 |
| DEVICE2 | 2024-03-07 00:01:21.000 | 28.0426 | 0.2944 | 1448 |
+-----------+-------------------------+-------------+-----------+-----------+
Esta consulta retorna duas linhas para DEVICE2: a última linha antes do espaço e a primeira linha depois do espaço.
Etapa 2: Gere uma série temporal completa para cobrir as lacunas conhecidas¶
Para gerar uma série temporal com granulação fina (uma linha por segundo) para a lacuna na tabela sensor_data_ts, crie a seguinte tabela com os carimbos de data/hora gerados:
CREATE OR REPLACE TABLE continuous_timestamps AS
SELECT 'DEVICE2' as DEVICE_ID,
DATEADD('SECOND', ROW_NUMBER() OVER (ORDER BY SEQ8()), '2024-03-07 00:01:15')::TIMESTAMP_NTZ AS TS
FROM TABLE(GENERATOR(ROWCOUNT => 5));
Nesta instrução SQL, 5 é o número de segundos que você precisa para cobrir a lacuna. Observe que o valor de ID do dispositivo (DEVICE2) está incluso nas linhas geradas.
A consulta a seguir retorna as cinco linhas geradas.
SELECT * FROM continuous_timestamps ORDER BY ts;
+-----------+-------------------------+
| DEVICE_ID | TS |
|-----------+-------------------------|
| DEVICE2 | 2024-03-07 00:01:16.000 |
| DEVICE2 | 2024-03-07 00:01:17.000 |
| DEVICE2 | 2024-03-07 00:01:18.000 |
| DEVICE2 | 2024-03-07 00:01:19.000 |
| DEVICE2 | 2024-03-07 00:01:20.000 |
+-----------+-------------------------+
Etapa 3: interpole valores usando ASOF JOIN¶
Agora você pode executar uma consulta ASOF JOIN que une continuous_timestamps para sensor_data_ts e interpola valores para linhas ausentes para DEVICE2. A condição de correspondência encontra a linha mais próxima no tempo para cada linha ausente e a condição ON garante que a interpolação ocorra nos IDs de dispositivo correspondente.
A linha mais próxima das linhas ausentes é a linha com o carimbo de data/hora 2024-03-07 00:01:16.000, assumindo que >= é especificado na condição de correspondência, conforme mostrado neste exemplo.
INSERT INTO sensor_data_ts(device_id, timestamp, temperature, vibration, motor_rpm)
SELECT t.device_id, t.ts, s.temperature, s.vibration, s.motor_rpm
FROM continuous_timestamps t
ASOF JOIN sensor_data_ts s
MATCH_CONDITION(t.ts >= s.timestamp)
ON t.device_id = s.device_id
WHERE TIMESTAMP >= ('2024-03-07 00:01:15')
AND TIMESTAMP < ('2024-03-07 00:01:21');
Essa instrução INSERT seleciona cinco linhas da operação ASOF JOIN e os insere na tabela sensor_data_ts.
+-------------------------+
| number of rows inserted |
|-------------------------|
| 5 |
+-------------------------+
Para verificar os resultados da interpolação, selecione essas cinco linhas e as duas linhas que as precedem e seguem diretamente, a partir da tabela sensor_data_ts. Observe que as cinco linhas interpoladas pegaram os mesmos valores para as colunas temperature, vibration e motor_rpm registradas na linha 2024-03-07 00:01:15.000. A interpolação foi bem-sucedida.
SELECT * FROM sensor_data_ts
WHERE device_id='DEVICE2'
AND TIMESTAMP >= ('2024-03-07 00:01:15')
AND TIMESTAMP <= ('2024-03-07 00:01:21')
ORDER BY TIMESTAMP;
+-----------+-------------------------+-------------+-----------+-----------+
| DEVICE_ID | TIMESTAMP | TEMPERATURE | VIBRATION | MOTOR_RPM |
|-----------+-------------------------+-------------+-----------+-----------|
| DEVICE2 | 2024-03-07 00:01:15.000 | 30.1088 | 0.2960 | 1457 |
| DEVICE2 | 2024-03-07 00:01:16.000 | 30.1088 | 0.2960 | 1457 |
| DEVICE2 | 2024-03-07 00:01:17.000 | 30.1088 | 0.2960 | 1457 |
| DEVICE2 | 2024-03-07 00:01:18.000 | 30.1088 | 0.2960 | 1457 |
| DEVICE2 | 2024-03-07 00:01:19.000 | 30.1088 | 0.2960 | 1457 |
| DEVICE2 | 2024-03-07 00:01:20.000 | 30.1088 | 0.2960 | 1457 |
| DEVICE2 | 2024-03-07 00:01:21.000 | 28.0426 | 0.2944 | 1448 |
+-----------+-------------------------+-------------+-----------+-----------+
Aplicação de funções baseadas em ML em dados de séries temporais¶
Você pode treinar um modelo com funções de ML para fazer análise preditiva em dados de séries temporais:
A previsão usa dados de séries temporais históricas para fazer previsões sobre dados futuros. Dada uma série temporal registrada com valores reais observados para datas e horas no passado, o modelo de ML prevê quais podem ser os valores observados para datas e horas no futuro.
A detecção de anomalias identifica valores discrepantes, que são pontos de dados que se desviam de um intervalo esperado. No contexto de uma série temporal, um outlier é uma medida que é muito maior ou menor do que outras medidas em um intervalo de tempo semelhante. Para encontrar outliers, a função de ML produz uma previsão para o mesmo período que está sendo verificado quanto a anomalias e, em seguida, compara os resultados da previsão com os dados reais.
O Top Insights encontra as dimensões mais importantes em um conjunto de dados, cria segmentos a partir dessas dimensões e detecta quais desses segmentos influenciaram uma métrica.
Nota
Para fins de aprendizado de máquina, os carimbos de data/hora em sua série temporal devem representar intervalos de tempo fixos. Se necessário, você pode usar a função DATE_TRUNC ou TIME_SLICE em colunas TIMESTAMP para remover irregularidades ao treinar o modelo de previsão.
Um exemplo de detecção de anomalias em uma série temporal¶
O exemplo a seguir usa uma exibição com apenas 30 linhas para treinar um modelo de detecção de anomalias. Comece gerando dados em uma tabela e depois crie uma exibição na tabela. A exibição não é obrigatória (você pode usar uma tabela para treinar um modelo), mas a opção de exibição oferece alguma flexibilidade para treinar modelos iterativamente, com diferentes contagens de linha, sem atualizar os dados de origem.
Nota
Se você executar este exemplo sozinho, sua saída não corresponderá exatamente porque a tabela sensor_data_30_rows é carregada com valores gerados aleatoriamente.
CREATE OR REPLACE TABLE sensor_data_30_rows (
device_id VARCHAR(10),
timestamp TIMESTAMP,
temperature DECIMAL(6,4),
vibration DECIMAL(6,4),
motor_rpm INT);
INSERT INTO sensor_data_30_rows (device_id, timestamp, temperature, vibration, motor_rpm)
SELECT 'DEVICE3', timestamp,
UNIFORM(30.2345, 36.3456, RANDOM()), --
UNIFORM(0.4000, 0.4718, RANDOM()), --
UNIFORM(1510, 1625, RANDOM()) --
FROM (
SELECT DATEADD(SECOND, SEQ4(), '2024-03-01') AS timestamp
FROM TABLE(GENERATOR(ROWCOUNT => 30))
);
CREATE OR REPLACE VIEW sensor_data_view AS SELECT * FROM sensor_data_30_rows;
Agora crie o modelo:
CREATE OR REPLACE SNOWFLAKE.ML.ANOMALY_DETECTION sensor_model(
INPUT_DATA => SYSTEM$REFERENCE('VIEW', 'sensor_data_view'),
TIMESTAMP_COLNAME => 'timestamp',
TARGET_COLNAME => 'temperature',
LABEL_COLNAME => '');
+---------------------------------------------+
| status |
|---------------------------------------------|
| Instance SENSOR_MODEL successfully created. |
+---------------------------------------------+
Quando o modelo for desenvolvido com sucesso, chame o método <nome_do_modelo>!DETECT_ANOMALIES para detectar outliers no conjunto de dados de teste especificado. Os carimbos de data/hora nos dados de teste devem seguir cronologicamente os carimbos de data/hora nos dados de treinamento, mas não deve haver um intervalo de tempo muito grande entre os dados de treinamento e os dados de teste. Por exemplo, se você tiver carimbos de data/hora para cada segundo, não use dados de teste que estejam milhões de segundos à frente dos dados de treinamento.
Este exemplo usa outra tabela como dados de teste, com apenas três linhas. Essas linhas têm carimbos de data/hora que seguem de perto aqueles nos dados de treinamento.
CREATE OR REPLACE TABLE sensor_data_device3 (
device_id VARCHAR(10),
timestamp TIMESTAMP,
temperature DECIMAL(6,4),
vibration DECIMAL(6,4),
motor_rpm INT);
INSERT INTO sensor_data_device3 VALUES
('DEVICE3','2024-03-01 00:00:30.000',36.0422,0.4226,1560),
('DEVICE3','2024-03-01 00:00:31.000',36.1519,0.4341,1515),
('DEVICE3','2024-03-01 00:00:32.000',36.1524,0.4321,1591);
CALL sensor_model!DETECT_ANOMALIES(
INPUT_DATA => SYSTEM$REFERENCE('TABLE', 'sensor_data_device3'),
TIMESTAMP_COLNAME => 'timestamp',
TARGET_COLNAME => 'temperature'
);
Quando a chamada de detecção de anomalias termina, ela retorna uma saída semelhante à seguinte:
+-------------------------+---------+--------------+--------------+--------------+------------+--------------+-------------+
| TS | Y | FORECAST | LOWER_BOUND | UPPER_BOUND | IS_ANOMALY | PERCENTILE | DISTANCE |
|-------------------------+---------+--------------+--------------+--------------+------------+--------------+-------------|
| 2024-03-01 00:00:30.000 | 36.0422 | 30.809998241 | 25.583156942 | 36.036839539 | True | 0.9950380683 | 2.578470982 |
| 2024-03-01 00:00:31.000 | 36.1519 | 32.559470456 | 27.332629158 | 37.786311755 | False | 0.961667911 | 1.770378085 |
| 2024-03-01 00:00:32.000 | 36.1524 | 32.205610776 | 26.978769478 | 37.432452075 | False | 0.9741130751 | 1.945009377 |
+-------------------------+---------+--------------+--------------+--------------+------------+--------------+-------------+
As colunas TS e Y retornam os carimbos de data/hora e os valores de temperatura dos dados de teste. Neste caso de teste muito pequeno, a função encontrou uma anomalia (IS_ANOMALY=True). Para obter mais informações sobre as colunas de saída, consulte a seção “Retornos” na descrição da função.
Criação da tabela sensor_data_ts¶
Se quiser testar os exemplos nesta seção que consultam a tabela sensor_data_ts, você pode criar e carregar uma cópia desta tabela executando o seguinte script em SQL. O script gera um mês de dados sintéticos para leituras de sensor chamando as funções UNIFORM, RANDOM e GENERATOR; portanto, sua cópia da tabela não retornará resultados idênticos. As leituras estarão no mesmo intervalo, mas não serão iguais.
CREATE OR REPLACE TABLE sensor_data_device1 (
device_id VARCHAR(10),
timestamp TIMESTAMP,
temperature DECIMAL(6,4),
vibration DECIMAL(6,4),
motor_rpm INT
);
INSERT INTO sensor_data_device1 (device_id, timestamp, temperature, vibration, motor_rpm)
SELECT 'DEVICE1', timestamp,
UNIFORM(25.1111, 40.2222, RANDOM()), -- Temperature range in °C
UNIFORM(0.2985, 0.3412, RANDOM()), -- Vibration range in mm/s
UNIFORM(1400, 1495, RANDOM()) -- Motor RPM range
FROM (
SELECT DATEADD(SECOND, SEQ4(), '2024-03-01') AS timestamp
FROM TABLE(GENERATOR(ROWCOUNT => 2678400)) -- seconds in 31 days
);
CREATE OR REPLACE TABLE sensor_data_device2 (
device_id VARCHAR(10),
timestamp TIMESTAMP,
temperature DECIMAL(6,4),
vibration DECIMAL(6,4),
motor_rpm INT
);
INSERT INTO sensor_data_device2 (device_id, timestamp, temperature, vibration, motor_rpm)
SELECT 'DEVICE2', timestamp,
UNIFORM(24.6642, 36.3107, RANDOM()), -- Temperature range in °C
UNIFORM(0.2876, 0.3333, RANDOM()), -- Vibration range in mm/s
UNIFORM(1425, 1505, RANDOM()) -- Motor RPM range
FROM (
SELECT DATEADD(SECOND, SEQ4(), '2024-03-01') AS timestamp
FROM TABLE(GENERATOR(ROWCOUNT => 2678400)) -- seconds in 31 days
);
INSERT INTO sensor_data_device1 SELECT * FROM sensor_data_device2;
DROP TABLE IF EXISTS sensor_data_ts;
ALTER TABLE sensor_data_device1 rename to sensor_data_ts;
DROP TABLE sensor_data_device2;
SELECT COUNT(*) FROM sensor_data_ts; -- verify row count = 5356800
Criação da tabela heavy_weather¶
O script a seguir cria e carrega a tabela heavy_weather, que é usada nos exemplos para as funções MAX_BY. A tabela contém 55 linhas de registros de precipitação de neve em cidades da Califórnia durante a última semana de 2021.
CREATE OR REPLACE TABLE heavy_weather
(start_time TIMESTAMP, precip NUMBER(3,2), city VARCHAR(20), county VARCHAR(20));
INSERT INTO heavy_weather VALUES
('2021-12-23 06:56:00.000',0.08,'Mount Shasta','Siskiyou'),
('2021-12-23 07:51:00.000',0.09,'Mount Shasta','Siskiyou'),
('2021-12-23 16:23:00.000',0.56,'South Lake Tahoe','El Dorado'),
('2021-12-23 17:24:00.000',0.38,'South Lake Tahoe','El Dorado'),
('2021-12-23 18:30:00.000',0.28,'South Lake Tahoe','El Dorado'),
('2021-12-23 19:35:00.000',0.37,'Mammoth Lakes','Mono'),
('2021-12-23 19:36:00.000',0.80,'South Lake Tahoe','El Dorado'),
('2021-12-24 04:43:00.000',0.25,'Alta','Placer'),
('2021-12-24 05:26:00.000',0.34,'Alta','Placer'),
('2021-12-24 05:35:00.000',0.42,'Big Bear City','San Bernardino'),
('2021-12-24 06:49:00.000',0.17,'South Lake Tahoe','El Dorado'),
('2021-12-24 07:40:00.000',0.07,'Alta','Placer'),
('2021-12-24 08:36:00.000',0.07,'Alta','Placer'),
('2021-12-24 11:52:00.000',0.08,'Alta','Placer'),
('2021-12-24 12:52:00.000',0.38,'Alta','Placer'),
('2021-12-24 15:44:00.000',0.13,'Alta','Placer'),
('2021-12-24 15:53:00.000',0.07,'South Lake Tahoe','El Dorado'),
('2021-12-24 16:55:00.000',0.09,'Big Bear City','San Bernardino'),
('2021-12-24 21:53:00.000',0.07,'Montague','Siskiyou'),
('2021-12-25 02:52:00.000',0.07,'Alta','Placer'),
('2021-12-25 07:52:00.000',0.07,'Alta','Placer'),
('2021-12-25 08:52:00.000',0.08,'Alta','Placer'),
('2021-12-25 09:48:00.000',0.18,'Alta','Placer'),
('2021-12-25 12:52:00.000',0.10,'Alta','Placer'),
('2021-12-25 17:21:00.000',0.23,'Alturas','Modoc'),
('2021-12-25 17:52:00.000',1.54,'Alta','Placer'),
('2021-12-26 01:52:00.000',0.61,'Alta','Placer'),
('2021-12-26 05:43:00.000',0.16,'South Lake Tahoe','El Dorado'),
('2021-12-26 05:56:00.000',0.08,'Bishop','Inyo'),
('2021-12-26 06:52:00.000',0.75,'Bishop','Inyo'),
('2021-12-26 06:53:00.000',0.08,'Lebec','Los Angeles'),
('2021-12-26 07:52:00.000',0.65,'Alta','Placer'),
('2021-12-26 09:52:00.000',2.78,'Alta','Placer'),
('2021-12-26 09:55:00.000',0.07,'Big Bear City','San Bernardino'),
('2021-12-26 14:22:00.000',0.32,'Alta','Placer'),
('2021-12-26 14:52:00.000',0.34,'Alta','Placer'),
('2021-12-26 15:43:00.000',0.35,'Alta','Placer'),
('2021-12-26 17:31:00.000',5.24,'Alta','Placer'),
('2021-12-26 22:52:00.000',0.07,'Alta','Placer'),
('2021-12-26 23:15:00.000',0.52,'Alta','Placer'),
('2021-12-27 02:52:00.000',0.08,'Alta','Placer'),
('2021-12-27 03:52:00.000',0.14,'Alta','Placer'),
('2021-12-27 04:52:00.000',1.52,'Alta','Placer'),
('2021-12-27 14:37:00.000',0.89,'Alta','Placer'),
('2021-12-27 14:53:00.000',0.07,'South Lake Tahoe','El Dorado'),
('2021-12-27 17:53:00.000',0.07,'South Lake Tahoe','El Dorado'),
('2021-12-30 11:23:00.000',0.12,'Lebec','Los Angeles'),
('2021-12-30 11:43:00.000',0.98,'Lebec','Los Angeles'),
('2021-12-30 13:53:00.000',0.23,'Lebec','Los Angeles'),
('2021-12-30 14:53:00.000',0.13,'Lebec','Los Angeles'),
('2021-12-30 15:15:00.000',0.29,'Lebec','Los Angeles'),
('2021-12-30 17:53:00.000',0.10,'Lebec','Los Angeles'),
('2021-12-30 18:53:00.000',0.09,'Lebec','Los Angeles'),
('2021-12-30 19:53:00.000',0.07,'Lebec','Los Angeles'),
('2021-12-30 20:53:00.000',0.07,'Lebec','Los Angeles')
;