azure - Azure Cosmos cassandra 数据库中的 "Cannot have aggregate and non-aggregate selectors in query"

Question

https://howtoprogram.xyz/2017/02/18/using-group-apache-cassandara/

我试图在 azure cosmos cassandra db 中执行上面链接中的相同示例，但出现以下错误 enter image description here

CREATE TABLE temperature_by_day (
   weatherstation_id text,
   date text,
   event_time timestamp,
   temperature float,
   PRIMARY KEY ((weatherstation_id,date),event_time)
);


INSERT INTO temperature_by_day(weatherstation_id,date,event_time,temperature)
VALUES ('1234WXYZ','2016-04-03','2016-04-03 07:01:00',73);
 
INSERT INTO temperature_by_day(weatherstation_id,date,event_time,temperature)
VALUES ('1234WXYZ','2016-04-03','2016-04-03 07:02:00',70);
 
INSERT INTO temperature_by_day(weatherstation_id,date,event_time,temperature)
VALUES ('1234WXYZ','2016-04-04','2016-04-04 07:01:00',73);
 
INSERT INTO temperature_by_day(weatherstation_id,date,event_time,temperature)
VALUES ('1234WXYZ','2016-04-04','2016-04-04 07:02:00',74);


SELECT weatherstation_id, date, MAX(temperature) FROM temperature_by_day GROUP BY weatherstation_id, date;

使用 group by 进行上述查询时出现错误

[cqlsh 5.0.1 | Cassandra 3.11.0 | CQL 规范 3.4.4 |原生协议(protocol)v4]

谁能帮忙解决这个问题吗？

enter image description here我期待这个结果。

Answer 1

FWIW，看起来这个问题在 Microsoft 的网站上得到了回答:https://learn.microsoft.com/en-us/answers/questions/1377555/cannot-have-aggregate-and-non-aggregate-selectors

The error message "Cannot have aggregate and non-aggregate selectors in query" in Azure Cosmos Cassandra DB typically occurs when you are trying to execute a query that contains both aggregate and non-aggregate selectors.

In Cassandra, you cannot mix aggregate functions (such as COUNT, SUM, AVG, MIN, MAX) with non-aggregate functions (such as SELECT, WHERE, ORDER BY) in the same query. This is because aggregate functions operate on groups of rows, while non-aggregate functions operate on individual rows.

他们一定指的是 Azure Cosmos Cassandra，因为我可以在 Apache Cassandra® 3.11 上运行 OP 问题中的所有 CQL。

Aggregate functions are supported in Azure Cosmos DB for SQL API and MongoDB API, but they are not supported for Cassandra API and Table API.

核心问题是 Azure Cosmos Cassandra 和 Apache Cassandra 之间确实存在差异。请记住，各位，仅仅因为云提供商在其产品名称中包含“Cassandra”或“Keyspaces”一词，并不意味着它与 Apache Cassandra 100% 兼容。