← Back to branch summary

~ubuntu-branches/ubuntu/oneiric/postgresql-9.1/oneiric-security

« back to all changes in this revision

Viewing changes to doc/src/sgml/html/tutorial-window.html

  • Committer: Bazaar Package Importer
  • Author(s): Martin Pitt
  • Date: 2011-05-11 10:41:53 UTC
  • Revision ID: james.westby@ubuntu.com-20110511104153-psbh2o58553fv1m0
Tags: upstream-9.1~beta1
Import upstream version 9.1~beta1

Show diffs side-by-side

added added

removed removed

Lines of Context:
doc/src/sgml/html/tutorial-window.html
 
1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
 
2
<HTML
 
3
><HEAD
 
4
><TITLE
 
5
>Window Functions</TITLE
 
6
><META
 
7
NAME="GENERATOR"
 
8
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
 
9
REV="MADE"
 
10
HREF="mailto:pgsql-docs@postgresql.org"><LINK
 
11
REL="HOME"
 
12
TITLE="PostgreSQL 9.1beta1 Documentation"
 
13
HREF="index.html"><LINK
 
14
REL="UP"
 
15
TITLE="Advanced Features"
 
16
HREF="tutorial-advanced.html"><LINK
 
17
REL="PREVIOUS"
 
18
TITLE="Transactions"
 
19
HREF="tutorial-transactions.html"><LINK
 
20
REL="NEXT"
 
21
TITLE="Inheritance"
 
22
HREF="tutorial-inheritance.html"><LINK
 
23
REL="STYLESHEET"
 
24
TYPE="text/css"
 
25
HREF="stylesheet.css"><META
 
26
HTTP-EQUIV="Content-Type"
 
27
CONTENT="text/html; charset=ISO-8859-1"><META
 
28
NAME="creation"
 
29
CONTENT="2011-04-27T21:20:33"></HEAD
 
30
><BODY
 
31
CLASS="SECT1"
 
32
><DIV
 
33
CLASS="NAVHEADER"
 
34
><TABLE
 
35
SUMMARY="Header navigation table"
 
36
WIDTH="100%"
 
37
BORDER="0"
 
38
CELLPADDING="0"
 
39
CELLSPACING="0"
 
40
><TR
 
41
><TH
 
42
COLSPAN="5"
 
43
ALIGN="center"
 
44
VALIGN="bottom"
 
45
><A
 
46
HREF="index.html"
 
47
>PostgreSQL 9.1beta1 Documentation</A
 
48
></TH
 
49
></TR
 
50
><TR
 
51
><TD
 
52
WIDTH="10%"
 
53
ALIGN="left"
 
54
VALIGN="top"
 
55
><A
 
56
TITLE="Transactions"
 
57
HREF="tutorial-transactions.html"
 
58
ACCESSKEY="P"
 
59
>Prev</A
 
60
></TD
 
61
><TD
 
62
WIDTH="10%"
 
63
ALIGN="left"
 
64
VALIGN="top"
 
65
><A
 
66
TITLE="Advanced Features"
 
67
HREF="tutorial-advanced.html"
 
68
>Fast Backward</A
 
69
></TD
 
70
><TD
 
71
WIDTH="60%"
 
72
ALIGN="center"
 
73
VALIGN="bottom"
 
74
>Chapter 3. Advanced Features</TD
 
75
><TD
 
76
WIDTH="10%"
 
77
ALIGN="right"
 
78
VALIGN="top"
 
79
><A
 
80
TITLE="Advanced Features"
 
81
HREF="tutorial-advanced.html"
 
82
>Fast Forward</A
 
83
></TD
 
84
><TD
 
85
WIDTH="10%"
 
86
ALIGN="right"
 
87
VALIGN="top"
 
88
><A
 
89
TITLE="Inheritance"
 
90
HREF="tutorial-inheritance.html"
 
91
ACCESSKEY="N"
 
92
>Next</A
 
93
></TD
 
94
></TR
 
95
></TABLE
 
96
><HR
 
97
ALIGN="LEFT"
 
98
WIDTH="100%"></DIV
 
99
><DIV
 
100
CLASS="SECT1"
 
101
><H1
 
102
CLASS="SECT1"
 
103
><A
 
104
NAME="TUTORIAL-WINDOW"
 
105
>3.5. Window Functions</A
 
106
></H1
 
107
><P
 
108
>    A <I
 
109
CLASS="FIRSTTERM"
 
110
>window function</I
 
111
> performs a calculation across a set of
 
112
    table rows that are somehow related to the current row.  This is comparable
 
113
    to the type of calculation that can be done with an aggregate function.
 
114
    But unlike regular aggregate functions, use of a window function does not
 
115
    cause rows to become grouped into a single output row &mdash; the
 
116
    rows retain their separate identities.  Behind the scenes, the window
 
117
    function is able to access more than just the current row of the query
 
118
    result.
 
119
   </P
 
120
><P
 
121
>    Here is an example that shows how to compare each employee's salary
 
122
    with the average salary in his or her department:
 
123
 
 
124
</P><PRE
 
125
CLASS="PROGRAMLISTING"
 
126
>SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;</PRE
 
127
><P>
 
128
 
 
129
</P><PRE
 
130
CLASS="SCREEN"
 
131
>  depname  | empno | salary |          avg          
 
132
-----------+-------+--------+-----------------------
 
133
 develop   |    11 |   5200 | 5020.0000000000000000
 
134
 develop   |     7 |   4200 | 5020.0000000000000000
 
135
 develop   |     9 |   4500 | 5020.0000000000000000
 
136
 develop   |     8 |   6000 | 5020.0000000000000000
 
137
 develop   |    10 |   5200 | 5020.0000000000000000
 
138
 personnel |     5 |   3500 | 3700.0000000000000000
 
139
 personnel |     2 |   3900 | 3700.0000000000000000
 
140
 sales     |     3 |   4800 | 4866.6666666666666667
 
141
 sales     |     1 |   5000 | 4866.6666666666666667
 
142
 sales     |     4 |   4800 | 4866.6666666666666667
 
143
(10 rows)</PRE
 
144
><P>
 
145
 
 
146
    The first three output columns come directly from the table
 
147
    <TT
 
148
CLASS="STRUCTNAME"
 
149
>empsalary</TT
 
150
>, and there is one output row for each row in the
 
151
    table.  The fourth column represents an average taken across all the table
 
152
    rows that have the same <TT
 
153
CLASS="STRUCTFIELD"
 
154
>depname</TT
 
155
> value as the current row.
 
156
    (This actually is the same function as the regular <CODE
 
157
CLASS="FUNCTION"
 
158
>avg</CODE
 
159
>
 
160
    aggregate function, but the <TT
 
161
CLASS="LITERAL"
 
162
>OVER</TT
 
163
> clause causes it to be
 
164
    treated as a window function and computed across an appropriate set of
 
165
    rows.)
 
166
   </P
 
167
><P
 
168
>    A window function call always contains an <TT
 
169
CLASS="LITERAL"
 
170
>OVER</TT
 
171
> clause
 
172
    directly following the window function's name and argument(s).  This is what
 
173
    syntactically distinguishes it from a regular function or aggregate
 
174
    function.  The <TT
 
175
CLASS="LITERAL"
 
176
>OVER</TT
 
177
> clause determines exactly how the
 
178
    rows of the query are split up for processing by the window function.
 
179
    The <TT
 
180
CLASS="LITERAL"
 
181
>PARTITION BY</TT
 
182
> list within <TT
 
183
CLASS="LITERAL"
 
184
>OVER</TT
 
185
> specifies
 
186
    dividing the rows into groups, or partitions, that share the same
 
187
    values of the <TT
 
188
CLASS="LITERAL"
 
189
>PARTITION BY</TT
 
190
> expression(s).  For each row,
 
191
    the window function is computed across the rows that fall into the
 
192
    same partition as the current row.
 
193
   </P
 
194
><P
 
195
>    You can also control the order in which rows are processed by
 
196
    window functions using <TT
 
197
CLASS="LITERAL"
 
198
>ORDER BY</TT
 
199
> within <TT
 
200
CLASS="LITERAL"
 
201
>OVER</TT
 
202
>.
 
203
    (The window <TT
 
204
CLASS="LITERAL"
 
205
>ORDER BY</TT
 
206
> does not even have to match the
 
207
    order in which the rows are output.)  Here is an example:
 
208
 
 
209
</P><PRE
 
210
CLASS="PROGRAMLISTING"
 
211
>SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary DESC) FROM empsalary;</PRE
 
212
><P>
 
213
 
 
214
</P><PRE
 
215
CLASS="SCREEN"
 
216
>  depname  | empno | salary | rank 
 
217
-----------+-------+--------+------
 
218
 develop   |     8 |   6000 |    1
 
219
 develop   |    10 |   5200 |    2
 
220
 develop   |    11 |   5200 |    2
 
221
 develop   |     9 |   4500 |    4
 
222
 develop   |     7 |   4200 |    5
 
223
 personnel |     2 |   3900 |    1
 
224
 personnel |     5 |   3500 |    2
 
225
 sales     |     1 |   5000 |    1
 
226
 sales     |     4 |   4800 |    2
 
227
 sales     |     3 |   4800 |    2
 
228
(10 rows)</PRE
 
229
><P>
 
230
 
 
231
    As shown here, the <CODE
 
232
CLASS="FUNCTION"
 
233
>rank</CODE
 
234
> function produces a numerical rank
 
235
    within the current row's partition for each distinct <TT
 
236
CLASS="LITERAL"
 
237
>ORDER BY</TT
 
238
>
 
239
    value, in the order defined by the <TT
 
240
CLASS="LITERAL"
 
241
>ORDER BY</TT
 
242
> clause.
 
243
    <CODE
 
244
CLASS="FUNCTION"
 
245
>rank</CODE
 
246
> needs no explicit parameter, because its behavior
 
247
    is entirely determined by the <TT
 
248
CLASS="LITERAL"
 
249
>OVER</TT
 
250
> clause.
 
251
   </P
 
252
><P
 
253
>    The rows considered by a window function are those of the <SPAN
 
254
CLASS="QUOTE"
 
255
>"virtual
 
256
    table"</SPAN
 
257
> produced by the query's <TT
 
258
CLASS="LITERAL"
 
259
>FROM</TT
 
260
> clause as filtered by its
 
261
    <TT
 
262
CLASS="LITERAL"
 
263
>WHERE</TT
 
264
>, <TT
 
265
CLASS="LITERAL"
 
266
>GROUP BY</TT
 
267
>, and <TT
 
268
CLASS="LITERAL"
 
269
>HAVING</TT
 
270
> clauses
 
271
    if any.  For example, a row removed because it does not meet the
 
272
    <TT
 
273
CLASS="LITERAL"
 
274
>WHERE</TT
 
275
> condition is not seen by any window function.
 
276
    A query can contain multiple window functions that slice up the data
 
277
    in different ways by means of different <TT
 
278
CLASS="LITERAL"
 
279
>OVER</TT
 
280
> clauses, but
 
281
    they all act on the same collection of rows defined by this virtual table.
 
282
   </P
 
283
><P
 
284
>    We already saw that <TT
 
285
CLASS="LITERAL"
 
286
>ORDER BY</TT
 
287
> can be omitted if the ordering
 
288
    of rows is not important.  It is also possible to omit <TT
 
289
CLASS="LITERAL"
 
290
>PARTITION
 
291
    BY</TT
 
292
>, in which case there is just one partition containing all the rows.
 
293
   </P
 
294
><P
 
295
>    There is another important concept associated with window functions:
 
296
    for each row, there is a set of rows within its partition called its
 
297
    <I
 
298
CLASS="FIRSTTERM"
 
299
>window frame</I
 
300
>.  Many (but not all) window functions act only
 
301
    on the rows of the window frame, rather than of the whole partition.
 
302
    By default, if <TT
 
303
CLASS="LITERAL"
 
304
>ORDER BY</TT
 
305
> is supplied then the frame consists of
 
306
    all rows from the start of the partition up through the current row, plus
 
307
    any following rows that are equal to the current row according to the
 
308
    <TT
 
309
CLASS="LITERAL"
 
310
>ORDER BY</TT
 
311
> clause.  When <TT
 
312
CLASS="LITERAL"
 
313
>ORDER BY</TT
 
314
> is omitted the
 
315
    default frame consists of all rows in the partition.
 
316
     <A
 
317
NAME="AEN1050"
 
318
HREF="#FTN.AEN1050"
 
319
><SPAN
 
320
CLASS="footnote"
 
321
>[1]</SPAN
 
322
></A
 
323
>
 
324
    Here is an example using <CODE
 
325
CLASS="FUNCTION"
 
326
>sum</CODE
 
327
>:
 
328
   </P
 
329
><PRE
 
330
CLASS="PROGRAMLISTING"
 
331
>SELECT salary, sum(salary) OVER () FROM empsalary;</PRE
 
332
><PRE
 
333
CLASS="SCREEN"
 
334
> salary |  sum  
 
335
--------+-------
 
336
   5200 | 47100
 
337
   5000 | 47100
 
338
   3500 | 47100
 
339
   4800 | 47100
 
340
   3900 | 47100
 
341
   4200 | 47100
 
342
   4500 | 47100
 
343
   4800 | 47100
 
344
   6000 | 47100
 
345
   5200 | 47100
 
346
(10 rows)</PRE
 
347
><P
 
348
>    Above, since there is no <TT
 
349
CLASS="LITERAL"
 
350
>ORDER BY</TT
 
351
> in the <TT
 
352
CLASS="LITERAL"
 
353
>OVER</TT
 
354
>
 
355
    clause, the window frame is the same as the partition, which for lack of
 
356
    <TT
 
357
CLASS="LITERAL"
 
358
>PARTITION BY</TT
 
359
> is the whole table; in other words each sum is
 
360
    taken over the whole table and so we get the same result for each output
 
361
    row.  But if we add an <TT
 
362
CLASS="LITERAL"
 
363
>ORDER BY</TT
 
364
> clause, we get very different
 
365
    results:
 
366
   </P
 
367
><PRE
 
368
CLASS="PROGRAMLISTING"
 
369
>SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;</PRE
 
370
><PRE
 
371
CLASS="SCREEN"
 
372
> salary |  sum  
 
373
--------+-------
 
374
   3500 |  3500
 
375
   3900 |  7400
 
376
   4200 | 11600
 
377
   4500 | 16100
 
378
   4800 | 25700
 
379
   4800 | 25700
 
380
   5000 | 30700
 
381
   5200 | 41100
 
382
   5200 | 41100
 
383
   6000 | 47100
 
384
(10 rows)</PRE
 
385
><P
 
386
>    Here the sum is taken from the first (lowest) salary up through the
 
387
    current one, including any duplicates of the current one (notice the
 
388
    results for the duplicated salaries).
 
389
   </P
 
390
><P
 
391
>    Window functions are permitted only in the <TT
 
392
CLASS="LITERAL"
 
393
>SELECT</TT
 
394
> list
 
395
    and the <TT
 
396
CLASS="LITERAL"
 
397
>ORDER BY</TT
 
398
> clause of the query. They are forbidden
 
399
    elsewhere, such as in <TT
 
400
CLASS="LITERAL"
 
401
>GROUP BY</TT
 
402
>, <TT
 
403
CLASS="LITERAL"
 
404
>HAVING</TT
 
405
>
 
406
    and <TT
 
407
CLASS="LITERAL"
 
408
>WHERE</TT
 
409
> clauses.  This is because they logically
 
410
    execute after the processing of those clauses.  Also, window functions
 
411
    execute after regular aggregate functions.  This means it is valid to
 
412
    include an aggregate function call in the arguments of a window function,
 
413
    but not vice versa.
 
414
   </P
 
415
><P
 
416
>    If there is a need to filter or group rows after the window calculations
 
417
    are performed, you can use a sub-select.  For example:
 
418
 
 
419
</P><PRE
 
420
CLASS="PROGRAMLISTING"
 
421
>SELECT depname, empno, salary, enroll_date
 
422
FROM
 
423
  (SELECT depname, empno, salary, enroll_date,
 
424
          rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos
 
425
     FROM empsalary
 
426
  ) AS ss
 
427
WHERE pos &lt; 3;</PRE
 
428
><P>
 
429
 
 
430
    The above query only shows the rows from the inner query having
 
431
    <TT
 
432
CLASS="LITERAL"
 
433
>rank</TT
 
434
> less than 3.
 
435
   </P
 
436
><P
 
437
>    When a query involves multiple window functions, it is possible to write
 
438
    out each one with a separate <TT
 
439
CLASS="LITERAL"
 
440
>OVER</TT
 
441
> clause, but this is
 
442
    duplicative and error-prone if the same windowing behavior is wanted
 
443
    for several functions.  Instead, each windowing behavior can be named
 
444
    in a <TT
 
445
CLASS="LITERAL"
 
446
>WINDOW</TT
 
447
> clause and then referenced in <TT
 
448
CLASS="LITERAL"
 
449
>OVER</TT
 
450
>.
 
451
    For example:
 
452
 
 
453
</P><PRE
 
454
CLASS="PROGRAMLISTING"
 
455
>SELECT sum(salary) OVER w, avg(salary) OVER w
 
456
  FROM empsalary
 
457
  WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);</PRE
 
458
><P>
 
459
   </P
 
460
><P
 
461
>    More details about window functions can be found in
 
462
    <A
 
463
HREF="sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS"
 
464
>Section 4.2.8</A
 
465
>,
 
466
    <A
 
467
HREF="functions-window.html"
 
468
>Section 9.19</A
 
469
>,
 
470
    <A
 
471
HREF="queries-table-expressions.html#QUERIES-WINDOW"
 
472
>Section 7.2.4</A
 
473
>, and the
 
474
    <A
 
475
HREF="sql-select.html"
 
476
>SELECT</A
 
477
> reference page.
 
478
   </P
 
479
></DIV
 
480
><H3
 
481
CLASS="FOOTNOTES"
 
482
>Notes</H3
 
483
><TABLE
 
484
BORDER="0"
 
485
CLASS="FOOTNOTES"
 
486
WIDTH="100%"
 
487
><TR
 
488
><TD
 
489
ALIGN="LEFT"
 
490
VALIGN="TOP"
 
491
WIDTH="5%"
 
492
><A
 
493
NAME="FTN.AEN1050"
 
494
HREF="tutorial-window.html#AEN1050"
 
495
><SPAN
 
496
CLASS="footnote"
 
497
>[1]</SPAN
 
498
></A
 
499
></TD
 
500
><TD
 
501
ALIGN="LEFT"
 
502
VALIGN="TOP"
 
503
WIDTH="95%"
 
504
><P
 
505
>       There are options to define the window frame in other ways, but
 
506
       this tutorial does not cover them.  See
 
507
       <A
 
508
HREF="sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS"
 
509
>Section 4.2.8</A
 
510
> for details.
 
511
      </P
 
512
></TD
 
513
></TR
 
514
></TABLE
 
515
><DIV
 
516
CLASS="NAVFOOTER"
 
517
><HR
 
518
ALIGN="LEFT"
 
519
WIDTH="100%"><TABLE
 
520
SUMMARY="Footer navigation table"
 
521
WIDTH="100%"
 
522
BORDER="0"
 
523
CELLPADDING="0"
 
524
CELLSPACING="0"
 
525
><TR
 
526
><TD
 
527
WIDTH="33%"
 
528
ALIGN="left"
 
529
VALIGN="top"
 
530
><A
 
531
HREF="tutorial-transactions.html"
 
532
ACCESSKEY="P"
 
533
>Prev</A
 
534
></TD
 
535
><TD
 
536
WIDTH="34%"
 
537
ALIGN="center"
 
538
VALIGN="top"
 
539
><A
 
540
HREF="index.html"
 
541
ACCESSKEY="H"
 
542
>Home</A
 
543
></TD
 
544
><TD
 
545
WIDTH="33%"
 
546
ALIGN="right"
 
547
VALIGN="top"
 
548
><A
 
549
HREF="tutorial-inheritance.html"
 
550
ACCESSKEY="N"
 
551
>Next</A
 
552
></TD
 
553
></TR
 
554
><TR
 
555
><TD
 
556
WIDTH="33%"
 
557
ALIGN="left"
 
558
VALIGN="top"
 
559
>Transactions</TD
 
560
><TD
 
561
WIDTH="34%"
 
562
ALIGN="center"
 
563
VALIGN="top"
 
564
><A
 
565
HREF="tutorial-advanced.html"
 
566
ACCESSKEY="U"
 
567
>Up</A
 
568
></TD
 
569
><TD
 
570
WIDTH="33%"
 
571
ALIGN="right"
 
572
VALIGN="top"
 
573
>Inheritance</TD
 
574
></TR
 
575
></TABLE
 
576
></DIV
 
577
></BODY
 
578
></HTML
 
579
>
 
 
b'\\ No newline at end of file'