The following list shows a set of handy functions:

Name Description
CAST() Converts data to other type.
CUMSUM() Calculates the cumulative sum of data in each group.
CUT() Converts values into different intervals.
DIFF() Calculates the difference of specified data between current row and previous row.
GROUP_RANK() Returns rank for each (grouped) row.
GROUP_ROW_NUMBER() Returns row number in each group.

CAST

cast(expression AS new_type)

CAST function converts expression to new_type of data. The following table shows the available conversion between types:

source type new type
INT8 INT64, DOUBLE
INT16 INT32, INT64, DOUBLE
INT32 INT64, DOUBLE
INT64 DOUBLE
FLOAT INT64, DOUBLE
DOUBLE INT64
STRING INT8, INT16, INT32, INT64, FLOAT, DOUBLE
CHAR INT8, INT16, INT32, INT64, FLOAT, DOUBLE, CHAR, STRING
VARSTRING INT8, INT16, INT32, INT64, FLOAT, DOUBLE, STRING
DATETIME32 DATE32, DATETIME64
DATETIME64 DATE32, DATETIME32
TIMESTAMP DATE32, DATETIME32, DATETIME64
HYPERLOGLOG INT64

CUT

cut(col_name, cut_point, interval, span_num)

CUT function converts values in col_name into different intervals.

  • cut_point specifies the break point where the intervals are spanned into both sides from the break point.
  • interval denotes the size of each interval.
  • span_num specifies the number of intervals for each side from the cut point. The default value is 3.

For example, cut(data, 5, 2, 2) will convert value in data column into intervals (-,1], (1,3], (3,5], (5,7], (7,9], (9,-].


CUMSUM

cumsum(data, [group])

CUMSUM returns the cumulative sum of data for each group. Note that it is assumed that table is in order by the group column. If group is not given, the whole table is deemed as one group.


DIFF

diff(data, [group])

DIFF function calculates the difference of data between current row and previous row. The diff value for the first row within the same group is set to 0. If group is not given, the whole table is deemed as one single group.


GROUP_RANK

GROUP_RANK[group])

GROUP_RANK returns rank number for each row. Note that each row in the same group has the same rank number. If group is not given, the whole table is treated as one single group.


GROUP_ROW_NUMBER

GROUP_ROW_NUMBER([group])

GROUP_ROW_NUMBER returns row number within the samp group. If group is not given, the whole table is treated as one single group.