Differentially private aggregate functions

GoogleSQL for BigQuery supports differentially private aggregate functions. For an explanation of how aggregate functions work, see Aggregate function calls.

You can only use differentially private aggregate functions with differentially private queries in a differential privacy clause.

Note: In this topic, the privacy parameters in the examples are not recommendations. You should work with your privacy or security officer to determine the optimal privacy parameters for your dataset and organization.

Function list

Name	Summary
AVG	DIFFERENTIAL_PRIVACY-supported AVG. Gets the differentially-private average of non-NULL, non-NaN values in a query with a DIFFERENTIAL_PRIVACY clause.
COUNT	DIFFERENTIAL_PRIVACY-supported COUNT. Signature 1: Gets the differentially-private count of rows in a query with a DIFFERENTIAL_PRIVACY clause. Signature 2: Gets the differentially-private count of rows with a non-NULL expression in a query with a DIFFERENTIAL_PRIVACY clause.
PERCENTILE_CONT	DIFFERENTIAL_PRIVACY-supported PERCENTILE_CONT. Computes a differentially-private percentile across privacy unit columns in a query with a DIFFERENTIAL_PRIVACY clause.
SUM	DIFFERENTIAL_PRIVACY-supported SUM. Gets the differentially-private sum of non-NULL, non-NaN values in a query with a DIFFERENTIAL_PRIVACY clause.

AVG (DIFFERENTIAL_PRIVACY)

WITH DIFFERENTIAL_PRIVACY ...
  AVG(
    expression,
    [contribution_bounds_per_group => (lower_bound, upper_bound)]
  )

Description

Returns the average of non-NULL, non-NaN values in the expression. This function first computes the average per privacy unit column, and then computes the final result by averaging these averages.

This function must be used with the DIFFERENTIAL_PRIVACY clause and can support the following arguments:

• expression: The input expression. This can be any numeric input type, such as INT64.
• contribution_bounds_per_group: The contribution bounds named argument. Perform clamping per each group separately before performing intermediate grouping on the privacy unit column.

Return type

FLOAT64

Examples

The following differentially private query gets the average number of each item requested per professor. Smaller aggregations might not be included. This query references a table called professors.

-- With noise, using the epsilon parameter.
SELECT
  WITH DIFFERENTIAL_PRIVACY
    OPTIONS(epsilon=10, delta=.01, max_groups_contributed=1, privacy_unit_column=id)
    item,
    AVG(quantity, contribution_bounds_per_group => (0,100)) average_quantity
FROM professors
GROUP BY item;

-- These results will change each time you run the query.
-- Smaller aggregations might be removed.
/*----------+------------------*
 | item     | average_quantity |
 +----------+------------------+
 | pencil   | 38.5038356810269 |
 | pen      | 13.4725028762032 |
 *----------+------------------*/

-- Without noise, using the epsilon parameter.
-- (this un-noised version is for demonstration only)
SELECT
  WITH DIFFERENTIAL_PRIVACY
    OPTIONS(epsilon=1e20, delta=.01, max_groups_contributed=1, privacy_unit_column=id)
    item,
    AVG(quantity) average_quantity
FROM professors
GROUP BY item;

-- These results will not change when you run the query.
/*----------+------------------*
 | item     | average_quantity |
 +----------+------------------+
 | scissors | 8                |
 | pencil   | 40               |
 | pen      | 18.5             |
 *----------+------------------*/

Note: For more information about when and when not to use noise, see Remove noise.

COUNT (DIFFERENTIAL_PRIVACY)

• Signature 1: Returns the number of rows in a differentially private FROM clause.
• Signature 2: Returns the number of non-NULL values in an expression.

Signature 1

WITH DIFFERENTIAL_PRIVACY ...
  COUNT(
    *,
    [contribution_bounds_per_group => (lower_bound, upper_bound)]
  )

Description

Returns the number of rows in the differentially private FROM clause. The final result is an aggregation across a privacy unit column.

This function must be used with the DIFFERENTIAL_PRIVACY clause and can support the following argument:

• contribution_bounds_per_group: The contribution bounds named argument. Perform clamping per each group separately before performing intermediate grouping on the privacy unit column.

Return type

INT64

Examples

The following differentially private query counts the number of requests for each item. This query references a table called professors.

-- With noise, using the epsilon parameter.
SELECT
  WITH DIFFERENTIAL_PRIVACY
    OPTIONS(epsilon=10, delta=.01, max_groups_contributed=1, privacy_unit_column=id)
    item,
    COUNT(*, contribution_bounds_per_group=>(0, 100)) times_requested
FROM professors
GROUP BY item;

-- These results will change each time you run the query.
-- Smaller aggregations might be removed.
/*----------+-----------------*
 | item     | times_requested |
 +----------+-----------------+
 | pencil   | 5               |
 | pen      | 2               |
 *----------+-----------------*/

-- Without noise, using the epsilon parameter.
-- (this un-noised version is for demonstration only)
SELECT
  WITH DIFFERENTIAL_PRIVACY
    OPTIONS(epsilon=1e20, delta=.01, max_groups_contributed=1, privacy_unit_column=id)
    item,
    COUNT(*, contribution_bounds_per_group=>(0, 100)) times_requested
FROM professors
GROUP BY item;

-- These results will not change when you run the query.
/*----------+-----------------*
 | item     | times_requested |
 +----------+-----------------+
 | scissors | 1               |
 | pencil   | 4               |
 | pen      | 3               |
 *----------+-----------------*/

Note: For more information about when and when not to use noise, see Remove noise.

Signature 2

WITH DIFFERENTIAL_PRIVACY ...
  COUNT(
    expression,
    [contribution_bounds_per_group => (lower_bound, upper_bound)]
  )

Description

Returns the number of non-NULL expression values. The final result is an aggregation across a privacy unit column.

This function must be used with the DIFFERENTIAL_PRIVACY clause and can support these arguments:

• expression: The input expression. This expression can be any numeric input type, such as INT64.
• contribution_bounds_per_group: The contribution bounds named argument. Perform clamping per each group separately before performing intermediate grouping on the privacy unit column.

Return type

INT64

Examples

The following differentially private query counts the number of requests made for each type of item. This query references a table called professors.

-- With noise, using the epsilon parameter.
SELECT
  WITH DIFFERENTIAL_PRIVACY
    OPTIONS(epsilon=10, delta=.01, max_groups_contributed=1, privacy_unit_column=id)
    item,
    COUNT(item, contribution_bounds_per_group => (0,100)) times_requested
FROM professors
GROUP BY item;

-- These results will change each time you run the query.
-- Smaller aggregations might be removed.
/*----------+-----------------*
 | item     | times_requested |
 +----------+-----------------+
 | pencil   | 5               |
 | pen      | 2               |
 *----------+-----------------*/

-- Without noise, using the epsilon parameter.
-- (this un-noised version is for demonstration only)
SELECT
  WITH DIFFERENTIAL_PRIVACY
    OPTIONS(epsilon=1e20, delta=.01, max_groups_contributed=1, privacy_unit_column=id)
    item,
    COUNT(item, contribution_bounds_per_group => (0,100)) times_requested
FROM professors
GROUP BY item;

-- These results will not change when you run the query.
/*----------+-----------------*
 | item     | times_requested |
 +----------+-----------------+
 | scissors | 1               |
 | pencil   | 4               |
 | pen      | 3               |
 *----------+-----------------*/

Note: For more information about when and when not to use noise, see Remove noise.

PERCENTILE_CONT (DIFFERENTIAL_PRIVACY)

WITH DIFFERENTIAL_PRIVACY ...
  PERCENTILE_CONT(
    expression,
    percentile,
    contribution_bounds_per_row => (lower_bound, upper_bound)
  )

Description

Takes an expression and computes a percentile for it. The final result is an aggregation across privacy unit columns.

This function must be used with the DIFFERENTIAL_PRIVACY clause and can support these arguments:

• expression: The input expression. This can be most numeric input types, such as INT64. NULL values are always ignored.
• percentile: The percentile to compute. The percentile must be a literal in the range [0, 1].
• contribution_bounds_per_row: The contribution bounds named argument. Perform clamping per each row separately before performing intermediate grouping on the privacy unit column.

NUMERIC and BIGNUMERIC arguments are not allowed. If you need them, cast them as the FLOAT64 data type first.

Return type

FLOAT64

Examples

The following differentially private query gets the percentile of items requested. Smaller aggregations might not be included. This query references a view called professors.

-- With noise, using the epsilon parameter.
SELECT
  WITH DIFFERENTIAL_PRIVACY
    OPTIONS(epsilon=10, delta=.01, max_groups_contributed=1, privacy_unit_column=id)
    item,
    PERCENTILE_CONT(quantity, 0.5, contribution_bounds_per_row => (0,100)) percentile_requested
FROM professors
GROUP BY item;

-- These results will change each time you run the query.
-- Smaller aggregations might be removed.
 /*----------+----------------------*
  | item     | percentile_requested |
  +----------+----------------------+
  | pencil   | 72.00011444091797    |
  | scissors | 8.000175476074219    |
  | pen      | 23.001075744628906   |
  *----------+----------------------*/

SUM (DIFFERENTIAL_PRIVACY)

WITH DIFFERENTIAL_PRIVACY ...
  SUM(
    expression,
    [contribution_bounds_per_group => (lower_bound, upper_bound)]
  )

Description

Returns the sum of non-NULL, non-NaN values in the expression. The final result is an aggregation across privacy unit columns.

This function must be used with the DIFFERENTIAL_PRIVACY clause and can support these arguments:

• expression: The input expression. This can be any numeric input type, such as INT64. NULL values are always ignored.
• contribution_bounds_per_group: The contribution bounds named argument. Perform clamping per each group separately before performing intermediate grouping on the privacy unit column.

Return type

One of the following supertypes:

• INT64
• FLOAT64

Examples

The following differentially private query gets the sum of items requested. Smaller aggregations might not be included. This query references a view called professors.

-- With noise, using the epsilon parameter.
SELECT
  WITH DIFFERENTIAL_PRIVACY
    OPTIONS(epsilon=10, delta=.01, max_groups_contributed=1, privacy_unit_column=id)
    item,
    SUM(quantity, contribution_bounds_per_group => (0,100)) quantity
FROM professors
GROUP BY item;

-- These results will change each time you run the query.
-- Smaller aggregations might be removed.
/*----------+-----------*
 | item     | quantity  |
 +----------+-----------+
 | pencil   | 143       |
 | pen      | 59        |
 *----------+-----------*/

-- Without noise, using the epsilon parameter.
-- (this un-noised version is for demonstration only)
SELECT
  WITH DIFFERENTIAL_PRIVACY
    OPTIONS(epsilon=1e20, delta=.01, max_groups_contributed=1, privacy_unit_column=id)
    item,
    SUM(quantity) quantity
FROM professors
GROUP BY item;

-- These results will not change when you run the query.
/*----------+----------*
 | item     | quantity |
 +----------+----------+
 | scissors | 8        |
 | pencil   | 144      |
 | pen      | 58       |
 *----------+----------*/

Note: For more information about when and when not to use noise, see Use differential privacy.

Clamp values in a differentially private aggregate function

In differentially private queries, aggregation clamping is used to limit the contribution of outliers. You can clamp explicitly or implicitly as follows:

• Clamp explicitly in the DIFFERENTIAL_PRIVACY clause.
• Clamp implicitly in the DIFFERENTIAL_PRIVACY clause.

Implicitly clamp values

If you don't include the contribution bounds named argument with the DIFFERENTIAL_PRIVACY clause, clamping is implicit, which means bounds are derived from the data itself in a differentially private way.

Implicit bounding works best when computed using large datasets. For more information, see Implicit bounding limitations for small datasets.

Details

In differentially private aggregate functions, explicit clamping is optional. If you don't include this clause, clamping is implicit, which means bounds are derived from the data itself in a differentially private way. The process is somewhat random, so aggregations with identical ranges can have different bounds.

Implicit bounds are determined for each aggregation. So if some aggregations have a wide range of values, and others have a narrow range of values, implicit bounding can identify different bounds for different aggregations as appropriate. Implicit bounds might be an advantage or a disadvantage depending on your use case. Different bounds for different aggregations can result in lower error. Different bounds also means that different aggregations have different levels of uncertainty, which might not be directly comparable. Explicit bounds, on the other hand, apply uniformly to all aggregations and should be derived from public information.

When clamping is implicit, part of the total epsilon is spent picking bounds. This leaves less epsilon for aggregations, so these aggregations are noisier.

Example

The following anonymized query clamps each aggregate contribution for each differential privacy ID and within a derived range from the data itself. As long as all or most values fall within this range, your results will be accurate. This query references a view called view_on_professors.

--Without noise (this un-noised version is for demonstration only)
SELECT WITH DIFFERENTIAL_PRIVACY
  OPTIONS (
    epsilon = 1e20,
    delta = .01,
    privacy_unit_column=id
  )
  item,
  AVG(quantity) average_quantity
FROM view_on_professors
GROUP BY item;

/*----------+------------------*
 | item     | average_quantity |
 +----------+------------------+
 | scissors | 8                |
 | pencil   | 72               |
 | pen      | 18.5             |
 *----------+------------------*/

Explicitly clamp values

contribution_bounds_per_group => (lower_bound,upper_bound)

contribution_bounds_per_row => (lower_bound,upper_bound)

Use the contribution bounds named argument to explicitly clamp values per group or per row between a lower and upper bound in a DIFFERENTIAL_PRIVACY clause.

Input values:

• contribution_bounds_per_row: Contributions per privacy unit are clamped on a per-row (per-record) basis. This means the following:
  • Upper and lower bounds are applied to column values in individual rows produced by the input subquery independently.
  • The maximum possible contribution per privacy unit (and per grouping set) is the product of the per-row contribution limit and max_groups_contributed differential privacy parameter.
• contribution_bounds_per_group: Contributions per privacy unit are clamped on a unique set of entity-specified GROUP BY keys. The upper and lower bounds are applied to values per group after the values are aggregated per privacy unit.
• lower_bound: Numeric literal that represents the smallest value to include in an aggregation.
• upper_bound: Numeric literal that represents the largest value to include in an aggregation.

NUMERIC and BIGNUMERIC arguments are not allowed.

Details

In differentially private aggregate functions, clamping explicitly clamps the total contribution from each privacy unit column to within a specified range.

Explicit bounds are uniformly applied to all aggregations. So even if some aggregations have a wide range of values, and others have a narrow range of values, the same bounds are applied to all of them. On the other hand, when implicit bounds are inferred from the data, the bounds applied to each aggregation can be different.

Explicit bounds should be chosen to reflect public information. For example, bounding ages between 0 and 100 reflects public information because the age of most people generally falls within this range.

Important: The results of the query reveal the explicit bounds. Do not use explicit bounds based on the entity data; explicit bounds should be based on public information.

Examples

The following anonymized query clamps each aggregate contribution for each differential privacy ID and within a specified range (0 and 100). As long as all or most values fall within this range, your results will be accurate. This query references a view called view_on_professors.

--Without noise (this un-noised version is for demonstration only)
SELECT WITH DIFFERENTIAL_PRIVACY
  OPTIONS (
    epsilon = 1e20,
    delta = .01,
    privacy_unit_column=id
  )
  item,
  AVG(quantity, contribution_bounds_per_group=>(0,100)) AS average_quantity
FROM view_on_professors
GROUP BY item;

/*----------+------------------*
 | item     | average_quantity |
 +----------+------------------+
 | scissors | 8                |
 | pencil   | 40               |
 | pen      | 18.5             |
 *----------+------------------*/

Notice what happens when most or all values fall outside of the clamped range. To get accurate results, ensure that the difference between the upper and lower bound is as small as possible, and that most inputs are between the upper and lower bound.

--Without noise (this un-noised version is for demonstration only)
SELECT WITH DIFFERENTIAL_PRIVACY
  OPTIONS (
    epsilon = 1e20,
    delta = .01,
    privacy_unit_column=id
  )
  item,
  AVG(quantity, contribution_bounds_per_group=>(50,100)) AS average_quantity
FROM view_on_professors
GROUP BY item;

/*----------+------------------*
 | item     | average_quantity |
 +----------+------------------+
 | scissors | 54               |
 | pencil   | 58               |
 | pen      | 51               |
 *----------+------------------*/

Note: For more information about when and when not to use noise, see Remove noise.