Approximate estimation of number of distinct keys in a multiset using a sample
Herein are quantitative analytics to increase the accuracy of cardinality estimation without increasing sample size. In an embodiment, a computer selects a few sample values from a multiset. A high-frequency exact count of distinct values that have at least a threshold amount of occurrences in the sample values is counted. A low-frequency exact count of distinct values in the sample that do not have at least the threshold amount of occurrences in the sample is counted. Based on multiple binomial probabilities, an upper bound of a count of missing distinct values in the multiset that are not in the sample is calculated. A total count of distinct values (NDV) in the multiset is estimated based on: a) the high-frequency exact count of distinct values, b) the low-frequency exact count of distinct values, and c) the upper bound of the count of missing distinct values in the multiset that are not in the sample.
1. A method comprising:
selecting a sample plurality of values from a multiset;
determining a high-frequency exact count of distinct values that have at least a threshold amount of occurrences in the sample plurality of values;
determining a low-frequency exact count of distinct values in the sample plurality of values that do not have at least said threshold amount of occurrences in the sample plurality of values;
calculating, based on a plurality of binomial probabilities, an upper bound of a count of missing distinct values in said multiset that are not in said sample plurality of values;
estimating a total count of distinct values in the multiset based on:
said high-frequency exact count of distinct values,
said low-frequency exact count of distinct values, and
said upper bound of said count of missing distinct values in said multiset that are not in said sample plurality of values.
2. The method of claim 1 wherein said estimating the total count of distinct values comprises estimating a count of distinct values that, due to sampling error, are not in the sample plurality of values and, without said sampling error, would have at least said threshold amount of occurrences in the sample plurality of values.
3. The method of claim 1 wherein said estimating the total count of distinct values comprises estimating an under-sampled estimated count of the low-frequency exact count of distinct values that, without sampling error, would have at least said threshold amount of occurrences in the sample plurality of values.
4. The method of claim 3 wherein said estimating said under-sampled estimated count of the low-frequency exact count of distinct values comprises estimating said under-sampled estimated count of the low-frequency exact count of distinct values that, without said sampling error, would have at least three occurrences in the sample plurality of values.
5. The method of claim 1 wherein said estimating the total count of distinct values comprises estimating a low-frequency estimated count of distinct values in said multiset that, regardless of sampling error, cannot have at least said threshold amount of occurrences in the sample plurality of values.
6. The method of claim 5 further comprising calculating at least one selected from the group consisting of:
a lower bound of said low-frequency estimated count of distinct values in said multiset, and
an upper bound of said low-frequency estimated count of distinct values in said multiset.
7. The method of claim 6 wherein at least one selected from the group consisting of:
said calculating said lower bound of said low-frequency estimated count of distinct values in said multiset comprises selecting a minimum of multiple estimates, and
said calculating said upper bound of said low-frequency estimated count of distinct values in said multiset comprises selecting a maximum of multiple estimates.
8. The method of claim 6 wherein said estimating said low-frequency estimated count of distinct values in said multiset is based on at least one selected from the group consisting of:
said lower bound of said low-frequency estimated count of distinct values in said multiset, and
said upper bound of said low-frequency estimated count of distinct values in said multiset.
9. The method of claim 6 wherein said estimating said low-frequency estimated count of distinct values in said multiset comprises calculating a geometric mean of:
said lower bound of said low-frequency estimated count of distinct values in said multiset, and
said upper bound of said low-frequency estimated count of distinct values in said multiset.
10. The method of claim 1 wherein said selecting the sample plurality of values comprises at least one selected from the group consisting of:
selecting less than one percent of values in a column of a database table,
selecting computed values based on original values stored in a database, and
selecting values of a compound value comprising at least one selected from the group consisting of:
multiple columns of a same database table, and
columns of multiple database tables.
11. One or more non-transitory computer-readable media storing instructions that, when executed by one or more processors, cause:
selecting a sample plurality of values from a multiset;
determining a high-frequency exact count of distinct values that have at least a threshold amount of occurrences in the sample plurality of values;
determining a low-frequency exact count of distinct values in the sample plurality of values that do not have at least said threshold amount of occurrences in the sample plurality of values;
calculating, based on a plurality of binomial probabilities, an upper bound of a count of missing distinct values in said multiset that are not in said sample plurality of values;
estimating a total count of distinct values in the multiset based on:
said high-frequency exact count of distinct values,
said low-frequency exact count of distinct values, and
said upper bound of said count of missing distinct values in said multiset that are not in said sample plurality of values.
12. The one or more non-transitory computer-readable media of claim 11 wherein said estimating the total count of distinct values comprises estimating a count of distinct values that, due to sampling error, are not in the sample plurality of values and, without said sampling error, would have at least said threshold amount of occurrences in the sample plurality of values.
13. The one or more non-transitory computer-readable media of claim 11 wherein said estimating the total count of distinct values comprises estimating an under-sampled estimated count of the low-frequency exact count of distinct values that, without sampling error, would have at least said threshold amount of occurrences in the sample plurality of values.
14. The one or more non-transitory computer-readable media of claim 13 wherein said estimating said under-sampled estimated count of the low-frequency exact count of distinct values comprises estimating said under-sampled estimated count of the low-frequency exact count of distinct values that, without said sampling error, would have at least three occurrences in the sample plurality of values.
15. The one or more non-transitory computer-readable media of claim 11 wherein said estimating the total count of distinct values comprises estimating a low-frequency estimated count of distinct values in said multiset that, regardless of sampling error, cannot have at least said threshold amount of occurrences in the sample plurality of values.
16. The one or more non-transitory computer-readable media of claim 15 wherein the instructions further cause calculating at least one selected from the group consisting of:
a lower bound of said low-frequency estimated count of distinct values in said multiset, and
an upper bound of said low-frequency estimated count of distinct values in said multiset.
17. The one or more non-transitory computer-readable media of claim 16 wherein at least one selected from the group consisting of:
said calculating said lower bound of said low-frequency estimated count of distinct values in said multiset comprises selecting a minimum of multiple estimates, and
said calculating said upper bound of said low-frequency estimated count of distinct values in said multiset comprises selecting a maximum of multiple estimates.
18. The one or more non-transitory computer-readable media of claim 16 wherein said estimating said low-frequency estimated count of distinct values in said multiset is based on at least one selected from the group consisting of:
said lower bound of said low-frequency estimated count of distinct values in said multiset, and
said upper bound of said low-frequency estimated count of distinct values in said multiset.
19. The one or more non-transitory computer-readable media of claim 16 wherein said estimating said low-frequency estimated count of distinct values in said multiset comprises calculating a geometric mean of:
said lower bound of said low-frequency estimated count of distinct values in said multiset, and
said upper bound of said low-frequency estimated count of distinct values in said multiset.
20. The one or more non-transitory computer-readable media of claim 11 wherein said selecting the sample plurality of values comprises at least one selected from the group consisting of:
selecting less than one percent of values in a column of a database table,
selecting computed values based on original values stored in a database, and
selecting values of a compound value comprising at least one selected from the group consisting of:
multiple columns of a same database table, and
columns of multiple database tables.