Redshift SQL Functions: A Quick Reference

Amazon Redshift offers a wide range of SQL functions to perform various operations on data. Here are some commonly used functions:

1. Mathematical Functions

Perform arithmetic and mathematical operations on data.

• ABS(): Returns the absolute value of a number.
• ROUND(): Rounds a number to a specified decimal place.
• POWER(): Raises a number to the power of another number.

2. String Functions

Manipulate and analyze text data.

• LENGTH(): Returns the length of a string.
• SUBSTRING(): Extracts a substring from a string.
• CONCAT(): Concatenates two or more strings.
• UPPER(): Converts a string to uppercase.
• LOWER(): Converts a string to lowercase.

3. Date and Time Functions

Handle date and time values and perform date-related calculations.

• CURRENT_DATE: Returns the current date.
• DATE_TRUNC(): Truncates a timestamp to a specified date part.
• DATE_ADD(): Adds a specified interval to a date or timestamp.
• EXTRACT(): Extracts a specific part (e.g., year, month, day) from a date or timestamp.

4. Aggregation Functions

Aggregate data to obtain summary results.

• SUM(): Calculates the sum of values in a column.
• AVG(): Calculates the average of values in a column.
• COUNT(): Counts the number of rows in a result set or a specific column.
• MAX(): Returns the maximum value in a column.
• MIN(): Returns the minimum value in a column.

5. Conditional Functions

Perform conditional operations and control flow.

• CASE WHEN: Allows conditional expressions in SQL queries.
• COALESCE(): Returns the first non-null value in a list of expressions.
• NULLIF(): Returns null if two expressions are equal; otherwise, returns the first expression.

6. Window Functions

Perform calculations across a set of rows related to the current row.

• ROW_NUMBER(): Assigns a unique integer value to each row in the result set.
• RANK(): Assigns a unique rank to each distinct row within the result set.
• LAG(): Accesses data from a previous row in the result set.
• LEAD(): Accesses data from the following row in the result set.

Conclusion

These are just some of the essential SQL functions offered by Amazon Redshift. The ability to leverage these functions allows you to perform complex data operations and obtain valuable insights from your data.

Happy querying!