Cassandra基于时间的查询 [英] Cassandra time based query

问题描述

我有以下Cassandra表，记录用户对网页的访问。

I have the following Cassandra table which records the user access to a web page.

create table user_access (
    id timeuuid primary key,
    user text,
    access_time timestamp
);

，并希望执行以下查询：

获取列表的用户在过去一小时内访问该网页的次数超过10次。

and would like to do a query like this:
get the list of users who access the page for more than 10 times in the last hour.

是否可以在Cassandra中执行此操作？ （我有点卡住有限的CQL查询功能）_
如果不是，我如何重新建模表这样做？

Is it possible to do it in Cassandra? (I'm kind of stuck with the limited CQL query functionalities)
If not, how do I remodel the table to do this?

推荐答案

你能做到吗？是。
你能有效地做到吗？我不信服。

Can you do it? yes. Can you do it efficiently? I'm not convinced.

您不清楚您使用的时间表示代表。

It's not clear what the timeuuid you are using represents.

这到

CREATE TABLE user_access (
  user text,
  access_time timestamp,
  PRIMARY KEY (user_id, access_time)
);

SELECT COUNT(*)
FROM user_access
WHERE user_id = '101'
  AND access_time > 'current unix timestamp - 3600'
  AND access_time < 'current unix timestamp';

然后使用您选择的语言自行筛选结果。我不会抱着你的呼吸等待子查询支持。

Then filter the results on your own in your language of choice. I wouldn't hold your breathe waiting for sub query support.

如果你有很多用户，这将是非常低效的。

That's going to be horribly inefficient if you have lots of users though.

可能有一个更好的解决方案使用cql的计数器列和binning访问的小时的开始。这可能让你每小时访问，但是不一样的在最后一小时内。

There may be a better solution using cql's counter columns and binning accesses to the start of the hour. That could get you per hour accesses, but that's not the same as within the last hour.

这篇关于Cassandra基于时间的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！