Cassandra cql：如何从表中选择最后n行 [英] Cassandra cql: how to select the LAST n rows from a table

问题描述

我想验证行是否已添加到表中。什么cql语句将显示下表中的最后 n行？

I want to verify that rows are getting added to the table. What cql statement would show the last n rows from the table below?

表描述如下：

cqlsh:timeseries> describe table option_data;

CREATE TABLE option_data (
  ts bigint,
  id text,
  strike decimal,
  callask decimal,
  callbid decimal,
  maturity timestamp,
  putask decimal,
  putbid decimal,
  PRIMARY KEY ((ts), id, strike)
) WITH
  bloom_filter_fp_chance=0.010000 AND
  caching='KEYS_ONLY' AND
  comment='' AND
  dclocal_read_repair_chance=0.100000 AND
  gc_grace_seconds=864000 AND
  index_interval=128 AND
  read_repair_chance=0.000000 AND
  replicate_on_write='true' AND
  populate_io_cache_on_flush='false' AND
  default_time_to_live=0 AND
  speculative_retry='99.0PERCENTILE' AND
  memtable_flush_period_in_ms=0 AND
  compaction={'class': 'SizeTieredCompactionStrategy'} AND
  compression={'sstable_compression': 'LZ4Compressor'};

cqlsh:timeseries>

推荐答案

。

要获取每个id的最后N个：

To get the last N per id:

SELECT * FROM option_data WHERE ts=1 ORDER BY id DESC LIMIT N;

ORDER BY子句只能应用于复合主键中的第二列。如果你需要按时间查询，你将需要更多地考虑你的数据模型。

ORDER BY clause can only be applied to the second column in a compound primary key. If you need to query by time you will need to think about your data model a little more.

如果你的查询最经常是最后N，你可能会考虑写像这样：

If your queries are most often "last N", you might consider writing something like this:

CREATE TABLE time_series (
    id text,
    t timeuuid,
    data text,
    PRIMARY KEY (id, t)
) WITH CLUSTERING ORDER BY (t DESC)

...其中'id'是您的时间序列ID。 CLUSTERING ORDER反转timeuuid't'的顺序，使单元格以自然顺序存储在您的查询中。

... where 'id' is your time series id. The CLUSTERING ORDER reverses the order of timeuuid 't', causing the cells to be stored in a natural order for your query.

有了这个，你会得到最后五事件如下：

With this, you would get the last five events as follows:

SELECT * FROM time_series WHERE id='stream id' LIMIT 5;

Cassandra中有很多关于时间序列的信息。我建议阅读一些最近的文章。这是简洁和相对最近的：
http://www.datastax.com/documentation/tutorials/Time_Series .pdf

There is a lot of information out there for time series in Cassandra. I suggest reading some of the more recent articles on the matter. This is concise and relatively recent: http://www.datastax.com/documentation/tutorials/Time_Series.pdf

这篇关于Cassandra cql：如何从表中选择最后n行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！