Opaleye.Window

Support for PostgreSQL window functions

Run window functions on a Select

runWindows :: Windows a b -> Select a -> Select b

runWindows runs a query composed of expressions containing window functions. runWindows is similar to 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, aggregate is to foldl as runWindows is to scanl.

Create Windows

data Windows a b

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

Instances

Profunctor Windows

Defined in Opaleye.Internal.Window

Methods

dimap :: (a -> b) -> (c -> d) -> Windows b c -> Windows a d

lmap :: (a -> b) -> Windows b c -> Windows a c

rmap :: (b -> c) -> Windows a b -> Windows a c

(#.) :: forall a b c q. Coercible c b => q b c -> Windows a b -> Windows a c

(.#) :: forall a b c q. Coercible b a => Windows b c -> q a b -> Windows a c

Applicative (Windows a)

Defined in Opaleye.Internal.Window

Methods

pure :: a0 -> Windows a a0

(<*>) :: Windows a (a0 -> b) -> Windows a a0 -> Windows a b

liftA2 :: (a0 -> b -> c) -> Windows a a0 -> Windows a b -> Windows a c

(*>) :: Windows a a0 -> Windows a b -> Windows a b

(<*) :: Windows a a0 -> Windows a b -> Windows a a0

Functor (Windows a)

Defined in Opaleye.Internal.Window

Methods

fmap :: (a0 -> b) -> Windows a a0 -> Windows a b

(<$) :: a0 -> Windows a b -> Windows a a0

over :: WindowFunction a b -> Window a -> Order a -> Windows a b

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

over (aggregatorWindowFunction sum salary) (partitionBy department) (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.

Create a Window

data Window a

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 <>.

Instances

Contravariant Window

Defined in Opaleye.Internal.Window

Methods

contramap :: (a' -> a) -> Window a -> Window a'

(>$) :: b -> Window b -> Window a

Monoid (Window a)

Defined in Opaleye.Internal.Window

Methods

mempty :: Window a

mappend :: Window a -> Window a -> Window a

mconcat :: [Window a] -> Window a

Semigroup (Window a)

Defined in Opaleye.Internal.Window

Methods

(<>) :: Window a -> Window a -> Window a

sconcat :: NonEmpty (Window a) -> Window a

stimes :: Integral b => b -> Window a -> Window a

partitionBy :: (a -> Field_ n b) -> Window a

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

Create a WindowFunction

data WindowFunction a b

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.

Instances

Profunctor WindowFunction

Defined in Opaleye.Internal.Window

Methods

dimap :: (a -> b) -> (c -> d) -> WindowFunction b c -> WindowFunction a d

lmap :: (a -> b) -> WindowFunction b c -> WindowFunction a c

rmap :: (b -> c) -> WindowFunction a b -> WindowFunction a c

(#.) :: forall a b c q. Coercible c b => q b c -> WindowFunction a b -> WindowFunction a c

(.#) :: forall a b c q. Coercible b a => WindowFunction b c -> q a b -> WindowFunction a c

Applicative (WindowFunction a)

Defined in Opaleye.Internal.Window

Methods

pure :: a0 -> WindowFunction a a0

(<*>) :: WindowFunction a (a0 -> b) -> WindowFunction a a0 -> WindowFunction a b

liftA2 :: (a0 -> b -> c) -> WindowFunction a a0 -> WindowFunction a b -> WindowFunction a c

(*>) :: WindowFunction a a0 -> WindowFunction a b -> WindowFunction a b

(<*) :: WindowFunction a a0 -> WindowFunction a b -> WindowFunction a a0

Functor (WindowFunction a)

Defined in Opaleye.Internal.Window

Methods

fmap :: (a0 -> b) -> WindowFunction a a0 -> WindowFunction a b

(<$) :: a0 -> WindowFunction a b -> WindowFunction a a0

Window functions

You might like to also refer to the Postgres documentation page that describes its window functions.

noWindowFunction :: (a -> b) -> WindowFunction a b

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

aggregatorWindowFunction :: Aggregator a b -> (a' -> a) -> WindowFunction a' b

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

rowNumber :: WindowFunction a (Field SqlInt8)

row_number()

rank :: WindowFunction a (Field SqlInt8)

rank()

denseRank :: WindowFunction a (Field SqlInt8)

dense_rank()

percentRank :: WindowFunction a (Field SqlFloat8)

percent_rank()

cumeDist :: WindowFunction a (Field SqlFloat8)

cume_dist()

ntile

Arguments

:: Field SqlInt4

num_buckets

-> WindowFunction a (Field SqlInt4)

ntile(num_buckets)

lag

Arguments

:: Field SqlInt4

offset

-> Field_ n a

default

-> WindowFunction (Field_ n a) (Field_ n a)

lag(value, offset, default)

lead

Arguments

:: Field SqlInt4

offset

-> Field_ n a

default

-> WindowFunction (Field_ n a) (Field_ n a)

lead(value, offset, default)

firstValue :: WindowFunction (Field_ n a) (Field_ n a)

first_value(value)

lastValue :: WindowFunction (Field_ n a) (Field_ n a)

last_value(value)

nthValue

Arguments

:: Field SqlInt4

n

-> WindowFunction (Field_ n a) (FieldNullable a)

nth_value(value, n)