Learning Apache Drill

Appendix A. List of Drill Functions

This appendix contains a list of Apache Drill functions that are available in queries as of Drill 1.14. Because Drill is a rapidly evolving open source tool, this list is current at the time of writing but will certainly grow.

Aggregate and Window Functions

Drill has many functions that perform aggregate operations on a column or columns of data. You should use these functions (see Table A-1) in conjunction with the GROUP BY clause, as shown here:

SELECT col1, AVG(column) AS col_avg
FROM data
GROUP BY col1

Table A-1. Drill aggregate functions

Function	Output	Description
AVG(column)	Same as data argument type	Returns the average of the expression
COUNT(*)	BIGINT	Returns the number of rows
COUNT(DISTINCT)	BIGINT	Returns the number of unique values and the number of times they occurred
MAX(column)	Same as argument type	Returns the largest value in the expression
MIN(column)	Same as argument type	Returns the smallest value in the expression
STDDEV(column)	DECIMAL	Returns the standard deviation of the column
STDDEV_POP(column)	DECIMAL	Returns the sample standard deviate of input column
STDDEV_SAMP(column)	DECIMAL	Returns the population standard deviate of input column
SUM(column)	Same as argument type	Returns the sum of the given column
VARIANCE(column)	DECIMAL	Sample variance of input values (sample standard deviation squared)
VAR_POP(column)	DECIMAL	Population ...