[ https://issues.apache.org/jira/browse/CASSANDRA-18060?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17642564#comment-17642564 ]
Andres de la Peña commented on CASSANDRA-18060: ----------------------------------------------- Sure, let's see an example of how the previously existing aggregate functions and the new collection functions are different: {code:java} > CREATE TABLE t (k int PRIMARY KEY, l list<int>); > INSERT INTO t(k, l) VALUES (1, [1, 2, 3]); > INSERT INTO t(k, l) VALUES (2, [4, 5, 6]); > SELECT count(l), max(l) FROM t; system.count(l) | system.max(l) -----------------+--------------- 2 | [4, 5, 6] > SELECT collection_count(l), collection_max(l) FROM t; system.collection_count(l) | system.collection_max(l) ----------------------------+-------------------------- 3 | 3 3 | 6 {code} We need different function names to determine what type of functions we want to run. Note that this is similar for example to the difference between MySQL's [MAX function|https://www.w3schools.com/sql/func_mysql_max.asp] and [GREATEST|https://www.w3schools.com/sql/func_mysql_greatest.asp], although here we work with collections. Having used the nice name for aggregates, we need to figure out a more convoluted name for the row-per-row functions. > Add aggregation scalar functions on collections > ----------------------------------------------- > > Key: CASSANDRA-18060 > URL: https://issues.apache.org/jira/browse/CASSANDRA-18060 > Project: Cassandra > Issue Type: New Feature > Components: CQL/Semantics > Reporter: Andres de la Peña > Assignee: Andres de la Peña > Priority: Normal > Fix For: 4.2 > > Time Spent: 1h 40m > Remaining Estimate: 0h > > The new mechanism for dynamically building native functions introduced by > CASSANDRA-17811 can be used to provide within-collection aggregation > functions. We can use that mechanism to add new CQL functions to get: > * The number of items in a collection. > * The max/min items of a collection. > * The sum/avg of the items of a numeric collection. > * The keys or the values of a map. > For example: > {code:java} > CREATE TABLE k.t (k int PRIMARY KEY, l list<int>, m map<int, int>); > INSERT INTO t(k, l, m) VALUES (0, [1, 2, 3], {1:10, 2:20, 3:30}); > > SELECT map_keys(m), map_values(m) FROM t; > system.map_keys(m) | system.map_values(m) > --------------------+---------------------- > {1, 2, 3} | [10, 20, 30] > > SELECT collection_count(m), collection_count(l) FROM t; > system.collection_count(m) | system.collection_count(l) > ----------------------------+---------------------------- > 3 | 3 > > SELECT collection_min(l), collection_max(l) FROM t; > system.collection_min(l) | system.collection_max(l) > --------------------------+-------------------------- > 1 | 3 > > SELECT collection_sum(l), collection_avg(l) FROM t; > system.collection_sum(l) | system.collection_avg(l) > --------------------------+-------------------------- > 6 | 2 > {code} > Note that this type of aggregation is different from the kind of aggregation > provided by {{min}}, {{max}}, {{sum}} and {{avg}}, which aggregate entire > collections across rows. Here we only aggregate the items of a collection row > per row. -- This message was sent by Atlassian Jira (v8.20.10#820010) --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@cassandra.apache.org For additional commands, e-mail: commits-h...@cassandra.apache.org