Azure Cosmos Cassandra DB GROUP BY查询报错：聚合与非聚合选择器共存

问题场景 #

创建表并插入测试数据：

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;

错误信息：Cannot have aggregate and non-aggregate selectors in query

原因分析 #

Azure Cosmos DB的Cassandra API对CQL聚合查询的语法支持有严格限制：当查询包含聚合函数（如MAX）时，SELECT子句不能同时存在非聚合字段和聚合函数——即使这些非聚合字段是GROUP BY的成员。而标准Apache Cassandra 3.11.x允许此类写法，但Cosmos的Cassandra API并未完全兼容该特性。

解决方案 #

由于weatherstation_id和date是复合分区键，每个分组内的这两个字段值唯一，因此可以用聚合函数（如MAX()或MIN()）包裹这两个字段，既符合Cosmos API的语法要求，又能得到正确的分组字段值。

修改后的查询语句：

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

结果验证 #

执行上述查询后，会返回预期的聚合结果：

weatherstation_id | date       | max_temperature
-------------------+------------+-----------------
         1234WXYZ | 2016-04-03 |              73
         1234WXYZ | 2016-04-04 |              74

内容的提问来源于stack exchange，提问作者Amrutha