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.google_ads_impressions
LEFT JOIN adh.google_ads_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.google_ads_impressions a
JOIN adh.google_ads_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.