First and last value of window function in one row in PostgreSQL

Question

I'd like to have first value of one column and last value of second column in one row for a specified partition. For that I created this query:

SELECT DISTINCT
b.machine_id,
batch,
timestamp_sta,
timestamp_stp,
FIRST_VALUE(timestamp_sta) OVER w AS batch_start,
LAST_VALUE(timestamp_stp) OVER w AS batch_end
FROM db_data.sta_stp AS a
JOIN db_data.ll_lu AS b
ON a.ll_lu_id=b.id
WINDOW w AS (PARTITION BY batch, machine_id ORDER BY timestamp_sta)
ORDER BY timestamp_sta, batch, machine_id;

But as you can see in the image, returned data in batch_end column are not correct.

batch_start column has correct first value of timestamp_sta column. However batch_end should be "2012-09-17 10:49:45" and it equals timestamp_stp from same row.

Why is it so?

Erwin Brandstetter · Accepted Answer · 2020-06-03T01:53:09.747

The question is old, but this solution is simpler and faster than what's been posted so far:

SELECT b.machine_id
     , batch
     , timestamp_sta
     , timestamp_stp
     , min(timestamp_sta) OVER w AS batch_start
     , max(timestamp_stp) OVER w AS batch_end
FROM   db_data.sta_stp a
JOIN   db_data.ll_lu   b ON a.ll_lu_id = b.id
WINDOW w AS (PARTITION BY batch, b.machine_id) -- No ORDER BY !
ORDER  BY timestamp_sta, batch, machine_id; -- why this ORDER BY?

If you add ORDER BY to the window frame definition, each next row with a greater ORDER BY expression has a later frame start. Neither min() nor first_value() can return the "first" timestamp for the whole partition then. Without ORDER BY all rows of the same partition are peers and you get your desired result.

Your added ORDER BY works (not the one in the window frame definition, the outer one), but doesn't seem to make sense and makes the query more expensive. You should probably use an ORDER BY clause that agrees with your window frame definition to avoid additional sort cost:

... 
ORDER BY batch, b.machine_id, timestamp_sta, timestamp_stp;

I don't see the need for DISTINCT in this query. You could just add it if you actually need it. Or DISTINCT ON (). But then the ORDER BY clause becomes even more relevant. See:

Select first row in each GROUP BY group?

If you need some other column(s) from the same row (while still sorting by timestamps), your idea with FIRST_VALUE() and LAST_VALUE() might be the way to go. You'd probably need to append this to the window frame definition then:

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

See:

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

score 8 · Answer 2 · answered Jun 05 '17 at 12:44

The explanations given by @Łukasz Kamiński solve the core of the issue.

However, the last_value should be replaced by max(). You are sorting by timestamp_sta so the last value is the one having the greatest timestamp_sta, which may or may not be related to timestamp_stp. Also I would sort by the two fields.

SELECT DISTINCT
  b.machine_id,
  batch,
  timestamp_sta,
  timestamp_stp,
  FIRST_VALUE(timestamp_sta) OVER w AS batch_start,
  MAX(timestamp_stp) OVER w AS batch_end
FROM db_data.sta_stp AS a
JOIN db_data.ll_lu AS b
ON a.ll_lu_id=b.id
WINDOW w AS (PARTITION BY batch, machine_id 
             ORDER BY timestamp_sta,timestamp_stp 
             RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
ORDER BY timestamp_sta, batch, machine_id;

http://rextester.com/UTDE60342

While this should work, it is needlessly complicated and expensive. I added an alternative. — Erwin Brandstetter, Jul 21 '18 at 15:54

score 3 · Answer 3 · edited Jun 20 '20 at 09:12

3

From syntax documentation:

The frame_clause specifies the set of rows constituting the window frame, which is a subset of the current partition, for those window functions that act on the frame instead of the whole partition. The frame can be specified in either RANGE or ROWS mode; in either case, it runs from the frame_start to the frame_end. If frame_end is omitted, it defaults to CURRENT ROW.

A frame_start of UNBOUNDED PRECEDING means that the frame starts with the first row of the partition, and similarly a frame_end of UNBOUNDED FOLLOWING means that the frame ends with the last row of the partition.

and function list

last_value(value any) returns value evaluated at the row that is the last row of the window frame

So correct SQL should be:

SELECT DISTINCT
b.machine_id,
batch,
timestamp_sta,
timestamp_stp,
FIRST_VALUE(timestamp_sta) OVER w AS batch_start,
LAST_VALUE(timestamp_stp) OVER w AS batch_end
FROM db_data.sta_stp AS a
JOIN db_data.ll_lu AS b
ON a.ll_lu_id=b.id
WINDOW w AS (PARTITION BY batch, machine_id ORDER BY timestamp_sta range between unbounded preceding and unbounded following)
ORDER BY timestamp_sta, batch, machine_id;

edited Jun 20 '20 at 09:12

Community

1
1

answered Jun 05 '17 at 12:25

Łukasz Kamiński

5,630
1
19
32

Great, that's it! I've just discovered this manual page, too, but I still didn't fully understand how it works. Thanks for working example. – Michal Špondr Jun 05 '17 at 12:39
1

Unfortunately, I do not think it returns the expected result. – klin Jun 05 '17 at 12:44
@klin Yes, it did for me. What's wrong with this solution? – Michal Špondr Jun 05 '17 at 12:47
3

See the note in the @JGH answer. – klin Jun 05 '17 at 12:51
It wouldn't work if you had concurrent inserts (or other reason to get different sort for those 2 columns) on that table and for batch your last _started_ insert would end before some other one in said batch. So it would be last started, not last finished, but `last_value()` would still return this row value, not actually last finished in batch. – Łukasz Kamiński Jun 05 '17 at 13:47
You have `ORDER BY timestamp_sta` in the window frame definition. Does not work for `LAST_VALUE(timestamp_stp)`. Note: `timestamp_stp`<> `timestamp_sta`. Easy to overlook. – Erwin Brandstetter Jul 21 '18 at 15:41

First and last value of window function in one row in PostgreSQL

3 Answers3

Linked