Início / dba / Perguntas / 331032
Accepted
lpscott
Asked: 2023-09-08 22:15:03 +0800 CST

Mesclando dados da sessão wifi se o tempo entre eles for inferior a 15 minutos

  • 772

Estou tentando processar logs de rede e ingressar em sessões se o tempo entre eles for inferior a 15 minutos. Os campos relevantes são hora de início, hora de término, endereço MAC e ponto de acesso wi-fi.

Estou trabalhando no Greenplum 6.22/Postgresql 9.4.26:

pdap=# SELECT version();
versão
PostgreSQL 9.4.26 (banco de dados Greenplum 6.22.2)

violino db

Logicamente, o que eu quero fazer é "Se o horário de início da próxima linha for inferior a 15 minutos após o horário de término desta linha, mescle as duas linhas em uma linha com o horário de início anterior e o horário de término posterior".

Aqui está um exemplo de tabela com alguns dados:

CREATE TABLE network_test
( start_ts TIMESTAMPTZ,
  end_ts TIMESTAMPTZ,
  mac_addr MACADDR,
  access_point VARCHAR
);

INSERT INTO network_test
VALUES
('2023-08-14 13:21:10.289'::timestamptz, '2023-08-14 13:31:20.855'::timestamptz, '00:00:00:00:00:01'::macaddr, 'access_point_01'),
('2023-08-14 13:58:10.638'::timestamptz, '2023-08-14 13:58:22.668'::timestamptz, '00:00:00:00:00:01'::macaddr, 'access_point_01'),
('2023-08-14 13:58:22.727'::timestamptz, '2023-08-14 13:58:38.966'::timestamptz, '00:00:00:00:00:01'::macaddr, 'access_point_01'),
('2023-08-14 13:28:28.190'::timestamptz, '2023-08-14 13:28:28.190'::timestamptz, '00:00:00:00:00:02'::macaddr, 'access_point_02'),
('2023-08-14 13:28:44.167'::timestamptz, '2023-08-14 13:28:44.288'::timestamptz, '00:00:00:00:00:02'::macaddr, 'access_point_02'),
('2023-08-14 13:45:40.281'::timestamptz, '2023-08-14 13:46:02.726'::timestamptz, '00:00:00:00:00:02'::macaddr, 'access_point_03'),
('2023-08-14 13:46:02.964'::timestamptz, '2023-08-14 13:46:10.783'::timestamptz, '00:00:00:00:00:02'::macaddr, 'access_point_03'),
('2023-08-14 13:46:11.026'::timestamptz, '2023-08-14 13:46:18.803'::timestamptz, '00:00:00:00:00:02'::macaddr, 'access_point_03'),
('2023-08-14 13:46:19.037'::timestamptz, '2023-08-14 13:46:26.798'::timestamptz, '00:00:00:00:00:02'::macaddr, 'access_point_03'),
('2023-08-14 13:46:27.036'::timestamptz, '2023-08-14 13:46:34.815'::timestamptz, '00:00:00:00:00:02'::macaddr, 'access_point_03'),
('2023-08-14 13:46:35.057'::timestamptz, '2023-08-14 13:46:46.980'::timestamptz, '00:00:00:00:00:02'::macaddr, 'access_point_03'),
('2023-08-14 13:46:47.213'::timestamptz, '2023-08-14 13:46:54.946'::timestamptz, '00:00:00:00:00:02'::macaddr, 'access_point_03'),
('2023-08-14 13:46:55.189'::timestamptz, '2023-08-14 13:47:17.040'::timestamptz, '00:00:00:00:00:02'::macaddr, 'access_point_03'),
('2023-08-14 13:47:17.297'::timestamptz, '2023-08-14 13:47:25.106'::timestamptz, '00:00:00:00:00:02'::macaddr, 'access_point_03'),
('2023-08-14 13:55:25.381'::timestamptz, '2023-08-14 13:58:33.059'::timestamptz, '00:00:00:00:00:02'::macaddr, 'access_point_03');

SELECT *
FROM network_test
ORDER BY mac_addr, access_point, start_ts
start_ts fim_ts mac_addr ponto de acesso
14/08/2023 13:21:10.289+00 14/08/2023 13:31:20.855+00 00:00:00:00:00:01 access_point_01
14/08/2023 13:58:10.638+00 14/08/2023 13:58:22.668+00 00:00:00:00:00:01 access_point_01
14/08/2023 13:58:22.727+00 14/08/2023 13:58:38.966+00 00:00:00:00:00:01 access_point_01
14/08/2023 13:28:28,19+00 14/08/2023 13:28:28,19+00 00:00:00:00:00:02 access_point_02
14/08/2023 13:28:44.167+00 14/08/2023 13:28:44.288+00 00:00:00:00:00:02 access_point_02
14/08/2023 13:45:40.281+00 14/08/2023 13:46:02.726+00 00:00:00:00:00:02 access_point_03
14/08/2023 13:46:02.964+00 14/08/2023 13:46:10.783+00 00:00:00:00:00:02 access_point_03
14/08/2023 13:46:11.026+00 2023-08-14 13:46:18.803+00 00:00:00:00:00:02 access_point_03
14/08/2023 13:46:19.037+00 14/08/2023 13:46:26.798+00 00:00:00:00:00:02 access_point_03
14/08/2023 13:46:27.036+00 14/08/2023 13:46:34.815+00 00:00:00:00:00:02 access_point_03
14/08/2023 13:46:35.057+00 14/08/2023 13:46:46,98+00 00:00:00:00:00:02 access_point_03
14/08/2023 13:46:47.213+00 14/08/2023 13:46:54.946+00 00:00:00:00:00:02 access_point_03
14/08/2023 13:46:55.189+00 14/08/2023 13:47:17.04+00 00:00:00:00:00:02 access_point_03
14/08/2023 13:47:17.297+00 14/08/2023 13:47:25.106+00 00:00:00:00:00:02 access_point_03
14/08/2023 13:55:25.381+00 14/08/2023 13:58:33.059+00 00:00:00:00:00:02 access_point_03

Aqui está o que eu gostaria que fosse o resultado:

start_ts fim_ts mac_addr ponto de acesso
14/08/2023 13:21:10.289+00 14/08/2023 13:31:20.855+00 00:00:00:00:00:01 access_point_01
14/08/2023 13:58:10.638+00 14/08/2023 13:58:38.966+00 00:00:00:00:00:01 access_point_01
14/08/2023 13:28:28,19+00 14/08/2023 13:28:44.288+00 00:00:00:00:00:02 access_point_02
14/08/2023 13:45:40.281+00 14/08/2023 13:58:33.059+00 00:00:00:00:00:02 access_point_03

A primeira sessão permanece como está. A 2ª e a 3ª sessões são mescladas em uma porque têm o mesmo endereço MAC e ponto de acesso e há menos de 15 minutos entre elas. O mesmo acontece para a 4ª e 5ª sessões, bem como da 6ª à 15ª.

Posso chegar perto usando funções de janela:

SELECT DISTINCT
       MIN(start_ts) OVER (PARTITION BY mac_addr, access_point, ROUND(EXTRACT(EPOCH FROM start_ts)/900)) AS start_ts,
       MAX(end_ts) OVER (PARTITION BY mac_addr, access_point, ROUND(EXTRACT(EPOCH FROM end_ts)/900)) AS end_ts,
       mac_addr,
       access_point
FROM network_test
ORDER BY mac_addr, access_point, start_ts
start_ts fim_ts mac_addr ponto de acesso
14/08/2023 13:21:10.289+00 14/08/2023 13:31:20.855+00 00:00:00:00:00:01 access_point_01
14/08/2023 13:58:10.638+00 14/08/2023 13:58:38.966+00 00:00:00:00:00:01 access_point_01
14/08/2023 13:28:28,19+00 14/08/2023 13:28:44.288+00 00:00:00:00:00:02 access_point_02
14/08/2023 13:45:40.281+00 14/08/2023 13:47:25.106+00 00:00:00:00:00:02 access_point_03
14/08/2023 13:55:25.381+00 14/08/2023 13:58:33.059+00 00:00:00:00:00:02 access_point_03

Mas observe que os dois últimos pontos de dados terminam em intervalos separados de 15 minutos, embora tenham apenas 8 minutos de intervalo.

Alguém sabe se existe uma maneira de fazer isso em SQL, ou terei que escrever uma função PL/pgSQL para percorrer os dados linha por linha e fazer a comparação?

postgresql

1 respostas

  1. Best Answer

    Isso funciona no Postgres 9.4:

    SELECT min(start_ts) AS start_ts
     , max(end_ts) AS end_ts
     , mac_addr, access_point
FROM  (
   SELECT *, count(step OR null) OVER (PARTITION BY mac_addr, access_point ORDER BY start_ts) AS grp
   FROM  (
      SELECT *, lag(end_ts) OVER (PARTITION BY mac_addr, access_point ORDER BY start_ts) + interval '15 min' <= start_ts AS step
      FROM   network_test
      ) sub1
   ) sub2
GROUP  BY mac_addr, access_point, grp
ORDER  BY mac_addr, access_point, grp;

    violino

    Ver:

    • 0

relate perguntas