Opaleye.Window

runWindows runs a query composed of expressions containing window functions. runWindows is similar to Opaleye.aggregate, with the main difference being that in a window query, each input row corresponds to one output row, whereas aggregation queries fold the entire input query down into a single row per group. In Haskell terminology, Opaleye.aggregate is to foldl as runWindows is to scanl.

You can create Windows using over, and combine and manipulate them using the Applicative and Profunctor operations.

over applies a WindowFunction on a particular Window. For example,

over (aggregatorWindowFunction Opaleye.sum salary) (partitionBy department) (Opaleye.desc salary)

If you want to use a Window that consists of the entire SELECT then supply mempty for the Window a argument. If you don't want to order the Window then supply mempty for the Order a argument.

In PostgreSQL, window functions must specify the "window" over which they operate. The syntax for this looks like: SUM(salary) OVER (PARTITION BY department). The Opaleye type Window represents the segment consisting of the PARTIION BY.

You can create a Window using partitionBy and combine two Windows in a single one which combines the partition of both by using <>.

The window where each partition shares the same value for the given Field.

WindowFunction represents expressions that contain window functions. You can choose a WindowFunction from the options below, and combine and manipulate them using the Applicative and Profunctor operations.

A WindowFunction that doesn't actually contain any window function.

aggregatorWindowFunction allows the use of Aggregators in WindowFunctions. In particular, aggregatorWindowFunction Opaleye.sum gives a running total (when combined with an order argument to over).

row_number()

rank()

dense_rank()

percent_rank()

cume_dist()

ntile(num_buckets)

lag(value, offset, default)

lead(value, offset, default)

first_value(value)

last_value(value)

nth_value(value, n)