Allowed SQL functions

For privacy reasons, we restrict which SQL functions you can use in ADH.

Scalar functions

All scalar functions are allowed.

Analysis queries

For aggregated analysis queries, we restrict functions that combine data from multiple users. All aggregate and analytic functions are allowed when processing a single users data. See User-level aggregations below for details.

Aggregate functions

Function Allowed?
ANY_VALUE Yes
APPROX_QUANTILES No
APPROX_TOP_COUNT No
APPROX_TOP_SUM No
ARRAY_AGG No
ARRAY_CONCAT_AGG No
AVG Yes
BIT_AND Yes
BIT_OR Yes
BIT_XOR Yes
COUNT Yes
COUNTIF Yes
LOGICAL_AND Yes
LOGICAL_OR Yes
MAX Yes
MIN Yes
SUM Yes
CORR Yes
COVAR_POP Yes
COVAR_SAMP Yes
STDDEV_POP Yes
STDDEV_SAMP Yes
STDDEV Yes
STRING_AGG No
VAR_POP Yes
VAR_SAMP Yes
VARIANCE Yes
APPROX_COUNT_DISTINCT Yes

Analytic functions

All analytic functions are restricted.

User-level aggregations

We allow all aggregate and analytic functions in SELECT statements that group or partition by user_id. For this to work, any preceding joins must make it clear that the data is kept at the user level by joining on user_id, like in this example:

SELECT ARRAY_AGG(campaign_id ORDER BY query_id.time_usec) AS path
FROM
  adh.adwords_impressions
  LEFT JOIN adh.adwords_clicks USING(query_id, user_id)
GROUP BY user_id

If this isn't possible because the IDs are separate and don't join, then the query should group or partition by all independent IDs, like in this example:

SELECT
  a.user_id AS user_a,
  b.user_id AS user_b,
  ARRAY_AGG(DISTINCT campaign_id) campaigns
FROM
  adh.adwords_impressions a
  JOIN adh.adwords_impressions b USING (campaign_id)
GROUP BY 1, 2

User list queries

In user list queries, aggregate functions are allowed, and analytic functions aren't supported.