You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Noisy aggregate functions are functions that provide random, noisy
approximations of common aggregations like :func:`!sum`, :func:`!count`, and
:func:`!approx_distinct` as well as sketches like :func:`!approx_set`. By
injecting random noise into results, noisy aggregation functions make it more
difficult to determine or confirm the exact data that was aggregated.
While many of these functions resemble
differential privacy
mechanisms, neither the values returned by these functions nor the query results
that incorporate these functions are differentially private in general.
See Limitations below for more details. Users who wish to support a strong
privacy guarantee should discuss with a suitable technical expert first.
Counts, Sums, and Averages
.. function:: noisy_count_gaussian(col, noise_scale[, random_seed]) -> bigint
Counts the non-``NULL`` values in ``col`` and then adds a normally distributed
random double value with 0 mean and standard deviation of ``noise_scale`` to the true count.
The noisy count is post-processed to be non-negative and rounded to bigint.
If provided, ``random_seed`` is used to seed the random number generator. Otherwise,
noise is drawn from a secure random. ::
SELECT noisy_count_gaussian(orderkey, 20.0) FROM tpch.tiny.lineitem; -- 60179 (1 row)
SELECT noisy_count_gaussian(orderkey, 20.0) FROM tpch.tiny.lineitem WHERE false; -- NULL (1 row)
.. note::
Unlike :func:`!count`, this function returns ``NULL`` when the (true) count of ``col`` is 0.
Distinct counting can be performed using ``noisy_count_gaussian(DISTINCT col, ...)``, or with
:func:`!noisy_approx_distinct_sfm`. Generally speaking, :func:`!noisy_count_gaussian`
returns more accurate results but at a larger computational cost.
.. function:: noisy_count_if_gaussian(col, noise_scale[, random_seed]) -> bigint
Counts the ``TRUE`` values in ``col`` and then adds a normally distributed random double
value with 0 mean and standard deviation of ``noise_scale`` to the true count.
The noisy count is post-processed to be non-negative and rounded to bigint.
If provided, ``random_seed`` is used to seed the random number generator. Otherwise,
noise is drawn from a secure random. ::
SELECT noisy_count_if_gaussian(orderkey > 10000, 20.0) FROM tpch.tiny.lineitem; -- 50180 (1 row)
SELECT noisy_count_if_gaussian(orderkey > 10000, 20.0) FROM tpch.tiny.lineitem WHERE false; -- NULL (1 row)
.. note::
Unlike :func:`!count_if`, this function returns ``NULL`` when the (true) count is 0.
.. function:: noisy_sum_gaussian(col, noise_scale, lower, upper[, random_seed]) -> double
Calculates the sum over the input values in ``col`` and then adds a normally distributed
random double value with 0 mean and standard deviation of noise_scale. Each value is clipped to the range
of [``lower``, ``upper``] before adding to the sum.
If provided, ``random_seed`` is used to seed the random number generator. Otherwise,
noise is drawn from a secure random.
.. function:: noisy_sum_gaussian(col, noise_scale[, random_seed]) -> double
:noindex:
Calculates the sum over the input values in ``col`` and then adds a normally
distributed random double value with 0 mean and standard deviation of ``noise_scale``.
If provided, ``random_seed`` is used to seed the random number generator. Otherwise,
noise is drawn from a secure random.
.. function:: noisy_avg_gaussian(col, noise_scale, lower, upper[, random_seed]) -> double
Calculates the average (arithmetic mean) of all the input values in ``col`` and then
adds a normally distributed random double value with 0 mean and standard deviation of ``noise_scale``.
Each value is clipped to the range of [``lower``, ``upper``] before averaging.
If provided, ``random_seed`` is used to seed the random number generator. Otherwise,
noise is drawn from a secure random.
.. function:: noisy_avg_gaussian(col, noise_scale[, random_seed]) -> double
:noindex:
Calculates the average (arithmetic mean) of all the input values in ``col`` and then adds
a normally distributed random double value with 0 mean and standard deviation of ``noise_scale``.
If provided, ``random_seed`` is used to seed the random number generator. Otherwise,
noise is drawn from a secure random.
Approximate Distinct Counting/Sketching
Noisy approximate distinct counting and sketching (analogous to the deterministic :doc:`hyperloglog`)
is supported via the Sketch-Flip-Merge (SFM) data sketch [Hehir2023].
.. function:: noisy_approx_set_sfm(col, epsilon[, buckets[, precision]]) -> SfmSketch
Returns an SFM sketch of the input values in ``col``. This is analogous to the
:func:`!approx_set` function, which returns a (deterministic) HyperLogLog sketch.
- ``col`` supports many types, similar to ``HyperLogLog``.
- ``epsilon`` (double) is a positive number that controls the level of noise in
the sketch, as described in [Hehir2023]_. Smaller values of epsilon correspond
to noisier sketches.
- ``buckets`` (int) defaults to 4096.
- ``precision`` (int) defaults to 24.
.. note::
Unlike :func:`!approx_set`, this function returns ``NULL`` when ``col`` is empty.
If this behavior is undesirable, use :func:`!coalesce` with :func:`!noisy_empty_approx_set_sfm`.
.. function:: noisy_approx_set_sfm_from_index_and_zeros(col_index, col_zeros, epsilon, buckets[, precision]) -> SfmSketch
Returns an SFM sketch of the input values in ``col_index`` and ``col_zeros``.
This is similar to :func:`!noisy_approx_set_sfm` except that function calculates a ``Murmur3Hash128.hash64()`` of ``col``,
and calculates the SFM PCSA bucket index and number of trailing zeros as described in
[FlajoletMartin1985]_. In this function, the caller must explicitly calculate the hash bucket index
and zeros themselves and pass them as arguments ``col_index`` and ``col_zeros``.
- ``col_index`` (bigint) must be in the range ``0..buckets-1``.
- ``col_zeros`` (bigint) must be in the range ``0..64``. If it exceeds ``precision``, it
is cropped to ``precision-1``.
- ``epsilon`` (double) is a positive number that controls the level of noise in
the sketch, as described in [Hehir2023]_. Smaller values of epsilon correspond
to noisier sketches.
- ``buckets`` (int) is the number of buckets in the SFM PCSA sketch as described in [Hehir2023]_.
- ``precision`` (int) defaults to 24.
.. note::
Like :func:`!noisy_approx_set_sfm`, this function returns ``NULL`` when ``col_index``
or ``col_zeros`` is ``NULL``.
If this behavior is undesirable, use :func:`!coalesce` with :func:`!noisy_empty_approx_set_sfm`.
.. function:: noisy_approx_distinct_sfm(col, epsilon[, buckets[, precision]]) -> bigint
Equivalent to ``cardinality(noisy_approx_set_sfm(col, epsilon, buckets, precision))``,
this returns the approximate cardinality (distinct count) of the column ``col``.
This is analogous to the (deterministic) :func:`!approx_distinct` function.
.. note::
Unlike :func:`!approx_distinct`, this function returns ``NULL`` when ``col`` is empty.
.. function:: noisy_empty_approx_set_sfm(epsilon[, buckets[, precision]]) -> SfmSketch
Returns an SFM sketch with no items in it. This is analogous to the :func:`!empty_approx_set`
function, which returns an empty (deterministic) HyperLogLog sketch.
- ``epsilon`` (double) is a positive number that controls the level of noise in the sketch,
as described in [Hehir2023]_. Smaller values of epsilon correspond to noisier sketches.
- ``buckets`` (int) defaults to 4096.
- ``precision`` (int) defaults to 24.
.. function:: cardinality(SfmSketch) -> bigint
Returns the estimated cardinality (distinct count) of an ``SfmSketch`` object.
.. function:: merge(SfmSketch) -> SfmSketch
An aggregator function that returns a merged ``SfmSketch`` of the set union of
individual ``SfmSketch`` objects, similar to ``merge(HyperLogLog)``. ::
SELECT year, cardinality(merge(sketch)) AS annual_distinct_count
FROM monthly_sketches
GROUP BY 1
.. function:: merge_sfm(ARRAY[SfmSketch, ...]) -> SfmSketch
A scalar function that returns a merged ``SfmSketch`` of the set union of an array
of ``SfmSketch`` objects, similar to :func:`!merge_hll`. ::
SELECT cardinality(merge_sfm(ARRAY[
noisy_approx_set_sfm(col_1, 5.0),
noisy_approx_set_sfm(col_2, 5.0),
noisy_approx_set_sfm(col_3, 5.0)
])) AS distinct_count_over_3_cols
FROM my_table
Limitations
While these functions resemble differential privacy mechanisms, the values returned
by these functions are not differentially private in general. There are several
important limitations to keep in mind if using these functions for
privacy-preserving purposes, including:
All noisy aggregate functions return NULL when aggregating empty sets.
This means a NULL return value noiselessly indicates the absence of data.
GROUP BY clauses used in combination with noisy aggregation functions
reveal non-noisy information: the presence or absence of a group noiselessly
indicates the presence or absence of data. See, e.g., [Wilkins2024].
Functions relying on floating-point noise may be susceptible to inference
attacks such as those identified in [Mironov2012] and [Casacuberta2022].