有人能告诉我如何编写select查询来返回每分钟的第一条记录吗?这里的第一条记录是指每分钟的第一条纪录(以秒为单位(。请参阅下面的示例。我的系统将定期每5秒从设备接收一次数据。我的表格结构将是这样的。
CREATE TABLE device (
deviceId text,
datetime timestamp,
temp float,
volt float,
PRIMARY KEY (deviceId, datetime)
) WITH CLUSTERING ORDER BY (datetime DESC);
是否可以提取select查询中的日期时间,对其进行处理并找到每分钟的第一条记录?
insert into device (deviceId,datetime,temp,volt)
values ('device123','2018-10-10 5:10:00+0000',0.12,6.7);
insert into device (deviceId,datetime,temp,volt)
values ('device123','2018-10-10 5:10:05+0000',3.12,61.7);
insert into device (deviceId,datetime,temp,volt)
values ('device123','2018-10-10 5:10:10+0000',2.12,16.7);
insert into device (deviceId,datetime,temp,volt)
values ('device123','2018-10-10 5:10:15+0000',1.12,26.7);
insert into device (deviceId,datetime,temp,volt)
values ('device123','2018-10-10 5:11:20+0000',4.12,11.7);
insert into device (deviceId,datetime,temp,volt)
values ('device123','2018-10-10 5:11:25+0000',5.12,12.7);
insert into device (deviceId,datetime,temp,volt)
values ('device123','2018-10-10 5:11:30+0000',6.12,23.7);
insert into device (deviceId,datetime,temp,volt)
values ('device123','2018-10-10 5:11:35+0000',7.12,126.7);
我的查询应该会给我这样的结果:
- 第10分钟,秒=00温度=0.12,电压=6.7
- 第11分钟,秒=20温度=4.12,电压=11.7
你能为我如何在卡桑德拉实现这一目标提供建议吗?
在Cassandra中,通常必须构建适合查询模式的表。因此,您可以使用上面的模型构建一个表,该表只包含每分钟数据的第一个条目。
或者,您可以构建一个表来partition
按分钟输出的数据。在这种情况下,您需要向表中再添加一个分区键minute_bucket
。此外,要每分钟收回"第一行"(或最早的一行(,请将datetime
聚类键上的排序方向翻转为升序(ASC
(。例如:
CREATE TABLE device_by_minute (
deviceId text,
datetime timestamp,
minute_bucket text,
temp float,
volt float,
PRIMARY KEY ((deviceId, minute_bucket), datetime)
) WITH CLUSTERING ORDER BY (datetime ASC);
然后(加载数据后(,使用PER PARTITION LIMIT
子句运行一个多关键字查询,如下所示:
aploetz@cqlsh:stackoverflow> SELECT * FROM device_by_minute
WHERE deviceid='device123'
AND minute_bucket IN ('2018-10-10 05:10','2018-10-10 05:11')
PER PARTITION LIMIT 1;
deviceid | minute_bucket | datetime | temp | volt
-----------+------------------+---------------------------------+------+------
device123 | 2018-10-10 05:10 | 2018-10-10 05:10:00.000000+0000 | 0.12 | 6.7
device123 | 2018-10-10 05:11 | 2018-10-10 05:11:20.000000+0000 | 4.12 | 11.7
(2 rows)
注:
- 像这样在分区键的部分上使用
IN
子句的多键查询将阻止Cassandra在查询时找出哪个节点包含数据。然后,它将指定一个"协调器节点"来处理结果集编译,以及与包含请求副本的节点的通信。相对于对完整分区键的查询,这将不会执行得很好 - 这个查询可以通过运行
SELECT * FROM device_by_minute PER PARTITION LIMIT 1;
来简化。但是,查询性能会随着数据集的增长而变差。最好限制WHERE
子句中的潜在结果集 - 我会将
IN
子句中的数字项保持在低个位数