SQL Returning Clause Aggregate Functions
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional database systems using SQL require complex iterative techniques to perform aggregate operations on multiple values-of-interest returned by DML commands, leading to inefficient data movement and programming complexity.
Innovation Solution
The returning clause in SQL is enhanced to support direct specification of aggregate operations within the clause, allowing multiple values-of-interest to be processed natively on the database server, with options to specify whether to use old or new values for aggregation using '.old' and '.new' extensions.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Loss of information
If the conventional returning clause is used to return multiple values-of-interest, then all individual values can be retrieved, but the programming complexity increases and data transfer overhead increases
Solution Approach 1:
The patent extracts the aggregate computation function from the client-side programming and relocates it to the database server. The returning clause is extended to support aggregate functions directly, allowing the server to compute aggregate values (sum, average, min, max, count) and return only the results rather than all individual values. This extraction eliminates the need for client-side loops and aggregate computations, reducing both data transfer overhead and programming complexity.
Solution Approach 2:
The patent changes the parameter of the returning clause from returning only individual values to returning both individual values and aggregate function results. The syntax is extended to allow specifications like 'RETURNING C1, SUM(C1), AVG(C1)' where aggregate functions can be applied directly to the values being returned. This parameter change enables the system to provide both detailed and summarized information in a single operation.
2Loss of time
If iterative techniques are used to perform aggregate operations on multiple values, then aggregate results can be obtained, but the execution time increases
Solution Approach 1:
The patent applies preliminary action by having the database server compute aggregate functions on the values-of-interest before returning them to the client. Instead of retrieving all individual values and performing aggregate operations afterward, the server pre-computes the aggregate results as part of the DML operation itself. This preliminary computation significantly reduces execution time by eliminating post-retrieval processing loops.
3Quantity of substance
If all individual values-of-interest are returned to the client for processing, then complete data is available for further operations, but the data transfer volume increases
Solution Approach 1:
The patent extracts only the essential aggregate information from the complete set of individual values and returns it to the client. When aggregate functions are specified in the returning clause, the server computes these functions and returns only the aggregate results rather than all individual values. This extraction dramatically reduces data transfer volume while providing the client with the specific information needed for further operations.
Solution Approach 2:
The patent applies partial action by returning only the necessary subset of information (aggregate values) rather than the complete set of individual values. The client receives exactly what is needed for aggregate operations without the overhead of processing and storing all individual values, making the operation more efficient while maintaining the ability to perform further operations on the aggregate results.
Data Source
AI summary
A statement is provided having a clause that can specify an aggregate function that is performed on a set of data. The statement may alter the data in the data set by deleting or updating the data. The clause may be a returning clause that accepts one or more aggregate functions for its arguments, operates on the set of data, and returns the result of the aggregate function without returning the entire set of data. Alternatively, a statement is provided for changing multiple sets of data without the need to place the multiple sets in a single array.