L2 Construct: aggregate
etl aggregateDescription
GroupAndAggregateTableEndpoint groups the input Table by specified columns and applies aggregation functions. It takes a Table, an array of column names to group by, and an array of Aggregation structs specifying the column and function (Average or Count) for each aggregation. The endpoint returns a new Table with the grouped and aggregated data.
L2 Data
- Provider: pontus
- Module: etl
- Action: aggregate
Example Step
{
"name": "insert-your-step-name",
"type": "l2",
"l2_data": {
"provider": "pontus",
"module": "etl",
"action": "aggregate"
}
}
Input
Example
{
"table": {
"headers": ["Date", "Product", "Sales", "Quantity"],
"rows": [
["2023-01-01", "A", 100, 5],
["2023-01-01", "B", 200, 3],
["2023-01-02", "A", 150, 7],
["2023-01-02", "B", 180, 4]
]
},
"group_by_cols": ["Date", "Product"],
"aggregation_columns": ["Sales", "Quantity"],
"aggregation_functions": [3, 7]
}
Schema
{
"$schema": "https://json-schema.org/draft/2020-12/schema",
"$id": "https://pontus.bpa/pkgs/l2/pontus/etl/group-and-aggregate-table-input",
"$ref": "#/$defs/GroupAndAggregateTableInput",
"$defs": {
"GroupAndAggregateTableInput": {
"properties": {
"table": {
"$ref": "#/$defs/Table"
},
"group_by_cols": {
"items": {
"type": "string"
},
"type": "array"
},
"aggregation_columns": {
"items": {
"type": "string"
},
"type": "array"
},
"aggregation_functions": {
"items": {
"type": "integer"
},
"type": "array"
}
},
"additionalProperties": false,
"type": "object",
"required": [
"table",
"group_by_cols",
"aggregation_columns",
"aggregation_functions"
]
},
"Table": {
"properties": {
"headers": {
"items": {
"type": "string"
},
"type": "array"
},
"rows": {
"items": {
"items": true,
"type": "array"
},
"type": "array"
}
},
"additionalProperties": false,
"type": "object",
"required": ["headers", "rows"]
}
}
}
Output
Example
{
"result_table": {
"headers": ["Date", "Product", "avg_Sales", "count_Quantity"],
"rows": [
["2023-01-01", "A", 100, 1],
["2023-01-01", "B", 200, 1],
["2023-01-02", "A", 150, 1],
["2023-01-02", "B", 180, 1]
]
}
}
Schema
{
"$schema": "https://json-schema.org/draft/2020-12/schema",
"$id": "https://pontus.bpa/pkgs/l2/pontus/etl/group-and-aggregate-table-output",
"$ref": "#/$defs/GroupAndAggregateTableOutput",
"$defs": {
"GroupAndAggregateTableOutput": {
"properties": {
"result_table": {
"$ref": "#/$defs/Table"
}
},
"additionalProperties": false,
"type": "object",
"required": ["result_table"]
},
"Table": {
"properties": {
"headers": {
"items": {
"type": "string"
},
"type": "array"
},
"rows": {
"items": {
"items": true,
"type": "array"
},
"type": "array"
}
},
"additionalProperties": false,
"type": "object",
"required": ["headers", "rows"]
}
}
}