sql/novice/06-agg.ipynb

   1 {
   2  "metadata": {
   3   "name": ""
   4  },
   5  "nbformat": 3,
   6  "nbformat_minor": 0,
   7  "worksheets": [
   8   {
   9    "cells": [
  10     {
  11      "cell_type": "heading",
  12      "level": 1,
  13      "metadata": {},
  14      "source": [
  15       "Aggregation"
  16      ]
  17     },
  18     {
  19      "cell_type": "markdown",
  20      "metadata": {},
  21      "source": [
  22       "We now want to calculate ranges and averages for our data.\n",
  23       "We know how to select all of the dates from the `Visited` table:"
  24      ]
  25     },
  26     {
  27      "cell_type": "code",
  28      "collapsed": false,
  29      "input": [
  30       "%load_ext sqlitemagic"
  31      ],
  32      "language": "python",
  33      "metadata": {},
  34      "outputs": [],
  35      "prompt_number": 1
  36     },
  37     {
  38      "cell_type": "code",
  39      "collapsed": false,
  40      "input": [
  41       "%%sqlite survey.db\n",
  42       "select dated from Visited;"
  43      ],
  44      "language": "python",
  45      "metadata": {},
  46      "outputs": [
  47       {
  48        "html": [
  49         "<table>\n",
  50         "<tr><td>1927-02-08</td></tr>\n",
  51         "<tr><td>1927-02-10</td></tr>\n",
  52         "<tr><td>1939-01-07</td></tr>\n",
  53         "<tr><td>1930-01-12</td></tr>\n",
  54         "<tr><td>1930-02-26</td></tr>\n",
  55         "<tr><td>None</td></tr>\n",
  56         "<tr><td>1932-01-14</td></tr>\n",
  57         "<tr><td>1932-03-22</td></tr>\n",
  58         "</table>"
  59        ],
  60        "metadata": {},
  61        "output_type": "display_data",
  62        "text": [
  63         "<IPython.core.display.HTML at 0x1023c4ed0>"
  64        ]
  65       }
  66      ],
  67      "prompt_number": 2
  68     },
  69     {
  70      "cell_type": "markdown",
  71      "metadata": {},
  72      "source": [
  73       "but to combine them,\n",
  74       "wee must use an [aggregation function](../gloss.html#aggregation-function)\n",
  75       "such as `min` or `max`.\n",
  76       "Each of these functions takes a set of records as input,\n",
  77       "and produces a single record as output:"
  78      ]
  79     },
  80     {
  81      "cell_type": "code",
  82      "collapsed": false,
  83      "input": [
  84       "%%sqlite survey.db\n",
  85       "select min(dated) from Visited;"
  86      ],
  87      "language": "python",
  88      "metadata": {},
  89      "outputs": [
  90       {
  91        "html": [
  92         "<table>\n",
  93         "<tr><td>1927-02-08</td></tr>\n",
  94         "</table>"
  95        ],
  96        "metadata": {},
  97        "output_type": "display_data",
  98        "text": [
  99         "<IPython.core.display.HTML at 0x1023c4ed0>"
 100        ]
 101       }
 102      ],
 103      "prompt_number": 3
 104     },
 105     {
 106      "cell_type": "code",
 107      "collapsed": false,
 108      "input": [
 109       "%%sqlite survey.db\n",
 110       "select max(dated) from Visited;"
 111      ],
 112      "language": "python",
 113      "metadata": {},
 114      "outputs": [
 115       {
 116        "html": [
 117         "<table>\n",
 118         "<tr><td>1939-01-07</td></tr>\n",
 119         "</table>"
 120        ],
 121        "metadata": {},
 122        "output_type": "display_data",
 123        "text": [
 124         "<IPython.core.display.HTML at 0x1023c4090>"
 125        ]
 126       }
 127      ],
 128      "prompt_number": 4
 129     },
 130     {
 131      "cell_type": "markdown",
 132      "metadata": {},
 133      "source": [
 134       "`min` and `max` are just two of\n",
 135       "the aggregation functions built into SQL.\n",
 136       "Three others are `avg`,\n",
 137       "`count`,\n",
 138       "and `sum`:"
 139      ]
 140     },
 141     {
 142      "cell_type": "code",
 143      "collapsed": false,
 144      "input": [
 145       "%%sqlite survey.db\n",
 146       "select avg(reading) from Survey where quant='sal';"
 147      ],
 148      "language": "python",
 149      "metadata": {},
 150      "outputs": [
 151       {
 152        "html": [
 153         "<table>\n",
 154         "<tr><td>7.20333333333</td></tr>\n",
 155         "</table>"
 156        ],
 157        "metadata": {},
 158        "output_type": "display_data",
 159        "text": [
 160         "<IPython.core.display.HTML at 0x1023c4090>"
 161        ]
 162       }
 163      ],
 164      "prompt_number": 7
 165     },
 166     {
 167      "cell_type": "code",
 168      "collapsed": false,
 169      "input": [
 170       "%%sqlite survey.db\n",
 171       "select count(reading) from Survey where quant='sal';"
 172      ],
 173      "language": "python",
 174      "metadata": {},
 175      "outputs": [
 176       {
 177        "html": [
 178         "<table>\n",
 179         "<tr><td>9</td></tr>\n",
 180         "</table>"
 181        ],
 182        "metadata": {},
 183        "output_type": "display_data",
 184        "text": [
 185         "<IPython.core.display.HTML at 0x1023c4fd0>"
 186        ]
 187       }
 188      ],
 189      "prompt_number": 8
 190     },
 191     {
 192      "cell_type": "code",
 193      "collapsed": false,
 194      "input": [
 195       "%%sqlite survey.db\n",
 196       "select sum(reading) from Survey where quant='sal';"
 197      ],
 198      "language": "python",
 199      "metadata": {},
 200      "outputs": [
 201       {
 202        "html": [
 203         "<table>\n",
 204         "<tr><td>64.83</td></tr>\n",
 205         "</table>"
 206        ],
 207        "metadata": {},
 208        "output_type": "display_data",
 209        "text": [
 210         "<IPython.core.display.HTML at 0x1023c4d90>"
 211        ]
 212       }
 213      ],
 214      "prompt_number": 9
 215     },
 216     {
 217      "cell_type": "markdown",
 218      "metadata": {},
 219      "source": [
 220       "We used `count(reading)` here,\n",
 221       "but we could just as easily have counted `quant`\n",
 222       "or any other field in the table,\n",
 223       "or even used `count(*)`,\n",
 224       "since the function doesn't care about the values themselves,\n",
 225       "just how many values there are.\n",
 226       "\n",
 227       "SQL lets us do several aggregations at once.\n",
 228       "We can,\n",
 229       "for example,\n",
 230       "find the range of sensible salinity measurements:"
 231      ]
 232     },
 233     {
 234      "cell_type": "code",
 235      "collapsed": false,
 236      "input": [
 237       "%%sqlite survey.db\n",
 238       "select min(reading), max(reading) from Survey where quant='sal' and reading<=1.0;"
 239      ],
 240      "language": "python",
 241      "metadata": {},
 242      "outputs": [
 243       {
 244        "html": [
 245         "<table>\n",
 246         "<tr><td>0.05</td><td>0.21</td></tr>\n",
 247         "</table>"
 248        ],
 249        "metadata": {},
 250        "output_type": "display_data",
 251        "text": [
 252         "<IPython.core.display.HTML at 0x1023c4fd0>"
 253        ]
 254       }
 255      ],
 256      "prompt_number": 10
 257     },
 258     {
 259      "cell_type": "markdown",
 260      "metadata": {},
 261      "source": [
 262       "We can also combine aggregated results with raw results,\n",
 263       "although the output might surprise you:"
 264      ]
 265     },
 266     {
 267      "cell_type": "code",
 268      "collapsed": false,
 269      "input": [
 270       "%%sqlite survey.db\n",
 271       "select person, count(*) from Survey where quant='sal' and reading<=1.0;"
 272      ],
 273      "language": "python",
 274      "metadata": {},
 275      "outputs": [
 276       {
 277        "html": [
 278         "<table>\n",
 279         "<tr><td>lake</td><td>7</td></tr>\n",
 280         "</table>"
 281        ],
 282        "metadata": {},
 283        "output_type": "display_data",
 284        "text": [
 285         "<IPython.core.display.HTML at 0x1023c4d90>"
 286        ]
 287       }
 288      ],
 289      "prompt_number": 11
 290     },
 291     {
 292      "cell_type": "markdown",
 293      "metadata": {},
 294      "source": [
 295       "Why does Lake's name appear rather than Roerich's or Dyer's?\n",
 296       "The answer is that when it has to aggregate a field,\n",
 297       "but isn't told how to,\n",
 298       "the database manager chooses an actual value from the input set.\n",
 299       "It might use the first one processed,\n",
 300       "the last one,\n",
 301       "or something else entirely.\n",
 302       "\n",
 303       "Another important fact is that when there are no values to aggregate,\n",
 304       "aggregation's result is \"don't know\"\n",
 305       "rather than zero or some other arbitrary value:"
 306      ]
 307     },
 308     {
 309      "cell_type": "code",
 310      "collapsed": false,
 311      "input": [
 312       "%%sqlite survey.db\n",
 313       "select person, max(reading), sum(reading) from Survey where quant='missing';"
 314      ],
 315      "language": "python",
 316      "metadata": {},
 317      "outputs": [
 318       {
 319        "html": [
 320         "<table>\n",
 321         "<tr><td>None</td><td>None</td><td>None</td></tr>\n",
 322         "</table>"
 323        ],
 324        "metadata": {},
 325        "output_type": "display_data",
 326        "text": [
 327         "<IPython.core.display.HTML at 0x1023c4fd0>"
 328        ]
 329       }
 330      ],
 331      "prompt_number": 12
 332     },
 333     {
 334      "cell_type": "markdown",
 335      "metadata": {},
 336      "source": [
 337       "One final important feature of aggregation functions is that\n",
 338       "they are inconsistent with the rest of SQL in a very useful way.\n",
 339       "If we add two values,\n",
 340       "and one of them is null,\n",
 341       "the result is null.\n",
 342       "By extension,\n",
 343       "if we use `sum` to add all the values in a set,\n",
 344       "and any of those values are null,\n",
 345       "the result should also be null.\n",
 346       "It's much more useful,\n",
 347       "though,\n",
 348       "for aggregation functions to ignore null values\n",
 349       "and only combine those that are non-null.\n",
 350       "This behavior lets us write our queries as:"
 351      ]
 352     },
 353     {
 354      "cell_type": "code",
 355      "collapsed": false,
 356      "input": [
 357       "%%sqlite survey.db\n",
 358       "select min(dated) from Visited;"
 359      ],
 360      "language": "python",
 361      "metadata": {},
 362      "outputs": [
 363       {
 364        "html": [
 365         "<table>\n",
 366         "<tr><td>1927-02-08</td></tr>\n",
 367         "</table>"
 368        ],
 369        "metadata": {},
 370        "output_type": "display_data",
 371        "text": [
 372         "<IPython.core.display.HTML at 0x1023c4fd0>"
 373        ]
 374       }
 375      ],
 376      "prompt_number": 13
 377     },
 378     {
 379      "cell_type": "markdown",
 380      "metadata": {},
 381      "source": [
 382       "instead of always having to filter explicitly:"
 383      ]
 384     },
 385     {
 386      "cell_type": "code",
 387      "collapsed": false,
 388      "input": [
 389       "%%sqlite survey.db\n",
 390       "select min(dated) from Visited where dated is not null;"
 391      ],
 392      "language": "python",
 393      "metadata": {},
 394      "outputs": [
 395       {
 396        "html": [
 397         "<table>\n",
 398         "<tr><td>1927-02-08</td></tr>\n",
 399         "</table>"
 400        ],
 401        "metadata": {},
 402        "output_type": "display_data",
 403        "text": [
 404         "<IPython.core.display.HTML at 0x1023c4090>"
 405        ]
 406       }
 407      ],
 408      "prompt_number": 14
 409     },
 410     {
 411      "cell_type": "markdown",
 412      "metadata": {},
 413      "source": [
 414       "### Challenges\n",
 415       "\n",
 416       "1.  How many temperature readings did Frank Pabodie record,\n",
 417       "    and what was their average value?\n",
 418       "\n",
 419       "2.  The average of a set of values is the sum of the values\n",
 420       "    divided by the number of values.\n",
 421       "    Does this mean that the `avg` function returns 2.0 or 3.0\n",
 422       "    when given the values 1.0, `null`, and 5.0?\n",
 423       "\n",
 424       "3.  We want to calculate the difference between\n",
 425       "    each individual radiation reading\n",
 426       "    and the average of all the radiation readings.\n",
 427       "    We write the query:\n",
 428       "\n",
 429       "    ```\n",
 430       "    select reading-avg(reading) from Survey where quant='rad';\n",
 431       "    ```\n",
 432       "\n",
 433       "    What does this actually produce, and why?\n",
 434       "\n",
 435       "4.  The function `group_concat(field, separator)`\n",
 436       "    concatenates all the values in a field\n",
 437       "    using the specified separator character\n",
 438       "    (or ',' if the separator isn't specified).\n",
 439       "    Use this to produce a one-line list of scientists' names,\n",
 440       "    such as:\n",
 441       "\n",
 442       "    ```\n",
 443       "    William Dyer, Frank Pabodie, Anderson Lake, Valentina Roerich, Frank Danforth\n",
 444       "    ```\n",
 445       "\n",
 446       "    Can you find a way to order the list by surname?"
 447      ]
 448     },
 449     {
 450      "cell_type": "heading",
 451      "level": 2,
 452      "metadata": {},
 453      "source": [
 454       "Next Steps"
 455      ]
 456     },
 457     {
 458      "cell_type": "markdown",
 459      "metadata": {},
 460      "source": [
 461       "FIXME"
 462      ]
 463     }
 464    ],
 465    "metadata": {}
 466   }
 467  ]
 468 }