[
https://issues.apache.org/jira/browse/CASSANDRA-9778?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14628427#comment-14628427
]
Brian Hess commented on CASSANDRA-9778:
----------------------------------------
Did you mean to have DAY be an argument to these functions: "day_time(dtime,
1h)"? Or is implied to be the "hour" of Now()?
Do we want to support "Day_time(dtime, 2h) as doublehour"? Or are the options
really Day_time(dtimedtime, 1h), Day_time(dtime, 1d), Day_time(dtime, 1w), etc?
If so, Oracle has syntax for extracting the parts of the date or datetime type:
https://docs.oracle.com/cd/E17952_01/refman-5.1-en/date-and-time-functions.html#function_hour
For example "Day(dtime)", "Hour(dtime)", etc.
Postgres also has this, though their syntax is a bit fancier:
http://www.postgresql.org/docs/8.2/static/functions-datetime.html
For example "Extract(hour FROM dtime)", "Extract(day FROM dtime), etc.
So, while windowed aggregates are overkill here (and really, they are), if we
want this functionality in Cassandra, we should consider the similar syntax in
Postgres or Oracle (or DB2 (Day(), Month(), etc), MySQL (Day(), Month(), etc),
SQL Server (DatePart()), etc).
> CQL support for time series aggregation
> ---------------------------------------
>
> Key: CASSANDRA-9778
> URL: https://issues.apache.org/jira/browse/CASSANDRA-9778
> Project: Cassandra
> Issue Type: New Feature
> Components: API, Core
> Reporter: Jonathan Ellis
> Fix For: 3.x
>
>
> Along with MV (CASSANDRA-6477), time series aggregation or "rollups" are a
> common design pattern in cassandra applications. I'd like to add CQL support
> for this along these lines:
> {code}
> CREATE MATERIALIZED VIEW stocks_by_hour AS
> SELECT exchange, day, day_time(1h) AS hour, symbol, avg(price), sum(volume)
> FROM stocks
> GROUP BY exchange, day, symbol, hour
> PRIMARY KEY ((exchange, day), hour, symbol);
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
