仅选择其中一列中具有最大值的行

qq24tv8q 于 2021-06-09 发布在 Cassandra

关注(0)|答案(2)|浏览(428)

我需要找到具有给定stationid的行，这些行的time1大于指定的time和maximum time2。
表的创建方式如下：

CREATE TABLE forec (
    stationid int,
    time1 timestamp,
    time2 timestamp,
    value double,
    PRIMARY KEY ((stationid), time1, time2)
) WITH CLUSTERING ORDER BY (time1 DESC)

假设表中的数据如下：

+------------+-----------------------+----------------------+--------+
    | stationid  | time1                 |  time2               |  value |
    +------------+-----------------------+----------------------+--------+
    | 1          | 2020-10-21 06:00:00   | 2020-10-21 05:00:00  | 1      |                                  
    | 1          | 2020-10-21 06:00:00   | 2020-10-21 04:00:00  | 2      |                                   
    | 1          | 2020-10-21 06:00:00   | 2020-10-21 03:00:00  | 3      |                                   
    | 1          | 2020-10-21 05:00:00   | 2020-10-21 04:00:00  | 4      |
    | 1          | 2020-10-21 05:00:00   | 2020-10-21 03:00:00  | 5      |
    | 1          | 2020-10-21 04:00:00   | 2020-10-21 02:00:00  | 6      |
    +------------+-----------------------+----------------------+--------+

我想查询：给我stationid=1和time1>=2020-10-21 05:00:00和time2有最大值的所有行。查询应返回以下行：

+------------+-----------------------+----------------------+--------+
    | stationid  | time1                 |  time2               |  value |
    +------------+-----------------------+----------------------+--------+
    | 1          | 2020-10-21 06:00:00   | 2020-10-21 05:00:00  | 1      |        
    | 1          | 2020-10-21 05:00:00   | 2020-10-21 04:00:00  | 4      | 
    +------------+-----------------------+----------------------+--------+

我知道我可以这样问：

SELECT * FROM forec WHERE stationid = 1 AND time1 >= '2020-10-21 05:00:00';

然后在客户机上过滤结果（并且只保留最长时间为2的行），但是我想知道这是否可以更有效地完成（在cassandra端过滤结果）。
或者我该换个table型号？

cassandra cql

来源：https://stackoverflow.com/questions/64460377/cql-cassandra-select-only-the-rows-with-maximum-value-in-one-of-the-columns

2条答案

按热度按时间

kwvwclae1#

编辑：根据cassandra文档，“如果在没有聚合函数的情况下选择了一个列，那么在一个带有groupby的语句中，每个组中遇到的第一个值都将被返回。”因此下面的示例仅在 time2 存储在 DESC 命令。
如果您使用的是最新版本的cassandra（如3.11.x），那么您可以使用 GROUP BY 像这样做

SELECT
  stationid,
  time1,
  max(time2) AS max_time2,
  value
FROM
  forec
WHERE
  stationid = 1
AND
  time1 >= '2020-10-21 05:00:00'
GROUP BY time1;

然后你得到

cqlsh:test> SELECT stationid, time1, max(time2) as max_time2, value FROM forec WHERE stationid = 1 AND time1 >= '2020-10-21 05:00:00' GROUP BY  time1;

 stationid | time1                           | max_time2                       | value
-----------+---------------------------------+---------------------------------+-------
         1 | 2020-10-21 06:00:00.000000+0000 | 2020-10-21 05:00:00.000000+0000 |     1
         1 | 2020-10-21 05:00:00.000000+0000 | 2020-10-21 04:00:00.000000+0000 |     4

(2 rows)

注意，这会扫描分区，所以要注意分区大小，特别是在集群列中使用时间戳时。

赞(0）回复(0）举报 2021-06-09

z31licg02#

使用uda/UDF的解决方案：
状态函数：

CREATE OR REPLACE FUNCTION curValState ( state tuple<timestamp,double>, time timestamp, value double ) CALLED ON NULL INPUT RETURNS tuple<timestamp, double> LANGUAGE java AS 'if (time != null && value != null) { if(state == null) {com.datastax.driver.core.TupleType tupleType = com.datastax.driver.core.TupleType.of(com.datastax.driver.core.ProtocolVersion.NEWEST_SUPPORTED, com.datastax.driver.core.CodecRegistry.DEFAULT_INSTANCE, com.datastax.driver.core.DataType.timestamp(), com.datastax.driver.core.DataType.cdouble()); state = tupleType.newValue(time, value);} else {if(state.getTimestamp(0).compareTo(time)<0){ state.setTimestamp(0, time); state.setDouble(1, value);}}} return state;';

最终功能：

CREATE OR REPLACE FUNCTION finalVal ( state tuple<timestamp, double> ) CALLED ON NULL INPUT RETURNS double LANGUAGE java AS 'return state.getDouble(1);';

聚合函数：

CREATE OR REPLACE AGGREGATE valueatlatesttime (timestamp, double) SFUNC curValState STYPE tuple<timestamp, double> FINALFUNC finalVal INITCOND null;

查询：

SELECT
  stationid,
  time1,
  max(time2) AS max_time2,
  valueatlatesttime(time2, value) AS value_at_max_time2
FROM
  forec
WHERE
  stationid = 1
AND
  time1 >= '2020-10-21 05:00:00'
GROUP BY time1;

赞(0）回复(0）举报 2021-06-09

我来回答

仅选择其中一列中具有最大值的行

2条答案

相关问题

热门标签

最新问答