A function that partitions a result set and computes something within that partition without rolling up the data in that partition.

A window function partitions a row set into blocks based on some grouping but does not roll up into that grouping. A local calculation can be done within that group that will reset when the system processes the next group.

The SQL syntax for a window function takes the form:

   over (partition by foo,
         order by bar)

The order by clause is optional, but sorts rows within a partition. This can be useful for calculations that should reset across partitions. For example, one could use the windowing function to apply a row number for sorting within the partition or to calculate a running sum within that partition based a date ordering.

The partition by clause in the window function differs from the group by clause in SQL in that it does not actually aggregate the data in the partitions.

history | excerpt history