With code examples on easy methods to use them
In case you’re a brand new Redshift person, you could 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. Nevertheless, there are quite a lot 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 operate features a definition and code instance of easy methods to use it.
PIVOT is a operate that’s constructed into Redshift that permits you, properly, to pivot your knowledge. What do I imply by this? Pivoting lets you reshape your knowledge the place the values in rows turn out to be columns or values in columns turn out to be rows.
PIVOT may help you:
- rely values in a column
- combination row values
- derive boolean fields primarily based on column or row values
I not too long ago used PIVOT in Redshift to seek out whether or not completely different pages had been energetic or not for every person. To do that, I wanted to PIVOT the page_type
discipline and use the user_id
discipline to group the info.
I set a situation inside the PIVOT operate to COUNT(*) for every of the completely different web page sorts, as every person may solely have certainly 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 won’t 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 ('residence' AS has_homepage, 'contact' AS has_contact_page, 'about' AS has_about_page))
With out the usage of 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 rather more clear and concise.