CODEWITHSUNDEEP
sqlpostgresqltime-series
Linch
Linch

Reputation: 531

Query timeseries in PostgreSQL

I have the following table with group members count:

CREATE TABLE "group_members_count" (
  group_id INTEGER,
  time     TIMESTAMP,
  members_count INTEGER
)

I want to query members count for specific group and time interval (from, to) with custom period.

I wrote the following query:

SELECT 
  "group_id",
  date_trunc('day', "time") as period,
  max(count) as value
FROM
  "group_members_count"
WHERE
  "channel_id" IN (123, 124) AND ("time"::date >= '2017-11-02' AND "time"::date <= '2017-11-02')
GROUP BY 
  "period", "group_id"
ORDER BY
  "group_id", "period"

As expected, I will get the maximum value per day, but I need the latest.

Upvotes: 0

Views: 89

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

One method is to use array_agg() and take the first element:

SELECT group_id,
       date_trunc('day', "time") as period,
       max(count) as value,
       array_agg(count order by "time" desc)[1]
FROM group_members_count
WHERE channel_id IN (123, 124) AND
      ("time"::date >= '2017-11-02' AND "time"::date <= '2017-11-02')
GROUP BY period, group_id
ORDER BY group_id, period

Upvotes: 1

Related Questions