时间戳

首页 > 解决方案 > 在 Postgres 中获取重复超过 2 分钟的值

postgresql - 在 Postgres 中获取重复超过 2 分钟的值

问题描述

我有一个事件表，每当超速警报超过 2 分钟时，我都需要获取deviceid并用"emailSent"=1.

我正在做的是，像这样抓取每辆车的所有事件SELECT * FROM tc_events tm where "emailSent" = 0 and date(servertime) = date(now());，然后与它的最后一个已知值一一比较，像这样

SELECT * 
FROM tc_events tm 
where deviceid = ? 
and id not in (?) 
and "attributes" = '{"alarm":"overspeed"}' 
and servertime > NOW() - INTERVAL '2 minute' 
and date(servertime) = date(now())
limit 1;

也许你们可以帮助我在一个查询中得到它。

CREATE TABLE public.tc_events (
    id serial NOT NULL,
    "type" varchar(128) NOT NULL,
    servertime timestamp NOT NULL,
    deviceid int4 NULL,
    positionid int4 NULL,
    geofenceid int4 NULL,
    "attributes" varchar(4000) NULL,
    maintenanceid int4 NULL,
    "emailSent" int4 NULL DEFAULT 0,
    CONSTRAINT tc_events_pkey PRIMARY KEY (id)
);

id    |type |servertime         |deviceid|positionid|geofenceid|attributes           |maintenanceid|emailSent|
------|-----|-------------------|--------|----------|----------|---------------------|-------------|---------|
631014|alarm|2020-09-03 20:46:36|     962|   8162779|          |{"alarm":"overspeed"}|             |        0|
630980|alarm|2020-09-03 20:42:43|     962|   8162521|          |{"alarm":"overspeed"}|             |        0|
630971|alarm|2020-09-03 20:40:08|     962|   8162385|          |{"alarm":"overspeed"}|             |        0|
607651|alarm|2020-09-02 14:46:13|     557|   7871167|          |{"alarm":"overspeed"}|             |        1|
607616|alarm|2020-09-02 14:44:33|     557|   7870620|          |{"alarm":"overspeed"}|             |        1|
591124|alarm|2020-09-01 17:13:05|     239|   7652421|          |{"alarm":"overspeed"}|             |        0|
590225|alarm|2020-09-01 16:30:31|     148|   7642031|          |{"alarm":"overspeed"}|             |        0|
590172|alarm|2020-09-01 16:28:35|     148|   7641467|          |{"alarm":"overspeed"}|             |        0|
588176|alarm|2020-09-01 15:14:42|     148|   7619691|          |{"alarm":"overspeed"}|             |        0|
582210|alarm|2020-09-01 11:34:11|     725|   7543204|          |{"alarm":"overspeed"}|             |        0|

标签： postgresql

解决方案

假设过去两分钟内仅存在EXISTS另一个超速警报就构成“持续”，则可以完成以下工作：

SELECT * 
FROM   tc_events tm
WHERE  deviceid = ? 
AND    id not in (?) 
AND    attributes = '{"alarm":"overspeed"}' 
AND    servertime > NOW() - INTERVAL '2 minute' -- only look at very recent rows?
AND    date(servertime) = date(now())  -- why? local midnight cancels "ongoing"?
AND    EXISTS (
   SELECT FROM tc_events
   WHERE  deviceid = tm.deviceid
   -- AND    id not in (?)  -- exclude here, too?
   AND    id <> tm.id  -- exclude self
   AND    attributes = '{"alarm":"overspeed"}' 
   AND    servertime <= tm.servertime -- same time possible?
   AND    servertime >= tm.servertime - interval '2 minute' 
   );

这会为每个新行重复报告“正在进行的”事件。

另外，您的attributes列看起来像一个 JSON 文档，并且varchar(4000)可能不是存储它的最佳方式......

推荐阅读