快照视图

当前版本:v1.4, 更新时间:2019-10-18

松果时序数据库为每个数据表提供一个快照视图,快照视图包含该表中每个设备最新的一条数据。若某个设备未添加过数据,则不会出现在快照视图中;快照视图名为表名后加".snapshot",例如:表tab01的快照视图为tab01.snapshot
快照视图使用实例:

--1. 创建表
CREATE TABLE testSnap
(
  devid bigint,
  tstamp datetime,
  val01 bool,
  val02 bigint
)

--2. 创建设备
INSERT INTO sys_dev(tabname, devid, devname)
VALUES('testSnap', 1, 'device 1'),
('testSnap', 2, 'device 2'),
('testsnap', 3, 'device 3')

--3. 插入数据
INSERT INTO testSnap(devid, tstamp, val01, val02)
VALUES(1, now(), true, 101),(2, now(), false, 201)

--4. 查询快照
SELECT * FrOM testSnap.snapshot
--结果集
--devid | tstamp              | val01 | val02
--    1 | 2019-10-14 15:37:41 |  true | 101
--    2 | 2019-10-14 15:37:41 | false | 201
--从查询结果可知,设备3未添加过数据,故不会出现在快照视图中

--5. 继续插入数据
INSERT INTO testSnap(devid, tstamp, val01, val02)
VALUES(2, now(), true, 202),(3, now(), false, 301)

--6. 查询快照
SELECT * FROM testSnap.snapshot
--结果集
--devid | tstamp              | val01 | val02
--    1 | 2019-10-14 15:37:41 |  true | 101
--    2 | 2019-10-14 15:42:27 |  true | 202
--    3 | 2019-10-14 15:42:27 | false | 301
--从查询结果可知,每个设备只有最新的一条数据在快照视图中

下面是利用快照视图提供的一些功能,使用上例中testSnap表:

  • 查询快照中满足某些条件的设备

SELECT * 
FROM testSnap.snapshot
WHERE devid in (1, 3, 5) AND val01 = true
  • 以5分钟作为设备离线的标准,即,某设备5分钟之内未上传新数据则认为该设备已离线:

--1. 查询离线设备的总数
SELECT count(*) AS cnt
FROM testSnap.snapshot
WHERE tstamp < now(-5m)

--2. 查询离线的设备,前1000条
SELECT devid
FROM testSnap.snapshot
WHERE tstamp < now(-5m)
LIMIT 1000

在松果时序数据库中,每次查询最多获取10000条数据,以查询离线设备为例:若要查询的设备超过10000条,则需要多次查询,如果简单使用LIMIT子句查询,两次查询之间可能有一些数据插入会导致某些数据无法查询到。

--1.创建设备
INSERT INTO sys_dev(tabname, devid, devname)
VALUES('testSnap', 101, 'device 101'),
('testSnap', 102, 'device 102'),
('testSnap', 103, 'device 103'),
('testSnap', 104, 'device 104'),
('testSnap', 105, 'device 105'),
('testSnap', 106, 'device 106')

--2.插入数据,以5分钟离线为例,所有设备都已离线
INSERT INTO testSnap(devid, tstamp, val01, val02)
VALUES(101, now(-10m), true, 1001),
(102, now(-10m), true, 2001),
(103, now(-10m), true, 3001),
(104, now(-10m), true, 4001),
(105, now(-10m), true, 5001),
(106, now(-10m), true, 6001)

--3.查询离线数据,每次查询两条
--3.1第一次查询
SELECT * FROM testSnap.snapshot
WHERE tstamp < now(-5m)
LIMIT 0,2
--查询到设备 101,102

--3.2第二次查询
SELECT * FROM testSnap.snapshot
WHERE tstamp < now(-5m)
LIMIT 2,2
--查询到设备 103,104

--3.3第三次查询
SELECT * FROM testSnap.snapshot
WHERE tstamp < now(-5m)
LIMIT 4,2
--查询到设备 105,106

在上例中,若3.1和3.2之间其他客户端插入了设备101的数据,如下示例:

--3.1第一次查询
SELECT * FROM testSnap.snapshot
WHERE tstamp < now(-5m)
LIMIT 0,2
--查询到设备 101,102

--插入数据
INSERT INTO testSnap(devid, tstamp, val01, val02)
VALUES(101, now(), false, 1002)

--3.2第二次查询
SELECT * FROM testSnap.snapshot
WHERE tstamp < now(-5m)
LIMIT 2,2
--查询到设备 104,105

--3.3第三次查询
SELECT * FROM testSnap.snapshot
WHERE tstamp < now(-5m)
LIMIT 4,2
--查询到设备 106

设备103未被查询到,故:直接使用LIMIT子句多次查询可能会少查询到数据,正确的方式应该如下:

--1. 第一次查询
SELECT * FROM testSnap.snapshot
WHERE tstamp < now(-5m)
LIMIT 2
--查询到设备 101,102

--2. 后面的查询
SELECT * FROM testSnap.snapshot
WHERE tstamp < now(-5m) AND devid > 102
LIMIT 2

从第二次开始每次查询限定查询的设备ID大于上一次最大的设备ID,不但能保证查询的数据正确性,还能提高查询性能。