PostgreSQL计算用户记录间时间差：自定义函数开发及代码调试

First, let's align on the requirement using your example: it looks like you want to calculate the time difference between each record and its previous entry (with the first record having a timediff of 0), then output a table with id, user (mapped from aid), time_read, and timediff. If you actually need the difference to the next record instead, I’ll note how to adjust the code later.

Issues in Your Original Code #

Your PL/pgSQL code has several syntax and logical problems that prevent it from working:

• Nested loops with invalid logic for fetching adjacent records (inefficient and syntactically broken)
• Malformed INSERT statement (insert into newtable counter, select... doesn’t follow SQL syntax rules)
• Undefined variable tripcount in the raise notice line
• No explicit handling for the first/last record’s timediff value

Solution 1: Pure SQL (Most Efficient) #

PostgreSQL’s window functions are made for this kind of sequential calculation—no loops required. This approach is faster, cleaner, and easier to maintain:

For Time Difference from Previous Record (Matches Your Example) #

DROP TABLE IF EXISTS newtable;
CREATE TABLE newtable AS
SELECT
    ROW_NUMBER() OVER (PARTITION BY aid ORDER BY time_read) - 1 AS id,
    aid AS "user",
    time_read,
    -- First record gets 0, others calculate difference from the prior entry
    COALESCE(time_read - LAG(time_read) OVER (PARTITION BY aid ORDER BY time_read), INTERVAL '0 seconds') AS timediff
FROM mytable
ORDER BY aid, time_read;

For Time Difference to Next Record (As Per Your Original Description) #

If you truly want the difference between the current record and the next one (with the last record getting 0), swap LAG() for LEAD():

DROP TABLE IF EXISTS newtable;
CREATE TABLE newtable AS
SELECT
    ROW_NUMBER() OVER (PARTITION BY aid ORDER BY time_read) - 1 AS id,
    aid AS "user",
    time_read,
    -- Last record gets 0, others calculate difference to the next entry
    COALESCE(LEAD(time_read) OVER (PARTITION BY aid ORDER BY time_read) - time_read, INTERVAL '0 seconds') AS timediff
FROM mytable
ORDER BY aid, time_read;

Solution 2: Corrected PL/pgSQL Code #

If you specifically need a PL/pgSQL implementation, here’s the fixed version that matches your example:

-- Drop and recreate the target table with a clear schema
DROP TABLE IF EXISTS newtable;
CREATE TABLE newtable (
    id integer,
    "user" character varying,
    time_read timestamp,
    timediff interval
);

DO $$
DECLARE
    v_current_aid character varying;
    v_prev_time timestamp;
    v_curr_time timestamp;
    v_id_counter integer;
BEGIN
    -- Iterate over each unique user in the source table
    FOR v_current_aid IN SELECT DISTINCT aid FROM mytable LOOP
        v_id_counter := 0;
        v_prev_time := NULL;
        
        -- Loop through the user's records ordered by time_read
        FOR v_curr_time IN SELECT time_read FROM mytable WHERE aid = v_current_aid ORDER BY time_read ASC LOOP
            IF v_prev_time IS NULL THEN
                -- First record: set timediff to 0
                INSERT INTO newtable (id, "user", time_read, timediff)
                VALUES (v_id_counter, v_current_aid, v_curr_time, INTERVAL '0 seconds');
            ELSE
                -- Calculate time difference from the previous record
                INSERT INTO newtable (id, "user", time_read, timediff)
                VALUES (v_id_counter, v_current_aid, v_curr_time, v_curr_time - v_prev_time);
            END IF;
            
            v_prev_time := v_curr_time;
            v_id_counter := v_id_counter + 1;
        END LOOP;
    END LOOP;
END $$;

Key Improvements in This PL/pgSQL Code: #

• Fixed INSERT syntax to properly target columns and pass valid values
• Used a single loop per user to track the previous record’s time (avoiding inefficient nested queries)
• Explicitly handled the first record’s timediff value
• Removed undefined variables and cleaned up loop logic

内容的提问来源于stack exchange，提问作者Zuenie