Question 89925 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql: FIRST_VALUE as aggregate funtion

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

Tags:

3

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
  3. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search