For privacy reasons, we restrict which SQL functions you can use in ADH.
Scalar functions
All scalar functions except the following are allowed:
ERRORST_AREAST_ASBINARYST_ASGEOJSONST_ASTEXTST_BOUNDARYST_CENTROIDST_CENTROID_AGGST_CLOSESTPOINTST_CLUSTERDBSCANST_CONTAINSST_CONVEXHULLST_COVEREDBYST_COVERSST_DIFFERENCEST_DIMENSIONST_DISJOINTST_DISTANCEST_DUMPST_DWITHINST_EQUALSST_GEOGFROMGEOJSONST_GEOGFROMTEXTST_GEOGFROMWKBST_GEOGPOINTST_GEOGPOINTFROMGEOHASHST_GEOHASHST_INTERSECTIONST_INTERSECTSST_INTERSECTSBOXST_ISCOLLECTIONST_ISEMPTYST_LENGTHST_MAKELINEST_MAKEPOLYGONST_MAKEPOLYGONORIENTEDST_MAXDISTANCEST_NPOINTSST_NUMPOINTSST_PERIMETERST_SIMPLIFYST_SNAPTOGRIDST_TOUCHESST_UNIONST_UNION_AGGST_WITHINST_XST_Y
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 user's 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 |
No |
BIT_OR |
No |
BIT_XOR |
No |
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:
CREATE TABLE paths AS (
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)
User list queries
In user list queries, aggregate functions are allowed, and analytic functions aren't supported.