1

I am trying to process network logs and join sessions together if the time between them is less than 15 minutes. The relevant fields are start time, end time, mac address, and wifi access point.

I am working in Greenplum 6.22/Postgresql 9.4.26:

pdap=# SELECT version();
version
PostgreSQL 9.4.26 (Greenplum Database 6.22.2)

db fiddle

Logically, what I want to do is "If the start time from the next row is less than 15 minutes after the end time from this row, merge the two rows into one row with the earlier start time and the later end time."

Here is an example table with some data:

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 end_ts mac_addr access_point
2023-08-14 13:21:10.289+00 2023-08-14 13:31:20.855+00 00:00:00:00:00:01 access_point_01
2023-08-14 13:58:10.638+00 2023-08-14 13:58:22.668+00 00:00:00:00:00:01 access_point_01
2023-08-14 13:58:22.727+00 2023-08-14 13:58:38.966+00 00:00:00:00:00:01 access_point_01
2023-08-14 13:28:28.19+00 2023-08-14 13:28:28.19+00 00:00:00:00:00:02 access_point_02
2023-08-14 13:28:44.167+00 2023-08-14 13:28:44.288+00 00:00:00:00:00:02 access_point_02
2023-08-14 13:45:40.281+00 2023-08-14 13:46:02.726+00 00:00:00:00:00:02 access_point_03
2023-08-14 13:46:02.964+00 2023-08-14 13:46:10.783+00 00:00:00:00:00:02 access_point_03
2023-08-14 13:46:11.026+00 2023-08-14 13:46:18.803+00 00:00:00:00:00:02 access_point_03
2023-08-14 13:46:19.037+00 2023-08-14 13:46:26.798+00 00:00:00:00:00:02 access_point_03
2023-08-14 13:46:27.036+00 2023-08-14 13:46:34.815+00 00:00:00:00:00:02 access_point_03
2023-08-14 13:46:35.057+00 2023-08-14 13:46:46.98+00 00:00:00:00:00:02 access_point_03
2023-08-14 13:46:47.213+00 2023-08-14 13:46:54.946+00 00:00:00:00:00:02 access_point_03
2023-08-14 13:46:55.189+00 2023-08-14 13:47:17.04+00 00:00:00:00:00:02 access_point_03
2023-08-14 13:47:17.297+00 2023-08-14 13:47:25.106+00 00:00:00:00:00:02 access_point_03
2023-08-14 13:55:25.381+00 2023-08-14 13:58:33.059+00 00:00:00:00:00:02 access_point_03

Here is what I would like the result to be:

start_ts end_ts mac_addr access_point
2023-08-14 13:21:10.289+00 2023-08-14 13:31:20.855+00 00:00:00:00:00:01 access_point_01
2023-08-14 13:58:10.638+00 2023-08-14 13:58:38.966+00 00:00:00:00:00:01 access_point_01
2023-08-14 13:28:28.19+00 2023-08-14 13:28:44.288+00 00:00:00:00:00:02 access_point_02
2023-08-14 13:45:40.281+00 2023-08-14 13:58:33.059+00 00:00:00:00:00:02 access_point_03

The first session stays as it is. The 2nd and 3rd sessions are merged into one because they have the same mac address and access point, and there is less than 15 minutes between them. The same happens for the 4th and 5th sessions, as well as the 6th through the 15th.

I can come close using window functions:

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 end_ts mac_addr access_point
2023-08-14 13:21:10.289+00 2023-08-14 13:31:20.855+00 00:00:00:00:00:01 access_point_01
2023-08-14 13:58:10.638+00 2023-08-14 13:58:38.966+00 00:00:00:00:00:01 access_point_01
2023-08-14 13:28:28.19+00 2023-08-14 13:28:44.288+00 00:00:00:00:00:02 access_point_02
2023-08-14 13:45:40.281+00 2023-08-14 13:47:25.106+00 00:00:00:00:00:02 access_point_03
2023-08-14 13:55:25.381+00 2023-08-14 13:58:33.059+00 00:00:00:00:00:02 access_point_03

But note that the last two data points end up in separate 15-minute buckets even though they're only 8 minutes apart.

Does anyone know if there is a way to do this in SQL, or am I going to have to write a PL/pgSQL function to go through the data row by row and do the comparison?

Improve this question

1 Answer 1

Reset to default
0

This works in 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;

fiddle

See:

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.