The present embodiments relate to aggregation operations. More specifically, the embodiments relate to translating a query involving a distinct aggregate(s) into a query that does not involve a distinct aggregate.
Data aggregation is a process in which information is gathered and expressed in a summary form. Submitted data may be processed by an aggregate which is used to compute user specified operations over user specified data ranges. Examples of such aggregates include, “AVG” which functions to return the average of values defined in the expression, “BASE” which returns the total number of cases includes in the expression, “COUNT” which functions to return a count of cases in a column, “DISTINCT” which functions to return unique values, “MIN” which functions to return the lowest value, “MAX” which functions to return the highest value, “STDEV” which functions to return the standard deviation of the values defined in the expression, and “SUM” which functions to return the sum of the values defined in the submitted expression. In addition to these example aggregates, a distinct aggregate is defined as an aggregation performed on the results of a DISTINCT operation, e.g. SUM (DISTINCT (value(s))).
One popular database language that may be used to perform aggregations is Structured Query Language (SQL). An SQL statement is used to query or manipulate data in data storage. For example, a SELECT, UPDATE, INSERT or DELETE statement can be used with the SQL statement. A query using a SELECT statement may contain the following clauses, SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, and JOIN. The SELECT clause is used to specify the data of interest. The FROM clause indicates a source for the data. The WHERE clause indicates a condition for filtering the data. The GROUP BY clause allows organization of data into subsets. The HAVING clause places a condition on membership in a subset. The ORDER BY clause organizes the resultant data. The JOIN clause matches a row of one table with a row of another on the basis of a join condition. The query presented in the SQL statement is not limited to a single clause.
Any query under workspaces can suffer performance issues (e.g. become time consuming and memory intensive). In order to compute a query containing a distinct aggregate, a calculation of the aggregate's corresponding distinct values in the database must be performed. Queries containing distinct aggregates are particularly susceptible to suffering performance issues especially when the query contains multiple distinct aggregates since each aggregate has to be computed separately for values belonging to each category defined in the aggregate. Further, the performance issues with a query become exacerbated when performed in distributed environments, such as the cloud, due to the structure of the data storage and plurality of data storage options. Accordingly, it is desirable to provide a system and method for executing distinct aggregate operations in a manner that is less memory intensive and time consuming than current techniques.