PostgreSQL query with max and min date plus associated id per row

aggregatepostgresqlwindow functions

I have the following table:

CREATE TABLE trans (
    id SERIAL PRIMARY KEY,
    trans_date date,
    trans_time time        
);

I want to have the following view

CREATE OR REPLACE VIEW daily_trans AS
SELECT trans_date,
    max(trans_time) as first, 
    min(trans_time) as last,
    calculate_status(min(trans_time), max(trans_time)) as status 
GROUP BY trans_date

with columns that specify the ids of the max and min trans_time.

How do I do that?

Best Answer

You can calculate this in one step with window functions:

CREATE OR REPLACE VIEW daily_trans AS
SELECT DISTINCT
       trans_date
     , first_value(trans_time) OVER w AS first_time
     , first_value(id)         OVER w AS first_id
     , last_value(trans_time)  OVER w AS last_time
     , last_value(id)          OVER w AS last_id
     , calculate_status(min(trans_time) OVER w
                      , max(trans_time) OVER w) AS status 
FROM   trans
WINDOW w AS (PARTITION BY trans_date ORDER BY trans_time, id
             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
ORDER  BY 1;

I added id as secondary ORDER column in the window clause, to make the sort order stable in case of identical times per day.

If you are not familiar with window functions, be sure to read this chapter of the manual.

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING is a not so commonly used clause to adjust the window frame, because we want last_value() to always return the last value of the whole partition (the date), not the last value up to the current (including peers) according to the sort order.

How you to combine DISTINCT with window functions:

PostgreSQL: running count of rows for a query 'by minute'

Related Solutions

PostgreSQL – Independent Serial for Each Foreign Key Value

You can't do this with SERIAL.

You could create a SEQUENCE for each owner_id but that'd be painful and scale poorly.

Instead, use a counter table where each owner_id has a separate counter, and

update players_per_user_counter 
set next_val = next_val + 1 
where player_id = $1 
returning next_val

to get new identifiers. For more info search for "PostgreSQL gapless sequence".

See:

https://stackoverflow.com/q/9984196/398670

Postgresql – How to make trigger function to make daily entering-exiting report

Let's assume every "in" is followed by an "out" event. In that case you can do:

SELECT worker_id, 
    the_date as in_date, 
    the_time as in_time,
    lead(the_date) over w as out_date, 
    lead(the_time) over w as out_time,
    -- Check if it is really an out event
    (lead(in_or_out) over w) = 0 as out
FROM daily_report
-- Filter for in events
WHERE in_or_out = 1 AND worker_id = [your_worker_id]
-- specify the window function
WINDOW w AS (order by the_date, the_time)
GROUP BY worker_id;

You didn't write which PG version do you have. A better approach is to have LATERAL JOINs (available since 9.3):

SELECT worker_id, i.the_date, i.the_time, o.the_date, o.the_time
FROM daily_report i
LEFT JOIN LATERAL (
    SELECT r.the_date, r.the_time 
    FROM daily_report r 
    WHERE 
        in_or_out = 0 AND r.worker_id = i.worker_id
        (
            (r.the_date = i.the_date and r.the_time > i.the_time) OR 
            (r.the_date > i.the_date)
        )
    ORDER BY 1, 2
    LIMIT 1
) o ON true
WHERE in_or_out = 1 AND worker_id = [your_worker_id]

This is a more generic query and will give you back better results if the data is somehow not completely correct.

Best Answer

Related Solutions

PostgreSQL – Independent Serial for Each Foreign Key Value

Postgresql – How to make trigger function to make daily entering-exiting report

Related Question