Learning Google BigQuery

Functions for transformation

Many of the functions discussed so far in this chapter can be used for the sanitation/transformation phase of data warehousing. Let's look at some of these functions with the specific task of sanitization/transformation:

1. Decoding of encoded values. The CASE function can be used to create columns based on conditions of other columns:

 CASE   WHEN region = "W" THEN "West" WHEN region = "E" THEN "East"   END AS region

2. Calculation of values. Any of the arithmetic functions can be used in this case:

 SUM(revenue) / COUNT(orders) AS average_order_value

3. Splitting delimited string values into individual columns. The REGEX_EXTRACT() function can be used to extract individual parts of a string. Here is a how the function ...