Bigquery safe divide. This topic lists information about what functions you can use in Spotfire, for example for writing custom expressions and adding calculated columns, when you work with in-database data tables from Google BigQuery. Bigquery safe divide

 
This topic lists information about what functions you can use in Spotfire, for example for writing custom expressions and adding calculated columns, when you work with in-database data tables from Google BigQueryBigquery safe divide Here are some key features of BigQuery storage: Managed

Note that I'm using IFNULL() and also SAFE_DIVIDE() - the latter one protects from dividing by 0. To prevent errors during the divide operation, consider using SAFE_DIVIDE or IEEE_DIVIDE. Formats a TIME value according to the specified format string. In some cases, there's no direct mapping between a SQL element in Hive and BigQuery. As far as I can google, there is no way to update automatically in SQL?. SELECT SAFE_DIVIDE(total_slot_ms, TIMESTAMP_DIFF(end_time, start_time, MILLISECOND)) AS slotCount FROM `<project-name>`. pip install --upgrade google-cloud-bigquery-reservation. The procedure: Multiply your value to move the last decimal to the tenths position. 0 model to create a tf. One way to do it is to do it is through a self-join with the same table and a 1-month delay. 5. This topic describes the syntax for. This page describes how to schedule recurring queries in BigQuery. Session acquisition dimensions can be found here. Approximate. id as string) = t3. but in my case I need to distinct the values (on 2 different columns) the value - number grouped values based on. Here are some common functions and operations that use the SAFE_ prefix: SAFE_ADD (x, y): Safely adds two numeric values, handling nulls gracefully. You can organize query results by date and time by. Gets the number of rows in the input, or the number of rows with an expression evaluated to any value other than NULL . Tried to fix using the below query but getting the same result as above. id = t2. I don't know much about the contents of event_v1 requests or ip_addresses which could divide it, but I'm willing to bet there's a lot more rows there than there are in the geo_ip_countries. Aggregate functions. This is extremely useful in situations where you need to calculate important metrics such as moving averages and cumulative. new_york_subway. The quotas and limits for query jobs can be found here. 6. Approximate. AVG, COUNT, COUNTIF, LOGICAL_AND, LOGICAL_OR, MAX, MIN, STRING_AGG, SUM. These data type conversions are explicit, but some conversions can happen implicitly. SELECT SAFE_DIVIDE(COUNTIF(properties. Federated queries let you send a query statement to Cloud Spanner or Cloud SQL databases and get the result back as a temporary table. If the subquery returns exactly one row, that single value is the scalar subquery result. Approximate. In BigQuery, SAFE_CAST is used to prevent errors from casting. Improve this answer. I would like to avoid a solution that addresses the problem at the column level (e. I am trying to divide turnover/weight and weight in some cases is in the '0. This topic lists information about what functions you can use in Spotfire, for example for writing custom expressions and adding calculated columns, when you work with in-database data tables from Google BigQuery. Aggregate functions. Aggregate functions. Equivalent to the division operator (/), but returns NULL if an error occurs, such as a division by zero error. AVG, COUNT, COUNTIF, LOGICAL_AND, LOGICAL_OR, MAX, MIN, STRING_AGG, SUM. Aggregate functions. Fundamentally, BigQuery is using the S2 index to map a geometry into a 64-bit integer, then BigQuery clusters on that integer using existing integer-based clustering mechanisms. In the Google Cloud console, open the BigQuery page. Below is for BigQuery Standard SQL and (I think) covers most of crazy variation of date representations by using moment. . I hope BigQuery Standard SQL documentation will be as good as your recent related answers! I really like those examples! Thank you Elliott!! – Mikhail Berlyant. For example, SAFE_CAST or SAFE_SUBSTR We reviewed 3 ways to. Aggregate functions. Bigquery safe_divide - This post is a collection of the most useful BigQuery SQL functions that offer ways to overcome common select SAFE_DIVIDE(10, 0) null. AVG, COUNT, COUNTIF, LOGICAL_AND, LOGICAL_OR, MAX, MIN, STRING_AGG, SUM. id = t3. The query yields:. 概要Queryで集計するときによく使うCOUNT関数の小技をご紹介します。. With the help of unnest and parse for dates or timestamps we can do the trick. Handling time is an essential task for most business. Facebook Ads, Google Ads, Google My Business data, etc. Ex column1 has 8 records of the number 2. peaceful_universe` FOR SYSTEM TIME AS OF TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL -5 MINUTE) Warning: CREATE OR REPLACE TABLE deletes the table history, so write the results elsewhere. Here is a situation where you can divide by zero. BigQuery is Google Cloud's fully managed, petabyte-scale, and cost-effective analytics data warehouse that lets you run analytics over vast amounts of data in near real time. FROM `bigquery-public-data. AS total_adv, ROUND(SAFE_DIVIDE(high_inc_adv, low. 本記事は、社内の非エンジニア向けの BigQuery SQL入門ドキュメント の BigQueryの関数編 を公開したものです. I'm planning to upload a bunch of dataframes (~32) each one with a similar size, so I want to. データポータルでの BigQuery の GEOGRAPHY ポリゴンのサポートにより、階級区分図で任意のポリゴンを可視化できるようになりました。GEOGRAPHY フィールドが含まれている BigQuery データに接続すると、地理空間データとして認識されます。SELECT COUNT (field1), bucket FROM ( SELECT field1, CASE WHEN age >= 0 AND age < 10 THEN 1 WHEN age >= 10 AND age < 20 THEN 2 WHEN age >= 20 AND age < 30 THEN 3. id JOIN t3 ON t2. You can think of it as “if NULL, then. (SUM(session_with_transaction. Approximate. If the point exists in the array, returns the index of the next larger value. Here's the simple function that I wrote. 💡. chicago_taxi_trips. Scale is how many of these digits appear after the decimal point. If the goal is only to get the number of users with more than twenty comments for each day from table user_comments. Checks if a groupable value in the GROUP BY clause is aggregated. Follow answered Jan 13, 2017 at 1:28. ifnull. g. Gets the number of intervals between two DATE values. Approximate. The data in this table is not kept in real time, and might be delayed by a few seconds to a few minutes. By dividing a large table into smaller partitions, you can improve query performance and control costs by reducing the number of bytes read by a query. BigQuery’s WITH statement, also known as Common Table Expressions (CTEs), can make your queries more readable. SAP HANA can now combine data from Google BigQuery, enabling data federation and/or data ingestion into the HANA platform. Approximate. Example: Patricia,Hamilton is split into Patricia and Hamilton. I used. This contains 23 columns, and 203 million rows, taking up 74GB of Google Clouds storage space. (Image by Author). The "CONVERT(type, expr [,style])" function (Microsoft SQL Server). SAFE_NEGATE (x. This macro performs division but returns null if the denominator is 0. Aggregate functions. Functions that return position values, such as STRPOS, encode those positions as INT64. , without having to issue a BigQuery query). These do not seem to calculate properly however. Como mencionado anteriormente, o BigQuery Editions faz o escalonamento automático de slots ⁷, ou seja, quando a plataforma está ociosa ou com pouca carga, apenas os slots necessários são. In GoogleSQL for BigQuery, an array is an ordered list consisting of zero or more values of the same data type. In BigQuery, SAFE_CAST is used to prevent errors from casting. Step 1: Log in to the Google Cloud Console. Step 2: Once logged in, go to the Explorer panel, and expand your Google BigQuery project. Divides X by Y; this function never fails. safe_add. Standard SQL - Sum arrays in a column. Even though most of these metrics seem pretty. somewhat similar to how the SAFE_CAST command gives a NULL if it cannot do the cast. Data Cloud Alliance An initiative to ensure that global businesses have more seamless access and insights into the data required for digital transformation. The digital agency we worked with wanted to A) see their clients’ GA4 data all in one place and B) allow each of their clients to see their GA4 data alongside data from other data sources (e. 3', '0. This will protect your queries from errors that could occur during casting. You can use this function to handle a potential division by zero by wrapping the denominator in a call to NULLIF. You given an example of fixed dates, which are compared to the current date. Description & motivation Created safe_divide macro to return null when dividing by 0 across all warehouses. 234 becomes 1823. fn(param ANY TYPE) RETURNS AS (( SELECT IFNULL(SAFE_DIVIDE(1,0), 0) ));GoogleSQL for BigQuery supports conversion functions. DataFrame. SAFE_DIVIDE. With that out of the way, let's get started. 3. g. OVER (PARTITION BY stock1, stock2 ORDER BY date ASC)) as logspreadreturn, SAFE_DIVIDE((spreadclose - sma20), stdev20) as zscore20 FROM `rw-algotrader-264713. Please use the safe_divide command, if there is any chance of diving by zero. BigQuery Reservation API. Click push_pin Pin. Here's a simplified example of what I. BigQuery supports multi-statement transactions inside a single query, or across multiple queries when using sessions. Bigquery then thinks it is a FLOAT64 instead of an INT. js library via BigQuery UDF with external libraries. This topic lists information about what functions you can use in Spotfire, for example for writing custom expressions and adding calculated columns, when you work with in-database data tables from Google BigQuery. Click on the query you want to explore to see the results. Constructs a DATE value. This topic lists information about what functions you can use in Spotfire, for example for writing custom expressions and adding calculated columns, when you work with in-database data tables from Google BigQuery. null のときに任意に書き換えができます。 最終アウトプットはグラフをイメージしているので今回は0に書き換えます。 カテゴリ別の売上を人数で割るとこんな感じですかね。To fix the divide-by-zero error, you can do a safe divide. For a large database project of mine, I have adopted a structure of building tables with nested structs, and combining these tables together (again using structs) into a single large table. safe 함수. Improve this answer. Click add_box Compose a new query. While we can never get away from data cleaning there are SQL functions that can help. ml_datasets. During the query execution, BigQuery automatically calculates how many slots a query requires, depending on the query size and complexity. Google BigQuery, a powerful serverless data warehouse, has become a cornerstone of data analysis and machine learning pipelines for many organizations. JOBS_BY_PROJECT This is an approximation because, unfortunately, BigQuery doesn’t return the exact values, and this calculation is. BigQuery is a serverless and cost-effective enterprise data warehouse that works across clouds and scales with your data. error:Query Failed Error:. Data partitioning in BigQuery is the process of dividing a large table into smaller, more manageable pieces called partitions. Gets the count of TRUE values for an expression. In BigQuery, the SAFE_ prefix is used as a way to perform safe operations on potentially null or problematic data, preventing errors that might occur with traditional. BigQuery Storage Write API: This is the cost of using storage write API in BigQuery. Cost / p. 1. Operators are represented by special characters or keywords; they do not use function call syntax. When prompted to allow BigQuery Client Tools to access data in Google BigQuery, click Accept, and then copy the code that Google provides. Google Cloud Platform has introduced an alternative BigQuery storage billing model based on the physical compressed storage of data rather than the logical data that is the default model. “Safe” functions like this one are often useful for calculated fields that are generated by performing operations on columns that may contain NULL or 0 values. Interprets an INT64 expression as the number of days since 1970-01-01. filter gets tranlated to WHERE clauses). MySQL will anyway give NULL if you try to do 0/0, as the SQL meaning fo NULL is "no data", or in this case "I don't. , ChatGPT) is banned. Method #1 Check the denominator is not zero during division using NULLIF. COUNTIF. You can also use != 0 in place of <>0 or use the SAFE_DIVIDE function . I honestly can not imagine a case where this is true of integral division, of course, since there is no infinite state for integers. I'm trying to upload a pandas. A UDF accepts columns of input, performs actions on the input, and returns the result of those actions as a value. FROM `bigquery-public-data. I previously was using a CASE WHEN function but wanted to change it to an IF as there is only one case. Returns FLOAT64. SELECT cast(ST_GEOGFROMTEXT('POINT(51. result () # Get the result # Third loop to list the tables and print the result for table in tables: print (' {} size: {}'. if current_date is 19 April 2022 then current_date ()-10 is 9 April 2022. In essence, they are evaluated left to right, with short-circuiting, and only evaluate the output value that was chosen. Here is the full query with example data. resolves #679 This is a: documentation update bug fix with no breaking changes new functionality a breaking change All pull requests from community contributors should target the main branch (default). AVG, COUNT, COUNTIF, LOGICAL_AND, LOGICAL_OR, MAX, MIN, STRING_AGG, SUM. GCP BIGQUERY YTD CUMILATIVE SUM BASED on MONTH. Provide details and share your research! But avoid. For example, now anyone can call this number parsing function:Unlimited parallelism: These features divide large volumes of data into smaller processes that are executed in parallel. This meant a relatively simple SQL query in Google BigQuery. population p JOIN medicare. ROUND(SAFE_DIVIDE(100*(MAX(Ask_Price)-MIN(Ask_Price)),AVG(Ask_Price)),3) AS DeltaAskPercent, ROUND(AVG(Bid_Price),3. Select the Set a destination table for query results option. With p ersistent UDF support in BigQuery, now you can: Create user-defined SQL and JavaScript functions. These core tasks are essential to all modern financial companies. chicago_taxi_trips. There is no current way to split () a value in BigQuery to generate multiple rows from a string, but you could use a regular expression to look for the commas and find the first value. Note that S2 coverings can overlap, as it is often impossible to divide data into non-overlapping regions. BigQuery will automatically convert the result type of an argument expression to another type if needed to match function signatures. The timestamp is continuous, non-ambiguous, has exactly 60 seconds per minute and does not repeat values over the leap second. Using BigQuery, is there a way I can select __TABLES__ from every dataset within my project? I've tried SELECT * FROM '*. Also, you're using FLOAT64 which is specific to Bigquery too, you should be using just float. You will get to know more about that in the subsequent sections. -- calculate the cohort users percentage SELECT first_seen, day_order, new_users, cohort_users, SAFE_DIVIDE. DISTANCE(a, b, 'COSINE') yields the cosine similarity for a, b. I'm about to migrate queries from Legacy to Standard in BQ. The SAFE_DIVIDE function returns a safe division of two numbers by returning NULL when the divisor is zero. In BigQuery, the SAFE_ prefix is used as a way to perform safe operations on potentially null or problematic data, preventing errors that might occur with traditional SQL operations. BigQuery Sandbox is a BigQuery initiative that allows you to explore the capabilities of this data warehouse at no cost, to determine whether BigQuery fits your needs. SELECT actiontimestamp, medium, source, sessions, ROUND(SAFE_DIVIDE(pageviews, sessions), 0) AS pages_per_session, CASE WHEN sessions = 0 THEN 0 ELSE. stories and a particular user:IBM Netezza data warehousing is designed to work with Netezza-specific SQL syntax. but you DO really need SAFE_DIVIDE. e. Syntax: CONVERT( data_type [ ( length ) ], expression [, style ] ) The optional "style" argument specifies how the value is going to be converted; this implementation ignores the style parameter. Last time I showed how to use a PostgreSQL instance with PostGIS to fix invalid geometries, now I’m glad to introduce new BigQuery native options that simplify loading geospatial data, including invalid geometries. Table 1:student table column name : student_name, student_address, student_mark Table 2:staff table Column name: Staff_name, Staff_address, Staff_age, staff_class Table 3: alumni table Column name: alumni_name, alumni_address, alumni_year_passing, Alumni_marks,. CASE WHEN COUNT(DISTINCT item_id) = 0 THEN NULL ELSE COUNT(DISTINCT item_id) END If you are writing your script in bigquery then, you could simply use. Aggregate functions. What we did is to find the network portion and then add zeros at the end of this prefix so that it. safe_divide(x, y): Safely divides two numeric values, handling nulls and division by zero gracefully. You can make your function more robust using SAFE functions inside a UDF and also make it return default value if it fails like below. I have a table in bigquery with more than 50 million rows. Conditional expressions. Data binning is a useful common practice in Data Science and Data Analysis in several ways: discretization of a continuous variable in Machine Learning or simply making a histogram for ease of visualization are a couple of familiar examples. Aggregate functions. 18. budget_manager`. Here is a situation where you can divide by zero. ビッグデータ分析・活用のためのSQLレシピ 4,180. RANGE_BUCKET scans through a sorted array and returns the 0-based position of the point's upper bound. Approximate. die BigQuery-Funktion SAFE_DIVIDE() In meinen ersten BigQuery Queries habe ich immer ein / benutzt, weil es funktioniert hat und ich gar nicht wusste, dass es dafür eine separate Funktion gibt. Improve this answer. First, let’s take a look at the original dataset. 1 Answer. source AS source, trafficSource. I would like is possible to divide the column A by the column B as column C, in Bigquery. You can follow along with this notebook on GitHub. Redshift, Snowflake, or BigQuery, this likely does not apply to you. BigQuery’s query mechanism is powered by. Below are the list of SAFE Functions available in Bigquery. Divide this result by the same figure used in step 1. カテゴリ内の関数の並び順. Share. num, 0). You can use the following link. Its serverless architecture allows it to operate at scale and speed to provide incredibly fast SQL analytics over large datasets. I want to take a single column from the table and load it into a python list. Follow. Working in Google BigQuery with StandardSQL. SAFE_DIVIDE((SUM(CASE WHEN EANR='Cancelled' THEN USD_amt END)),(SUM(USD_amt))) as Cancel_rev_Perc Share. State, cbs. signature and are classified based on their use cases: i would like to divide two colums in Bigquery. GoogleSQL for BigQuery supports operators. Using SAFE_DIVIDE Function. As I described in an earlier blog post, you can use clustering to understand your data in order to make data-driven decisions. It returns null if the denominator is zero or either value is null. function or the. I have the following table that contains nested STRUCT s, and in a subquery, I am trying to add additional columns at the struct level. Оператор SAFE_DIVIDE позволяет игнорировать деление на 0. Once you start a BigQuery project, you’ll see two messages at the top of the BigQuery console: 1. These string functions work on two different values: STRING and BYTES data types. A function syntax is a predefined set of clauses that accomplish a complex action. taxi_trips` Below is a screenshot of the schema. sql #227 #261. Below, I’ll discuss how we recently helped a client connect all of their clients’ Google Analytics accounts to one BigQuery instance and set up an automated process to consolidate all of that data into a unified table for downstream analysis/data visualization. May 13, 2016 at 2:25. Improve this question. (i. . 2 UNION ALL SELECT 'Female', NULL, 84 UNION ALL SELECT 'Male', IEEE_Divide(-3,0), 0) SELECT * FROM new_data WHERE Height < 100. DATE_DIFF. Asking for help, clarification, or responding to other answers. In BigQuery (standard SQL), I would like to randomly split result lines from a query. survey_rating >= 9) THEN 1 ELSE NULL END) AS survey_responses_num_positives. dataset. あとはPythonやRなどの言語からBigQueryのデータを読み込んで計算処理をすることが多い。. SELECT * FROM `bigquery-public-data. The issue is when you querying and for example, when you divide the value by 0, BigQuery will show you:It can be modified to get more fields. This has a dramatic impact on the duration for the query. My approach was to do this operation directly in python just before doing the BigQuery API call, by leveraging python's internal time function (assuming you want your variables to be time-based). The answer is a precision of 38 and a scale of 9. One method just uses avg (): SELECT country_code, AVG (case when store_code IS NULL then 1. With flat-rate pricing, you can purchase BigQuery slot commitments in 100-slot increments in either flex, monthly, or yearly plans instead of paying for queries on demand. 0. BigQuery automatically allocates storage for you when you load data into the system. However, using FORMAT () function can only accomodate up to 6 decimal places only. By using WITH statements, you can break down complex queries into smaller, reusable components, making it easier to understand and debug your queries. Note: you need to upload moment. They use our service But It is not a waste of money. Sign up for free to join this conversation on GitHub . Think of most frequently used marketing channel, device, visit frequency & recency and most viewed. I'm looking at calculating the number of inventory turns that occur in a three month period. For instance, COUNT, SUM, and so on are some examples of the function syntax. cast(t2. AVG, COUNT, COUNTIF, LOGICAL_AND, LOGICAL_OR, MAX, MIN, STRING_AGG, SUM. BigQuery: No matching signature for operator over Github public data. I would like to update my FTE_YTD Calculation to be a cumulative sum of previous month/s based on CALENDAR_MONTH. Google Cloud Platform and BigQuery . Below is for BigQuery Standard SQL and (I think) covers most of crazy variation of date representations by using moment. Aggregate functions. I have the following. Function list. It would be great to add a bigquery style 'safe_divide' function. marketing_hub. There is now a better initialization algorithm available for k-Means in BigQuery, and in this blog, I’ll. A window function includes an OVER clause, which defines a window of rows around the row. This contains 23 columns, and 203 million rows, taking up 74GB of Google Clouds storage space. 2. SAFE_DIVIDE( u. The CAST and EXTRACT functions also do not support the SAFE. prefix. Also :. id JOIN t4 ON t4. SAFE_DIVIDE (x, y): Safely divides two numeric values, handling nulls and division by zero gracefully. Try Safe_divide. Approximate aggregate functions But a single stage might spike to 2000 slots. Below is for BigQuery Standard SQL . I put together a safe-divide function as we do a lot of dividing with our invoice data, and it saves from having to add conditionalized fluff when building our queries. With flat-rate pricing, you can purchase BigQuery slot in 100-slot increments in either flex, monthly, or yearly plans instead of paying for queries on demand. For divisions with BigQuery, you can use SAFE_DIVIDE with ROUND functions (example with 2 digits) : SELECT outbound_calls, total_tasks, ROUND (SAFE_DIVIDE (outbound_calls, total_tasks), 2) as divres FROM ( SELECT 5 as outbound_calls, 3 as total_tasks ); According to the BigQuery documentation the aliases are visibles in the from not in the. SELECT p. slot_ms,unnest_job_stages. This function takes two arguments, and returns NULL if the two arguments are equal. Cost / p. 現在、承認済みのビューを利用してこうしたユースケースに対応しているお客様は、RLS を活用する. Precision is the number of digits that the number contains. INFORMATION_SCHEMA. Because it is possible that a page path only has exit pageviews, we need to use a safe divide function to avoid dividing the total time on page with zero pageviews. Yes, no problem! It seems like you have been quick to catch on to standard SQL as well :) We'll be continuing to improve the documentation as we move. 500989020415034 -0. Here's an example that you can test: SAFE_DIVIDE Description. OPTIONS. These translations are able to look up values from variables as per your example:Total revenue for each product category. This document details the similarities and differences in SQL syntax between Netezza. AVG, COUNT, COUNTIF, LOGICAL_AND, LOGICAL_OR, MAX, MIN, STRING_AGG, SUM. Indeed you need to join that. BigQuery is ranked 5th in Cloud Data Warehouse with 25 reviews while Teradata is ranked 3rd in Data Warehouse with 19 reviews. BigQuery: Divided by sum of values in column to find the ratio. PaymentMethodName, COUNT(t. If you only need one dimension or metric. revenue),2) AS revenue,. So, in your code where you have, for example sum(iff(iscode=1,1,0)), you. I'd like to create a column2 with 4 records of the number 2 and column3 with 4 records of. In BigQuery, you can use a subquery to define the alias: SELECT Flat, Year, (SUM(CASE WHEN EANR = 'Cancelled' THEN USD_amt END) / SUM(SUM(USD_amt)) OVER (PARTITION BY. In SQL Server, if we divide a number with null, the output is null as well #standardSQL CREATE TEMP FUNCTION barchart(v ARRAY<FLOAT64>, mm STRUCT<min FLOAT64, max FLOAT64>) AS (( SELECT STRING_AGG(SUBSTR('🏿🏾🏽🏼🏻', 1+CAST(ROUND(y) AS INT64), 1), '') FROM (SELECT IFNULL(SAFE_DIVIDE((e-mm. With BigQuery, there's no infrastructure to set up or manage, letting you focus on finding meaningful insights using GoogleSQL and taking advantage. safe_divide처럼 0으로 나눌때 nan을 반환해서 편리할 수 있지만, 0이 아닌 수를 0으로 나눌때 inf를 반환하기에 개인적으로는 평상시에는 잘 사용하지 않는다. Already have an account?想要定期更新某個 Query 的執行結果. To create a saved query, follow these steps: In the Google Cloud console, go to the BigQuery page. Sum of array elements in Bigquery. Alternative Method: Another way to handle division by zero in Standard SQL is to use the CASE statement, like this: SELECT column1 / CASE WHEN column2 = 0 THEN NULL ELSE column2 END AS result FROM your_table. For GA 360 users, Google provides you the option to get the daily data dump for website’s sessions into BigQuery. 1. Netezza SQL is based on Postgres 7. 0 / total. Geography functions in BigQuery are prefixed with ST_. First you create new table (temp_table) with extra field rnd. I'm pretty new to SQL and Google BigQuery, so forgive me if this is a really stupid question. mk_paymentstatus) AS DepositAttempts, SUM(CASE WHEN t. Is it safe to become separated from your passport in a foreign country? ~乙女ならばともかく: Translation and grammar issue more hot questions. 168, 74. Sergey Geron. 12. AVG, COUNT, COUNTIF, LOGICAL_AND, LOGICAL_OR, MAX, MIN, STRING_AGG, SUM.