sql - 每次列更改时选择第一行

Question

我在 postgres 数据库中有下表，有 3 列:

ReaderId: String
TagId: String
Timestamp: Timestamp

ReaderId  TagId  Timestamp
A         T1      20190101-00:00:00  *  ~      
A         T1      20190101-00:00:00     ~
A         T1      20190101-00:00:01    
A         T1      20190101-00:00:02   
B         T1      20190101-00:00:03  *
B         T1      20190101-00:00:03 
B         T1      20190101-00:00:04   
A         T1      20190101-00:00:05  * 
A         T1      20190101-00:00:06 
A         T1      20190101-00:00:07   
C         T1      20190101-00:00:08  *
C         T1      20190101-00:00:09   
B         T2      20190101-00:00:01  *
B         T2      20190101-00:00:04 
B         T2      20190101-00:00:05   
C         T2      20190101-00:00:06  *
C         T2      20190101-00:00:07   
B         T2      20190101-00:00:07  *   ~
B         T2      20190101-00:00:07      ~
B         T2      20190101-00:00:08   

我想要一个查询/函数，当提供 TagId 时，每次在不同的阅读器上读取该标签时返回第一行，这是最后一次读取的(或者第一行，如果标签从未被读取过)以前读过)。根据此条件有资格返回的行在上面用 * 突出显示。如果有多行相同且“同等第一”，则应只返回其中一个(如上面标有 ~ 的行的情况)。

此功能需要高性能，因为预计数据量很容易增长到数百万/数十亿行。我可以创建所需的任何索引。

我的 SQL 生锈了，一开始就不是很好，所以非常感谢您的帮助!

Answer 1

使用lag()就像其他人已经建议的那样。但是您指定:

when supplied with a TagId

所以你可以简化。也快一点:

SELECT reader_id, tag_id, ts
FROM  (
   SELECT *, lag(reader_id) OVER (ORDER BY ts) IS DISTINCT FROM reader_id AS pick
   FROM   tbl
   WHERE  tag_id = 'T1'  --  your tag_id here
   ) sub
WHERE  pick;

db<> fiddle here

也适用于 reader_id 列中的 NULL 值。

您可以将其包装在 SQL 函数或准备好的语句中，并且只传递您的 tag_id。