主页 / dba / 问题 / 326406
Accepted
Leon
Asked: 2023-04-25 23:20:15 +0800 CST

在 Postgresql 中编写复杂查询

  • 772

我有一张像下面这样的大桌子：

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);

该表存储了一个时间序列的许多样本（行），一个样本一行。大多数时候，“crit_feat”字段的值很低，例如 1 或 2。在某些异常时间点，它会变得很高，例如 100。

在单个查询中找出这些特殊行非常容易。我们称它们为“特殊时间点”。

现在我需要查看查询结果集中上述每个特殊时间点之前/之后5s内发生的行，以找出特殊事件之前/之后发生的事情。

如果我只需要找出一个这样的特殊时间点和它前后5s的行，相对容易一些。

我可以这样做：

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;

但是我需要对每个特殊点和UNION所有特殊点都这样做！

所有特殊点的数量至少在20k左右，应该选出的事件不仅是“ crit_feat”字段具有最大值的事件，而且还包括值大于指定阈值的事件，例如：整个数据集中“crit_feat”字段的中值。

我想我可以在存储过程/函数中使用临时表和游标来获取它，但我期待一种更简单的方法！

抱歉我的英语不好，我希望我已经正确表达了我想说的话。

谢谢！

postgresql

1 个回答

  1. Best Answer

    很确定，如果我对你的理解正确，你可以通过这样的自我加入来实现你的目标：

    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

    您可能希望在(tick_time, crit_feat).

    Results此外，如果它与表实例中的多行匹配，则上面的代码可能会多次返回Threshold表实例中的同一行。如果是这样，并且您想要唯一的行列表，您可以在关键字DISTINCT之后添加关键字SELECT。

    • 1

相关问题