Cassandra错误-仅当分区键受EQ或IN限制时才支持“排序依据" [英] Cassandra error - Order By only supported when partition key is restricted by EQ or IN

问题描述

这是我正在创建的表格，该表格包含有关上次世界杯比赛的球员的信息.

Here is the table I'm creating, this table contains information about players that played the last mundial cup.

CREATE TABLE players ( 
      group text, equipt text, number int, position text, name text,
      day int, month int, year int, 
      club text, liga text, capitan text,
PRIMARY key (name, day, month, year));

执行以下查询时:

从担任选拔队队长的最老球员中选出5个名字

这是我的查询:

SELECT name FROM players WHERE captain='YES' ORDER BY year DESC LIMIT 5;

我收到此错误:

订购仅当分区键受EQ或IN限制时受支持

我认为正在创建的表存在问题，但我不知道如何解决.

I think is a problem about the table I'm creating, but I don't know how to solve it.

谢谢.

推荐答案

您的表定义对于您要运行的查询不正确.

Your table definition is incorrect for the query you're trying to run.

您已经定义了一个表，该表具有分区键名称"，群集列天"，月"，年"以及其他各个列.

You've defined a table with partition key "name", clustering columns "day", "month", "year", and various other columns.

在Cassandra中，所有SELECT查询都必须使用EQ或IN指定分区键.您可以使用在SQL中使用的相等和不相等运算符来包括部分或全部集群列.

In Cassandra all SELECT queries must specify a partition key with EQ or IN. You're permitted to include some or all of the clustering columns, using the equality and inequality operators you're used to in SQL.

必须按定义的顺序包含聚类列.同样，ORDER BY子句只能按定义的顺序包含尚未由EQ特定的聚簇列.

The clustering columns must be included in the order they're defined. An ORDER BY clause can only include clustering columns that aren't already specific by an EQ, again in the order they're defined.

例如，您可以编写查询

select * from players where name = 'fiticida' and day < 5 order by month desc;

或

select * from players where name = 'fiticida' and day = 10 and month > 2 order by month asc;

但不是

select * from players where name = 'fiticida' and year = 2017;

其中不包含天"或月"

which doesn't include "day" or "month"

不是

select * from players where name = 'fiticida' and day = 5 order by year desc;

其中不包含月".

此处是有关SELECT查询.

Here is the official documentation on the SELECT query.

为了满足您的查询，表需要

To satisfy your query, the table needs

• 由EQ或IN指定的分区键:"captain"将起作用
• 使用最左侧的群集列的ORDER BY子句:在主键定义的月"和日"的左边放置年"

这篇关于Cassandra错误-仅当分区键受EQ或IN限制时才支持“排序依据"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！