Lindorm:Functions

Lindorm CQL supports the following types of functions: scalar functions and native aggregate functions.

• Scalar functions are used to obtain values and generate outputs.

• Native aggregate functions are used to aggregate values of multiple rows that are returned by a SELECT statement.

The following table describes common scalar functions and native aggregate functions.

Cast

The cast function is used to convert data from one native data type into another data type.

Data types

The following table lists the source data types that can be converted by the cast function and the destination data types to which each source data type can be converted.

Usage notes

The conversions strictly rely on the semantics of Java.

Example

CREATE TABLE persioninfo (c1 int PRIMARY KEY, c2 timeuuid);
SELECT avg(cast(c1 as double)) FROM persioninfo;

Now

The now function is used to return the current time.

The now function accepts no parameters. When you call this function, the system generates a new unique value in the timeuuid format on a coordinator node. If you specify the now function in a WHERE clause, no result is returned. This way, each value returned by the now function is unique. The following sample code provides an example:

SELECT * FROM persioninfo WHERE c2 = now();

maxTimeuuid function and minTimeuuid function

The maxTimeuuid function accepts a timestamp value that can be a timestamp or a date string and returns the largest TimeUUID for the timestamp value. The minTimeuuid function accepts a timestamp value that can be a timestamp or a date string and returns the smallest TimeUUID for the timestamp value.

Example

SELECT * FROM persioninfo WHERE c2 > maxTimeuuid('2013-01-01 00:05+0000')    AND c2 < minTimeuuid('2013-02-02 10:00+0000') ALLOW FILTERING ;

Datetime

A date or time-related function is used to retrieve the date or point in time when the function is called.

Time Conversion

A time conversion function is used to convert timeuuid data, timestamps, or dates into native data types supported by Lindorm CQL.

Blob Conversion

A blob conversion function is used to convert native data types into binary data or blobs.

For each native data type supported by Lindorm CQL, the typeAsBlob function accepts a parameter value of that data type and returns the value as a blob. The blobAsType function accepts only 64-bit binary data and converts the data into a bigint value. For example, bigintAsBlob(3) returns 0x0000000000000003 and blobAsBigint(0x0000000000000003) returns 3.

CREATE TABLE persioninfo (c1 text PRIMARY KEY, c2 bigint);
INSERT INTO persioninfo (c1, c2) VALUES ( '11', blobAsBigint(0x0000000000000003));

Count

The count function is used to count the number of non-empty values in a specified column. This function can also be used to count the number of returned rows.

Examples

Calculate the number of returned rows.

SELECT COUNT (*) FROM persioninfo;
SELECT COUNT (1) FROM persioninfo;

Calculate the number of non-empty values in a specified column.

SELECT COUNT (c2) FROM persioninfo;

max function and min function

The max function is used to return the maximum value in a specified column. The min function is used to return the minimum value in a specified column.

Example

CREATE TABLE persioninfo (c1 text PRIMARY KEY, c2 int);
INSERT INTO persioninfo (c1, c2) VALUES ('k1', 3);
INSERT INTO persioninfo (c1, c2) VALUES ('k2', 4);
SELECT min(c2), max(c2) FROM persioninfo;

Sum

The sum function is used to calculate the sum of all values in a specified column.

Example

SELECT sum(c2) FROM persioninfo ;

Avg

The avg function is used to calculate the average of all values in a specified column.

Example

SELECT avg(c2) FROM persioninfo ;