Início / dba / Perguntas / 265527
Accepted
feketegy
Asked: 2020-04-22 09:32:52 +0800 CST

Como criar intervalos de datas de eventos provenientes de uma API onde o estado aberto representa o intervalo de datas e o estado fechado as lacunas entre os intervalos?

  • 772

Eu gostaria de armazenar intervalos de data/hora no PostgreSQL de alguns eventos arbitrários que têm um estado de aberto ou fechado e uma data/hora de quando o estado mudou.

Os eventos provenientes de uma API terão os seguintes dados para um único evento:

Request 1:
{
  id: 1,
  state: 'open',
  date: '2020-02-17T10:00:00Z'
}

Request 2:
{
  id: 1,
  state: 'close',
  date: '2020-02-17T10:10:00Z'
}

Request 3:
{
  id: 1,
  state: 'open',
  date: '2020-02-17T11:00:00Z'
}

As solicitações podem vir em qualquer ordem, portanto, datas futuras podem vir antes de datas passadas ou estados nem sempre estão abertos -> fechar -> abrir -> fechar. Por exemplo, a API pode enviar os estados abertos para o mesmo evento um após o outro.

Eu estava pensando em usar o tstzrangepara salvar esses dados no banco de dados da seguinte forma:

CREATE TABLE events (
  id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  event_id int,
  validity tstzrange
);

Os estados abertos são capturados na coluna de validade e os estados fechados são as lacunas entre as colunas de validade. Por exemplo, se um único evento tiver os seguintes estados e datas/horas (usando apenas as horas para simplificar) nesta ordem:

state          date/time
close          20:30
open           18:00
close          16:00
open           15:00
open           20:00
close          19:30

As linhas de validade devem se parecer com:

id          event_id           validity
1           1                  [15:00, 16:00)
2           1                  [18:00, 19:30)
3           1                  [20:00, 20:30)

Event 1tem o estado openentre 15:00 - 16:00, estado closeentre 16:00 - 18:00, estado openentre 18:00 - 19:30e assim por diante.

Para ilustrar isso visualmente:

validade visualizada

Meu problema é que os eventos não estão vindo em ordem, então não sei como manipular as colunas de validade individuais para inserir/atualizar essas linhas.

postgresql postgresql-12

1 respostas

  1. Best Answer

    Eu descobri como inserir/atualizar esse tipo de dados em intervalos de datas do PostgreSQL de uma perspectiva algorítmica que isso pode ser útil para você também.

    Como esses dados são uma série temporal, em última análise, quando conhecemos o conjunto de dados completo, não pode haver sobreposições entre os estados de abertura e fechamento de um único evento ao longo do tempo.

    Mantendo o algoritmo flexível o suficiente, pois quando não conhecemos toda a gama de eventos e estados de abertura/fechamento, há algumas regras que precisam ser levadas em consideração:

    Se houver uma coluna de validade contendo a data do evento e:

    1. se o estado do evento for openentão:

    1.1. se a validade do recipiente tiver infinito negativo = atualizar o limite inferior da validade do recipiente para a data do evento.

    1.2. se houver outra linha de validade estritamente antes da validade de conteúdo, significando que o limite inferior de validade de conteúdo é igual a outro limite superior de validade = atualize o limite inferior da validade de conteúdo para a data do evento.

    1.3. se os pontos acima forem falsos = divida a validade contida em duas linhas separadas:

    1.3.1. atualize o limite inferior de validade contido para a data do evento. 1.3.2. insira uma nova linha onde o limite inferior é o limite inferior original da validade contida e o limite superior é a data do evento.

    1. se o estado do evento for closeentão (reverso das regras do estado aberto):

    2.1. se a validade do recipiente tiver infinito positivo = atualizar o limite superior da validade do recipiente para a data do evento.

    2.2. se houver outra linha de validade estritamente após a validade de conteúdo, significando que o limite superior de validade de conteúdo é igual a outro limite inferior de validade = atualize o limite superior da validade de conteúdo para a data do evento.

    2.3. se os pontos acima forem falsos = divida a validade contida em duas linhas separadas:

    2.3.1. atualize o limite superior de validade contido para a data do evento.

    2.3.2. insira uma nova linha onde o limite superior é o limite superior original da validade contida e o inferior é a data do evento.

    Se não houver intervalo de validade contendo a data do evento, então:

    1. se o estado do evento foropen , insira uma nova linha de validade com o limite inferior definido para a data do evento e o limite superior definido como infinito positivo.

    2. se o estado do evento for close, insira uma nova linha de validade com o limite superior definido para a data do evento e o limite inferior definido como infinito negativo.

    Essas regras fornecem a melhor imagem possível dos estados do evento onde oopen estado é capturado nas próprias colunas de validade e o closeestado são as lacunas entre as linhas de validade.

    Quando houver mais e mais dados chegando, seja de dados históricos ou atuais, as colunas de validade fornecerão uma imagem mais clara dos estados do evento ao longo do tempo.

    • 0

relate perguntas