PostgreSQL, Groupwise Min and Max values

Question

I have a table like;

CREATE TABLE public.user_locations
(
  id integer NOT NULL DEFAULT nextval('user_locations_id_seq'::regclass),
  user_id integer,
  created_at timestamp without time zone,
  location geography(Point,4326),
  cluster_id integer,
  CONSTRAINT user_locations_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

CREATE INDEX index_user_locations_on_location
  ON public.user_locations
  USING gist
  (location);

CREATE INDEX index_user_locations_on_user_id
  ON public.user_locations
  USING btree
  (user_id);

I would like to get minimum and maximum created_at values for each cluster for a specific user. So that i will see how long the user stayed in a cluster.

Currently i do this;

SELECT * FROM (
  (
    SELECT DISTINCT ON (cluster_id) * FROM user_locations 
    WHERE user_id = 6
    ORDER BY cluster_id, created_at DESC
  )
  UNION
  (
    SELECT DISTINCT ON (cluster_id) * FROM user_locations 
    WHERE user_id = 6
    ORDER BY cluster_id, created_at ASC
  )
) a
ORDER BY cluster_id, created_at

I think this is not a good solution. Is this the right query for my problem? If not could you suggest better ways to retrieve the minimum and maximum values for each cluster for a specific user?

tozlu · Accepted Answer · 2020-11-20 14:36:08Z

3

This is what window functions are for:

  select distinct on (cluster_id) id, user_id, cluster_id, location, 
         created_at, 
         min(created_at) over (partition by cluster_id) as min_created,
         max(created_at) over (partition by cluster_id) as max_created
  from user_locations
  where user_id = 6
  order by cluster_id, created_at

edited Nov 20, 2020 at 14:36

tozlu

4,8854 gold badges33 silver badges46 bronze badges

answered Sep 4, 2015 at 15:59

user330315

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

3 Comments

tozlu Over a year ago

Sorry, this returns all rows with user_id = 6

tozlu Over a year ago

Adding: "distinct on (cluster_id)" to start of select, returns correct results. Thanks for great info.

tozlu Over a year ago

Please edit your answer so i can accept it as answer. Another thing is; are my indexes proper? Could you check and recommend some tips if possible?

Collectives™ on Stack Overflow

PostgreSQL, Groupwise Min and Max values

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related