Relation aware aggregation (RAA) on normalized datasets

1. A method for performing an aggregate function using a database query, the method comprising:

executing a single Structured Query Language (SQL) database query with a processor of a database engine having access to one or more relational databases as received from over a computer communications network from a requesting client, wherein the database query joins a plurality of database tables of the one or more databases and comprises a distinct aggregation function, which, when executed against the one or more databases, computes an aggregation value of an aggregation of database records corresponding to distinct keys by which the tables are joined, without creating an intermediate table, by applying a uniform hash function to convert a key value corresponding to a record value into a random value and adding the record value to the random value to provide a distinct key value-record value composite integer,

the distinct keys each correlating separate data sets into a single data set for computation of the aggregation value but each differing from a value used in computing the aggregation value,

the execution of the query avoiding a resultant value skewed relative to an expected value of the computed aggregation value due to a presence of more than one instance of a primary key in the database tables.

2. The method of claim 1 , wherein the processor is comprised by a server device, and wherein the method comprises providing results of the database query to a client device over a network connection.

3. The method of claim 1 , wherein the distinct aggregation function is a summation function configured to compute a sum of record values corresponding to distinct keys, and wherein the summation function comprises summing the distinct key value-record value composite integers and subtracting the sum of the random values to provide the sum of the record values corresponding to distinct keys.

4. The method of claim 1 , wherein the distinct aggregation function is an average function configured to compute an average of record values corresponding to distinct keys, and wherein the average function comprises summing the distinct key value-record value composite integers, subtracting the sum of the random values to obtain the sum of the record values corresponding to distinct keys, and dividing the sum of the record values corresponding to distinct keys by the number of distinct keys to provide the average of the record values corresponding to the distinct keys.

5. The method of claim 1 , wherein generating the distinct key value-record value composite integer comprises selecting a bit depth data type for the distinct key value-record value composite integer which is sufficient to contain a full summation of all the record values to be aggregated.

6. The method of claim 1 , wherein the executing comprises:

accessing a collection of aggregation computation instructions; selecting from the collection of aggregation computation instructions, instructions which, when executed by the processor of the database engine, cause the database engine to:

examine only the first instance of each given key value; and

utilize a record value corresponding to the first instance of the given key value in the distinct aggregation function.

7. The method of claim 6 , wherein the collection of computation instructions is provided to the database engine by a client device.

8. The method of claim 6 , wherein the collection of computation instructions is loaded as source or compiled code by the database engine.

9. The method of claim 6 , wherein the collection of computation instructions is comprised by the database engine.

10. The method of claim 1 , wherein the executing comprises accessing a user-defined library, wherein the user-defined library comprises instructions, which, when executed by the processor of the database engine, cause the processor of the database engine to:

examine only the first instance of each given key value; and

utilize a record value corresponding to the first instance of the given key value in the distinct aggregation function.

11. The method of claim 10 , wherein the user-defined library is stored in database memory.

12. A non-transitory recording medium comprising instructions, which, when executed by a processor of a database engine, cause the database engine to:

execute a single Structured Query Language (SQL) database query against one or more relational databases as received from over a computer communications network from a requesting client, wherein the database query joins a plurality of database tables of the one or more databases and comprises a distinct aggregation function, which, when executed against the one or more databases, computes an aggregation value of an aggregation of database records corresponding to distinct keys by which the tables are joined, without creating an intermediate table, by applying a uniform hash function to convert a key value corresponding to a record value into a random value and adding the record value to the random value to provide a distinct key value-record value composite integer,

the distinct keys each correlating separate data sets into a single data set for computation of the aggregation value but each differing from a value used in computing the aggregation value,

the execution of the query avoiding a resultant value skewed relative to an expected value of the computed aggregation value due to a presence of more than one instance of a primary key in the database tables.

13. The non-transitory recording medium of claim 12 , wherein the processor is comprised by a server device, and wherein the non-transitory recording medium comprises instructions, which when executed by the processor, cause the processor to provide results of the database query to a client device over a network connection.

14. The non-transitory recording medium of claim 12 , wherein the distinct aggregation function is a summation function configured to compute a sum of record values corresponding to distinct keys, and wherein the summation function comprises summing the distinct key value-record value composite integers and subtracting the sum of the random values to provide the sum of the record values corresponding to distinct keys.

15. The non-transitory recording medium of claim 12 , wherein the distinct aggregation function is an average function configured to compute an average of record values corresponding to distinct keys, and wherein the average function comprises summing the distinct key value-record value composite integers, subtracting the sum of the random values to obtain the sum of the record values corresponding to distinct keys, and dividing the sum of the record values corresponding to distinct keys by the number of distinct keys to provide the average of the record values corresponding to the distinct keys.

16. The non-transitory recording medium of claim 12 , wherein the non-transitory recording medium comprises instructions, which, when executed by the processor, cause the database engine to select a bit depth data type for the distinct key value-record value composite integer which is sufficient to contain a full summation of all the record values to be aggregated.

17. The non-transitory recording medium of claim 12 , wherein the executing comprises:

accessing a collection of aggregation computation instructions; and

selecting from the collection of aggregation computation instructions, instructions which, when executed by the processor of the database engine, cause the database engine to:

examine only the first instance of each given key value; and

utilize a record value corresponding to the first instance of the given key value in the distinct aggregation function.

18. The non-transitory recording medium of claim 12 , wherein the executing comprises accessing a user-defined library, wherein the user-defined library comprises instructions, which, when executed by the processor of the database engine, cause the processor of the database engine to:

examine only the first instance of each given key value; and

utilize a record value corresponding to the first instance of the given key value in the distinct aggregation function.

19. A system for performing an aggregate function using a database query, the system comprising:

a client device;

a server device; and

one or more relational databases,

wherein the client device transmits a single Structured Query Language (SQL) database query over a network connection from the client device to the server device, which, when received by the server device, causes the server device to execute a database query against the one or more relational databases as received from over a computer communications network from a requesting client, wherein the database query joins a plurality of database tables of the one or more databases and comprises a distinct aggregation function, which, when executed against the one or more databases, computes an aggregation value of an aggregation of database records corresponding to distinct keys by which the tables are joined, without creating an intermediate table, by applying a uniform hash function to convert a key value corresponding to a record value into a random value and adding the record value to the random value to provide a distinct key value-record value composite integer,

the distinct keys each correlating separate data sets into a single data set for computation of the aggregation value but each differing from a value used in computing the aggregation value,

the execution of the query avoiding a resultant value skewed relative to an expected value of the computed aggregation value due to a presence of more than one instance of a primary key in the database tables.