add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE bulk_sample (
serial_number varchar(255), -- odd type decision?
validation_date timestamp,
station_id integer,
direction integer
);

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

22 rows affected
-- simple solution if transactions never overlap for the same serial_number
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

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
-- consider 2x UUID:
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;
serial_number uuid1 uuid2 reverse_engineered identical varchar_size uuid_size
019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270 019b5526-970f-cfcf-7813-e9fe1acf8a41 bcaf5a5a-5c10-870b-3211-d82f63fbf270 019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270 t 65 32
019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270 019b5526-970f-cfcf-7813-e9fe1acf8a41 bcaf5a5a-5c10-870b-3211-d82f63fbf270 019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270 t 65 32
019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270 019b5526-970f-cfcf-7813-e9fe1acf8a41 bcaf5a5a-5c10-870b-3211-d82f63fbf270 019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270 t 65 32
019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270 019b5526-970f-cfcf-7813-e9fe1acf8a41 bcaf5a5a-5c10-870b-3211-d82f63fbf270 019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270 t 65 32
019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270 019b5526-970f-cfcf-7813-e9fe1acf8a41 bcaf5a5a-5c10-870b-3211-d82f63fbf270 019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270 t 65 32
019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270 019b5526-970f-cfcf-7813-e9fe1acf8a41 bcaf5a5a-5c10-870b-3211-d82f63fbf270 019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270 t 65 32
019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270 019b5526-970f-cfcf-7813-e9fe1acf8a41 bcaf5a5a-5c10-870b-3211-d82f63fbf270 019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270 t 65 32
019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270 019b5526-970f-cfcf-7813-e9fe1acf8a41 bcaf5a5a-5c10-870b-3211-d82f63fbf270 019b5526970fcfcf7813e9fe1acf8a41bcaf5a5a5c10870b3211d82f63fbf270 t 65 32
23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663 23cc9678-e8cf-834d-ecb0-96ba36be0efe e418402b-ce03-aab5-2e69-026adfec7663 23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663 t 65 32
23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663 23cc9678-e8cf-834d-ecb0-96ba36be0efe e418402b-ce03-aab5-2e69-026adfec7663 23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663 t 65 32
23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663 23cc9678-e8cf-834d-ecb0-96ba36be0efe e418402b-ce03-aab5-2e69-026adfec7663 23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663 t 65 32
23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663 23cc9678-e8cf-834d-ecb0-96ba36be0efe e418402b-ce03-aab5-2e69-026adfec7663 23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663 t 65 32
23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663 23cc9678-e8cf-834d-ecb0-96ba36be0efe e418402b-ce03-aab5-2e69-026adfec7663 23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663 t 65 32
23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663 23cc9678-e8cf-834d-ecb0-96ba36be0efe e418402b-ce03-aab5-2e69-026adfec7663 23cc9678e8cf834decb096ba36be0efee418402bce03aab52e69026adfec7663 t 65 32
2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af 2a8f28bf-0afc-6552-10aa-337aff016d33 100282ac-73cc-a660-a397-b924808499af 2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af t 65 32
2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af 2a8f28bf-0afc-6552-10aa-337aff016d33 100282ac-73cc-a660-a397-b924808499af 2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af t 65 32
2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af 2a8f28bf-0afc-6552-10aa-337aff016d33 100282ac-73cc-a660-a397-b924808499af 2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af t 65 32
2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af 2a8f28bf-0afc-6552-10aa-337aff016d33 100282ac-73cc-a660-a397-b924808499af 2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af t 65 32
2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af 2a8f28bf-0afc-6552-10aa-337aff016d33 100282ac-73cc-a660-a397-b924808499af 2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af t 65 32
2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af 2a8f28bf-0afc-6552-10aa-337aff016d33 100282ac-73cc-a660-a397-b924808499af 2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af t 65 32
2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af 2a8f28bf-0afc-6552-10aa-337aff016d33 100282ac-73cc-a660-a397-b924808499af 2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af t 65 32
2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af 2a8f28bf-0afc-6552-10aa-337aff016d33 100282ac-73cc-a660-a397-b924808499af 2a8f28bf0afc655210aa337aff016d33100282ac73cca660a397b924808499af t 65 32