PostgreSQL,Groupwise最小值和最大值
问题描述:
我有一张表;PostgreSQL,Groupwise最小值和最大值
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);
我想获得的最小和最大created_at
值为每个集群为特定用户。这样我就会看到用户在群集中停留了多久。
目前我这样做;
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
我认为这不是一个好的解决方案。这是对我的问题正确的查询?如果不是,您能否提出更好的方法来为特定用户检索每个群集的最小值和最大值?
答
这是窗函数是:
select 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
对不起,此方法返回USER_ID = 6 –
添加的所有行:“上(CLUSTER_ID)独特的”开始的选择,返回正确的结果。感谢您的好消息。 –
请编辑您的答案,以便我可以接受它作为答案。另一件事是;我的索引是否正确?你可以检查并推荐一些提示,如果可能的话? –