在Postgres中获取在2分钟内重复的值

Question

我有一个事件表，每当任何deviceid的超速警报超过2分钟时，我都需要获取，并用"emailSent"=1标记它们。

我正在做的是，获取每辆车的所有事件，比如SELECT * FROM tc_events tm where "emailSent" = 0 and date(servertime) = date(now());，然后与上次已知的值进行1比1的比较，比如

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|

Answer 1

假设在过去两分钟内仅存在另一个超速警报就构成"；"正在进行"；，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)可能不是存储它的最佳方式。。。