With code examples on methods to use them

If you happen to’re a brand new Redshift person, you might discover that the SQL syntax varies from the SQL you’ve written inside different information warehouses.

Every information warehouse has its personal taste of SQL and Redshift isn’t any exception.

At first, it may be irritating to find that your favourite capabilities don’t exist. Nonetheless, there are loads of nice Redshift capabilities that you could benefit from in your code.

On this article, I’ll stroll you thru probably the most useful Redshift capabilities I’ve found in my work. Every perform features a definition and code instance of methods to use it.

PIVOT is a perform that’s constructed into Redshift that permits you, effectively, to pivot your information. What do I imply by this? Pivoting means that you can reshape your information the place the values in rows turn into columns or values in columns turn into rows.

PIVOT may help you:

• depend values in a column
• mixture row values
• derive boolean fields based mostly on column or row values

I lately used PIVOT in Redshift to search out whether or not completely different pages have been energetic or not for every person. To do that, I wanted to PIVOT the page_typesubject and use the user_id subject to group the information.

I set a situation throughout the PIVOT perform to COUNT(*) for every of the completely different web page sorts, as every person might solely have considered one of every sort.

Remember the fact that if a person can have a number of of every web page sort then utilizing COUNT to return a boolean is not going to work.

The code regarded like this:

SELECT
id, 
has_homepage::boolean, 
has_contacts_page::boolean, 
has_about_page::boolean
FROM (SELECT id, page_type FROM user_pages WHERE is_active) 
PIVOT(COUNT(*) FOR page_type IN ('dwelling' AS has_homepage, 'contact' AS has_contact_page, 'about' AS has_about_page))

With out the usage of PIVOT, I’d have needed to create a separate CTE for every page_type after which JOIN all of those collectively within the closing CTE. Utilizing PIVOT made my code far more clear and concise.