Aggregations

This article introduces aggregate functions and their uses in Knowi. Aggregate functions allow you to summarize or change the granularity of your data.

Function	Syntax	Purpose
count	count(fieldName)	Returns the count of non-null values in the column
sum	sum(fieldName)	Returns the sum of the column
avg	avg(fieldName)	Returns the average of the column
distinct	distinct(fieldName)	Returns the count of distinct non-null values in the column
max	max(fieldName)	Returns the maximum of an expression across all records.
min	min(filedName)	Returns the minimum of an expression across all records.
sd	sd(fieldName)	Returns the statistical standard deviation of all values in the given expression
median	median(fieldName)	Returns the median of an expression across all records. Median can only be used with numeric fields. Null values are ignored.

Aggregations functions enable grouping/dimensions from the data.

 

Without GROUP BY

 select sum(sent)
 select sum(sent), avg(sent), count(*), median(sent), max(sent), min(sent)

 

With GROUP BY

Enables aggregations based on one or more groups/dimensions.

  select sum(sent) as Total Sent, Customer group by Customer

 

ARRAY

Combines values on multiple rows of a given field into an array based on group by field(s).

ARRAY(<field>)
ARRAY(<field>, <remove-duplicates-flag>)
ARRAY(<field>, <remove-duplicates-flag>, <filter-out-null-flag>)

select Stock, array(Price) as Trends group by Stock
select Stock, array(Price, true, true) as Trends group by Stock

 

Window Functions (OVER with PARTITION BY)

Window functions perform calculations across a set of rows that are related to the current row, similar to aggregate functions but without collapsing the rows into a single output row. Currently, only aggregate window functions are supported.

 

Syntax

AGGREGATE_FUNCTION(column) OVER (PARTITION BY partition_column[, ...])
AGGREGATE_FUNCTION(column) OVER ()

Supported Window Functions

• SUM(column) OVER (PARTITION BY ...) - Sum values within each partition
• AVG(column) OVER (PARTITION BY ...) - Average values within each partition
• COUNT(*) OVER (PARTITION BY ...) - Count rows within each partition
• MAX(column) OVER (PARTITION BY ...) - Maximum value within each partition
• MIN(column) OVER (PARTITION BY ...) - Minimum value within each partition

All window functions also support OVER () without PARTITION BY to calculate over all rows.

 

Examples:
Get total sales per category for each row:

select id, category, amount, SUM(amount) OVER (PARTITION BY category) as category_total

Get maximum salary per department:

select employee_id, department, salary, MAX(salary) OVER (PARTITION BY department) as max_dept_salary

Count orders per customer:

select order_id, customer, order_date, COUNT(*) OVER (PARTITION BY customer) as customer_order_count

Get grand total for all rows:

select id, amount, SUM(amount) OVER () as grand_total

Mix window functions with and without partitions:

select id, region, sales, 
SUM(sales) OVER () as total_sales,
SUM(sales) OVER (PARTITION BY region) as regional_sales

Multiple partitions:

select year, quarter, region, revenue, SUM(revenue) OVER (PARTITION BY year, quarter) as quarter_total

Multiple window functions in one query:

select product, region, sales,
SUM(sales) OVER (PARTITION BY region) as region_total,
AVG(sales) OVER (PARTITION BY product) as product_avg

Note:

• Window functions with ORDER BY inside OVER() clause are not currently supported
• RANK() window function is not currenlty supported