快照视图

快照视图

当前版本: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