Conversions:

  • CAST ( expr AS type )

 

Aggregate functions:

  • Return single aggregate value for group of rows
  • ANY_VALUE ( expr) [ OVER (…) ] FROM …
  • ARRAY_AGG( DISTINCT …. IGNORE|RESPECT NULLS ORDER BY …. LIMIT …) [ OVER () ]
  • ARRAY_CONCAT_AGG
  • AVG
  • BIT_AND
  • BIT_OR
  • BIT_XOR
  • COUNT
  • COUNTIF (expr) [ OVER () ]
  • LOGICAL_AND
  • LOGICAL_OR
  • MAX
  • MIN
  • STRING_AGG
  • SUM

 

Statistical aggregate functions:

  • CORR
  • COVAR_POP
  • COVAR_SAMP
  • STDDEV_POP
  • STDDEV_SAMP
  • STDDEV
  • VAR_POP
  • VAR_SAMP
  • VARIANCE

 

Approximate aggregate functions:

  • Produce approximate results
  • APPROX_COUNT_DISTINCT
  • APPROX_QUANTILES
  • APPROX_TOP_COUNT
  • APPROX_TOP_SUM

 

HyperLogLog++ functions:

  • HLL_COUNT.INIT
  • HLL_COUNT.MERGE
  • HLL_COUNT.MERGE_PARTIAL
  • HLL_COUNT.EXTRACT

 

Analytic functions:

  • Return value for every row
  • FUNCTION_NAME ( argument ) OVER ( PARTITION BY … ORDER BY … window_name )
  • Frame start: ROWS | RANGE
    • UNBOUNDED PRECEDING | … PRECEDING | CURRENT ROW | BETWEEN … AND …
  • Frame end: ROWS | RANGE
    • UNBOUNDED FOLLOWING | … FOLLOWING | CURRENT ROW | BETWEEN … AND …
  • RANK – skips following ordinal number(s) in case of duplicities
  • DENSE_RANK – uses all ordinal numbers even in case of duplicities
  • PERCENT_RANK
  • CUME_DIST
  • NTILE
  • ROW_NUMBER – simple order
  • FIRST_VALUE
  • LAST_VALUE
  • NTH_VALUE
  • LEAD – following values
  • LAG – preceding values

 

Aggregate analytic functions:

  • ANY_VALUE
  • ARRAY_AGG
  • AVG
  • COUNT
  • COUNTIF
  • MAX
  • MIN
  • STRING_AGG
  • SUM

 

Bit functions:

  • BIT_COUNT

 

Mathematical functions:

  • ABS
  • SIGN
  • IS_INF
  • IS_NAN
  • IEEE_DIVIDE – never fails, returns NaN in case of error
  • RAND
  • SQRT
  • POW, POWER
  • EXP
  • LN
  • LOG
  • LOG10
  • GREATEST (list)
  • LEAST (list)
  • DIV
  • SAVE_DIVIDE – returns NULL in case of error
  • MOD

 

Hash functions:

  • FARM_FINGERPRINT
  • MD5
  • SHA1
  • SHA256
  • SHA512

 

String functions:

  • BYTE_LENGTH
  • CHAR_LENGTH, CHARACTER_LENGTH
  • CODE_POINTS_TO_BYTES
  • CODE_POINTS_TO_STRING
  • CONCAT
  • ENDS_WITH
  • FORMAT
  • FROM_BASE64
  • LENGTH
  • LPAD
  • LOWER
  • LTRIM
  • REGEXP_CONTAINS
  • REGEXP_EXTRACT
  • REGEXP_EXTRACT_ALL
  • REGEXP_REPLACE
  • REPLACE
  • REPEAT
  • REVERSE
  • RPAD
  • RTRIM
  • SAFE_CONVERT_BYTES_TO_STRING
  • SPLIT
  • STARTS_WITH
  • STRPOS
  • SUBSTR
  • TO_BASE64
  • TO_CODE_POINTS
  • TRIM
  • UPPER

 

JSON functions:

  • JSON_EXTRACT
  • JSON_EXTRACT_SCALAR

 

Array functions:

  • ARRAY_CONCAT
  • ARRAY_LENGTH
  • ARRAY_TO_STRING
  • GENERATE_ARRAY
  • GENERATE_DATE_ARRAY
  • OFFSET and ORDINAL
  • ARRAY_REVERSE
  • SAFE_OFFSET and SAFE_ORDINAL

 

Date functions:

  • CURRENT_DATE
  • EXTRACT (part FROM date)
  • DATE
  • DATE_ADD (date, INTERVAL … )
  • DATE_SUB
  • DATE_DIFF
  • DATE_TRUNC
  • DATE_FROM_UNIX_DATE
  • FORMAT_DATE
  • PARSE_DATE
  • UNIX_DATE

 

Datetime functions:

  • CURRENT_DATETIME
  • DATETIME
  • DATETIME_ADD
  • DATETIME_SUB
  • DATETIME_DIFF
  • DATETIME_TRUNC
  • FORMAT_DATETIME
  • PARSE_DATETIME

 

Time functions:

  • CURRENT_TIME
  • TIME
  • TIME_ADD
  • TIME_SUB
  • TIME_DIFF
  • TIME_TRUNC
  • FORMAT_TIME
  • PARSE_TIME

 

Timestamp functions:

  • CURRENT_TIMESTAMP
  • EXTRACT
  • STRING
  • TIMESTAMP
  • TIMESTAMP_ADD
  • TIMESTAMP_SUB
  • TIMESTAMP_DIFF
  • TIMESTAMP_TRUNC
  • FORMAT_TIMESTAMP
  • PARSE_TIMESTAMP
  • TIMESTAMP_SECONDS
  • TIMESTAMP_MILLIS
  • TIMESTAMP_MICROS
  • UNIX_SECONDS
  • UNIX_MILLIS
  • UNIX_MICROS

 

Security functions:

  • SESSION_USER

 

NET functions:

  • NET.IP_FROM_STRING
  • NET.SAFE_IP_FROM_STRING
  • NET.IP_TO_STRING
  • NET.IP_NET_MASK
  • NET.IP_TRUNC
  • NET.IPV4_FROM_INT64
  • NET.IPV4_TO_INT64
  • NET.HOST
  • NET.PUBLIC_SUFFIX
  • NET.REG_DOMAIN

 

 

Conditional expressions:

  • CASE … WHEN … THEN … ELSE … END
  • COALESCE ()
  • IF (cond, true, else)
  • IFNULL(expr, null_result)
  • NULLIF(expr, expr_to_match)

 

Expression subqueries

  • Scalar
  • ARRAY
  • IN
  • EXISTS
  • STRUCT