PostgreSQL获取部分结果集中再获取最大值最小值的结果集
Contents
PostgreSQL获取部分结果集中再获取最大值最小值的结果集
假设表结构如下。想要获取create_at在某时间范围的内的，最大的以及最小的use_id的值的所有结果.
[local]:5432 sky@sky=# \d wb_status
Table "public.wb_status"
Column | Type | Modifiers
------------------------+-----------------------------+--------------------------------------------------------
id | integer | not null default nextval('wb_status_id_seq'::regclass)
sid | bigint | not null default '0'::bigint
idstr | character varying(64) | not null default ''::character varying
mid | bigint | default '0'::bigint
user_id | bigint |
user_screen_name | character varying(32) | not null default ''::character varying
user_profile_image_url | character varying(128) | not null default ''::character varying
text | character varying(512) | not null default ''::character varying
source | character varying(256) | not null default ''::character varying
thumbnail_pic | character varying(256) | not null default ''::character varying
bmiddle_pic | character varying(256) | not null default ''::character varying
original_pic | character varying(256) | not null default ''::character varying
retweeted_status_id | character varying(32) | not null default ''::character varying
geo | character varying(256) | default ''::character varying
reposts_count | integer | default 0
comments_count | integer | default 0
attitudes_count | integer | default 0
visible | character varying(64) | not null default ''::character varying
pic_urls | character varying(1024) | not null default ''::character varying
create_at | timestamp without time zone | not null default now()
update_at | timestamp without time zone | not null default now()
ad | character varying(256) | not null default ''::character varying
is_deleted | smallint | not null default '0'::smallint
[local]:5432 sky@sky=#
SQL
[local]:5432 sky@sky=# with tmp as (SELECT * from wb_status where create_at >= '2015-03-15' and create_at < '2015-03-16'), r as (select max(user_id) as mx, min(user_id) as mi from tmp) select * from tmp inner join r on tmp.user_id = r.mx or tmp.user_id = r.mi;
Time: 128.032 ms
[local]:5432 sky@sky=# explain (analyze, verbose, buffers) with tmp as (SELECT * from wb_status where create_at >= '2015-03-15' and create_at < '2015-03-16'), r as (select max(user_id) as mx, min(user_id) as mi from tmp) select * from tmp inner join r on tmp.user_id = r.mx or tmp.user_id = r.mi;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=83681.38..83694.49 rows=4 width=4932) (actual time=123.466..123.573 rows=2 loops=1)
Output: tmp.id, tmp.sid, tmp.idstr, tmp.mid, tmp.user_id, tmp.user_screen_name, tmp.user_profile_image_url, tmp.text, tmp.source, tmp.thumbnail_pic, tmp.bmiddle_pic, tmp.original_pic, tmp.retweeted_status_id, tmp.geo, tmp.reposts_count, tmp.comments_count, tmp.attitudes_count, tmp.visible, tmp.pic_urls, tmp.create_at, tmp.update_at, tmp.ad, tmp.is_deleted, r.mx, r.mi
Join Filter: ((tmp.user_id = r.mx) OR (tmp.user_id = r.mi))
Rows Removed by Join Filter: 763
Buffers: shared hit=2031 read=71628
CTE tmp
-> Gather (cost=1000.00..83672.02 rows=374 width=372) (actual time=0.156..122.751 rows=765 loops=1)
Output: wb_status.id, wb_status.sid, wb_status.idstr, wb_status.mid, wb_status.user_id, wb_status.user_screen_name, wb_status.user_profile_image_url, wb_status.text, wb_status.source, wb_status.thumbnail_pic, wb_status.bmiddle_pic, wb_status.original_pic, wb_status.retweeted_status_id, wb_status.geo, wb_status.reposts_count, wb_status.comments_count, wb_status.attitudes_count, wb_status.visible, wb_status.pic_urls, wb_status.create_at, wb_status.update_at, wb_status.ad, wb_status.is_deleted
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=2031 read=71628
-> Parallel Seq Scan on public.wb_status (cost=0.00..82634.62 rows=156 width=372) (actual time=0.359..120.620 rows=255 loops=3)
Output: wb_status.id, wb_status.sid, wb_status.idstr, wb_status.mid, wb_status.user_id, wb_status.user_screen_name, wb_status.user_profile_image_url, wb_status.text, wb_status.source, wb_status.thumbnail_pic, wb_status.bmiddle_pic, wb_status.original_pic, wb_status.retweeted_status_id, wb_status.geo, wb_status.reposts_count, wb_status.comments_count, wb_status.attitudes_count, wb_status.visible, wb_status.pic_urls, wb_status.create_at, wb_status.update_at, wb_status.ad, wb_status.is_deleted
Filter: ((wb_status.create_at >= '2015-03-15 00:00:00'::timestamp without time zone) AND (wb_status.create_at < '2015-03-16 00:00:00'::timestamp without time zone))
Rows Removed by Filter: 456744
Buffers: shared hit=1825 read=71628
Worker 0: actual time=0.215..118.234 rows=71 loops=1
Buffers: shared hit=566 read=23219
Worker 1: actual time=0.855..121.365 rows=658 loops=1
Buffers: shared hit=575 read=24297
CTE r
-> Aggregate (cost=9.35..9.36 rows=1 width=16) (actual time=123.370..123.370 rows=1 loops=1)
Output: max(tmp_1.user_id), min(tmp_1.user_id)
Buffers: shared hit=2031 read=71628
-> CTE Scan on tmp tmp_1 (cost=0.00..7.48 rows=374 width=8) (actual time=0.159..123.271 rows=765 loops=1)
Output: tmp_1.id, tmp_1.sid, tmp_1.idstr, tmp_1.mid, tmp_1.user_id, tmp_1.user_screen_name, tmp_1.user_profile_image_url, tmp_1.text, tmp_1.source, tmp_1.thumbnail_pic, tmp_1.bmiddle_pic, tmp_1.original_pic, tmp_1.retweeted_status_id, tmp_1.geo, tmp_1.reposts_count, tmp_1.comments_count, tmp_1.attitudes_count, tmp_1.visible, tmp_1.pic_urls, tmp_1.create_at, tmp_1.update_at, tmp_1.ad, tmp_1.is_deleted
Buffers: shared hit=2031 read=71628
-> CTE Scan on r (cost=0.00..0.02 rows=1 width=16) (actual time=123.372..123.372 rows=1 loops=1)
Output: r.mx, r.mi
Buffers: shared hit=2031 read=71628
-> CTE Scan on tmp (cost=0.00..7.48 rows=374 width=4916) (actual time=0.001..0.118 rows=765 loops=1)
Output: tmp.id, tmp.sid, tmp.idstr, tmp.mid, tmp.user_id, tmp.user_screen_name, tmp.user_profile_image_url, tmp.text, tmp.source, tmp.thumbnail_pic, tmp.bmiddle_pic, tmp.original_pic, tmp.retweeted_status_id, tmp.geo, tmp.reposts_count, tmp.comments_count, tmp.attitudes_count, tmp.visible, tmp.pic_urls, tmp.create_at, tmp.update_at, tmp.ad, tmp.is_deleted
Planning time: 0.108 ms
Execution time: 124.163 ms
(34 rows)
(END)
[local]:5432 sky@sky=# SELECT count(1) from wb_status;
count
---------
1370996
(1 row)
Time: 91.415 ms