Reading the SQL Expression

We've seen a bunch of different query clauses now, and done some mixing and matching. How do they fit together? The order of evaluation should be thought of like this:

  1. The FROM and WHERE clauses are evaluated to compute selections and joins.
  2. The GROUP BY and HAVING clauses are evaluated to for groups resulting from the previous step
  3. The SELECT clause is evaluated, including any aggregates
  4. The ORDER BY clause is evaluated
  5. The LIMIT clause is used to cut off output production.
In [1]:
%load_ext sql
%sql postgresql://jegonzal:@localhost:5432/fec
%sql DROP VIEW IF EXISTS date_stats;

Named Queries: Views and CTEs

Up to now we've looked at a single query at a time. SQL also allows us to nest queries in various ways. In this section we look at the cleaner examples of how to do this in SQL: views and Common Table Expressions (CTEs).


In earlier examples, we created new tables and populated them from the result of queries over stored tables. There are two main drawbacks of that approach that may concern us in some cases:

  1. The new table uses up storage, even though it is recomputable from other tables.
  2. If the input tables change, the stored output does not reflect the new state of the input.

For this reason, SQL provides a notion of logical views: these are basically named queries that are re-evaluated upon each reference. They are rather like "macros" if you're familiar with that term.

The syntax is straightforward:

<SELECT statement>;

The resulting view <name> can be used in an SELECT query, but not in an INSERT, DELETE or UPDATE query!

As an example, we might want a view that stores just some summary statistics of transaction_amts for each date:

In [2]:
In [3]:

CREATE VIEW date_stats AS
    to_date(transaction_dt, 'MMDDYYYY') as day, -- Date Parsing
FROM indiv_sample
GROUP BY transaction_dt;
In [4]:
SELECT * from date_stats limit 5;
5 rows affected.
day min avg stddev max
2015-02-06 355 355.0000000000000000 None 355
2016-02-06 1 181.8666666666666667 518.303395246268 2000
None 0 125.0000000000000000 144.337567297406 250
2015-03-23 25 1311.8750000000000000 1605.471763480546 5000
2016-07-18 0 325.9500000000000000 1105.281458557764 5000

Notice that this did not create a table:

In [5]:
!psql fec -c "\dt"
            List of relations
 Schema |     Name     | Type  |  Owner   
 public | ccl          | table | postgres
 public | cm           | table | postgres
 public | cn           | table | postgres
 public | gradebook    | table | jegonzal
 public | indiv_sample | table | jegonzal
 public | individual   | table | postgres
 public | oppexp       | table | postgres
 public | oth          | table | postgres
 public | pas          | table | postgres
 public | profs        | table | jegonzal
(10 rows)

it created a view

In [6]:
!psql fec -c "\dv"
           List of relations
 Schema |    Name    | Type |  Owner   
 public | date_stats | view | jegonzal
(1 row)

In [7]:
!psql fec -c "\d date_stats"
   View "public.date_stats"
 Column |  Type   | Modifiers 
 day    | date    | 
 min    | integer | 
 avg    | numeric | 
 stddev | numeric | 
 max    | integer | 

Views are not materialized

In [8]:
In [9]:

CREATE VIEW rando(rownum, rnd) AS
SELECT rownum, round(random())::INTEGER
FROM generate_series(1,50) AS ind(rownum)

What is the value of the sum of all the rows?

In [10]:
SELECT SUM(rnd) FROM rando
1 rows affected.

What happens if I run it again?

In [11]:
SELECT SUM(rnd) FROM rando
1 rows affected.

Views are useful for composition

One of the nice things about views is modularity: if we have a complex query, we can break it up into smaller views and the run queries on the views.

For example, now we can ask for the day with the highest variance in donations per state:

In [12]:

SELECT day, stddev
FROM date_stats
5 rows affected.
day stddev
2016-07-13 106041.27290974
2016-02-02 76219.14861970
2016-10-26 65370.45080386
2016-06-28 52401.89791164
2016-10-27 49074.16141287

Too Many Views

Views can help:

  • Simplify queries
  • Make complex queries more readable
  • Share "sql programs" with others


  • Creating a new view for each (exploratory) query will result in a lot of views!
  • views like: temp1, temp1_joey, temp1_joey_fixed, ...

We need a mechanism to decompose query into views for the scope of a single query.

Common Table Expressions (WITH)

Think of these as a view that exists only during the query.

If we're only going to use a view within a single query, it is a little inelegant to CREATE it, and then have to DROP it later to recycle the view name.

Common Table Expressions (CTEs) are like views that we use on-the-fly. (If you know about lambdas in Python, you can think of CTEs as lambda views.) The syntax for CTEs is to use a WITH clause in front of the query:

WITH <name> [(renamed columns)] AS (<SELECT statement>) [, <name2> AS (<SELECT statement>)...]

If you need multiple CTEs, you separate them with commas. We can rewrite our query above without a view as follows:

In [13]:
WITH per_day_stats AS (
        to_date(transaction_dt, 'MMDDYYYY') as day, -- Date Parsing
    FROM indiv_sample
    GROUP BY transaction_dt
SELECT day, stddev
  FROM per_day_stats
 ORDER by stddev DESC
1 rows affected.
day stddev
2016-07-13 106041.27290974

We can of course use views or CTEs in join queries as well, just as if they were tables. For example, we can compute the "argmax" of transaction_amt for indiv_sample: those rows that have the maximum transaction_amt:

In [14]:
WITH biggest_gifts AS (
    SELECT max(transaction_amt) AS max
    FROM indiv_sample
SELECT I.transaction_dt,, I.state, I.transaction_amt
FROM indiv_sample I, biggest_gifts B
WHERE I.transaction_amt = B.max;
1 rows affected.
transaction_dt name state transaction_amt

Nested Queries

It is also possible to nest a SQL query within the WHERE clause of another SQL query: this is usually called a "subquery" or "nested query". Time prevents us from covering subqueries here. It's best if you can avoid them anyhow: they are relatively confusing, they often lead to poor performance, and in most cases there is some way to achieve the same effect without using them.

If you'd like to learn more, you can read the relevant material in the PostgreSQL manual or look at slides from CS186 (slides 35-41).

Set and Multiset Operators (skip in class)

Like the relational algebra, SQL supports the operators for union, intersect, and difference of relations. Becase SQL is a multiset (i.e. duplicate-aware) language, it distinguishes between the set-based versions of these operators (which remove duplicates) and the multiset versions (which have rules about the number of duplicates in the output.

The syntax is simple:

<SELECT query>
<set operator>
<SELECT query>;

where the two queries are compatible in the sense of schemas, and the set operator is one of:

  • Union: UNION (set) or UNION ALL (multiset)
  • Intersection: INTERSECT (set) or INTERSECT ALL (multiset)
  • Difference: EXCEPT (set) or EXCEPT ALL (multiset).

The set-based versions of these operations are straightforward. Rather than teach the number of duplicates formed for each multiset operator, I'd encourage you to think about what's intuitive, and then test it out yourself!

As an example, you can run the query below to find the individual records that did not make it into our sample. (This query will run slowly).

In [15]:
# %%sql
# SELECT * FROM individual
# SELECT * FROM indiv_sample
# LIMIT 5;

Getting Fancy with SQL Aggregates: Statistics, Windows and UDAs

Simple Descriptive Statistics in SQL

Statistics doesn't deal with individuals, it deals with groups: distributions, populations, samples and the like. As such, computing statistics in SQL focuses heavily on aggregation functions.

All SQL systems have simple descriptive statistics built in as aggregation functions:

  • min, max
  • count
  • sum
  • avg
  • stddev and variance, the sample standard deviation and variance.

PostgreSQL offers many more. Some handy ones include

  • stddev_pop and var_pop: the population standard deviation and variance, which you should use rather than stddev and variance if you know your data is the full population, not a sample.
  • covar_samp and covar_pop: sample and population covariance
  • corr, Pearson's correlation coefficient

Order Statistics: Aggregates requiring ordered input

You'll notice that a number of handy statistics are missing from this list, including the median and quartiles. That's because those are order statistics: they are defined based on an ordering of the values in a column.

SQL provides for this by allowing what it calls "ordered set functions", which require a WITHIN GROUP (ORDER BY <columns>) clause to accompany the order-statistic aggregate. For example, to compute the 25th percentile, 50th percentile (median) and 75th percentile in SQL, we can use the following:

There are two versions of the percentile function:

  • percentile_cont inuous : interpolates
  • percentile_disc rete : returns an entry from the table

What will the following expressions return?

In [16]:
    percentile_cont(0.5) WITHIN GROUP (ORDER BY x) 
FROM generate_series(1,10) AS data(x);
1 rows affected.
In [17]:
    percentile_disc(0.5) WITHIN GROUP (ORDER BY x) 
FROM generate_series(1,10) AS data(x);
1 rows affected.
In [18]:
    percentile_disc(0.25) WITHIN GROUP (ORDER BY x) as lower_quartile,
    percentile_disc(0.5) WITHIN GROUP (ORDER BY x) as median,
    percentile_disc(0.75) WITHIN GROUP (ORDER BY x) as upper_quartile
FROM generate_series(1,10) AS data(x);
1 rows affected.
lower_quartile median upper_quartile
3 5 8


Note the difference between WITHIN GROUP and GROUP BY:

  • WITHIN GROUP is in the FROM clause
  • WITHIN GROUP is associated with a single aggregate function
  • WITHIN GROUP does not affect the number of groups

Side note for database aficionados: If you're clever, you can express order statistics like median in more "purely relational" SQL without resorting to WITHIN GROUP (ORDER BY ...), but (a) it's hard for people to understand, (b) it's very messy to get more than one order statistic in a single query, and (c) it's quite difficult for a query optimizer to understand and make it go fast.

Of course you can combine WITHIN GROUP and GROUP BY to compute order statistics within groups:

In [19]:
SELECT state, 
       percentile_cont(0.25) WITHIN GROUP (ORDER BY transaction_amt) as lower_quartile,
       percentile_cont(0.5) WITHIN GROUP (ORDER BY transaction_amt) as median,
       percentile_cont(0.75) WITHIN GROUP (ORDER BY transaction_amt) as upper_quartile
FROM indiv_sample
GROUP BY state
5 rows affected.
state lower_quartile median upper_quartile
AA -10.0 -10.0 -10.0
AE 15.0 15.0 25.0
AK 25.0 40.5 100.0
AL 20.0 28.0 115.0
AP 27.0 48.5 83.5

Window Functions

Sometimes, for each row in the output of a query, you want perform a calculation on a related set of rows in the output—often a "window" of rows that precede or follow in some order. Again, this is not very "set-oriented", but SQL provides a mechanism to do it, called a window function. The most common window functions are row_number in some order, rank in some order (where equivalent values in the ordering get the same rank), and ntile(n) in some order, which reports which n-tile the row is in:

simple example:

What will the following query compute?

In [20]:
WITH empsalary(depname, empno, salary) AS (
    VALUES ('develop', 8, 6000), 
           ('develop', 10, 5200),
           ('develop', 11, 5200),
           ('develop', 9, 4500),
           ('develop', 7, 4200),
           ('personnel', 2, 3900),
           ('personnel', 5, 3500),
           ('sales', 1, 5000),
           ('sales', 4, 4800),
           ('sales', 3, 4800)
    empno, depname, salary, 
    rank() OVER (PARTITION BY depname ORDER BY salary DESC),
    ntile(4) OVER (ORDER BY salary DESC) 
FROM empsalary
ORDER BY empno
10 rows affected.
empno depname salary rank ntile
1 sales 5000 1 2
2 personnel 3900 1 4
3 sales 4800 2 2
4 sales 4800 2 2
5 personnel 3500 2 4
7 develop 4200 5 3
8 develop 6000 1 1
9 develop 4500 4 3
10 develop 5200 2 1
11 develop 5200 2 1

Dissecting The Query

rank() OVER (PARTITION BY depname ORDER BY salary DESC)
  • rank() is the window function
  • OVER connects the function to where it is run
  • PARTITION BY (optional) like group by splits the table according to the column(s) provided. However unlike group by the split is only applied during the window computation and the result of the window computation is then attached to the original row.
  • ORDER BY determines the column on which the windowed function is applied

COMMIT -- end of SQL discussion

Concluding thoughts:

  • SQL is the most widely used language for manipulating and accessing data.
  • Learn SQL through practice. Give yourself time to do the homework and try writing queries multiple different ways.
  • We will return to SQL and the Ralational model later in the class.

... and now we transition from the tools of summarizing data to estimating probabilities and paramters of models.