The most commonly used functions in BigQuery are focused on
data retrieval, transformation, and analysis. Some of the top functions
include:
1. Aggregate
Functions
- COUNT(): Returns
the number of rows that match a specified condition.
- SUM(): Adds up
the values of a numeric column.
- AVG(): Calculates
the average value of a numeric column.
- MAX(): Returns
the maximum value in a column.
- MIN(): Returns
the minimum value in a column.
2. String Functions
- CONCAT():
Concatenates multiple strings into one.
- FORMAT(): Formats
strings according to the given format.
- SUBSTR():
Extracts a substring from a string.
- UPPER()/LOWER():
Converts text to uppercase or lowercase.
- REGEXP_EXTRACT():
Extracts a substring that matches a regular expression.
3. Date and Time
Functions
- CURRENT_DATE() /
CURRENT_TIMESTAMP(): Returns the current date or timestamp.
- DATE_ADD() /
TIMESTAMP_ADD(): Adds an interval to a date or timestamp.
- DATE_DIFF() /
TIMESTAMP_DIFF(): Computes the difference between two dates or timestamps.
- EXTRACT():
Extracts parts of a date or timestamp (like year, month, day).
4. Array Functions
- ARRAY_AGG():
Aggregates values into an array.
- ARRAY_CONCAT():
Concatenates arrays.
- ARRAY_LENGTH():
Returns the number of elements in an array.
- UNNEST():
Converts an array into a set of rows.
5. Analytic Functions
- ROW_NUMBER():
Assigns a unique number to rows in a result set.
- RANK(): Assigns a
rank to rows based on a specific column's value.
- LEAD() / LAG():
Accesses data from preceding or following rows.
- PERCENT_RANK():
Returns the relative rank of a row.
6. Mathematical
Functions
- ROUND(): Rounds
numeric values to a specified precision.
- ABS(): Returns
the absolute value of a number.
- FLOOR() / CEIL():
Rounds a number down or up to the nearest integer.
7. JSON Functions
- JSON_EXTRACT():
Extracts a value from a JSON object.
- JSON_EXTRACT_SCALAR():
Extracts a scalar value (like a string or number) from a JSON object.
- JSON_QUERY():
Extracts and returns JSON elements that match a path.
8. Window Functions
- NTILE(): Divides
rows into a specified number of groups.
- CUME_DIST():
Calculates the cumulative distribution of a value in a set.
