Início / dba / Perguntas / 271037
Accepted
Deepan Kaviarasu
Asked: 2020-07-16 19:36:21 +0800 CST

Agrupe por intervalo de tempo e produza a origem e destino station_id e contagem

  • 772

Estou preso com uma consulta:

CREATE TABLE public.bulk_sample (
    serial_number character varying(255),
    validation_date timestamp,  -- timestamp of entry and exit
    station_id integer,
    direction integer           -- 1 = Entry | 2 = Exit
);

INSERT INTO public.bulk_sample VALUES
  ('019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270', '2020-02-01 08:31:58', 120, 1)
, ('019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270', '2020-02-01 08:50:22', 113, 2)
, ('019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270', '2020-02-01 10:16:56', 113, 1)
, ('019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270', '2020-02-01 10:47:06', 120, 2)
, ('019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270', '2020-02-01 16:02:12', 120, 1)
, ('019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270', '2020-02-01 16:47:45', 102, 2)
, ('019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270', '2020-02-01 19:26:38', 102, 1)
, ('019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270', '2020-02-01 20:17:24', 120, 2)
, ('23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663', '2020-02-01 07:58:20', 119, 1)
, ('23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663', '2020-02-01 08:43:35', 104, 2)
, ('23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663', '2020-02-01 16:38:10', 104, 1)
, ('23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663', '2020-02-01 17:15:01', 119, 2)
, ('23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663', '2020-02-01 17:42:29', 119, 1)
, ('23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663', '2020-02-01 17:48:05', 120, 2)
, ('2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af', '2020-02-01 15:17:59', 120, 1)
, ('2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af', '2020-02-01 15:25:25', 118, 2)
, ('2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af', '2020-02-01 16:16:12', 118, 1)
, ('2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af', '2020-02-01 16:32:51', 120, 2)
, ('2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af', '2020-02-01 19:31:20', 120, 1)
, ('2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af', '2020-02-01 19:39:33', 118, 2)
, ('2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af', '2020-02-01 20:57:50', 118, 1)
, ('2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af', '2020-02-01 21:16:25', 120, 2)
;

Eu tenho que criar uma consulta que dá um resultado da seguinte forma

source | dest | Count
120    | 113  |  1
113    | 120  |  1

Eu tentei o seguinte código, mas não consegui obter o resultado desejado:

SELECT serial_number
     , count(*)
     , min(validation_date) AS start_time
     , CASE WHEN count(*) > 1 THEN max(validation_date) END AS end_time
FROM  (
   SELECT serial_number, validation_date, count(step OR NULL) OVER (ORDER BY serial_number, 
validation_date) AS grp
   FROM  (
      SELECT *
           , lag(validation_date) OVER (PARTITION BY serial_number ORDER BY validation_date)
           < validation_date - interval '60 min' AS step
      FROM   table1 
       where BETWEEN '2020-02-01 00:00:00' AND '2020-02-01 23:59:59'
      ) sub1
   ) sub2
GROUP  BY serial_number, grp;

O intervalo de tempo é de cerca de 55 minutos a 60 minutos entre cada entrada e saída.

Eu também tentei uma junção interna, mas não consigo agrupar pelo intervalo de tempo em uma junção interna

SELECT source.station_id AS source_station ,dest.station_id AS destination_station ,source.count FROM 
    (
        SELECT serial_number,station_id,count(bulk_transaction_id) FROM table1
        WHERE 
            direction = 1 AND 
            validation_date BETWEEN '2020-02-01 00:00:00' AND '2020-02-01 23:59:59' 
        GROUP BY serial_number,station_id
    )source

 INNER JOIN 
    (
        SELECT serial_number,station_id,count(bulk_transaction_id) FROM table1
        WHERE 
            direction = 2 AND 
            validation_date BETWEEN '2020-02-01 00:00:00' AND '2020-02-01 23:59:59'
        GROUP BY serial_number,station_id
    )dest
ON source.serial_number = dest.serial_number and source.station_id <> dest.station_id

O desafio é que às vezes há nulo na data de entrada e às vezes é nulo na data de saída.

postgresql postgresql-10

2 respostas

  1. Para isso você vai precisar de duas coisas:

    1. Uma subconsulta correlacionada em sua condição de associação
    2. Um índice exclusivo em (serial_number,validation_date)

    Depois disso, sua consulta se torna:

    SELECT
  station_entry.station_id AS source
 ,station_exit.station_id AS dest
 ,COUNT(*) AS count
FROM
  public.bulk_sample station_entry
INNER JOIN
  public.bulk_sample station_exit
    ON station_exit.serial_number = station_entry.serial_number
        AND station_exit.validation_date =
              (
                SELECT
                  MIN(validation_date)
                FROM
                  public.bulk_sample
                WHERE
                  serial_number = station_entry.serial_number
                    AND validation_date > station_entry.validation_date
              )
WHERE
  station_entry.direction = 1
    AND station_exit.direction = 2  --Ensure next transaction is valid
    AND station_entry.validation_date >= '2020-02-01 00:00:00'
    AND station_entry.validation_date <= '2020-02-01 23:59:59'
    AND station_exit.validation_date <= '2020-02-01 23:59:59' --Ensure both events occurred within specified timeframe
GROUP BY
  station_entry.station_id
 ,station_exit.station_id

    Deve retornar:

    source  dest    count
102     120     1
104     119     1
113     120     1
118     120     2
119     104     1
119     120     1
120     102     1
120     113     1
120     118     2
    • 2
  2. Best Answer

    Isso deve ser mais simples e rápido, enquanto as transações serial_numbernunca se sobrepõem :

    WITH cte AS (
   SELECT serial_number, validation_date, station_id, direction
        , row_number() OVER (PARTITION BY serial_number ORDER BY validation_date) AS rn
   FROM   bulk_sample
   WHERE  validation_date >= '2020-02-01'  -- ①
   AND    validation_date <  '2020-02-02'  -- entry & exit must be within time frame
   )
SELECT s.station_id AS source, d.station_id AS dest, count(*)
FROM   cte s
JOIN   cte d USING (serial_number)
WHERE  s.direction = 1
AND    d.rn = s.rn + 1
GROUP  BY 1, 2
ORDER  BY 1, 2;  -- optional sort order

    db<>fique aqui

    ① Reescrevi a WHEREcondição para obter todo o dia 1º de fevereiro de 2020 da maneira ideal. BETWEENé quase sempre a ferramenta errada para intervalos de tempo . Ver:

    timestampAlém disso, '2020-02-01' é uma constante perfeitamente válida que 00:00:00é assumida quando o componente de tempo está ausente.

    Ao recuperar resultados para um determinado período de tempo, um índice btree simples é o (validation_date)ideal. Para a tabela completa, um índice em (serial_number, validation_date)ajudaria mais.

    validation_date IS NULL?

    A consulta continua funcionando enquanto apenas o último destino porserial_number no período de tempo fornecido, validation_date IS NULLporque NULLos valores são classificados por último em ordem crescente padrão. Mas rompe com quaisquer outros casos de validation_date IS NULL. Você terá que definir mais de perto onde eles podem aparecer e como lidar com eles exatamente.

    (2x)uuid em vez de varchar(255)para serial_number?

    Seu serial_numberparece ser um número hexadecimal com exatamente 64 dígitos. Se for assim,varchar(255) é uma má escolha. Ver:

    Além disso, um único uuid(32 dígitos hexadecimais) deve ser suficiente. Se todos os 64 dígitos hexadecimais forem necessários, ainda considere 2 uuidcolunas. Menor, mais rápido, mais seguro. Considerar:

    SELECT *
     , replace(uuid1::text || uuid2::text, '-', '') AS reverse_engineered
     , replace(uuid1::text || uuid2::text, '-', '') = serial_number AS identical
     , pg_column_size(serial_number) AS varchar_size
     , pg_column_size(uuid1) + pg_column_size(uuid2) AS uuid_size
FROM  (
   SELECT serial_number
        , left(serial_number, 32)::uuid  AS uuid1
        , right(serial_number, 32)::uuid AS uuid2
   FROM   bulk_sample
   ) sub;

    db<>violinoaqui

    Ver:

    • 1

relate perguntas