Compose a complex query in Postgresql

Question

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.

Thanks!

Answer 1

Pretty sure if I understand you correctly, you can accomplish your goal with just a self join like so:

SELECT Results.* -- Don't actually use *, it's an anti-pattern. Instead, list out only the fields you need.
FROM huge_table AS Threshold
INNER JOIN huge_table AS Results
    ON Threshold.tick_time 
        BETWEEN 
            Results.tick_time - INTERVAL '5s'
            AND Results.tick_time + INTERVAL '5s'
WHERE Threshold.crit_feat >= 100 -- Input your threshold value here

You'll likely want an index minimally on (tick_time, crit_feat).

Also, the above may return the same row from the Results instance of the table multiple times, if it matches on multiple rows from the Threshold instance of the table. If that's so, and you want the unique list of rows, you can just add the DISTINCT keyword after the SELECT keyword.