Safe Haskell | Safe-Inferred |
---|---|
Language | Haskell2010 |
Support for PostgreSQL window functions
Synopsis
- runWindows :: Windows a b -> Select a -> Select b
- data Windows a b
- over :: WindowFunction a b -> Window a -> Order a -> Windows a b
- data Window a
- partitionBy :: (a -> Field_ n b) -> Window a
- data WindowFunction a b
- noWindowFunction :: (a -> b) -> WindowFunction a b
- aggregatorWindowFunction :: Aggregator a b -> (a' -> a) -> WindowFunction a' b
- rowNumber :: WindowFunction a (Field SqlInt8)
- rank :: WindowFunction a (Field SqlInt8)
- denseRank :: WindowFunction a (Field SqlInt8)
- percentRank :: WindowFunction a (Field SqlFloat8)
- cumeDist :: WindowFunction a (Field SqlFloat8)
- ntile :: Field SqlInt4 -> WindowFunction a (Field SqlInt4)
- lag :: Field SqlInt4 -> Field_ n a -> WindowFunction (Field_ n a) (Field_ n a)
- lead :: Field SqlInt4 -> Field_ n a -> WindowFunction (Field_ n a) (Field_ n a)
- firstValue :: WindowFunction (Field_ n a) (Field_ n a)
- lastValue :: WindowFunction (Field_ n a) (Field_ n a)
- nthValue :: Field SqlInt4 -> WindowFunction (Field_ n a) (FieldNullable a)
Run window functions on a Select
runWindows :: Windows a b -> Select a -> Select b Source #
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
You can create Windows
using over
, and combine and manipulate
them using the Applicative
and Profunctor
operations.
Instances
Profunctor Windows Source # | |
Defined in Opaleye.Internal.Window 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) Source # | |
Defined in Opaleye.Internal.Window | |
Functor (Windows a) Source # | |
over :: WindowFunction a b -> Window a -> Order a -> Windows a b Source #
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
argument. If you don't
want to order the Window
aWindow
then supply mempty
for the
argument.Order
a
Create a Window
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 <>
.
partitionBy :: (a -> Field_ n b) -> Window a Source #
The window where each partition shares the same value for the
given Field
.
Create a WindowFunction
data WindowFunction a b Source #
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
Window functions
You might like to also refer to the Postgres documentation page that describes its window functions.
noWindowFunction :: (a -> b) -> WindowFunction a b Source #
A WindowFunction
that doesn't actually contain any window
function.
aggregatorWindowFunction :: Aggregator a b -> (a' -> a) -> WindowFunction a' b Source #
aggregatorWindowFunction
allows the use of Aggregator
s in
WindowFunction
s. In particular,
gives a running total (when combined with an order
argument to aggregatorWindowFunction
sum
over
).
firstValue :: WindowFunction (Field_ n a) (Field_ n a) Source #
:: Field SqlInt4 | n |
-> WindowFunction (Field_ n a) (FieldNullable a) |