CTEs and Views
SQL to a relational database is like a translator for a person speaking another language. This ‘other language’ is data of course. It is crucial that data scientists to learn a good amount of SQL so that we can do our job of making the data talk. Much like a large SQL database, any language can be vast. There are a few concepts that data scientists should be aware of that will make it easier to communicate on both ends. What I am referring to specifically are common table expressions and views.
These two concepts are much like the WHERE clause where you’d be using filtering in your query, except they are done on a higher level. Using these techniques, the data is first filtered into a subsection, and then the following query can be used to filter the data further. This can improve the speed of your query because it will have less to run through.
Common Table Expressions
Common Table Expressions (CTEs) can be recognized in SQL syntax from the appearance of the WITH clause. We use these to get a segment of the data that can be used for further filtering purposes. Here is a simple example:
-- creating a CTE with the total gross of movies by genre
WITH totalGross(genre, total) as
(SELECT genre, SUM(gross)
FROM movies
GROUP BY genre),-- creating another CTE with the average gross of all movies
avgGross(average) as
(SELECT avg(gross)
FROM movies)
SELECT genre
FROM totalGross, avgGross
WHERE totalGross.total > avgGross.average;
The example query is selecting the genre(s) of movies where the total gross is greater than the average gross of all movies. The WITH clause is helping to find the information we need to consider for this task. It is important to note that using this clause does not store these ‘tables’ in memory and that they appear as a temporary result for the duration of the query.
totalGross is the alias for a temporary result that is used to find the total gross of movies by genre and avgGross is another alias for a result giving us the average gross of movies across all genres. By doing this, our syntax becomes more understandable we can access our data quicker.
Views
Views are similar to CTEs, they create tables from existing tables assisting us to find information more easily. The difference here is that these views are stored in memory. This can be beneficial if you know that you will be calling upon this table more than once. Here is a brief example:
--creating a View. This is fairly easy to accomplish in SQL.CREATE VIEW gold_medals AS
SELECT country, sport, season, medal
FROM olympics
WHERE medal = 'Gold';--we can now call on the View to access desired information.
SELECT country, COUNT(medal)
FROM gold_medals
GROUP BY country;
Above, the view gold_medal is being created, taking a subsection of the original database to include only the gold medalists in the olympics. This view is also now stored for later usage. After the query is executed, you can call upon the the name of the view very easily.
It is also worth noting that the DROP and REPLACE keywords can be used to drop it from memory and/or replace the current view thereby potentially freeing up space. Another great usage of a view is when you are constantly performing a specific join between tables. You can store the output of the join as a view and simply refer back to it that easily.
In conclusion, Data Scientists should be aware of the importance of Common Table Expressions and Views. They can be great additions to our knowledge of SQL and allow us to communicate with the data faster and easier.
REFERENCES
