Postgresql: FIRST_VALUE as aggregate funtion

Question

In Postgres, we want to use the window function as an aggregate function.

We have a table, where every line consists of two timestamps and a value. We first extend the table by adding a column with the difference between timestamps - only a few results are possible. Then we group data by timestamp1 and timediff. In each group, there can be more than one line. We need to choose in each group one value, the one that has the smallest timestamp2.

SELECT
  timestamp1,
  timediff,
  FIRST_VALUE(value) OVER (ORDER BY timestamp2) AS value
FROM (
  SELECT
    timestamp1,
    timestamp2,
    value,
    timestamp2 - timestamp1 AS timediff
  FROM forecast_table WHERE device = 'TEST'
) sq
GROUP BY timestamp1,timediff
ORDER BY timestamp1

Error: column "sq.value" must appear in the GROUP BY clause or be used in an aggregate function

Vau, I got three solutions and it seems that all three are correct. Now, I have problem to accept the best answer. Maybe, I should wait to see which one will have the most votes. — meolic, Feb 03 '23 at 07:32

score 3 · Answer 1 · answered Jan 31 '23 at 13:18

You can workaround this by aggregating into an array, then pick the first array element:

SELECT
  timestamp1,
  timediff,
  (array_agg(value ORDER BY timestamp2))[1] AS value
FROM (
  SELECT
    timestamp1,
    timestamp2,
    value,
    timestamp2 - timestamp1 AS timediff
  FROM forecast_table 
  WHERE device = 'TEST'
) sq
GROUP BY timestamp1,timediff
ORDER BY timestamp1

score 2 · Answer 2 · answered Jan 31 '23 at 14:37

Or you may use DISTINCT ON with custom ORDER BY.

  SELECT DISTINCT ON (timestamp1, timediff)
    timestamp1, timestamp2, value,
    timestamp2 - timestamp1 AS timediff  
  FROM forecast_table WHERE device = 'TEST'
  ORDER BY timestamp1, timediff, timestamp2;

score 2 · Answer 3 · answered Feb 01 '23 at 19:26

There is no need for GROUP BY if you are not actually doing any aggregation.
You can get what you want if you define PARTITION BY timestamp1, timestamp2 - timestamp1 inside FIRST_VALUE():

SELECT DISTINCT timestamp1,
       FIRST_VALUE(value) OVER (PARTITION BY timestamp1, timestamp2 - timestamp1 ORDER BY timestamp2) AS value,
       timestamp2 - timestamp1 AS timediff
FROM forecast_table 
WHERE device = 'TEST'
ORDER BY timestamp1, timediff;

Postgresql: FIRST_VALUE as aggregate funtion

3 Answers3