# Data Manipulation with BigQuery

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

b. SUBSTR Function

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