BigQuery, Google’s fully-managed, serverless data warehouse, offers a plethora of functions and operators for data manipulation. Mastering these tools is crucial for efficient data analysis and processing. In this guide, we’ll delve into the intricacies of using BigQuery’s functions and operators to manipulate data effectively.
1. Basic Data Manipulation Functions
a. CONCAT Function
The CONCAT function is used to concatenate two or more strings.
SELECT CONCAT('Hello ', 'World') AS ConcatenatedString;
Output:
ConcatenatedString
Hello World
SUBSTR extracts a substring from a string.
SELECT SUBSTR('BigQuery', 1, 3) AS Substring;
Output:
Substring
Big
2. Aggregate Functions
a. AVG Function
Calculates the average of a set of values.
SELECT AVG(salary) AS AverageSalary FROM employees;
Output:
AverageSalary
55000
b. SUM Function
Adds up all the values in a column.
SELECT SUM(revenue) AS TotalRevenue FROM sales;
Output:
TotalRevenue
1050000
3. Date and Time Functions
a. DATE Function
Extracts the date part of a datetime expression.
SELECT DATE('2024-02-26 10:30:00') AS DateOnly;
Output:
DateOnly
2024-02-26
b. DATE_DIFF Function
Calculates the difference between two dates.
SELECT DATE_DIFF('2024-02-28', '2024-02-26', DAY) AS DaysDifference;
Output:
DaysDifference
2
4. Conditional Functions
a. IF Function
Returns one value if a condition is TRUE and another value if it’s FALSE.
SELECT IF(sales > 1000, 'High', 'Low') AS SalesCategory FROM monthly_sales;
Output:
SalesCategory
High
Low
High
b. CASE Function
Similar to IF but allows for multiple conditions.
SELECT CASE
WHEN age < 18 THEN 'Child'
WHEN age BETWEEN 18 AND 65 THEN 'Adult'
ELSE 'Senior'
END AS AgeGroup
FROM customers;
Output:
AgeGroup
Adult
Senior
Adult
BigQuery import urls to refer
