With code examples on find out how to use them
When 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 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 plenty of nice Redshift capabilities you could benefit from in your code.
On this article, I’ll stroll you thru essentially the most useful Redshift capabilities I’ve found in my work. Every perform features a definition and code instance of find out how to use it.
PIVOT is a perform that’s constructed into Redshift that permits you, properly, to pivot your information. What do I imply by this? Pivoting permits you to reshape your information the place the values in rows grow to be columns or values in columns grow to be rows.
PIVOT may help you:
- depend values in a column
- mixture row values
- derive boolean fields primarily based on column or row values
I lately used PIVOT in Redshift to seek out whether or not totally different pages had been lively or not for every person. To do that, I wanted to PIVOT the page_typesubject and use the user_id subject 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 every of every sort.
Understand 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 appeared 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 using PIVOT, I might 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.
