forked from swcarpentry/sql-novice-survey
-
Notifications
You must be signed in to change notification settings - Fork 0
/
06-agg.html
498 lines (498 loc) · 19.7 KB
/
06-agg.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="generator" content="pandoc">
<title>Software Carpentry: Databases and SQL</title>
<link rel="shortcut icon" type="image/x-icon" href="/favicon.ico" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="css/bootstrap/bootstrap.css" />
<link rel="stylesheet" type="text/css" href="css/bootstrap/bootstrap-responsive.css" />
<link rel="stylesheet" type="text/css" href="css/swc.css" />
<link rel="stylesheet" type="text/css" href="css/swc-workshop-and-lesson.css" />
<link rel="stylesheet" type="text/css" href="css/lesson.css" />
<link rel="alternate" type="application/rss+xml" title="Software Carpentry Blog" href="http://software-carpentry.org/feed.xml"/>
<meta charset="UTF-8" />
<!-- HTML5 shim, for IE6-8 support of HTML5 elements -->
<!--[if lt IE 9]>
<script src="http://html5shim.googlecode.com/svn/trunk/html5.js"></script>
<![endif]-->
</head>
<body class="lesson">
<div class="container container-full-width card">
<div class="banner">
<a href="http://software-carpentry.org" title="Software Carpentry">
<img alt="Software Carpentry banner" src="img/software-carpentry-banner.png" />
</a>
</div>
<div class="row-fluid">
<div class="span10 offset1">
<h1 class="title">Databases and SQL</h1>
<h2 class="subtitle">Aggregation</h2>
<div id="learning-objectives" class="objectives">
<h2>Learning Objectives</h2>
<ul>
<li>Define "aggregation" and give examples of its use.</li>
<li>Write queries that compute aggregated values.</li>
<li>Trace the execution of a query that performs aggregation.</li>
<li>Explain how missing data is handled during aggregation.</li>
</ul>
</div>
<p>We now want to calculate ranges and averages for our data. We know how to select all of the dates from the <code>Visited</code> table:</p>
<pre class="sourceCode sql"><code class="sourceCode sql"><span class="kw">SELECT</span> dated <span class="kw">FROM</span> Visited;</code></pre>
<table>
<thead>
<tr class="header">
<th align="left">dated</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">1927-02-08</td>
</tr>
<tr class="even">
<td align="left">1927-02-10</td>
</tr>
<tr class="odd">
<td align="left">1939-01-07</td>
</tr>
<tr class="even">
<td align="left">1930-01-12</td>
</tr>
<tr class="odd">
<td align="left">1930-02-26</td>
</tr>
<tr class="even">
<td align="left">-null-</td>
</tr>
<tr class="odd">
<td align="left">1932-01-14</td>
</tr>
<tr class="even">
<td align="left">1932-03-22</td>
</tr>
</tbody>
</table>
<p>but to combine them, we must use an <a href="reference.html#aggregation-function">aggregation function</a> such as <code>min</code> or <code>max</code>. Each of these functions takes a set of records as input, and produces a single record as output:</p>
<pre class="sourceCode sql"><code class="sourceCode sql"><span class="kw">SELECT</span> <span class="fu">min</span>(dated) <span class="kw">FROM</span> Visited;</code></pre>
<table>
<thead>
<tr class="header">
<th align="left">min(dated)</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">1927-02-08</td>
</tr>
</tbody>
</table>
<p><img src="fig/sql-aggregation.svg" alt="SQL Aggregation" /></p>
<pre class="sourceCode sql"><code class="sourceCode sql"><span class="kw">SELECT</span> <span class="fu">max</span>(dated) <span class="kw">FROM</span> Visited;</code></pre>
<table>
<thead>
<tr class="header">
<th align="left">max(dated)</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">1939-01-07</td>
</tr>
</tbody>
</table>
<p><code>min</code> and <code>max</code> are just two of the aggregation functions built into SQL. Three others are <code>avg</code>, <code>count</code>, and <code>sum</code>:</p>
<pre class="sourceCode sql"><code class="sourceCode sql"><span class="kw">SELECT</span> <span class="fu">avg</span>(reading) <span class="kw">FROM</span> Survey <span class="kw">WHERE</span> quant=<span class="ot">"sal"</span>;</code></pre>
<table>
<thead>
<tr class="header">
<th align="left">avg(reading)</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">7.20333333333333</td>
</tr>
</tbody>
</table>
<pre class="sourceCode sql"><code class="sourceCode sql"><span class="kw">SELECT</span> <span class="fu">count</span>(reading) <span class="kw">FROM</span> Survey <span class="kw">WHERE</span> quant=<span class="ot">"sal"</span>;</code></pre>
<table>
<thead>
<tr class="header">
<th align="left">count(reading)</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">9</td>
</tr>
</tbody>
</table>
<pre class="sourceCode sql"><code class="sourceCode sql"><span class="kw">SELECT</span> <span class="fu">sum</span>(reading) <span class="kw">FROM</span> Survey <span class="kw">WHERE</span> quant=<span class="ot">"sal"</span>;</code></pre>
<table>
<thead>
<tr class="header">
<th align="left">sum(reading)</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">64.83</td>
</tr>
</tbody>
</table>
<p>We used <code>count(reading)</code> here, but we could just as easily have counted <code>quant</code> or any other field in the table, or even used <code>count(*)</code>, since the function doesn't care about the values themselves, just how many values there are.</p>
<p>SQL lets us do several aggregations at once. We can, for example, find the range of sensible salinity measurements:</p>
<pre class="sourceCode sql"><code class="sourceCode sql"><span class="kw">SELECT</span> <span class="fu">min</span>(reading), <span class="fu">max</span>(reading) <span class="kw">FROM</span> Survey <span class="kw">WHERE</span> quant=<span class="ot">"sal"</span> <span class="kw">AND</span> reading<=<span class="fl">1.0</span>;</code></pre>
<table>
<thead>
<tr class="header">
<th align="left">min(reading)</th>
<th align="left">max(reading)</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">0.05</td>
<td align="left">0.21</td>
</tr>
</tbody>
</table>
<p>We can also combine aggregated results with raw results, although the output might surprise you:</p>
<pre class="sourceCode sql"><code class="sourceCode sql"><span class="kw">SELECT</span> person, <span class="fu">count</span>(*) <span class="kw">FROM</span> Survey <span class="kw">WHERE</span> quant=<span class="ot">"sal"</span> <span class="kw">AND</span> reading<=<span class="fl">1.0</span>;</code></pre>
<table>
<thead>
<tr class="header">
<th align="left">person</th>
<th align="left">count(*)</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">lake</td>
<td align="left">7</td>
</tr>
</tbody>
</table>
<p>Why does Lake's name appear rather than Roerich's or Dyer's? The answer is that when it has to aggregate a field, but isn't told how to, the database manager chooses an actual value from the input set. It might use the first one processed, the last one, or something else entirely.</p>
<p>Another important fact is that when there are no values to aggregate, aggregation's result is "don't know" rather than zero or some other arbitrary value:</p>
<pre class="sourceCode sql"><code class="sourceCode sql"><span class="kw">SELECT</span> person, <span class="fu">max</span>(reading), <span class="fu">sum</span>(reading) <span class="kw">FROM</span> Survey <span class="kw">WHERE</span> quant=<span class="ot">"missing"</span>;</code></pre>
<table>
<thead>
<tr class="header">
<th align="left">person</th>
<th align="left">max(reading)</th>
<th align="left">sum(reading)</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">-null-</td>
<td align="left">-null-</td>
<td align="left">-null-</td>
</tr>
</tbody>
</table>
<p>One final important feature of aggregation functions is that they are inconsistent with the rest of SQL in a very useful way. If we add two values, and one of them is null, the result is null. By extension, if we use <code>sum</code> to add all the values in a set, and any of those values are null, the result should also be null. It's much more useful, though, for aggregation functions to ignore null values and only combine those that are non-null. This behavior lets us write our queries as:</p>
<pre class="sourceCode sql"><code class="sourceCode sql"><span class="kw">SELECT</span> <span class="fu">min</span>(dated) <span class="kw">FROM</span> Visited;</code></pre>
<table>
<thead>
<tr class="header">
<th align="left">min(dated)</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">1927-02-08</td>
</tr>
</tbody>
</table>
<p>instead of always having to filter explicitly:</p>
<pre class="sourceCode sql"><code class="sourceCode sql"><span class="kw">SELECT</span> <span class="fu">min</span>(dated) <span class="kw">FROM</span> Visited <span class="kw">WHERE</span> dated <span class="kw">IS</span> <span class="kw">NOT</span> <span class="kw">NULL</span>;</code></pre>
<table>
<thead>
<tr class="header">
<th align="left">min(dated)</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">1927-02-08</td>
</tr>
</tbody>
</table>
<p>Aggregating all records at once doesn't always make sense. For example, suppose Gina suspects that there is a systematic bias in her data, and that some scientists' radiation readings are higher than others. We know that this doesn't work:</p>
<pre class="sourceCode sql"><code class="sourceCode sql"><span class="kw">SELECT</span> person, <span class="fu">count</span>(reading), <span class="fu">round</span>(<span class="fu">avg</span>(reading), <span class="dv">2</span>)
<span class="kw">FROM</span> Survey
<span class="kw">WHERE</span> quant=<span class="ot">"rad"</span>;</code></pre>
<table>
<thead>
<tr class="header">
<th align="left">person</th>
<th align="left">count(reading)</th>
<th align="left">round(avg(reading), 2)</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">roe</td>
<td align="left">8</td>
<td align="left">6.56</td>
</tr>
</tbody>
</table>
<p>because the database manager selects a single arbitrary scientist's name rather than aggregating separately for each scientist. Since there are only five scientists, she could write five queries of the form:</p>
<pre class="sourceCode sql"><code class="sourceCode sql"><span class="kw">SELECT</span> person, <span class="fu">count</span>(reading), <span class="fu">round</span>(<span class="fu">avg</span>(reading), <span class="dv">2</span>)
<span class="kw">FROM</span> Survey
<span class="kw">WHERE</span> quant=<span class="ot">"rad"</span>
<span class="kw">AND</span> person=<span class="ot">"dyer"</span>;</code></pre>
<table>
<thead>
<tr class="header">
<th align="left">person</th>
<th align="left">count(reading)</th>
<th align="left">round(avg(reading), 2)</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">dyer</td>
<td align="left">2</td>
<td align="left">8.81</td>
</tr>
</tbody>
</table>
<p>but this would be tedious, and if she ever had a data set with fifty or five hundred scientists, the chances of her getting all of those queries right is small.</p>
<p>What we need to do is tell the database manager to aggregate the hours for each scientist separately using a <code>GROUP BY</code> clause:</p>
<pre class="sourceCode sql"><code class="sourceCode sql"><span class="kw">SELECT</span> person, <span class="fu">count</span>(reading), <span class="fu">round</span>(<span class="fu">avg</span>(reading), <span class="dv">2</span>)
<span class="kw">FROM</span> Survey
<span class="kw">WHERE</span> quant=<span class="ot">"rad"</span>
<span class="kw">GROUP</span> <span class="kw">BY</span> person;</code></pre>
<table>
<thead>
<tr class="header">
<th align="left">person</th>
<th align="left">count(reading)</th>
<th align="left">round(avg(reading), 2)</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">dyer</td>
<td align="left">2</td>
<td align="left">8.81</td>
</tr>
<tr class="even">
<td align="left">lake</td>
<td align="left">2</td>
<td align="left">1.82</td>
</tr>
<tr class="odd">
<td align="left">pb</td>
<td align="left">3</td>
<td align="left">6.66</td>
</tr>
<tr class="even">
<td align="left">roe</td>
<td align="left">1</td>
<td align="left">11.25</td>
</tr>
</tbody>
</table>
<p><code>GROUP BY</code> does exactly what its name implies: groups all the records with the same value for the specified field together so that aggregation can process each batch separately. Since all the records in each batch have the same value for <code>person</code>, it no longer matters that the database manager is picking an arbitrary one to display alongside the aggregated <code>reading</code> values.</p>
<p>Just as we can sort by multiple criteria at once, we can also group by multiple criteria. To get the average reading by scientist and quantity measured, for example, we just add another field to the <code>GROUP BY</code> clause:</p>
<pre class="sourceCode sql"><code class="sourceCode sql"><span class="kw">SELECT</span> person, quant, <span class="fu">count</span>(reading), <span class="fu">round</span>(<span class="fu">avg</span>(reading), <span class="dv">2</span>)
<span class="kw">FROM</span> Survey
<span class="kw">GROUP</span> <span class="kw">BY</span> person, quant;</code></pre>
<table>
<thead>
<tr class="header">
<th align="left">person</th>
<th align="left">quant</th>
<th align="left">count(reading)</th>
<th align="left">round(avg(reading), 2)</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">-null-</td>
<td align="left">sal</td>
<td align="left">1</td>
<td align="left">0.06</td>
</tr>
<tr class="even">
<td align="left">-null-</td>
<td align="left">temp</td>
<td align="left">1</td>
<td align="left">-26.0</td>
</tr>
<tr class="odd">
<td align="left">dyer</td>
<td align="left">rad</td>
<td align="left">2</td>
<td align="left">8.81</td>
</tr>
<tr class="even">
<td align="left">dyer</td>
<td align="left">sal</td>
<td align="left">2</td>
<td align="left">0.11</td>
</tr>
<tr class="odd">
<td align="left">lake</td>
<td align="left">rad</td>
<td align="left">2</td>
<td align="left">1.82</td>
</tr>
<tr class="even">
<td align="left">lake</td>
<td align="left">sal</td>
<td align="left">4</td>
<td align="left">0.11</td>
</tr>
<tr class="odd">
<td align="left">lake</td>
<td align="left">temp</td>
<td align="left">1</td>
<td align="left">-16.0</td>
</tr>
<tr class="even">
<td align="left">pb</td>
<td align="left">rad</td>
<td align="left">3</td>
<td align="left">6.66</td>
</tr>
<tr class="odd">
<td align="left">pb</td>
<td align="left">temp</td>
<td align="left">2</td>
<td align="left">-20.0</td>
</tr>
<tr class="even">
<td align="left">roe</td>
<td align="left">rad</td>
<td align="left">1</td>
<td align="left">11.25</td>
</tr>
<tr class="odd">
<td align="left">roe</td>
<td align="left">sal</td>
<td align="left">2</td>
<td align="left">32.05</td>
</tr>
</tbody>
</table>
<p>Note that we have added <code>person</code> to the list of fields displayed, since the results wouldn't make much sense otherwise.</p>
<p>Let's go one step further and remove all the entries where we don't know who took the measurement:</p>
<pre class="sourceCode sql"><code class="sourceCode sql"><span class="kw">SELECT</span> person, quant, <span class="fu">count</span>(reading), <span class="fu">round</span>(<span class="fu">avg</span>(reading), <span class="dv">2</span>)
<span class="kw">FROM</span> Survey
<span class="kw">WHERE</span> person <span class="kw">IS</span> <span class="kw">NOT</span> <span class="kw">NULL</span>
<span class="kw">GROUP</span> <span class="kw">BY</span> person, quant
<span class="kw">ORDER</span> <span class="kw">BY</span> person, quant;</code></pre>
<table>
<thead>
<tr class="header">
<th align="left">person</th>
<th align="left">quant</th>
<th align="left">count(reading)</th>
<th align="left">round(avg(reading), 2)</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">dyer</td>
<td align="left">rad</td>
<td align="left">2</td>
<td align="left">8.81</td>
</tr>
<tr class="even">
<td align="left">dyer</td>
<td align="left">sal</td>
<td align="left">2</td>
<td align="left">0.11</td>
</tr>
<tr class="odd">
<td align="left">lake</td>
<td align="left">rad</td>
<td align="left">2</td>
<td align="left">1.82</td>
</tr>
<tr class="even">
<td align="left">lake</td>
<td align="left">sal</td>
<td align="left">4</td>
<td align="left">0.11</td>
</tr>
<tr class="odd">
<td align="left">lake</td>
<td align="left">temp</td>
<td align="left">1</td>
<td align="left">-16.0</td>
</tr>
<tr class="even">
<td align="left">pb</td>
<td align="left">rad</td>
<td align="left">3</td>
<td align="left">6.66</td>
</tr>
<tr class="odd">
<td align="left">pb</td>
<td align="left">temp</td>
<td align="left">2</td>
<td align="left">-20.0</td>
</tr>
<tr class="even">
<td align="left">roe</td>
<td align="left">rad</td>
<td align="left">1</td>
<td align="left">11.25</td>
</tr>
<tr class="odd">
<td align="left">roe</td>
<td align="left">sal</td>
<td align="left">2</td>
<td align="left">32.05</td>
</tr>
</tbody>
</table>
<p>Looking more closely, this query:</p>
<ol style="list-style-type: decimal">
<li><p>selected records from the <code>Survey</code> table where the <code>person</code> field was not null;</p></li>
<li><p>grouped those records into subsets so that the <code>person</code> and <code>quant</code> values in each subset were the same;</p></li>
<li><p>ordered those subsets first by <code>person</code>, and then within each sub-group by <code>quant</code>; and</p></li>
<li><p>counted the number of records in each subset, calculated the average <code>reading</code> in each, and chose a <code>person</code> and <code>quant</code> value from each (it doesn't matter which ones, since they're all equal).</p></li>
</ol>
<div id="counting-temperature-readings" class="challenge">
<h2>Counting Temperature Readings</h2>
<p>How many temperature readings did Frank Pabodie record, and what was their average value?</p>
</div>
<div id="averaging-with-null" class="challenge">
<h2>Averaging with NULL</h2>
<p>The average of a set of values is the sum of the values divided by the number of values. Does this mean that the <code>avg</code> function returns 2.0 or 3.0 when given the values 1.0, <code>null</code>, and 5.0?</p>
</div>
<div id="what-does-this-query-do" class="challenge">
<h2>What Does This Query Do?</h2>
<p>We want to calculate the difference between each individual radiation reading and the average of all the radiation readings. We write the query:</p>
<pre class="sourceCode sql"><code class="sourceCode sql"><span class="kw">SELECT</span> reading - <span class="fu">avg</span>(reading) <span class="kw">FROM</span> Survey <span class="kw">WHERE</span> quant=<span class="st">'rad'</span>;</code></pre>
<p>What does this actually produce, and why?</p>
</div>
<div id="ordering-when-concatenating" class="challenge">
<h2>Ordering When Concatenating</h2>
<p>The function <code>group_concat(field, separator)</code> concatenates all the values in a field using the specified separator character (or ',' if the separator isn't specified). Use this to produce a one-line list of scientists' names, such as:</p>
<pre class="sourceCode sql"><code class="sourceCode sql">William Dyer, Frank Pabodie, Anderson Lake, Valentina Roerich, Frank Danforth</code></pre>
<p>Can you find a way to order the list by surname?</p>
</div>
</div>
</div>
<div class="footer">
<a class="label swc-blue-bg" href="http://software-carpentry.org">Software Carpentry</a>
<a class="label swc-blue-bg" href="https://github.com/swcarpentry/sql-novice-survey">Source</a>
<a class="label swc-blue-bg" href="mailto:admin@software-carpentry.org">Contact</a>
<a class="label swc-blue-bg" href="LICENSE.html">License</a>
</div>
</div>
<!-- Javascript placed at the end of the document so the pages load faster -->
<script src="http://software-carpentry.org/v5/js/jquery-1.9.1.min.js"></script>
<script src="http://software-carpentry.org/v5/js/bootstrap/bootstrap.min.js"></script>
</body>
</html>