Postgresql: FIRST_VALUE as aggregate funtion-CodePudding

In Postgres we somehow want to use window function as aggregate function.

We have a table, where every line consist of two timestamps and a value. We first extend the table by adding column with difference between timestamps - only a few results is 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

CodePudding user response：

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

CodePudding user response：

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;