With code examples on tips on how to use them
In case you’re a brand new Redshift person, it’s possible you’ll discover that the SQL syntax varies from the SQL you’ve written inside different knowledge warehouses.
Every knowledge 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 numerous nice Redshift capabilities that you could reap the benefits of 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 tips on how to use it.
PIVOT is a perform that’s constructed into Redshift that enables you, properly, to pivot your knowledge. What do I imply by this? Pivoting lets you reshape your knowledge the place the values in rows develop into columns or values in columns develop into rows.
PIVOT can assist you:
depend values in a columnaggregate row valuesderive boolean fields primarily based on column or row values
I just lately used PIVOT in Redshift to search out whether or not totally different pages have been energetic or not for every person. To do that, I wanted to PIVOT the page_typefield and use the user_id discipline to group the info.
I set a situation throughout the PIVOT perform to COUNT(*) for every of the totally different web page varieties, as every person might solely have one in all every kind.
Take into account that if a person can have a number of of every web page kind then utilizing COUNT to return a boolean is not going to work.
The code appeared like this:
SELECTid, has_homepage::boolean, has_contacts_page::boolean, has_about_page::booleanFROM (SELECT id, page_type FROM user_pages WHERE is_active) PIVOT(COUNT(*) FOR page_type IN (‘residence’ AS has_homepage, ‘contact’ AS has_contact_page, ‘about’ AS has_about_page))
With out using PIVOT, I’d have needed to create a separate CTE for every page_type after which JOIN all of those collectively within the ultimate CTE. Utilizing PIVOT made my code far more clear and concise.
