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>

推荐答案

您没有指定最后 n 个由什么".

You didn't specify last n "by what".

获取每个 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 中有很多关于时间序列的信息.我建议阅读一些关于这个问题的最新文章.

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.

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