SQL-like query language for csv

Analytic Functions

Analytic functions calculate values of groups. Analytic Functions can be used only in Select Clause and Order By Clause

name description
ROW_NUMBER Return sequential numbers
RANK Return ranks
DENSE_RANK Return ranks without any gaps in the ranking
CUME_DIST Return cumulative distributions
PERCENT_RANK Return relative ranks
NTILE Return the number of groups
FIRST_VALUE Return the first value in a group
LAST_VALUE Return the last value in a group
NTH_VALUE Return the n-th value in a group
LAG Return the value in a previous row
LEAD Return the value in a following row
COUNT Return the number of values in a group
MIN Return the minimum value in a group
MAX Return the maximum value in a group
SUM Return the sum of values in a group
AVG Return the average of values in a group
STDEV Return the sample standard deviation of values
STDEVP Return the population standard deviation of values
VAR Return the sample variance of values
VARP Return the population variance of values
MEDIAN Return the median of values in a group
LISTAGG Return the concatenated string of values in a group
JSON_AGG Return the string formatted in JSON array of values in a group

Basic Syntax

analytic_function
  : function_name([args]) OVER ([partition_clause] [order_by_clause [windowing_clause]])

args
  : value [, value ...]

partition_clause
  : PARTITION BY value [, value ...]

windowing_clause
  : ROWS window_position
  | ROWS BETWEEN window_frame_low AND window_frame_high

window_position
  : {UNBOUNDED PRECEDING|offset PRECEDING|CURRENT ROW}

window_frame_low
  : {UNBOUNDED PRECEDING|offset PRECEDING|offset FOLLOWING|CURRENT ROW}

window_frame_high
  : {UNBOUNDED FOLLOWING|offset PRECEDING|offset FOLLOWING|CURRENT ROW}

value
value
order_by_clause
Order By Clause
offset
integer

Analytic Functions sort the result set by order_by_clause and calculate values within each of groups partitioned by partition_clause. If there is no partition_clause, then all records of the result set are dealt with as one group.

Definitions

ROW_NUMBER

ROW_NUMBER() OVER ([partition_clause] [order_by_clause])
partition_clause
Partition Clause
order_by_clause
Order By Clause
return
integer

Returns the sequential numbers of records in a group.

RANK

RANK() OVER ([partition_clause] [order_by_clause])
partition_clause
Partition Clause
order_by_clause
Order By Clause
return
integer

Returns the ranks of records in a group.

DENSE_RANK

DENSE_RANK() OVER ([partition_clause] [order_by_clause])
partition_clause
Partition Clause
order_by_clause
Order By Clause
return
integer

Returns the ranks of records without any gaps in the ranking in a group.

CUME_DIST

CUME_DIST() OVER ([partition_clause] [order_by_clause])
partition_clause
Partition Clause
order_by_clause
Order By Clause
return
float

Returns the cumulative distributions in a group. The return value is greater than 0 and less than or equal to 1.

PERCENT_RANK

PERCENT_RANK() OVER ([partition_clause] [order_by_clause])
partition_clause
Partition Clause
order_by_clause
Order By Clause
return
float

Returns the relative ranks in a group. The return value is greater than or equal to 0 and less than or equal to 1.

NTILE

NTILE(number_of_groups) OVER ([partition_clause] [order_by_clause])
number_of_groups
integer
partition_clause
Partition Clause
order_by_clause
Order By Clause
return
integer

Splits the records into number_of_groups groups, then returns the sequential numbers of the groups.

FIRST_VALUE

FIRST_VALUE(expr) [IGNORE NULLS] OVER ([partition_clause] [order_by_clause [windowing_clause]])
expr
value
partition_clause
Partition Clause
order_by_clause
Order By Clause
return
primitive type

Returns the first value in a group. If IGNORE NULLS keywords are specified, then returns the first value that is not a null.

LAST_VALUE

LAST_VALUE(expr) [IGNORE NULLS] OVER ([partition_clause] [order_by_clause [windowing_clause]])
expr
value
partition_clause
Partition Clause
order_by_clause
Order By Clause
return
primitive type

Returns the last value in a group. If IGNORE NULLS keywords are specified, then returns the last value that is not a null.

NTH_VALUE

NTH_VALUE(expr, n) [IGNORE NULLS] OVER ([partition_clause] [order_by_clause [windowing_clause]])
expr
value
n
integer
partition_clause
Partition Clause
order_by_clause
Order By Clause
return
primitive type

Returns the n-th value in a group. If IGNORE NULLS keywords are specified, then returns the n-th value excluding null values.

LAG

LAG(expr [, offset [, default]]) [IGNORE NULLS] OVER ([partition_clause] [order by clause])
expr
value
offset
integer

The number of rows from current row. The default is 1.

default
value

The value to set when the offset row does not exist. The default is NULL.

partition_clause
Partition Clause
order_by_clause
Order By Clause
return
float or integer

Returns the value in a previous row. If IGNORE NULLS keywords are specified, then rows that expr values are null will be skipped.

LEAD

LEAD(expr [, offset [, default]]) [IGNORE NULLS] OVER ([partition_clause] [order by clause])
expr
value
offset
integer

The number of rows from current row. The default is 1.

default
value

The value to set when the offset row does not exist. The default is NULL.

partition_clause
Partition Clause
order_by_clause
Order By Clause
return
float or integer

Returns the value in a following row. If IGNORE NULLS keywords are specified, then rows that expr values are null will be skipped.

COUNT

COUNT([DISTINCT] expr) OVER ([partition_clause] [order_by_clause [windowing_clause]])
expr
value
partition_clause
Partition Clause
return
integer

Returns the number of non-null values of expr.

COUNT([DISTINCT] *) OVER ([partition_clause])
partition_clause
Partition Clause
return
integer

Returns the number of all values including null values.

MIN

MIN(expr) OVER ([partition_clause] [order_by_clause [windowing_clause]])
expr
value
partition_clause
Partition Clause
return
primitive type

Returns the minimum value of non-null values of expr. If all values are null, then returns a null.

MAX

MAX(expr) OVER ([partition_clause] [order_by_clause [windowing_clause]])
expr
value
partition_clause
Partition Clause
return
primitive type

Returns the maximum value of non-null values of expr. If all values are null, then returns a null.

SUM

SUM([DISTINCT] expr) OVER ([partition_clause] [order_by_clause [windowing_clause]])
expr
value
partition_clause
Partition Clause
return
float or integer

Returns the sum of float values of expr. If all values are null, then returns a null.

AVG

AVG([DISTINCT] expr) OVER ([partition_clause] [order_by_clause [windowing_clause]])
expr
value
partition_clause
Partition Clause
return
float or integer

Returns the average of float values of expr. If all values are null, then returns a null.

STDEV

STDEV([DISTINCT] expr) OVER ([partition_clause] [order_by_clause [windowing_clause]])
expr
value
partition_clause
Partition Clause
return
float or integer

Returns the sample standard deviation of float values of expr. If all values are null, then returns a null.

STDEVP

STDEVP([DISTINCT] expr) OVER ([partition_clause] [order_by_clause [windowing_clause]])
expr
value
partition_clause
Partition Clause
return
float or integer

Returns the population standard deviation of float values of expr. If all values are null, then returns a null.

VAR

VAR([DISTINCT] expr) OVER ([partition_clause] [order_by_clause [windowing_clause]])
expr
value
partition_clause
Partition Clause
return
float or integer

Returns the sample variance of float values of expr. If all values are null, then returns a null.

VARP

VARP([DISTINCT] expr) OVER ([partition_clause] [order_by_clause [windowing_clause]])
expr
value
partition_clause
Partition Clause
return
float or integer

Returns the population variance of float values of expr. If all values are null, then returns a null.

MEDIAN

MEDIAN([DISTINCT] expr) OVER ([partition_clause] [order_by_clause [windowing_clause]])
expr
value
partition_clause
Partition Clause
return
float or integer

Returns the median of float or datetime values of expr. If all values are null, then returns a null.

Even if expr values are datetime values, this function returns a float or integer value. The return value can be converted to a datetime value by using the DATETIME function.

LISTAGG

LISTAGG([DISTINCT] expr [, separator]) OVER ([partition_clause] [order by clause])
expr
value
separator
string
partition_clause
Partition Clause
order_by_clause
Order By Clause

Returns the string result with the concatenated non-null values of expr. If all values are null, then returns a null.

separator is placed between values. Empty string is the default.

JSON_AGG

JSON_AGG([DISTINCT] expr) OVER ([partition_clause] [order by clause])
expr
value
partition_clause
Partition Clause
order_by_clause
Order By Clause

Returns the string formatted in JSON array of expr.