Query timeseries in PostgreSQL

Question

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.

max(("time"::date) ?..

Vao Tsun
– Vao Tsun

2018-02-20 12:49:16 +00:00
Commented Feb 20, 2018 at 12:49 — Vao Tsun
– Vao Tsun, Commented Feb 20, 2018 at 12:49

Gordon Linoff · Accepted Answer · 2018-02-20 12:51:27Z

1

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

answered Feb 20, 2018 at 12:51

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Query timeseries in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related