0

I have a table of visit data. The easiest way to visualise it is as website visits:

CREATE visits (
    previous_path   TEXT,
    next_path       TEXT,
    path            TEXT NOT NULL,
    visitor_id      INTEGER,
    visit_timestamp TIMESTAMP without time zone
);

In normal operation, path, visitor_id, and visit_timestamp are inserted into the table with each visit. A post-processing function should fill in previous_path and next_path as it can.

Conceptually, I want to consider

SELECT visitor_id, path
FROM visits
ORDER BY visitor_id, visit_timestamp;

and UPDATE previous_path and next_path when they are NULL and a previous or next row with the same visitor_id is present.

I've found some SELECT-only examples using CTE's and LAG/LEAD with PARTITION, but these don't appear to permit doing the UPDATE.

I can easily see how to do this if I first SELECT then UPDATE (and so transfer data to a python program that does the matching). If there's a readable and maintainable way to do this server-side, however, that seems better, as it's imaginable that the number of visits with a single visitor id gets quite large.

jma
  • 3,580
  • 6
  • 40
  • 60
  • 1
    Why do you need `previous_path` and `next_path` to be stored in the table at all? It might fit better defining a `VIEW` for this. – Bergi Dec 18 '21 at 19:51
  • You can do a (self-)[join against the query in the `UPDATE`](https://stackoverflow.com/q/7869592/1048572) – Bergi Dec 18 '21 at 19:54

0 Answers0