Cassandra 错误 - Order By 仅在分区键受 EQ 或 IN 限制时支持 [英] Cassandra error - Order By only supported when partition key is restricted by EQ or IN

问题描述

这是我正在创建的表格，该表格包含有关上次参加 Mundial 杯的球员的信息.

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;

我收到此错误:

Order By 仅在分区键受 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.

您已经定义了一个包含分区键name"、聚集列day"、month"、year"和各种其他列的表.

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;

不包括日"或月"

而不是

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 错误 - Order By 仅在分区键受 EQ 或 IN 限制时支持的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！