Apache Drill supports the Aggregation functions that we can use to apply on the input expressions such as AVG function will return the average value, COUNT function will return the number of rows matches in the expression, MIN and Max function will return the minimum and maximum value of the expression, SUM function will sum the expression and so on.
The following is the list of Drill Aggregate functions.
| Drill Aggregate Functions | Argument Type |
|---|---|
| ANY_VALUE(expression) | BIT, INT, BIGINT, FLOAT4, FLOAT8, DATE, TIMESTAMP, TIME, VARCHAR, VARBINARY, LIST, MAP, INTERVAL, INTERVALDAY, INTERVALYEAR, VARDECIMAL |
| AVG(expression) | SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL, INTERVAL |
| BOOL_AND(expression), BOOL_OR(expression) | BIT |
| BIT_AND(expression), BIT_OR(expression) | INT, BIGINT |
| COUNT(*) | * |
| COUNT([DISTINCT] expression) | any |
| MAX(expression), MIN(expression) | BINARY, DECIMAL, VARCHAR, DATE, TIME, or TIMESTAMP |
| SUM(expression) | SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL, INTERVAL |
Now we will see a couple of examples of the Drill Aggregate function.
1. Apache Drill AVG Function
Drill AVG function works on the expression and returns the average value of the numerical expression.
AVG Function Syntax:
The following is the syntax of the AVG Function.
AVG([ALL | DISTINCT] expression)
AVG Example
In the below example, we are taking the average salary of employee_id 1, 2, 3,4,5;
Command:
Output:
2. Apache Drill COUNT(*) Function
The drill count function is used to count the number of records, for example, if we are passing "*" then it will count the number of records present in the dataset.
COUNT(*) Function Syntax:
The following is the syntax of the COUNT(*) Function.
COUNT(*)
COUNT(*) Example
We will count the number of records present in the "employee.json" file.
Command:
Output:
3. Apache Drill MIN and MAX Function
Drill MIN and MAX functions are used to return the minimum and maximum values of an expression.
MIN and MAX Function Syntax:
The following is the syntax of the MIN and MAX Function.
MIN(expression)
MAX(expression)
MIN and MAX Function Examples
In this example, we will see the minimum and maximum birth date of the employee.
Command:
Output:
