Database aggregation query result estimator

• US 20060036600A1
• Filed: 10/07/2005
• Published: 02/16/2006
• Est. Priority Date: 01/12/2001
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Chaudhuri, Surajit, Datar, Mayur D., Narasayya, Vivek R., Motwani, Rajeev
• Granted Patent: US 7,363,301 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24556 Aggregation; Duplicate elimination
• CPC Class Codes: G06F 16/2462 Approximate or statistical queries
• CPC Class Codes: G06F 2216/03 Data mining
• CPC Class Codes: Y10S 707/957 Multidimensional
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99935 Query augmenting and refining, e.g. inexact access
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

Aggregation queries are performed by first identifying outlier values, aggregating the outlier values, and sampling the remaining data after pruning the outlier values. The sampled data is extrapolated and added to the aggregated outlier values to provide an estimate for each aggregation query. Outlier values are identified by selecting values outside of a selected sliding window of data having the lowest variance. An index is created for the outlier values. The outlier data is removed from the window of data, and separately aggregated. The remaining data without the outliers is then sampled to provide a statistically relevant sample that is then aggregated and extrapolated to provide an estimate for the remaining data. This sampled estimate is combined with the outlier aggregate to form an estimate for the entire set of data.

Claims

1. A method of providing an estimated aggregation result for a set of values having first values and second values, the method comprising:

obtaining a first result of aggregating the first values and a second result of aggregating the second values; and

estimating an aggregation result for the set of values based on the first and second results.

2. The method as set forth in claim 1, wherein the first values comprise non-outlier values with respect to the set of values.

3. The method as set forth in claim 2, wherein the second values comprise outlier values with respect to the set of values.

4. The method as set forth in claim 1, further comprising:

sampling the first values; and

aggregating the sampled first values.

5. The method as set forth in claim 1, further comprising:

extrapolating the first result of aggregating the first values based on a sampling frequency associated with sampled first values used for the aggregating.

6. The method as set forth in claim 1, further comprising:

aggregating the second values.

7. The method as set forth in claim 1, further comprising:

aggregating the first and second values separately.

8. The method as set forth in claim 1, wherein estimating an aggregation result for the set of values based on the first and second results further comprises:

combining the first result of aggregating the first values and the second result of aggregating the second values.

9. The method as set forth in claim 1, further comprising:

sorting at least a portion of the set of values comprising the first and second values.

10. The method as set forth in claim 1, wherein obtaining a first result of aggregating the first values and a second result of aggregating the second values further comprises:

obtaining a variance of the first values in different groups of first values; and

selecting the group of first values having a lower variance than one or more other different groups of first values along with corresponding second values for that group to use for obtaining the first result of aggregating the first values.