Aggregation

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.

Execution

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):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
SELECT
    vendor_id AS Vendor_ID,
    YEAR(pickup_datetime) AS Year,
    MAX(trip_distance) AS Max_Trip,
    MIN(trip_distance) AS Min_Trip,
    ROUND(AVG(trip_distance),2) AS Avg_Trip,
    INT(AVG(passenger_count)) AS Avg_Passenger_Count
FROM demo.nyctaxi
WHERE
    trip_distance > 0 AND
    trip_distance < 100
GROUP BY vendor_id, 2
ORDER BY Vendor_ID, Year

In Python, create a histogram based on passenger counts from taxi data:

1
2
3
4
5
6
7
8
9
resp = kinetica.aggregate_histogram(
        table_name="demo.nyctaxi",
        column_name="passenger_count",
        start=1, end=6, interval=1
)

print("Passenger count groups by size:")
for histo_group in zip([1, 2, 3, 4, 5, 6], resp['counts']):
    print("\t{}s: {:4.0f}".format(*histo_group))

In Python, unpivot a student_grade table and then create a memory-only table student_quarter_grade of the results:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
kinetica.aggregate_unpivot(
    table_name = "example.student_grade",
    column_names = ["*"],
    variable_column_name = "quarter",
    value_column_name = "grade",
    pivoted_columns = [
        "q1_grade AS q1",
        "q2_grade AS q2",
        "q3_grade AS q3",
        "q4_grade AS q4"
    ],
    options= {"result_table":"example.student_quarter_grade"}
)

In Java, find aggregate statistics for the trip_distance column from taxi data:

1
2
3
4
5
6
7
8
Map<String,Double> stats = gpudb.aggregateStatistics("nyctaxi", "trip_distance", "count,min,max,mean,percentile(50,200)", null).getStats();
System.out.println("Statistics of values in the trip_distance column:");
System.out.printf
(
    "\tCount: %.0f%n\tMin:  %4.2f%n\tMean: %4.2f%n\tMax:  %4.2f%n%n",
    stats.get("count"), stats.get("min"), stats.get("mean"),
    stats.get("max")
);

In the REST API, find the unique Sector values from the stocks table using /aggregate/unique:

1
2
3
4
5
6
7
8
{
    "table_name":"stocks",
    "column_name":"Sector",
    "offset":0,
    "limit":-9999,
    "encoding":"json",
    "options":{}
}

Pivot

A pivot operation does not have its own endpoint and is instead achieved through the /aggregate/groupby endpoint options. Details can be found on Pivot.

Unpivot

As mentioned above, unpivot operations have their own endpoint. Details on unpivot operations can be found on Unpivot.

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:

1
"result_table":"my_table_name"

To persist the memory-only table, specify the result_table_persist option as well:

1
2
"result_table":"my_table_name",
"result_table_persist":"true"

A memory-only table name must adhere to the standard naming criteria. Each memory-only table exists within a schema and follows the standard name resolution rules for tables.

Sharding and Replication

Aggregate memory-only tables can vary in how they are sharded or replicated depending on the endpoint they are created from.

/aggregate/groupby: The aggregate groupby memory-only table will be sharded if the entire shard key is included in the column_names parameter and all result records are selected (offset is 0 and limit is -9999). The aggregate groupby memory-only table can be replicated if the result_table_force_replicated option is set to true, regardless if the source table or view is sharded or not.
/aggregate/unique: An aggregate unique memory-only table is replicated by default, but it can be sharded if the shard key is included in the column_names parameter. If the result_table_force_replicated option is set to true, the aggregate unique memory-only table will be replicated regardless if the source table or view is sharded or not.
/aggregate/unpivot: The aggregate unpivot memory-only table will be sharded if the shard key is not part of the pivoted_columns parameter. The aggregate unpivot memory-only table will be replicated if the source table or view is replicated.

Limitations

General

The vector data type cannot be grouped on.
Aggregate expressions can only be applied to integer, floating-point, and basic string types; other types may have the COUNT function applied.
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.
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.