Kinetica is able to aggregate data via the native APIs and SQL using various aggregate expressions. The native API endpoints related to aggregation:
To read more about aggregation in SQL, see Aggregation.
In SQL, to find the minimum, maximum, & average trip distances, as well as the average passenger count for each vendor per year from the taxi data set (weeding out data with errant trip distances):
In Python, create a histogram based on passenger counts from taxi data:
In Python, unpivot a course_sales table and then create a memory-only table of the results:
In Java, find aggregate statistics for the trip_distance column from taxi data:
In the REST API, find the unique Sector values from the stocks table using /aggregate/unique:
Memory-Only Tables and Persistence
The following aggregate endpoints have the ability to create and persist memory-only tables from query results:
To create a memory-only table from any of the above endpoint operations, specify the name of the table in the options map:
To persist the memory-only table, specify the result_table_persist option as well:
- Store-only columns cannot be grouped on, nor can aggregation functions be applied to them.
- Non-charN string column types can be grouped on, but can only have COUNT & COUNT_DISTINCT aggregation functions applied to them.
- In the native APIs, an alias must be used for the column name if using column or aggregate functions in the relevant selected columns parameter, e.g., count(column_name) as count_col_records.
Aggregate Memory-Only Tables
- Creating a memory-only table results in an entirely new data set, so be mindful of the memory usage implications.
- An aggregate memory-only table cannot be created with a non-charN string column type.
- If an aggregate memory-only table is created and its source data set's rows are updated (or removed), the memory-only table will not be updated to reflect the changes in the source data set.
- An aggregate memory-only table is transient, by default, and will expire after the default TTL setting.
- An aggregate memory-only table is not persisted, by default, and will not survive a database restart; specifying a result_table_persist option of true will make the table permanent and not expire.