Towards estimation error guarantees for distinct values

Abstract

We consider the problem of estimating the number of distinct values in a column of a table. For large tables without an index on the column, random sampling appears to be the only scalable approach for estimating the number of distinct values. We establish a powerful negative result stating that no estimator can guarantee small error across all input distributions, unless it examines a large fraction of the input data. In fact, any estimator must incur a signi cant error on at least some of a natural class of distributions. We then provide a new estimator which i s p r o vably optimal, in that its error is guaranteed to essentially match our negative result. A drawback of this estimator is that while its worst-case error is reasonable, it does not necessarily give the best possible error bound on any given distribution. Therefore, we develop heuristic estimators that are optimized for a class of typical input distributions. While these estimators lack strong guarantees on distribution-independent w orstcase error, our extensive empirical comparison indicate their e ectiveness both on real data sets and on synthetic data sets.

Space-Efficient Estimation of Statistics Over Sub-Sampled Streams

“…Distinct Elements: For the number of distinct elements, F 0 , we show that the current best offline methods for estimating F 0 from a random sample can be implemented in a streaming fashion using very small space. While it is known that random sampling can significantly reduce the accuracy of an estimate for F 0 [7], we show that the need to process this stream using small space does not. The upper and lower bounds are presented in Section 4.…”

“…The following theorem is from Charikar et al [7], which we have restated slightly to fit our notation (the original theorem is about database tables). Let F 0 be the number of elements in a data set T of total size n. Note that T maybe a stored data set, and need not be processed in a one-pass streaming manner.…”

“…Theorem 3 (Charikar et al [7]) Consider any (randomized) estimatorF 0 for the number of distinct values F 0 of T , that examines at most r out of the n elements in T . For any γ > e −r , there exists a choice of the input T such that with probability at least γ, the multiplicative error is at least (n − r)/(2r) ln γ −1 .…”

On Approximation Algorithms for Data Mining Applications

“…It it also shown that the k-th statistical moment can be approximated within an additive error of by using a random sample of size O(1/ 2 log 1 δ ), and that this is a lower bound on the size of the sample. Work that also refer to lower bounds on query complexity for approximate solutions include results on the approximation of the mean [28], [36,91], the approximation on the frequency moment [31].…”

Comparing Data Streams Using Hamming Norms (How to Zero In)

“…It is of importance to query optimization and otherwise to know the number of distinct values that each attribute of the table assumes. The importance of this problem is highlighted in [9]: "A principled choice of an execution plan by an optimizer heavily depends on the availability of 1. Notice that the generated "difference stream," a a À b b, will usually contain negative values corresponding to points where b i > a i .…”