Extend and aggregate a pipeline
Summaries are easier to write when you can build the columns you need before you group. Pipe syntax lets you do that one step at a time.
How it works
EXTEND adds columns to every row and keeps the ones you already have. AGGREGATE changes the grain to one row per group and returns only the group keys and the aggregate results.
|> EXTEND expression AS nameadds a computed column.|> AGGREGATE aggregate AS name GROUP BY keysummarizes the current rows.- A
SELECTafter that can rename or reorder the aggregate output.
Common mistake
AGGREGATE changes the grain of the table. Columns that are not group keys are gone afterward.
Worked example
FROM customers
|> EXTEND state AS region
|> AGGREGATE COUNT(*) AS people GROUP BY region
|> ORDER BY people DESCEXTEND adds region and keeps the customer rows.
AGGREGATE replaces those rows with one row per region and its count. The last step sorts the groups.