I can teach you Yellowbrick analytics! I have never seen analytics perform faster than on a Yellowbrick Data Warehouse. Today we are going to learn about Last_Value. Last_Value performs slightly differently than First_Value, but I will explain precisely how it works. I will show you multiple tricks to maximize the effectiveness of trending analysis. I want first to review and show you the First_Value example so that the Last_Value will make even more sense.
In the picture below, notice that the column of the First_Value function is daily_sales. After we sort the data using the ORDER BY statement, the first row’s daily_sales value is 48850.40. The first value of 48850.40 repeats on each subsequent row.
How easy is the above example to understand? Real easy! However, Last_Value will take some additional work before it becomes easy. Stay with me for a couple more examples, and you will master the Last_Value function.
Our next example below displays the Last_Value function. It does not make sense when you compare it with the previous First_Value example. However, I will explain what is happening here and then show you the trick to do Last_Value, much like First_Value.
In the picture below, we ORDER BY sale_date. Since there is a different sale_date for each row, the Last_Value resets on each sale_date break. The confusion is because the LAST_VALUE is on the column daily_sales, but Last_Value is grouping everything by the ORDER BY statement of sale_date. I will show you in the example after this one how to fix it to make perfect sense.
The next example adds the keywords ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Now we are in business. Even though our ORDER BY sale_date is in ascending mode because we are doing a Last_Value function, the rows’ sorting is in descending mode. Notice that the earliest sale_date is on the bottom, and the more current sale_date is the first row. So, that Last_Value of 54553.10 comes from the first row because the rows sort descending.
The next example shows you a similar example, but we have taken out the WHERE clause. We have also added a PARTITION BY product_id statement. The PARTITION BY statement is identical to a group by statement in that the calculation resets with each product_id break.
The next example shows you the First_Value and the Last_Value together. Using multiple functions that sort differently is an important lesson to understand. The data display is from the Last_Value role because the SQL’s last function determines the final sorting of rows.
The data is correct for both functions. The First_Value shows the earliest date’s daily_sales value, and the Last_Value offers the most current date’s daily_sales value. Because we use a PARTITION BY statement for each function, both calculations are within product_id.
News – CoffingDW becomes a Partner with Yellowbrick
I am excited to announce that Coffing Data Warehousing is partners with Yellowbrick. This partnership is excellent for both sides.
Yellowbrick Data Warehouse is a modern, standards-based MPP analytic database that shatters ceilings on price/performance, achieving 100X performance versus legacy alternatives at a fraction of their cost. With its unified hybrid cloud architecture, applications can deploy anywhere with the same data and performance, de-risking cloud migrations and unlocking multi-cloud innovation.
Coffing Data Warehousing’s Nexus Desktop and Nexus Server is the perfect combination for a remote workforce to manage a hybrid-cloud environment. From the user’s desktop, users can query every system, migrate to every system, and join data across every database. Users set up everything with the click of a button, but the Nexus Server’s job is to execute the work. The Nexus Desktop and Server ensure that all processing is done on a high-speed network so that users can work from any location. We are migrating millions of rows per second to Yellowbrick.
Check out the picture below. Notice how the desktops connect to the Nexus Server, and the Server interacts with the database platforms. The Nexus Server is how you get high-speed movement between database platforms. When you put the Nexus Server next to your Yellowbrick Data Warehouse, you get the fastest federated queries on the market.
Why not try out a Proof of Concept (POC) of Yellowbrick and Nexus. The POC is free for 30 days. Please contact me for more information or check out www.Yellowbrick.com.
Tom Coffing, better known as Tera-Tom, is the founder of Coffing Data Warehousing where he has been CEO for the past 25 years. Tom has written over 75 books on all aspects of Teradata, Netezza, Yellowbrick, Snowflake, Redshift, Aurora, Vertica, SQL Server, and Greenplum. Tom has taught over 1,000 classes worldwide, and he is the designer of the Nexus Product Line.