assets/notebooks/sql/sql_lecture_part1_without_output.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Set up Python and Jupyter support for PostgreSQL\n",
    "In a bash shell, add the `psycopg2` package to make connections to postgres, and the very handy [`ipython-sql`] package (https://github.com/catherinedevlin/ipython-sql) to integrate SQL queries into Jupyter notebooks.\n",
    "\n",
    "```bash\n",
    "% pip install psycopg2\n",
    "% pip install ipython-sql\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Load the ipython-sql magic. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%sql postgresql://jmh:@localhost:5432/jmh"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## SQL: The Query Language"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The canonical SQL query block includes the following clauses, in the order they appear. Square brackets indicate optional clauses.\n",
    "```sql\n",
    "SELECT ...\n",
    "  FROM ...\n",
    "[WHERE ...]\n",
    "[GROUP BY ...]\n",
    "[HAVING ...]\n",
    "[ORDER BY ...]\n",
    "[LIMIT ...];\n",
    "```\n",
    "Query blocks can reference one or more tables, and be nested in various ways.\n",
    "\n",
    "Before we worry about multi-table queries or nested queries, we'll work our way through examples that exercise all of these clauses on a single table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Easy Peasy SQL Basics\n",
    "To start, we are going to define a toy *relation* (a.k.a. *table*), populate it with some toy data, and work through some basic SQL. Deeper stuff coming soon though, I promise!\n",
    "\n",
    "### CREATE TABLE\n",
    "First, let's create a table storing information about the professors in this class. This defines the *schema* of the table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql \n",
    "drop table if exists profs;\n",
    "create table profs (firstname    text,\n",
    "                    lastname     text,\n",
    "                    luckynumber  integer,\n",
    "                    primary key (firstname, lastname));"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that each column has a fixed data type; PostgreSQL will enforce these types as data is inserted. Note also the definition of a primary key, as we discussed in the Data Wrangling lecture. PostgreSQL will *enforce* the uniqueness of values in the key columns.\n",
    "\n",
    "To see what we've done, let's run our first query, dumping out the content of the table: every column for each row. We denote every column with `*`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%sql select * from profs;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### INSERT VALUES\n",
    "Now let's manually insert some values into the table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql insert into profs values\n",
    "      ('Bin', 'Yu', 3),\n",
    "      ('Joseph', 'Gonzalez', 6),\n",
    "      ('Joseph', 'Hellerstein', 6),\n",
    "      ('Deborah', 'Nolan', 7);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note how insertions need to have values in the same order as the columns in the `create table` statement! Let's make sure our data is there:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%sql select * from profs;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And now let's see what happens if we try to mess with that primary key!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql insert into profs values\n",
    "      ('Joseph', 'Hellerstein', 7);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### UPDATE values\n",
    "OK, we can't have two rows for me, but we *can* change my lucky number via update:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql update profs\n",
    "         set luckynumber = 7\n",
    "       where firstname = 'Joseph'\n",
    "             and lastname = 'Hellerstein';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And let's check the table now:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%sql select * from profs;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice two things: \n",
    "1. The rows may come back in a different order than before. Remember: relations do not have a defined order, and in fact two different orders are just two ways of describing the same relation!\n",
    "2. Note the relational style of the `update` statement: we decide which rows get updated based entirely on the values in each row, as checked by the `where` clause. There is no notion of any information outside the values in the row--e.g. there are no \"object identifiers\" or \"row numbers\"... everything is *just the data and only the data*."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Simple SELECT Queries\n",
    "Now let's start looking at some slightly more interesting queries.\n",
    "\n",
    "We can do relational projection--i.e., select specific *columns* of interest--via the `select` list:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "select firstname\n",
    "  from profs;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can do relational selection--i.e. select specific *rows* of interest--by adding a `where` clause:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql \n",
    "select *\n",
    "  from profs\n",
    " where luckynumber > 5; "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And of course we can specify both rows and columns explicitly. If we have a primary key, we can filter things down to even the cell level via a `select` list of one column, and a `where` clause checking equality on the primary key columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql \n",
    "select luckynumber\n",
    "  from profs\n",
    " where lastname = 'Yu'\n",
    "   and firstname = 'Bin';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that even this \"single-celled\" response still has a uniform data type of a *relation*. SQL is always manipulating and returning objects of type *relation*.\n",
    "\n",
    "Now that you can slice and dice tables into columns, rows and cells, you have enough knowledge to poke around in a database. Let's move on to skills that you'll need as a data scientist."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Working with Real Data in `psql`\n",
    "In a separate notebook (`load_fec.ipynb`) you'll find the commands to load publicly-available campaign finance [data from the Federal Election Commission](http://www.fec.gov/finance/disclosure/ftpdet.shtml#a2015_2016) into a PostgreSQL database.\n",
    "\n",
    "To see what we have in the database, it's simplest to use the PostgreSQL shell command `psql` to interact with the database.  You can run `man psql` to learn more about it. A few handy tips:\n",
    "1. `psql` supports some useful non-SQL \"meta-\"commands, which you access via backslash (`\\`). To find out about them, run `psql` in a bash shell, and at the prompt you can type `\\?`.\n",
    "2. `psql` has builtin documentation for SQL. To see that, at the `psql` prompt type `\\help`.\n",
    "3. `psql` is an interactive SQL shell, so not suitable for use inside a Jupyter notebook. If you want to invoke it within a Jupyter notebook, you should use `!psql -c <SQL statement>` -- the `-c` flag tells psql to run the SQL statement and then exit:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "!psql -c \"select * from profs;\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's see what tables we have our database after loading the FEC data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "!psql -c \"\\d\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And let's have a look at the `individual` table's schema:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "!psql -c \"\\d individual\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Browsing Tables: `LIMIT` and sampling\n",
    "*This is not the first topic usually taught in SQL, but it's extremely useful for exploration.*\n",
    "\n",
    "OK, now we have some serious data loaded and we're ready to explore it.\n",
    "\n",
    "Database tables are often big--hence the use of a database system. When browsing them at first, we may want to look at exemplary rows: e.g., an arbitrary number of rows, or a random sample of the rows.\n",
    "\n",
    "To look at all of the data in the `individual` table, we would simply write:\n",
    "\n",
    "```sql\n",
    "select * \\\n",
    "  from individual;\n",
    "```\n",
    "\n",
    "But that would return ** *39,986,122* ** rows into our Jupyter notebook's memory, and perhaps overflow the RAM in your computer.  Instead, we could limit the size of the output to the first 3 rows as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql \n",
    "select *\n",
    "  from individual \n",
    " limit 4;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Some notes on the `limit` clause:\n",
    "\n",
    "1. Not only does it produce a small output, it's quite efficient: the database system stops iterating over the table after producing the first three rows, saving the work of examining the other nearly 40 million rows. \n",
    "2. Recall that relations have no intrinsic order, so this is some arbitrary choice of 3 rows. Two issues to keep in mind:\n",
    "    1. This is a *biased* choice of rows. Very likely these are the first 3 rows stored in some disk file managed by the database, which may (for example) be the first 3 rows that were entered into the database, so they may not be representative of rows entered later.\n",
    "    1. The result is non-deterministic. Given that tables are not guaranteed to have an intrinsic order, it is considered correct for an SQL engine to return *any* 3 rows that satisfy this query, and return a different 3 rows each time!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As data scientists, we should be concerned about spending much time looking at a biased subset of our data. Instead, we might want an i.i.d. random sample of the rows in the table. There are various methods for sampling from a table. A simple one built into many database systems including PostgreSQL is [Bernoulli](https://en.wikipedia.org/wiki/Bernoulli_sampling) sampling, in which the decision to return each row is made randomly and independently. As a metaphor, the database engine \"flips a coin\" for each row to decide whether to return it. We can influence the sampling rate by choosing the probability of a \"true\" result of the coinflip. This is done on a per-table basis in the `FROM` clause of the query like so:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql \n",
    "select *\n",
    "  from individual tablesample bernoulli(.00001);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Three things to note relative to our previous `limit` construct:\n",
    "\n",
    "1. Bernoulli sampling is slow: it scales linearly with the table size by iterating through every row in the table.\n",
    "2. The number of rows returned by Bernoulli sampling is probabilistic. For a table with $n$ rows and a sampling probability $p$, the output size comes from a [binomial distribution]() with mean $np$ and variance ($np(1-p)$). For a very small $p$, the variance means we could easily get 0 rows back when trying our query!\n",
    "3. If we don't know the size of the table, it's hard to choose a practical sampling probability. First we want to count up the number of rows $n$ (see the discussion of aggregation queries below), to inform us of a good $p$ to choose to get our desired output size. That means yet another full pass of the table to compute the count before we compute the sample!\n",
    "\n",
    "For these reasons, if we want a proper i.i.d sample, it's a good idea to compute a nice-sized sample and store it elsewhere, keeping it reasonably large for more general use. Since we will not be updating and rows in our `individual` table, we can do this without worrying that the sample will get \"out of date\" with respect to the context of `individual`.  \n",
    "\n",
    "We can use the `CREATE TABLE AS SELECT ...` (a.k.a. CTAS) pattern to do create a table that saves the output of a query:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql \n",
    "-- drop table if exists indiv_sample;\n",
    "create table indiv_sample as\n",
    "select *\n",
    "  from individual tablesample bernoulli(.03);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Selecting rows and columns, and calling scalar (per-row) functions."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "OK, we already had a peek at the `individual` table. Now let's look at specific attributes (columns) relates to who is donating how much. \n",
    "\n",
    "In addition to referencing the columns of `individual` in the `select` clause, we can also derive new columns by writing field-level (so-called \"scalar\") functions. Typically we reference some table columns in those functions.\n",
    "\n",
    "In our case, let's compute the log of `transaction_amt` for subsequent plotting. SQL comes with many typical functions you can use in this way, and PostgreSQL is particularly rich on this front; see the [PostgreSQL manual](https://www.postgresql.org/docs/9.6/static/functions.html) for details.\n",
    "\n",
    "We'll look at `indiv_sample` rather than `individual` while we're just exploring."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql \n",
    "select name, state, cmte_id,\n",
    "       transaction_amt, log(transaction_amt)\n",
    "  from indiv_sample\n",
    "  limit 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### `CASE` statements: SQL conditionals in the `FROM` clause\n",
    "What about smaller donations?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql \n",
    "select name, state, cmte_id,\n",
    "       transaction_amt, log(transaction_amt)\n",
    "  from indiv_sample\n",
    " WHERE transaction_amt < 10\n",
    "  limit 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Uh oh, log is not defined for numbers <= 0! We need a *conditional* statement in the `select` clause to decide what function to call. We can use SQL's `case` construct for that."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql select name, state, cmte_id, transaction_amt,\n",
    "             CASE WHEN transaction_amt > 0 THEN log(transaction_amt)\n",
    "                  WHEN transaction_amt = 0 THEN 0\n",
    "                  ELSE -1*(log(abs(transaction_amt)))\n",
    "             END\n",
    "        from indiv_sample\n",
    "       where transaction_amt < 10\n",
    "       limit 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To make things more readable, let's rename the derived column using an `AS` clause:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql select name, state, cmte_id, transaction_amt,\n",
    "             case when transaction_amt > 0 then log(transaction_amt)\n",
    "                  when transaction_amt = 0 then 0\n",
    "                  else -1*(log(abs(transaction_amt)))\n",
    "             end AS log_magnitude\n",
    "        from indiv_sample\n",
    "       where transaction_amt < 10\n",
    "       limit 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Selecting rows: more interesting `WHERE` clauses\n",
    "We can choose which rows do and do not appear in the query by putting boolean-valued expressions (\"predicates\") in the `WHERE` clause, right after the `FROM` clause. For example, we might be looking for big donations greater than $1000:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql select name, city, state, transaction_amt\n",
    "        from individual \n",
    "       WHERE transaction_amt > 1000 \n",
    "       limit 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can form more complex predicates using Boolean connectives AND, OR and NOT:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql select name, city, state, transaction_amt\n",
    "        from individual\n",
    "       WHERE transaction_amt > 1000\n",
    "         AND (state = 'WI' OR state = 'IL')\n",
    "         AND NOT (city = 'CHICAGO')\n",
    "         limit 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data Generation\n",
    "Sometimes it's useful to auto-generate data in queries, rather than examine data in the database. This is nice for testing, but also can be useful to play some computational tricks as you'll see in your homework.\n",
    "\n",
    "SQL has a simple scalar function called [`random`](https://www.postgresql.org/docs/9.6/static/functions-math.html#FUNCTIONS-MATH-RANDOM-TABLE) that returns a random value between 0.0 and 1.0. You can use this if you need to generate a column of random numbers.  (The PostgreSQL manual doesn't promise much about the statistical properties of this random number generator.)\n",
    "\n",
    "Let's see if any Professor rolls their lucky number on a 10-sided die. (Side-question: is this query really implementing a 10-sided die? If not, what would be a better query?)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql select *, \n",
    "             round(random()*10) as roll_dice \n",
    "        from profs;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Suppose we want to generate a whole bunch of random numbers, not tied to any particular stored table -- can we do that in SQL?\n",
    "\n",
    "SQL has a notion of [table-valued functions](https://www.postgresql.org/docs/9.6/static/functions-srf.html): functions that return tables, and hence can be used in a `FROM` clause of a query. The standard table-valued function is called `generate_series`, and it's much like numpy's `arange`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%sql select * from generate_series(1,5);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%sql select * from generate_series(1, 10, 2);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So to generate 5 random real numbers between 0 and 10, we might use this SQL:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%sql select (10*random()) as rando from generate_series(1,5);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And if we want integers, we can use a PostgreSQL typecast operator (postfix `::<type>`):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%sql select (10*random())::integer as rando_int from generate_series(1,5);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now suppose we want to populate a \"matrix\" relation `my_matrix(x, y, val)` full of random values. In Python during Lecture 7 we used `np.random.randn(3,2)`. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "# normally distributed random numbers, mean 0 variance 1\n",
    "np.random.randn(3,2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this relational version we need to explicitly generate the `x` and `y` values. We can do this via SQL's built-in cartesian product!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT rows.x, columns.y, random() AS val\n",
    "  FROM generate_series(0,2) AS rows(x),\n",
    "       generate_series(0,1) AS columns(y);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We may want to store a matrix as a table—in which case we should set up the schema properly to ensure that it remains a legal matrix."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "DROP TABLE IF EXISTS my_matrix;\n",
    "\n",
    "CREATE TABLE my_matrix(x integer, y integer, val float, PRIMARY KEY(x,y));\n",
    "\n",
    "INSERT INTO my_matrix\n",
    "SELECT rows.x, columns.y, random() AS val\n",
    "  FROM generate_series(0,2) AS rows(x),\n",
    "       generate_series(0,1) AS columns(y);\n",
    "        \n",
    "SELECT * FROM my_matrix;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A few take-aways from the previous cell:\n",
    "- Notice the schema of `my_matrix` reflects the fact that `val` is a function of the row (`x`) and column (`y`) IDs.\n",
    "- We've said before that the order of rows in a table isn't defined in SQL. Is this relational representation of a \"matrix\" faithful to the mathematical definition of a matrix? Why or why not?\n",
    "- Notice the `INSERT` statement, which contains a `SELECT` query rather than the `VALUES` we saw before. You might want to experiment and see what would happen if the `SELECT` query produces a different schema than `my_matrix`: try having it produce too few columns, too many columns, columns in different orders, etc.\n",
    "- In the `INSERT...SELECT` statement, notice the definition of output column names via the `AS` in the `SELECT` clause. Is that necessary here?\n",
    "- In the `INSERT...SELECT` statement, notice the definition of table *and* column names in the `FROM` clause via `AS`, and the way they get referenced in the `SELECT` clause. Do we need the tablenames specified in the `SELECT` clause? Try it and see!\n",
    "- Count the rows in the output...does it look good?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### User-defined functions (UDFs)\n",
    "Sometimes we may want a custom scalar function that isn't built into SQL. Some database systems allow you to register your own *user-defined functions* (UDFs) in one or more programming languages. Conveniently, PostgreSQL allows us to register user-defined functions written in Python. Be aware of two things:\n",
    "\n",
    "1. Calling Python for each row in a query is quite a bit slower than using the pre-compiled built-in functions in SQL ... this is akin to the use of Python loops instead of `numpy` calls. *If you can avoid using Python UDFs you should do so to get better performance*.\n",
    "\n",
    "2. Python is a full-feature programming language with access to your operating system's functionality, which means it can reach outside of the scope of the query and wreak havoc, including running arbitrary UNIX commands. (PostgreSQL refers to this as an `untrusted` language.) Be *very* careful with the Python UDFs you use in your Postgres queries! If you want to be safer write UDFs in a trusted language. PostgreSQL has a [number of other languages](https://www.postgresql.org/docs/current/static/xplang.html) to choose from, including [Java](https://www.postgresql.org/docs/current/static/external-pl.html) and even [R](https://www.postgresql.org/docs/current/static/external-pl.html)!.\n",
    "\n",
    "First we tell PostgreSQL we want to use the plpythonu package (so named because of \"pl\" for \"programming language\", \"u\" for \"untrusted\"):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%sql create extension if not exists plpythonu;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's write some trivial Python code and register it as a UDF using the `create function` command. Since SQL is a typed language, we need to specify the SQL types for the input and output to our function, in addition to the code (within $$ delimiters) and the language:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql \n",
    "drop function if exists double(x integer);\n",
    "\n",
    "create function double(x integer) returns integer\n",
    "as $$\n",
    "  return x + x\n",
    "$$ language plpythonu;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql select double(10), 10+10 as builtin_double \n",
    "        from generate_series(1,1);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "OK let's do a little performance test on a generated table of 1 million rows. To get timing numbers, we'll prefix our queries with `EXPLAIN ANALYZE`: this tells postgres to return us information about the query execution, rather than returning us actual data. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%sql EXPLAIN ANALYZE select double(10) \\\n",
    "                       from generate_series(1, 1000000);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%sql explain analyze select 10+10 from generate_series(1, 1000000);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice the Execution Time: the UDF makes things how many times slower? \n",
    "\n",
    "*Question on experimental methodology: as data scientists, how should we get more confidence in our understanding of the performance of these queries?*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### More interesting UDFs\n",
    "As we said above, PostgreSQL already provides many standard arithmetic and string functions. The reason to use a UDF is typically to invoke some more involved procedural code -- e.g. something from an interesting open-source library you don't want to rewrite.\n",
    "\n",
    "Obviously there are many interesting Python libraries we might like to use in doing our data science. One that's often useful in a SQL context is the Natural Language Tool Kit [nltk](http://nltk.org). One common use of nltk is *sentiment analysis*: converting full text into a summary feature that describes the \"feelings\" behind the text.\n",
    "\n",
    "Ordinarily you'd train your sentiment analysis package (and we'll learn about training models soon!) But in the next example, we'll use a simple pre-trained sentiment analysis package from nltk called Vader.\n",
    "\n",
    "First, let's see how we use Vader from Python:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from nltk.sentiment.vader import SentimentIntensityAnalyzer\n",
    "s = SentimentIntensityAnalyzer()\n",
    "score = s.polarity_scores('Data scientists are the bomb: the sexiest job of the 21st century!!')\n",
    "score"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![NYT Crossword 2/10/17](http://www.ds100.org/assets/images/crossword.jpg)\n",
    "(Yes, this was in the New York Times crossword puzzle on 2/10/17... thanks Mom!)\n",
    "\n",
    "Let's boil that down to a single number, the difference between positive and negative"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "score['pos'] - score['neg']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we've seen what we want in Python, let's register that idea as a Postgres UDF:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "drop function if exists sentiment(t text);\n",
    "\n",
    "create function sentiment(t text) returns float\n",
    "as $$\n",
    "from nltk.sentiment.vader import SentimentIntensityAnalyzer\n",
    "s = SentimentIntensityAnalyzer()\n",
    "scores = s.polarity_scores(t)\n",
    "return scores['pos'] - scores['neg']\n",
    "$$\n",
    "language plpythonu;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "select name, transaction_amt, memo_text, sentiment(memo_text)\n",
    "  from individual\n",
    " where memo_text IS NOT NULL\n",
    " limit 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "OK, let's look at some tweets -- they'll be more interesting."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "select timestamp, content, sentiment(content)\n",
    "  from trump\n",
    " where content IS NOT NULL\n",
    " limit 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Ordering the output\n",
    "As a nicety, SQL allows you to order your output rows, in either ascending (ASC) or descending (DESC) order of the values in columns. For example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "select name, city, state, transaction_amt\n",
    "  from indiv_sample\n",
    " where transaction_amt > 1000\n",
    "   and (state = 'WI' or state = 'IL')\n",
    "   and not (city = 'CHICAGO')\n",
    " ORDER BY transaction_amt DESC\n",
    " limit 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note how the combination of `ORDER BY` and `LIMIT 10` gives you the \"top 10\" results. That's often handy!\n",
    "\n",
    "You can order by multiple columns, which will leave you with \"lexicographic\" ordering: it's ordered by the first column, and ties on the first column are ordered by the second column, etc.  For example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "select name, city, state, transaction_amt\n",
    "  from indiv_sample\n",
    " where transaction_amt > 2000\n",
    "   and (state = 'WI' or state = 'IL')\n",
    "   and not (city = 'CHICAGO')\n",
    " order by state DESC, city\n",
    " limit 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Removing duplicates: SELECT DISTINCT\n",
    "As we know, SQL is a multiset logic, preserving the meaning of the number of duplicates in query results. Sometimes, however, we don't want to keep the duplicates, we want to eliminate them.  This is done simply by adding the keyword `DISTINCT` after the `SELECT` statement:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT DISTINCT firstname\n",
    "  FROM profs;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "SELECT DISTINCT firstname, luckynumber\n",
    "  FROM profs;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Aggregation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What's the granularity of our `individual` table? Transactions? Examining the schema it doesn't look like there's a key for the donor. Maybe the `image_num` is a key? Or the `file_num`?  \n",
    "\n",
    "To determine this, we need to count up the total number of rows, and the number of *distinct values* that occur in the `image_num` column. SQL provides a family of [*aggregate functions*] for use in the `select` clause. In the simplest form, queries with aggregates in the `select` clause generate a single row of output, with each aggregate function performing a summary of all the rows of input. You can have many aggregate functions in your `select` clause:\n",
    "\n",
    "A list of built-in aggregate functions in PostgreSQL is [here](https://www.postgresql.org/docs/9.6/static/functions-aggregate.html). In our case, the query we are looking for is as follows. To start with, we'll run it on our sample for a sanity check:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql select count(*) as total_rows,\n",
    "             count(DISTINCT image_num) as image_nums,\n",
    "             count(distinct file_num) as file_nums\n",
    "        from indiv_sample;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note how the `distinct` modifier can be used inside an aggregate function: it removes any duplicated values *prior to computing the aggregate*.\n",
    "\n",
    "Let's examine some more descriptive statistics of our data using SQL aggregates:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql select count(*), sum(transaction_amt),\n",
    "             avg(transaction_amt), stddev(transaction_amt)\n",
    "        from indiv_sample;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Group By and Aggregation\n",
    "Often we want to partition our input table into groups, and compute an aggregate per group. SQL makes this easy: we use a `group by` clause to specify our groups:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql select state, count(*), sum(transaction_amt),\n",
    "             avg(transaction_amt), stddev(transaction_amt)\n",
    "        from indiv_sample\n",
    "       GROUP BY state;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that we can also group by more than one column. (It does not matter what order the columns appear in the `group by` clause.) *In the absence of a `LIMIT` clause, would the query below have more rows than the previous one, or fewer?*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql select city, state, count(*), sum(transaction_amt),\n",
    "             avg(transaction_amt), stddev(transaction_amt)\n",
    "        from indiv_sample\n",
    "       group by state, city\n",
    "       order by state, city\n",
    "       limit 20;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Legal columns in the `select` clause of a `group by` query\n",
    "Note that we can reference the columns `city` and `state` from the `group by` clause in the `select` list: this makes sense because each row (and each aggregate value) in the output can have at most one city or state associated with it. However it would not make sense to reference `name`, since there are many names associated with a given row in the output:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql select city, state, name, count(*), sum(transaction_amt),\n",
    "             avg(transaction_amt), stddev(transaction_amt)\n",
    "        from indiv_sample\n",
    "       group by city, state\n",
    "       order by state, city\n",
    "       limit 20;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Read the error message carefully: note that it *would* have been legal to use `name` inside an aggregate function, since the aggregate would have taken the many names associated with an output row and summarized them into one value. (Aggregating strings is a bit unusual, but use your imagination!)\n",
    "\n",
    "#### Connection to Index variables in math notation (skip in class)\n",
    "\n",
    "There is a direct analogy to index subscripts in familiar math notation. Suppose you have a set of objects $a_{ij}$. Consider the expression $\\sum_i a_{ij}$. Which index corresponds to the `group by` column: $i$ or $j$?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### The HAVING Clause in GROUP BY queries\n",
    "We can use the `WHERE` clause in an aggregate query to filter the rows that are to be aggregated. In relational algebra terms, this is a selection prior to the aggregation, i.e. $\\gamma_{c_1...c_m, a_1...a_n}(\\sigma_\\theta(R))$.\n",
    "\n",
    "But what about if we want to filter out groups based on a property of the group, say its size? The `HAVING` clause allows us to do selections *after* the aggregation, i.e. $\\sigma_{\\theta_2}(\\gamma_{c_1...c_m, a_1...a_n}(R))$. Here, $\\theta_2$ can refer to the aggregate properties $a_1, ..., a_n$ computed by $\\gamma$.\n",
    "\n",
    "Let's first use a `WHERE` clause to focus our aggregates on only large donations."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql select city, state, count(*), sum(transaction_amt),\n",
    "             avg(transaction_amt), stddev(transaction_amt)\n",
    "        from indiv_sample\n",
    "       where transaction_amt > 1000\n",
    "       group by state, city\n",
    "       order by state, city\n",
    "       limit 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we will using the HAVING clause to only look at cities with more than 10 million dollars in donations:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql select city, state, count(*), sum(transaction_amt),\n",
    "            avg(transaction_amt), stddev(transaction_amt)\n",
    "       from indiv_sample\n",
    "      where transaction_amt > 1000\n",
    "      group by state, city\n",
    "     HAVING sum(transaction_amt) > 10000\n",
    "      order by state, city;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that we have to compute (and type!) `sum(transaction_amt)` twice. We can clean that up later when we learn about views and common table expressions."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### What's up with indiv_sample.image_num?\n",
    "OK, let's return to that query above, and this time identify the duplicates!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql select image_num, count(*) as total_rows\n",
    "        from indiv_sample\n",
    "       group by image_num\n",
    "       having count(*) > 1;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "## Summary\n",
    "OK, you now have enough information to do basic exploration of the contents of data set, using the equivalent of relational algebra *selection* and *projection*, along with niceties around limits, samples, and function invocation. Next time we'll pick up with statistical uses of aggregation, as well as *joins*."
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}