Commonly used functions in BigQuery in Google Cloud Platform (GCP)

 

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.