appRules
  • Welcome to appRules®
  • PLATFORM
    • appRules® Concepts & Features
    • Platform Overview
    • Platform Features
      • Sourced Values
        • Overview
        • Sourced Values List
      • Actions
        • Overview
        • Action Categories
          • Actions-DataSource
            • Accounting
            • AIMachineLearning
            • Analytics
            • ApplicationPlatforms
            • BigData
            • CalendarsAndContactMgrs
            • CloudStorage
            • Collaboration
            • CRM
            • CustomerService
            • Databases
            • DataWarehouse
            • ECommercePlatforms
            • EmailSystems
            • ERP
            • ExpenseManagement
            • Files
            • Financial
            • Marketing
            • Miscelleanous
            • NoSQL
            • PaymentProcessing
            • PLM
            • ProjectManagement
            • Search
            • Services
            • ShippingAndMailing
            • Social
            • Spreadsheets
          • Actions-Execution
          • Actions-Fields-Field
          • Actions-Fields-Library-Financial
          • Actions-Fields-Library-Math
          • Actions-Utility-Automation
          • Actions-Utility-FileSystem
      • Conditions
        • Overview
        • Condition Categories
          • Conditions-Cache
          • Conditions-Custom
          • Conditions-Fields
          • Conditions-FileSystem
          • Conditions-Records
          • Conditions-Results
          • Conditions-Workflow
      • Workflow Activities
        • Control Flow Activities
        • Base Activities
        • Dialog Activities
        • Base Data Activities
      • System Settings
        • Runtime Settings
        • Users
        • Data Sources
          • Overview
          • Configuring Connection Settings
          • Generating Metadata
          • Connection Settings
            • Accounting
              • Exact Online
              • Raisers Edge NXT
              • Xero
              • Quickbooks
              • Quickbooks Online
              • Quickbooks POS
              • Freshbooks
              • Sage Business Cloud Accounting
              • Sage Intacct
              • Sage50 UK
              • Reckon
            • Application Platforms
              • Alfresco Platform
              • Kintone Platform
              • Quickbase Platform
              • Veeva Vault
            • AI & Machine Learning
              • DataRobot
              • Salesforce Einstein
              • Splunk
            • Analytics
              • Google Analytics
              • Adobe Analytics
                • Adobe Custom oAuth App
              • Azure Analysis Services
              • SAS
              • Youtube Analytics
            • Big Data
              • Apache HBase
              • Apache Hive
              • Apache HDFS
              • Apache Spark
              • Greenplum
              • HPCC LexisNexis
            • Calendar & Contact Managers
              • Google Calendar
              • Google Contacts
            • Cloud Storage
              • Azure Table Storage
              • Amazon S3
              • IBM Cloud Object Storage
              • Wasabi
            • Collaboration
              • Airtable
              • Asana
              • Box
              • Dropbox
              • Exchange
              • Google Drive
              • Microsoft Teams
              • Office365
              • Microsoft OneDrive
              • Microsoft OneNote
              • Salesforce Chatter
              • SharePoint
              • Slack
              • Smartsheet
            • CRM
              • Act! CRM
              • Bullhorn CRM
              • Microsoft Dynamics 365 Sales
              • Microsoft Dynamics CRM
              • Highrise
              • Oracle Sales Cloud
              • Salesforce
              • SAP Hybris C4C
              • Sugar CRM
              • Suite CRM
              • Veeva CRM
              • Zoho CRM
            • Customer Service & Support
              • ServiceNow
              • Zendesk
              • Jira
            • Data Warehouse
              • Azure Data Lake Storage Gen1
              • Azure Data Lake Storage Gen2
              • Google BigQuery
              • Redshift
              • Databricks
              • Snowflake
              • Azure Synapse
            • E-Commerce Platforms
              • Amazon MarketPlace
              • Magento
              • Shopify
              • Woocommerce
            • Electronic Signature
              • Docusign
            • Email
              • Gmail
              • Email
            • ERP
              • NetSuite
              • Odoo
              • Acumatica
              • Dynamics NAV
              • Dynamics GP
              • Microsoft Dynamics 365 FinOp
              • Microsoft Dynamics365 Business Central
                • Business Central Endpoints
              • SAP Business One
              • SAP ERP
            • Expense Management
              • SAP Concur
            • Files
              • CSV Database
              • Fixed Length File
              • Delimited File
            • Marketing
              • Bing Ads
              • Marketo
              • MailChimp
              • HubSpot
              • Eloqua
              • Salesforce Marketing Cloud
            • NoSQL
              • Cassandra
                • Cassandra Advanced Settings
              • Azure CosmosDB
              • DynamoDB
              • Couchbase
              • MongoDB
              • IBM Cloudant
              • Redis
            • Payment Processing
              • Authorize.Net
              • Square
              • Paypal
              • Stripe
            • Product Lifecycle Management (PLM)
              • Propel
            • Relational Databases
              • appRules Embedded Database
              • Firebird
              • IBM DB2
              • IBM Informix
              • Microsoft Access
              • MySQL
              • MariaDB
              • Oracle
              • PostgreSQL
              • Microsoft SQL Server
              • Microsoft SQL Server CE
              • SQL Azure
              • SQLlite
              • Vertica
            • Search
              • ElasticSearch
              • Bing
              • Google Search
            • Shipping and Mailing
              • Fedex
              • UPS
              • USPS
            • Social Networks
              • Linkedin
              • Facebook
              • Instagram
              • Twitter
            • Services
              • REST
              • RSS
              • XML
                • Connecting to XML Datasources
                • Parsing Hierarchical Data
                • Raw Data
              • Active Directory
              • Amazon Athena
              • Microsoft Dataverse
              • JSON
              • LDAP
              • GraphQL
            • Spreadsheets
              • Excel
              • Excel Services
              • Excel Online
              • GoogleSheets
          • SQL Compliance
            • Accounting
              • Exact Online
              • Raisers Edge NXT
              • Xero
              • Quickbooks
              • Quickbooks Online
              • QuickBooks POS
              • Freshbooks
              • Reckon
              • Sage Intacct
              • Sage Business Cloud Accounting
              • Sage50 UK
            • AI & Machine Learning
              • Salesforce Einstein
              • DataRobot
              • Splunk
            • Analytics
              • Google Analytics
              • Youtube Analytics
              • Azure Analysis Services
              • Adobe Analytics
              • SAS
            • Application Platforms
              • Alfresco Platform
              • Kintone Platform
              • Quick Base Platform
              • Veeva Vault
            • Big Data
              • Apache HBase
              • Apache HDFS
              • Apache Hive
              • Apache Spark
              • Greenplum
              • HPCC Systems
            • Calendar & Contact Managers
              • Google Calendar
              • Google Contacts
            • Cloud Storage
              • Azure Table Storage
              • Amazon S3
              • Wasabi
              • IBM Cloud Object Storage
            • Collaboration
              • Airtable
              • Asana
              • Box
              • Dropbox
              • Google Drive
              • Microsoft Teams
              • Microsoft OneDrive
              • Microsoft OneNote
              • Microsoft Exchange
              • Office365
              • Salesforce Chatter
              • SharePoint
              • Slack
              • Smartsheet
            • CRM
              • Act! CRM
              • Bullhorn CRM
              • Highrise
              • Microsoft Dynamics CRM
              • Microsoft Dynamics 365 Sales
              • Oracle Sales Cloud
              • Salesforce
              • SAP Hybris C4C
              • Sugar CRM
              • Veeva CRM
              • Zoho CRM
            • Customer Service & Support
              • ServiceNow
              • Zendesk
              • Jira
            • E-commerce Platforms
              • Woocommerce
              • Magento
              • Shopify
              • Amazon Marketplace
            • DataWarehouse
              • Azure Data Lake Storage Gen1
              • Azure Data Lake Storage Gen2
              • Azure Synapse
              • Databricks
              • Google BigQuery
              • Redshift
              • Snowflake
            • Email
              • Gmail
            • ERP
              • Acumatica
              • NetSuiteSQL
              • NetSuiteTalk
              • Odoo
              • Dynamics NAV
              • Dynamics GP
              • Microsoft Dynamics 365 FinOp
              • Microsoft Dynamics365 Business Central
              • SAP Business One
              • SAP NetWeaver
              • Workday
            • Expense Management
              • SAP Concur
            • Files
              • CSV Database
            • Marketing
              • Bing Ads
              • Google Ads
              • HubSpot
              • MailChimp
              • Marketo
              • Oracle Eloqua
              • Salesforce Marketing Cloud
              • Survey Monkey
              • Youtube 2020
            • NoSQL
              • Cassandra
              • Azure CosmosDB
              • Amazon SimpleDB
              • Amazon DynamoDB
              • Couchbase
              • MongoDB
              • IBM Cloudant
              • Redis
            • Payment Processing
              • Authorize.Net
              • Paypal
              • Stripe
              • Square
            • Product Lifecycle Management (PLM)
              • Propel
            • Project Management
              • Basecamp
              • Microsoft Project
            • Search
              • ElasticSearch
              • Bing
              • Google Search
            • Shipping and Mailing
              • FedEx
              • UPS
              • USPS
            • Spreadsheets
              • Excel Online
              • Excel
              • Excel Services
              • GoogleSheets
            • Social Networks
              • Facebook
              • Instagram
              • Linkedin
              • Twitter
            • Services
              • Active Directory
              • Amazon Athena
              • JSON
              • LDAP
              • Microsoft Dataverse
              • OData
              • REST
              • RSS
              • XML
        • Projects
          • Overview
          • Project Settings
      • Utilities
        • DataSource Browser
        • Job Monitor
        • Logs/Statistics
        • CheckIn/CheckOut
        • Master Admin Options
      • Creating & Running Projects
        • Creating a New Project
        • Orchestrating Workflows with Designer
          • Designer Home Page Overview
          • Using the appRules Designer
        • Reading Data
        • Mapping Data
        • Writing Data
        • Project Validation
        • Running Projects
          • Running from the Home Page
          • Using the Web API
          • Using the Scheduler
        • Monitoring Jobs
        • Logging
      • Flow Data Migration
        • Overview
        • Basic Flow Data Migrations
        • Bulk Loader Flow Data Migrations
    • Self-Hosting
      • Software Installation
        • Installation Prerequisites
        • Running the Installer
        • HostService Settings
        • IIS Settings
        • Web API Settings
        • Scheduler
        • Creating a New Project Database
      • Application Configuration
      • appRules Embedded Database DBA Tool
  • PLATFORM PRODUCTS
    • appRules Data Migration Engine
      • Overview
      • Flow Data Migration
      • The Migrate Data Activity
        • Overview
        • Initializing the Migrate Data Activity
        • Target Entities Collection
          • Overview
          • Toolbar
          • Target Entity Properties
        • Basic Data Migration
        • Matching Schema Migration
        • One-To-Many Migration
        • Entities to Csv Migration
        • Data Replication
        • Data Synchronization
        • Bidirectional Data Synchronization
      • Field Mapping & Transformation
      • Reviewing the Project Settings
      • Validations, Error Handling & Notifications
      • Job Execution & Monitoring
      • Evaluating Data Migration Job Results
    • appRules Integration & Automation Engine
      • Overview
      • Flow Data Migration
      • Process Automation, Business Rules & Decision Support
        • Overview
        • Control Flow Activities
          • If
          • While
        • Conditions & Actions
          • Overview
          • Conditions
          • Actions
        • Evaluating Conditions
        • Performing Actions
        • Decision Tables
        • Executing Rule Sets
    • appRules Enterprise 360
      • Overview
      • Data Migration
      • Integration & Automation
  • Samples
    • Samples Overview
    • Sample Projects
      • Data Migration, Replication & Synchronization
        • Replicate Dynamics365 In Snowflake
        • Migrate Parent/Child Records Using Target / Bulk Loader
        • Export SQL Table to CSV Using Flow
        • One-to-Many Data Migration
        • Batch Data Source Entities to Files
      • Integration & Automation
        • Decision Table - Customer Discount
        • Iterate Deleted Salesforce Records
        • Use CSV Folder As SQL Database
        • Conditional Actions Execution
        • Round Robin Lead Assignment
      • Snippets
        • Snippet - Arguments, Variables, Dynamic Values & Sourced Values
        • Snippet - Counters & Running Totals
        • Snippet - Executing Financial Functions
        • Snippet - Infinite Loop
        • Using Composite Keys
      • Miscellaneous
        • Custom Queries
        • Parent-Child: Child Project
        • Parent-Child: Parent Project
        • Scheduled System Maintenance
        • Target Activity Errors & Logs
        • Task Parallelism
  • APPENDIX
    • Miscellaneous
      • Common Connection Properties
      • Troubleshooting a Connection
      • Dynamics365 - Connection Settings
      • Dynamics365 App Registration
      • Getting the oAuth Access and Refresh Tokens
      • Editing Windows Environment Variables
      • PickLists
      • Creating a Custom Azure oAuth App
      • Using Kerberos
      • Stored Procedures
    • FAQs
Powered by GitBook
On this page
  • SELECT Statements
  • SELECT Syntax
  • Aggregate Functions
  • JOIN Queries
  • Projection Functions
  • Predicate Functions
  • POW(expression1, expression2)
Export as PDF
  1. PLATFORM
  2. Platform Features
  3. System Settings
  4. Data Sources
  5. SQL Compliance
  6. DataWarehouse

Google BigQuery

SELECT Statements

Google BigQuery API Syntax

The Google BigQuery API offers additional SQL operators and functions. A complete list of the available syntax is located at: https://cloud.google.com/bigquery/query-reference

A SELECT statement can consist of the following basic clauses.

  • SELECT

  • INTO

  • FROM

  • JOIN

  • WHERE

  • GROUP BY

  • HAVING

  • UNION

  • ORDER BY

  • LIMIT

SELECT Syntax

The following syntax diagram outlines the syntax supported by the SQL engine of the provider:

SELECT {

[ TOP <numeric_literal> | DISTINCT ]

{

*

| {

<expression> [ [ AS ] <column_reference> ]

| { <table_name> | <correlation_name> } .*

} [ , ... ]

}

[ INTO csv:// [ filename= ] <file_path> [ ;delimiter=tab ] ]

{

FROM <table_reference> [ [ AS ] <identifier> ]

} [ , ... ]

[ [

INNER | { { LEFT | RIGHT | FULL } [ OUTER ] }

] JOIN <table_reference> [ ON <search_condition> ] [ [ AS ] <identifier> ]

] [ ... ]

[ WHERE <search_condition> ]

[ GROUP BY <column_reference> [ , ... ]

[ HAVING <search_condition> ]

[ UNION [ ALL ] <select_statement> ]

[

ORDER BY

<column_reference> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]

]

[

LIMIT <expression>

[

{ OFFSET | , }

<expression>

]

]

} | SCOPE_IDENTITY()

<expression> ::=

| <column_reference>

| @ <parameter>

| ?

| COUNT( * | { [ DISTINCT ] <expression> } )

| { AVG | MAX | MIN | SUM | COUNT } ( <expression> )

| NULLIF ( <expression> , <expression> )

| COALESCE ( <expression> , ... )

| CASE <expression>

WHEN { <expression> | <search_condition> } THEN { <expression> | NULL } [ ... ]

[ ELSE { <expression> | NULL } ]

END

| <literal>

| <sql_function>

<search_condition> ::=

{

<expression> { = | > | < | >= | <= | <> | != | LIKE | NOT LIKE | IN | NOT IN | IS NULL | IS NOT NULL | AND | OR | CONTAINS | BETWEEN } [ <expression> ]

} [ { AND | OR } ... ]

Examples

  1. Return all columns:

    SELECT * FROM publicdata.samples.github_nested

  2. Rename a column:

    SELECT [repository.name] AS MY_repository.name FROM publicdata.samples.github_nested

  3. Cast a column's data as a different data type:

    SELECT CAST(repository.watchers AS VARCHAR) AS Str_repository.watchers FROM publicdata.samples.github_nested

  4. Search data:

    SELECT * FROM publicdata.samples.github_nested WHERE repository.name = 'EntityFramework';

  5. The Google BigQuery APIs support the following operators in the WHERE clause: =, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR.

    SELECT * FROM publicdata.samples.github_nested WHERE repository.name = 'EntityFramework';

  6. Return the number of items matching the query criteria:

    SELECT COUNT(*) AS MyCount FROM publicdata.samples.github_nested

  7. Return the unique items matching the query criteria:

    SELECT DISTINCT repository.name FROM publicdata.samples.github_nested

  8. Summarize data:

    SELECT repository.name, MAX(repository.watchers) FROM publicdata.samples.github_nested GROUP BY repository.name

    See Aggregate Functions below for details.

  9. Retrieve data from multiple tables.

    SELECT * FROM CRMAccounts INNER JOIN ERPCustomers ON CRMAccounts.BillingState = ERPCustomers.BillingState

    See JOIN Queries below for details.

  10. Sort a result set in ascending order:

    SELECT actor.attributes.email, repository.name FROM publicdata.samples.github_nested ORDER BY repository.name ASC

  11. Restrict a result set to the specified number of rows:

    SELECT actor.attributes.email, repository.name FROM publicdata.samples.github_nested LIMIT 10

  12. Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.

    SELECT * FROM publicdata.samples.github_nested WHERE repository.name = @param

Aggregate Functions

Google BigQuery API Syntax

The Google BigQuery API offers additional SQL operators and functions. A complete list of the available syntax is located at: https://cloud.google.com/bigquery/query-reference

Examples of Aggregate Functions

Below are several examples of SQL aggregate functions. You can use these with a GROUP BY clause to aggregate rows based on the specified GROUP BY criterion. This can be a reporting tool.

COUNT

Returns the number of rows matching the query criteria.

SELECT COUNT(*) FROM publicdata.samples.github_nested WHERE repository.name = 'EntityFramework'

AVG

Returns the average of the column values.

SELECT repository.name, AVG(repository.watchers) FROM publicdata.samples.github_nested WHERE repository.name = 'EntityFramework' GROUP BY repository.name

MIN

Returns the minimum column value.

SELECT MIN(repository.watchers), repository.name FROM publicdata.samples.github_nested WHERE repository.name = 'EntityFramework' GROUP BY repository.name

MAX

Returns the maximum column value.

SELECT repository.name, MAX(repository.watchers) FROM publicdata.samples.github_nested WHERE repository.name = 'EntityFramework' GROUP BY repository.name

SUM

Returns the total sum of the column values.

SELECT SUM(repository.watchers) FROM publicdata.samples.github_nested WHERE repository.name = 'EntityFramework'

CORR

Returns the Pearson correlation coefficient of a set of number pairs.

SELECT repository.name, CORR(repository.watchers, repository.size) FROM publicdata.samples.github_nested

COVAR_POP

Computes the population covariance of the values computed by a set of number pairs.

SELECT repository.name, COVAR_POP(repository.watchers, repository.size) FROM publicdata.samples.github_nested

COVAR_SAMP

Computes the sample covariance of the values computed by a set of number pairs.

SELECT repository.name, COVAR_SAMP(repository.watchers, repository.size) FROM publicdata.samples.github_nested

NTH

Returns the nth sequential value in the scope of the function, where n is a constant. The NTH function starts counting at 1, so there is no zeroth term. If the scope of the function has less than n values, the function returns NULL.

SELECT repository.name, NTH(n, actor.attributes.email) FROM publicdata.samples.github_nested

STDDEV

Returns the standard deviation of the computed values. Rows with a NULL value are not included in the calculation.

SELECT repository.name, STDDEV(repository.watchers) FROM publicdata.samples.github_nested

JOIN Queries

The provider supports the complete join syntax in Google BigQuery. Google BigQuery supports inner joins, outer joins, and cross joins. The default is inner. Multiple join operations are supported.

SELECT field_1 [..., field_n] FROM table_1 [[AS] alias_1] [[INNER|[FULL|RIGHT|LEFT] OUTER|CROSS] JOIN [EACH] table_2 [[AS] alias_2] [ON join_condition_1 [... AND join_condition_n]] ]+

Note that the default join is an inner join. The following limitations exist on joins in Google BigQuery:

  • Cross joins must not contain an ON clause.

  • Normal joins require that the right-side table must contain less than 8 MB of compressed data. If you are working with tables larger than 8 MB, use the EACH modifier. Note that EACH cannot be used in cross joins.

Projection Functions

ANY_VALUE(expression)

Returns any value from the input or NULL if there are zero input rows. The value returned is non-deterministic, which means you might receive a different result each time you use this function. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

  • expression: The expression to retrieve a value from.

APPROX_COUNT_DISTINCT(expression)

Returns the approximate result for COUNT(DISTINCT expression). The value returned is a statistical estimate-not necessarily the actual value. This function is less accurate than COUNT(DISTINCT expression), but performs better on huge input. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

  • expression: The expression to perform the approximate count distinct on.

APPROX_QUANTILES(expression, number)

Returns the approximate boundaries for a group of expression values, where number represents the number of quantiles to create. This function returns an array of number + 1 elements, where the first element is the approximate minimum and the last element is the approximate maximum. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

  • expression: The expression to perform the approximate quantiles on.

  • number: The number of quantiles to create.

APPROX_TOP_COUNT(expression, number)

Returns the approximate top elements of expression. The number parameter specifies the number of elements returned. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

  • expression: The expression to perform the approximate top count on.

  • number: The number of elements to be returned.

APPROX_TOP_SUM(expression, weight, number)

Returns the approximate top elements of expression, based on the sum of an assigned weight. The number parameter specifies the number of elements returned. If the weight input is negative or NaN, this function returns an error. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

  • expression: The expression to perform the approximate top sum on.

  • weight: The assigned weight.

  • number: The number of elements to be returned.

ARRAY(subquery)

The ARRAY function returns an ARRAY with one element for each row in a subquery. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

  • subquery: The subquery to execute.

ARRAY_CONCAT(array_expr1 [, array_expr2 [, ...]])

Concatenates one or more arrays with the same element type into a single array. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

  • array_expr1: The first array to concatenate.

  • array_expr2: The second array to concatenate.

ARRAY_LENGTH(array_expr)

Returns the size of the array. Returns 0 for an empty array. Returns NULL if the array_expression is NULL. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

  • array_expr: The array expression to get the size of.

ARRAY_TO_STRING(array_expr, delimiter [, null_text])

Returns a concatenation of the elements in array_expression as a STRING. The value for array_expression can either be an array of STRING or BYTES data types. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

  • array_expr: The array expression to convert to string.

  • delimiter: The delimiter string used to delimit the array values.

  • null_text: If the null_text parameter is used, the function replaces any NULL values in the array with the value of null_text. If the null_text parameter is not used, the function omits the NULL value and its preceding delimiter.

GENERATE_ARRAY(start_expr, end_expr [, step_expr])

Returns an array of values. The start_expression and end_expression parameters determine the inclusive start and end of the array. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

  • start_expr: The starting value.

  • end_expr: The ending value.

  • step_expr: The increment used to generate array values.

GENERATE_DATE_ARRAY(start_date, end_date [, INTERVAL int_expr date_part])

Returns an array of dates. The start_date and end_date parameters determine the inclusive start and end of the array. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

  • start_date: The starting date.

  • end_date: The ending date.

  • int_expr: The increment used to generate dates.

  • date_part: The date part used to increment the generated dates. Valid values are: DAY, WEEK, MONTH, QUARTER, and YEAR.

ARRAY_REVERSE(array_expr)

Returns the input ARRAY with elements in reverse order. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

  • array_expr: The array to reverse.

ARRAY_AGG(expression)

Returns an ARRAY of expression values. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

  • expression: The expression values to generate an array from.

ARRAY_CONCAT_AGG(expression1[, expression2 [,...]])

Concatenates elements from expression of type ARRAY, returning a single ARRAY as a result. This function ignores NULL input arrays, but respects the NULL elements in non-NULL input arrays (an error is raised, however, if an array in the final query result contains a NULL element). Note: this function is only available when using Standard SQL (UseLegacySQL=False).

  • expression1: The first expression to concatenate.

  • expression2: The first expression to concatenate.

AVG([DISTINCT] expression)

Returns the average on non-null values. Each distinct value of expression is aggregated only once into the result.

  • expression: The expression to use to compute the average.

BIT_AND(numeric_expression)

Returns the result of a bitwise AND operation between each instance of numeric_expr across all rows. NULL values are ignored. This function returns NULL if all instances of numeric_expr evaluate to NULL.

  • numeric_expression: The numeric expression to perform the bitwise operation.

BIT_COUNT(expression)

The input, expression, must be an integer or BYTES. Returns the number of bits that are set in the input expression. For integers, this is the number of bits in two's complement form. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

  • expression: The expression to perform the bit count operation on.

BIT_OR(numeric_expression)

Returns the result of a bitwise OR operation between each instance of numeric_expr across all rows. NULL values are ignored. This function returns NULL if all instances of numeric_expr evaluate to NULL.

  • numeric_expression: The numeric expression to perform the bitwise operation.

BIT_XOR(numeric_expression)

Returns the result of a bitwise XOR operation between each instance of numeric_expr across all rows. NULL values are ignored. This function returns NULL if all instances of numeric_expr evaluate to NULL.

  • numeric_expression: The numeric expression to perform the bitwise operation.

CORR(numeric_expression1, numeric_expression2)

Returns the Pearson correlation coefficient of a set of number pairs.

  • numeric_expression1: The first series.

  • numeric_expression2: The second series.

COUNTIF(expression)

Returns the count of TRUE values for expression. Returns 0 if there are zero input rows or expression evaluates to FALSE for all rows. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

  • expression: The expression to evaluate.

COVAR_POP(numeric_expression1, numeric_expression2)

Computes the population covariance of the values computed by numeric_expression1 and numeric_expression2.

  • numeric_expression: The first series.

  • numeric_expression: The second series.

COVAR_SAMP(numeric_expression1, numeric_expression2)

Computes the sample covariance of the values computed by numeric_expression1 and numeric_expression2.

  • numeric_expression: The first series.

  • numeric_expression: The second series.

FIRST(column)

Returns the first sequential value in the scope of the function. Note: this function is only available when UseLegacySQL=True.

  • column: Any column expression.

FIRST_VALUE(value_expression [(IGNORE/RESPECT) NULLS])

Returns the value of the value_expression for the first row in the current window frame. Note: this function only supports [IGNORE NULLS] when using Standard SQL (UseLegacySQL=False).

  • value_expression: Any value expression

GROUP_CONCAT(string_expression [, separator])

Concatenates multiple strings into a single string, where each value is separated by the optional separator parameter. If separator is omitted, returns a comma-separated string. Note: this function is only available when UseLegacySQL=True.

  • string_expression: The string expression to concat.

  • separator: The separator.

GROUP_CONCAT_UNQUOTED(string_expression [, separator])

Concatenates multiple strings into a single string, where each value is separated by the optional separator parameter. If separator is omitted, BigQuery returns a comma-separated string. Unlike GROUP_CONCAT, this function will not add double quotes to returned values that include a double quote character. For example, the string a"b would return as a"b. Note: this function is only available when UseLegacySQL=True.