4 Useful BigQuery SQL Functions You May Not Know
As a long-time SQL user, I'm always looking for ways to make it easier to analyze data with SQL. In a previous article, I reviewed 6 BigQuery SQL functions I wish I had known earlier, and today I want to share 4 more functions I hope you'll find useful.
1. PERCENTILE_CONT
PERCENTILE_CONT calculates the percentile from a column of values. Bigquery doesn't have a MEDIAN function but you can use PERCENTILE_CONT to calculate the median because it's equivalent to the 50th percentile. Calculating the median and percentiles is useful to get a sense of the distribution and determine outliers that may affect your analysis.
In the example below, I have 6 numbers ( 1, 3, 5, 8, 10, and 1000 ) in an array that are expanded into rows using the UNNEST function. Row 4 calculates the median with 0.5 as the argument indicating the 50th percentile and row 5 uses 0.95 to calculate the 95th percentile. Notice the results show the 95th percentile is 752 while the 25th percentile is 3.5 and the median is 6.5. This indicates outliers may need to be removed for analysis because the differences are very large.
2. COUNTIF
The COUNTIF function counts the value if it satisfies a condition. This is useful to get counts from a table with different conditions without having to run multiple Sql queries.
In the example below, to get the count of the negative numbers and positive numbers I could run the query twice with two different where conditions in rows 3 and 7.
However, using COUNTIF I can run this query once to get the count of both positive and negative numbers in rows 1 and 2.
3. IF
IF is an alternative to CASE if you only need to evaluate whether a condition is true or false. In row 10, if A is less than B and A is greater than 0 then the result_from_if column to set to true else result_from_if is set to false. In row 11, CASE is used to get the same result but it is a longer expression compared to using the IF function.
Note: You can combine COUNT and IF with DISTINCT to get a count of distinct values meeting a condition. In the example below, only positive numbers ( 1, 3, and 4 ) are counted in the DISTINCT returning a count of 3.
4. ERROR
ERROR is useful to alert you of unexpected values in the data. In the example below, the ERROR function is used on row 4 when CASE encounters a value other than cat or dog. This is useful for troubleshooting, especially in ETL pipelines where you may want the SQL to fail if there are unexpected values in a column that requires investigation.
Final Thoughts
While the functions I mentioned are available in BigQuery, they may also be available in other databases but with different names. For example, COUNTIF is COUNT_IF in Snowflake. If you have a few minutes I highly recommend reading your database documentation because you never know the useful functions you may find.
Note: All queries above were run on BigQuery sandbox that's free to anyone with a Google account.
4 BigQuery SQL Shortcuts That Can Simplify Your Queries
6 BigQuery SQL Functions Every User Should Know
BigQuery SQL Procedural Language to Simplify Data Engineering
