INLINE STATS

Stack Planned Serverless Preview

The INLINE STATS processing command groups rows according to a common value and calculates one or more aggregated values over the grouped rows. The results are appended as new columns to the input rows.

The command is identical to STATS except that it preserves all the columns from the input table.

Syntax

INLINE STATS [column1 =] expression1 [WHERE boolean_expression1][,
      ...,
      [columnN =] expressionN [WHERE boolean_expressionN]]
      [BY [grouping_name1 =] grouping_expression1[,
          ...,
          [grouping_nameN = ] grouping_expressionN]]
		

Parameters

columnX

The name by which the aggregated value is returned. If omitted, the name is equal to the corresponding expression (expressionX). If multiple columns have the same name, all but the rightmost column with this name will be ignored.

expressionX

An expression that computes an aggregated value.

grouping_expressionX

An expression that outputs the values to group by. If its name coincides with one of the existing or computed columns, that column will be overridden by this one.

boolean_expressionX

The condition that determines which rows are included when evaluating expressionX.

Description

The INLINE STATS processing command groups rows according to a common value (also known as the grouping key), specified after BY, and calculates one or more aggregated values over the grouped rows. The output table contains the same number of rows as the input table. The command only adds new columns or overrides existing columns with the same name as the result.

If column names overlap, existing column values may be overridden and column order may change. The new columns are added/moved so that they appear in the order they are defined in the INLINE STATS command.

For the calculation of each aggregated value, the rows in a group can be filtered with WHERE. If BY is omitted the aggregations are applied over the entire dataset.

The following aggregation functions are supported:

• AVG
• COUNT
• COUNT_DISTINCT
• MAX
• MEDIAN
• MEDIAN_ABSOLUTE_DEVIATION
• MIN
• PERCENTILE
• SAMPLE
• [preview] ST_CENTROID_AGG
• [preview] ST_EXTENT_AGG
• STD_DEV
• SUM
• TOP
• [preview] VALUES
• WEIGHTED_AVG
• PRESENT

The following grouping functions are supported:

• BUCKET
• TBUCKET

Examples

The following example shows how to calculate a statistic on one column and group by the values of another column.

FROM employees
| KEEP emp_no, languages, salary
| INLINE STATS max_salary = MAX(salary) BY languages
		

The following example shows how to calculate an aggregation over the entire dataset by omitting BY. The order of the existing columns is preserved and a new column with the calculated maximum salary value is added as the last column:

FROM employees
| KEEP emp_no, languages, salary
| INLINE STATS max_salary = MAX(salary)
		

The following example shows how to calculate multiple aggregations with multiple grouping keys:

FROM employees
| WHERE still_hired
| KEEP emp_no, languages, salary, hire_date
| EVAL tenure = DATE_DIFF("year", hire_date, now())
| DROP hire_date
| INLINE STATS avg_salary = AVG(salary), count = count(*) BY languages, tenure
		

The following example shows how to filter which rows are used for each aggregation, using the WHERE clause:

FROM employees
| KEEP emp_no, salary
| INLINE STATS avg_lt_50 = ROUND(AVG(salary)) WHERE salary < 50000,
               avg_lt_60 = ROUND(AVG(salary)) WHERE salary >=50000 AND salary < 60000,
               avg_gt_60 = ROUND(AVG(salary)) WHERE salary >= 60000
		

Limitations

• The CATEGORIZE grouping function is not currently supported.
• INLINE STATS cannot yet have an unbounded SORT before it. You must either move the SORT after it, or add a LIMIT before the SORT.