Group by time interval and output the source and destination station_id and count

Question

I am stuck with a query:

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)
;

I have to create a query which gives a result as follows

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

I tried the following code but not able to get the desired result:

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;

The time interval is about 55 mins to 60 mins between every entry and exit.

I have also tried an inner join but not able to group by the time interval in an inner join

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

The challenge is sometimes there is null in entry date and sometimes there is null in exit date.

Could you please provide your table structure as DDL and your data as DML? Help us to help you! — Vérace, Jul 16 '20 at 05:48
Hi Verace, Thank You for your response, I have edited the query as per your comment. — Deepan Kaviarasu, Jul 16 '20 at 08:37
Hi McNets, As you can see in the inner join query I have to get the source, destination, and count from the table and generate a Pivot Table with the result. — Deepan Kaviarasu, Jul 16 '20 at 12:32
I seem to recognize my SQL style in your queries. So I guess you started with code from a related answer? It would help to link to your source. — Erwin Brandstetter, Jul 17 '20 at 00:24
The challenge is sometimes there is null in entry date and sometimes there is null in exit date. Then how to determine the corresponding entry/exit? If there are multiple rows with validation_date IS NULL for the same serial_number, the information can quickly become ambiguous. Also, can transaction time ranges overlap? If so, what is possible? — Erwin Brandstetter, Jul 17 '20 at 14:30

score 2 · Answer 1 · answered Jul 16 '20 at 13:58

For this you will need two things:

A correlated subquery on your join condition
An unique index on (serial_number,validation_date)

After that, your query becomes:

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

Should return:

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

@DeepanKaviarasu, glad to help. Thankfully your table layout lent itself to the easiest possible solution. — , Jul 16 '20 at 18:47

Erwin Brandstetter · Accepted Answer · 2020-08-27T16:03:36.883

This should be simplest and fastest while transactions per serial_number never overlap:

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<>fiddle here

① I rewrote the WHERE condition to get all of Feb 1 2020 in optimal fashion. BETWEEN is almost always the wrong tool for time ranges. See:

How to add a day/night indicator to a timestamp column?

Also, '2020-02-01' is a perfectly valid timestamp constant 00:00:00 is assumed when the time component is missing.

While retrieving results for a given time frame, a plain btree index on (validation_date) is the optimum. For the complete table, an index on (serial_number, validation_date) would help more.

`validation_date IS NULL`?

The query keeps working while only the last destination per serial_number in the given time frame has validation_date IS NULL because NULL values happen to sort last in default ascending order. But it breaks with any other cases of validation_date IS NULL. You'll have to define more closely where those can pop up and how to deal with them exactly.

(2x) `uuid` instead of `varchar(255)` for `serial_number`?

Your serial_number seems to be a hexadecimal number with exactly 64 digits. If so, varchar(255) is a poor choice. See:

Should I add an arbitrary length limit to VARCHAR columns?

Moreover, a single uuid (32 hex digits) should suffice. If all 64 hex digits are needed, still consider 2 uuid columns. Smaller, faster, safer. Consider:

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<>fiddle here

See:

Thank you for your time and effort @Erwin. I have added more data for analysis. Please do have a look in this link. — Deepan Kaviarasu, Jul 20 '20 at 15:24
Hi, @Erwin your assumption is correct, the link which I referred is this. Your solution in the following link almost suited my requirement. — Deepan Kaviarasu, Jul 20 '20 at 15:33
@DeepanKaviarasu: More data is good. Clarifying open question and assumptions would help even more. — Erwin Brandstetter, Jul 23 '20 at 03:25

Group by time interval and output the source and destination station_id and count

2 Answers2

validation_date IS NULL?

(2x) uuid instead of varchar(255) for serial_number?

`validation_date IS NULL`?

(2x) `uuid` instead of `varchar(255)` for `serial_number`?