CQL supports two main categories of functions:
The scalar functions, which simply take a number of values and produce an output with it.
The aggregate functions, which are used to aggregate multiple rows of results from a
SELECT statement.
Supported starting from Scylla version 2.1
The cast function can be used to convert one native datatype to another.
The following table describes the conversions supported by the cast function. Scylla will silently ignore any cast converting a cast datatype into its own datatype.
From |
To |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
The conversions rely strictly on Java’s semantics. For example, the double value 1 will be converted to the text value ‘1.0’. For instance:
SELECT avg(cast(count as double)) FROM myTable
The token function computes a token for a given partition key. The exact signature of the token function
depends on the table concerned and on the partitioner used by the cluster.
The arguments of the token depend on the type of the partition key columns that are used. The return type depends on
the partitioner in use:
For Murmur3Partitioner, the return type is bigint.
For instance, in a cluster using the default Murmur3Partitioner, if a table is defined by:
CREATE TABLE users (
userid text PRIMARY KEY,
username text,
)
The token function accepts single argument of type text (in that case, the partition key is userid
(there are no clustering columns, so the partition key is the same as the primary key)), and the return type will be
bigint.
The uuid function takes no parameters and generates a random type 4 uuid suitable for use in INSERT or
UPDATE statements.
Note
We called the values generated by minTimeuuid and maxTimeuuid fake UUID because they do no respect
the Time-Based UUID generation process specified by the RFC 4122. In
particular, the value returned by these two methods will not be unique. This means you should only use those methods
for querying (as in the example above). Inserting the result of those methods is almost certainly a bad idea.
New in version 2.3.
The following functions can be used to retrieve the date/time at the time where the function is invoked:
Function name |
Output type |
|---|---|
|
|
|
|
|
|
|
|
For example, to retrieve data up to today, run the following query:
SELECT * FROM myTable WHERE date >= currentDate()
A number of functions are provided to “convert” a timeuuid, a timestamp, or a date into another native
type.
Function name |
Input type |
Description |
|---|---|---|
|
|
Converts the |
|
|
Converts the |
|
|
Converts the |
|
|
Converts the |
|
|
Converts the |
|
|
Converts the |
|
|
Converts the |
|
|
Similar to |
|
|
Similar to |
A number of functions are provided to “convert” the native types into binary data (blob). For every
<native-type> type supported by CQL (a notable exception is a blob, for obvious reasons), the function
typeAsBlob takes an argument of type type and returns it as a blob. Conversely, the function blobAsType
takes a 64-bit blob argument and converts it to a bigint value. For example, bigintAsBlob(3) is
0x0000000000000003 and blobAsBigint(0x0000000000000003) is 3.
Aggregate functions work on a set of rows. They receive values for each row and return one value for the whole set.
If normal columns, scalar functions, UDT fields, writetime, or ttl are selected together with
aggregate functions, the values returned for them will be the ones of the first row matching the query.
Note
The LIMIT and PER PARTITION LIMIT used in the SELECT query will have no effect if the limit is greater
than or equal to 1 because they are applied to the output of the aggregate functions (which return one value for
the whole set of rows).
The count function can be used to count the rows returned by a query. Example:
SELECT COUNT (*) FROM plays;
SELECT COUNT (1) FROM plays;
It also can be used to count the non-null value of a given column:
SELECT COUNT (scores) FROM plays;
The max and min functions can be used to compute the maximum and the minimum value returned by a query for a
given column. For instance:
SELECT MIN (players), MAX (players) FROM plays WHERE game = 'quake';
The sum function can be used, to sum up all the values returned by a query for a given column. For instance:
SELECT SUM (players) FROM plays;
The avg function can be used to compute the average of all the values returned by a query for a given column. For
instance:
SELECT AVG (players) FROM plays;
Apache Cassandra Query Language
Copyright
© 2016, The Apache Software Foundation.
Apache®, Apache Cassandra®, Cassandra®, the Apache feather logo and the Apache Cassandra® Eye logo are either registered trademarks or trademarks of the Apache Software Foundation in the United States and/or other countries. No endorsement by The Apache Software Foundation is implied by the use of these marks.