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 ...

Get Learning Apache Drill now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.