Functions¶
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.
Scalar functions¶
Native functions¶
Cast¶
Supported starting from Scylla version 2.1
The cast
function can be used to converts 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
Token¶
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
.
Uuid¶
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.
Datetime functions¶
New in version 2.3.
Retrieving the current date/time¶
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 the last two days of data, run the following query:
SELECT * FROM myTable WHERE date >= currentDate() - 2d
Time conversion functions¶
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 |
Blob conversion functions¶
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¶
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.
Native aggregates¶
Count¶
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;
Max and Min¶
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';
Sum¶
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;
Avg¶
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.