L2 Construct: aggregate

etl aggregate

Description

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"]
    }
  }
}

Dummy Task Filter