I have a huge table like following:
CREATE TABLE public.huge_table ( sampl_day date NOT NULL, tick_time timestamp(6) with time zone NOT NULL, crit_feat integer NOT NULL, --- --- other fields --- CONSTRAINT pkey_huge_table PRIMARY KEY (tick_time) ); CREATE INDEX idx_huge_table_day_time ON public.huge_table USING brin (sampl_day, tick_time);
This table stores much many samples(rows) of a time series, one row for one sample. Most time, the field "crit_feat" has a very low value, for example 1 or 2. At some abnormal time point, it would go very high, for example 100.
It's very easy to find out these special rows in a single query. Let's call them as "special time points".
And now I need to view rows those happened in 5s before/after each of above special time points in a query result set, to find out what happened before/after a special event.
If I only need to find out a single one such special time point and rows in 5s before/after it, it is relatively easier.
I can do like this:
WITH spec_time as ( SELECT tick_time tp FROM huge_table ORDER BY crit_feat DESC LIMIT 1) SELECT * FROM huge_table WHERE tick_time BETWEEN (SELECT tp FROM spec_time) - INTERVAL '5s' AND (SELECT tp FROM spec_time) + INTERVAL '5s' ORDER BY tick_time;
But I need to do so with every special points and
UNION all of them!
The number of all speical points is about 20k at least, and the events that should be selected out are NOT ONLY whose "crit_feat" field has the largest value, BUT ALSO include ones with a value greater than a specified threshold, for example: the median value of "crit_feat" field within entire data set.
I guess that I can get it with Temporary-Table and Cursor in a Storage Procedure/Function, but I'm expecting a simpler method!
Sorry about my ugly English, I hope that I have expressed correctly things what I want to say.