SQL : PostgreSQL
Aggregate Functions
Like most other relational database products, PostgreSQL supports aggregate functions. An aggregate function computes a single result from multiple input rows. For example, there are aggregates to compute the count, sum, avg (average), max (maximum) and min (minimum) over a set of rows.
As an example, we can find the highest low-temperature reading anywhere with:
SELECT max(temp_lo) FROM weather;
max ----- 46 (1 row)
If we wanted to know what city (or cities) that reading occurred in, we might try:
SELECT city FROM weather WHERE temp_lo = max(temp_lo); WRONG
but this will not work since the aggregate max cannot be used in the WHERE clause. (This restriction exists because the WHERE clause determines which rows will be included in the aggregate calculation; so obviously it has to be evaluated before aggregate functions are computed.) However, as is often the case the query can be restated to accomplish the desired result, here by using a subquery:
SELECT city FROM weather
WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
city --------------- San Francisco (1 row)
This is OK because the subquery is an independent computation that computes its own aggregate separately from what is happening in the outer query.
Aggregates are also very useful in combination with GROUP BY clauses. For example, we can get the maximum low temperature observed in each city with:
SELECT city, max(temp_lo)
FROM weather
GROUP BY city;
city | max ---------------+----- Hayward | 37 San Francisco | 46 (2 rows)
which gives us one output row per city. Each aggregate result is computed over the table rows matching that city. We can filter these grouped rows using HAVING:
SELECT city, max(temp_lo)
FROM weather
GROUP BY city
HAVING max(temp_lo) < 40;
city | max ---------+----- Hayward | 37 (1 row)
which gives us the same results for only the cities that have all temp_lo values below 40. Finally, if we only care about cities whose names begin with “S”, we might do:
SELECT city, max(temp_lo)
FROM weather
WHERE city LIKE 'S%' -- (1)
GROUP BY city
HAVING max(temp_lo) < 40;
The |
It is important to understand the interaction between aggregates and SQL's WHERE and HAVING clauses. The fundamental difference between WHERE and HAVING is this: WHERE selects input rows before groups and aggregates are computed (thus, it controls which rows go into the aggregate computation), whereas HAVING selects group rows after groups and aggregates are computed. Thus, the WHERE clause must not contain aggregate functions; it makes no sense to try to use an aggregate to determine which rows will be inputs to the aggregates. On the other hand, the HAVING clause always contains aggregate functions. (Strictly speaking, you are allowed to write a HAVING clause that doesn't use aggregates, but it's seldom useful. The same condition could be used more efficiently at the WHERE stage.)
In the previous example, we can apply the city name restriction in WHERE, since it needs no aggregate. This is more efficient than adding the restriction to HAVING, because we avoid doing the grouping and aggregate calculations for all rows that fail the WHERE check.
SQL Tooling Alternatives
SQL Tooling
Now that we’re kicking into high gear I thought it would be important to elaborate on our SQL tooling choices and alternatives.
In the realm of SQL new tools pop up all the time and depending on the environment you will end up working in it’s important to know outside of the tools we chose what’s out there!
Why did we choose Valentina DB
When it comes to SQL we wanted to give you all the versatility of a tried and true software. Something that is well-regarded and easy to get up and go. Some people will expect "true" experts to use command line and PGAdmin - however our choice was based on multiple criteria:
Is it cross-platform
Is it easy to use
Is it free
Can it be extended to multiple database providers
We want our students to be able to get comfortable with a tool and use it with whatever database provider they end up trying or learning later on.
Valentina DB is just that, it has the ability to connect to a multitude of different databases, as well as give you the visual tooling and ease of use you would want when starting out.
It also doesn’t hurt that this entire suite of features is free to use! That said there are many alternative out there that boast the exact same feature sets and we would like to highlight those as well.
Alternatives
When you look at the landscape of SQL tooling it can be a bit overwhelming to see the forest through the trees!
That’s what we're here for! Here’s a curated list of tools that we believe are best in class!
1. PG Admin + Command line
pgAdmin is the most popular and feature rich Open Source administration and development platform for PostgreSQL, the most advanced Open Source database in the world. pgAdmin may be used on Linux, Unix, Mac OS X and Windows to manage PostgreSQL 9.2 and above.
This is certainly the most common tooling you will see available, however although our course uses Postgres the majority of the concepts you will learn can be applied to any number of database providers and so we chose not to lock in your knowledge to the Postgres specific tool suite.
2. Datagrip
For those of you that been around the coding block, you’ve probably heard of JetBrains. JetBrains is a company that creates best-in class tooling for many programming languages and such.
On the front of SQL there tooling is no-joke either! Datagrip is widely regarded as a powerhouse when it comes to database management.
With great power comes a great price tag however, as their subscription-based model is no joking matter.
However for the suite of features they offer the cost is often quickly forgotten for the performance you gain as a user!
Feel free to check them out at DataGrip: The Cross-Platform IDE for Databases & SQL by JetBrains
3. DBeaver
DBeaver is straight up one of the most comprehensive free database management studios you will find and has a strong and loyal community backing them.
When it comes to a well-rounded, well-oiled piece of software that can stand the test of time, DBeaver is definitely in that category.
Check them out at DBeaver Community | Free Universal Database Tool
Comments
Post a Comment