This blog comes from the Snowflake Architecture and SQL book by Tom Coffing and David Cook.
Are you ready to learn Snowflake SQL from the best technical trainer the world has ever seen? Tom Coffing, AKA Tera-Tom, makes learning fun, exciting, and easy. Learning SQL is one of the best moves you can make in your career because you can query every database with SQL.
Tom became famous as a teacher because he memorized every student's name when he greeted them. Tom has taught over 1,000 classes and never missed a single name. Ask anyone who attended a class taught by Tom Coffing.
Tom has also led a team of developers for almost 20 years to create the Nexus, which queries, migrates, and joins data across all systems. Amazingly, Nexus migrates between all the databases below with the click of the mouse.
• MySQL
• Excel
• Microsoft Access
• Snowflake
• Redshift
• Synapse
• BigQuery
• Teradata
• Oracle
• SQL Server
• DB2
• Hadoop
• MapR
• Greenplum
• Postgres
• Vertica
• Netezza
• SAP HANA
• Yellowbrick
• SQLite
The blog is below this first video, but did you know you can migrate directly to Snowflake in a heartbeat with Nexus? This video shows the many features and options for migrating 19 different databases to Vertica with the click of a button, but it works the same for Snowflake. We also show how we can join tables from 19 different systems with Snowflake in a single query (federated query). We also show how to search for column names (i.e., customer_number) across all systems in your enterprise and then join them. Finally, you will see that users can work from home to migrate and join data, but they have the option to execute the migrations from a Nexus Server (located on-premises or on any cloud) for high-speed transfers.
Advanced UNION for Amazing Totals
Few people know about or have ever seen this brilliant technique using UNION, but prepare to be amazed.
Set operators such as UNION, UNION ALL, INTERCEPT, and EXCEPT have rules. You can combine multiple queries with a set operator in between, but each query must have the same number of columns and be the same data type. The top query is used for aliasing, and the bottom query for sorting using the ORDER BY statement.
Most SQL developers deal a lot with aggregation and understand that you must use a GROUP BY when involving aggregation with normal columns. However, in the query below, we combine UNION with nulls to produce three different reports in one.
In the query below, we have three different totals:
PRODUCT_ID
MTH
YEAR
Check out the answer set and focus on what I will tell you. First, notice that we made $862,404.35 in the year 2000. Now, focus on that we made $418,769.36 in September and $443,634.99 in October, but if you total them up, it equals $862,404.35, which is our yearly total.
Finally, focus on what we made per PRODUCT_ID and understand that if you total PRODUCT_ID 1000, 2000, and 3000, it equals $862,404.35, which is our yearly total.
Combining UNION with nulls gives you a single report with multiple ways to view the amounts. Since we have three groupings, we have three queries separated by a UNION, but the trick is to SUM(DAILY_SALES) but has only one column value per query, with the rest given a null value. Notice that PRODUCT_ID is the only non-null value in the top query, but then EXTRACT(YEAR FROM SALE_DATE) is the only non-null value in the second query, and EXTRACT(MONTH FROM SALE_DATE) is the only non-null value in the third query.
In the next query below, we use five unions to produce these totals:
YEAR
PRODUCT
SALES_PERSON
REGION
QUARTER
First, notice line one in the answer set, which tells us that in 2020, our sum is $25,875,689.91 for the year. Now, focus on that our salespeople sell four products: Boots, Jackets, Jeans, and T-Shirts, and what we made for each product combined in 2020 totals $25,875,689.91.
Next, focus on our four salespeople: Gary Lewis, Helen Smith, Mary Jones, and Will Davis, and what they sold in 2020. Combine their total sales, and the total is $25,875,689.91.
Next, we see what the Regions: North and South sold during 2020, totaling $25,875,689.91.
Finally, we see what we sold in 2020 per quarter, totaling $25,875,689.91.
This example is from Nexus in dark mode, where the user has chosen to put the answer set to the right of the query window (book mode). The Nexus queries, migrates, and joins data across all systems in the systems tree on the left.
It is never too early to teach your children about data. Do what this family does and read them a story of the greatest data scientist ever!
Commenti