Início / dba / Perguntas / 345713
Accepted
anon
Asked: 2025-03-14 12:52:13 +0800 CST

Como criar essa visualização no Postgres?

  • 772

Digamos que temos a tabela abaixo conosco:

 id |     entry_time      |  state  
----+---------------------+---------
 d1 | 2024-09-20 12:01:01 | Open
 d1 | 2024-09-20 12:01:20 | EnRoute
 d1 | 2024-09-20 12:02:40 | OnTrip
 d1 | 2024-09-20 12:04:10 | Open

E queremos uma visualização final que ficará como abaixo (saída esperada):

| id | start_minute        | open_time | enroute_time | ontrip_time |
|----|---------------------|-----------|--------------|-------------|
| d1 | 2024-09-20 12:01:00 | 19        | 40           | 0           |
| d1 | 2024-09-20 12:02:00 | 0         | 40           | 20          |
| d1 | 2024-09-20 12:03:00 | 0         | 0            | 60          |
| d1 | 2024-09-20 12:04:00 | 50        | 0            | 10          |

Para calcular a visualização (para a primeira linha)

  • open_time é 19 porque o driver permaneceu em estado aberto de 12:01:01 a 12:01:20
  • enroute_time é 40 porque o motorista permaneceu no estado enroute de 12:01:20 a 12:02:00 (e permaneceu até 2024-09-20 12:02:40, marcador do próximo minuto)

Eu estava tentando calcular o mesmo, mas não tinha 100% de certeza de como prosseguir:

  • Estou um pouco preso tentando descobrir como dividir o campo de registro de data e hora em rastreador de minutos

    • Eu estava lendo mais sobre funções de agregação e data e hora e date_trunc pode ser usado para conseguir isso para agrupar os registros

  • Estou tentando descobrir como fazer algo como uma tabela dinâmica que pode ser útil para nos ajudar a chegar à resposta final (no meu entendimento limitado)

OU:

  • Precisamos encontrar a proporção para cada intervalo de minuto dado o estado, para que possamos conseguir isso calculando o marcador de tempo inicial e final para cada estado após explodir a visualização para o marcador de minuto da tabela atual. Essa é uma boa ideia ou pode haver uma abordagem melhor?

Minha tentativa:

select 
    id,
    state,
    date_trunc('minute', entry_time + interval '30 second') AS trunc_min,
    entry_time as curr_time,
    date_trunc('minute', entry_time + interval '60 second') AS ceil_min,
    LEAD(entry_time) over (PARTITION by id order by entry_time) as next_time,
    extract(epoch from (lead(entry_time) over (partition by id order by entry_time) - entry_time)) as duration
from (
    values 
    ('d1', '2024-09-20 12:01:01'::timestamp, 'Open'),
    ('d1', '2024-09-20 12:01:20'::timestamp, 'EnRoute'), 
    ('d1', '2024-09-20 12:02:40'::timestamp, 'OnTrip'),
    ('d1', '2024-09-20 12:04:10'::timestamp, 'Open')
) t("id", "entry_time", "state");

Saída:

 id |  state  |      trunc_min      |      curr_time      |      ceil_min       |      next_time      | duration  
----+---------+---------------------+---------------------+---------------------+---------------------+-----------
 d1 | Open    | 2024-09-20 12:01:00 | 2024-09-20 12:01:01 | 2024-09-20 12:02:00 | 2024-09-20 12:01:20 | 19.000000
 d1 | EnRoute | 2024-09-20 12:01:00 | 2024-09-20 12:01:20 | 2024-09-20 12:02:00 | 2024-09-20 12:02:40 | 80.000000
 d1 | OnTrip  | 2024-09-20 12:03:00 | 2024-09-20 12:02:40 | 2024-09-20 12:03:00 | 2024-09-20 12:04:10 | 90.000000
 d1 | Open    | 2024-09-20 12:04:00 | 2024-09-20 12:04:10 | 2024-09-20 12:05:00 |                     |

Como prosseguir a partir daqui?

postgresql

1 respostas

  1. Best Answer
    WITH
source_data (id, entry_time, state) AS (
    values 
    ('d1', '2024-09-20 12:01:01'::timestamp, 'Open'),
    ('d1', '2024-09-20 12:01:20'::timestamp, 'EnRoute'), 
    ('d1', '2024-09-20 12:02:40'::timestamp, 'OnTrip'),
    ('d1', '2024-09-20 12:04:10'::timestamp, 'Open')
  ),
prepared_data (id, entry_time, next_entry_time, state) AS (
  SELECT id, 
         entry_time, 
         COALESCE(LEAD(entry_time) OVER (PARTITION BY id ORDER BY entry_time), entry_time + interval '1 minute'),
         state
  FROM source_data
  ),
timestamps (start_minute, end_minute) AS (
  SELECT generate_series, 
         generate_series + interval '1 minute'
  FROM generate_series((SELECT date_trunc('minute', MIN(entry_time)) FROM source_data),
                       (SELECT date_trunc('minute', MAX(entry_time)) FROM source_data),
                       interval '1 minute') 
  ),
overlappings (id, start_minute, state, length) AS (
  SELECT t1.id, t2.start_minute, t1.state,
         LEAST(t1.next_entry_time, t2.end_minute) - GREATEST(t1.entry_time, t2.start_minute)
  FROM prepared_data t1
  JOIN timestamps t2 ON t1.entry_time < t2.end_minute AND t1.next_entry_time > t2.start_minute
  )
SELECT id,
       start_minute,
       COALESCE(EXTRACT(EPOCH FROM SUM(CASE WHEN state = 'Open' THEN length END))::INT, 0) open_time,
       COALESCE(EXTRACT(EPOCH FROM SUM(CASE WHEN state = 'EnRoute' THEN length END))::INT, 0) enroute_time,
       COALESCE(EXTRACT(EPOCH FROM SUM(CASE WHEN state = 'OnTrip' THEN length END))::INT, 0) ontrip_time
FROM overlappings
GROUP BY id, start_minute
ORDER BY id, start_minute
    eu ia início_minuto hora_aberta tempo_de_rota tempo_de_viagem
    d1 2024-09-20 12:01:00 19 40 0
    d1 2024-09-20 12:02:00 0 40 20
    d1 2024-09-20 12:03:00 0 0 60
    d1 2024-09-20 12:04:00 50 0 10

    Veja o passo a passo fiddle . Investigue a saída após cada CTE.

    • 3

relate perguntas