Safe Haskell | None |
---|---|
Language | Haskell2010 |
Synopsis
- window :: Select (Window a) -> Select a
- data Window a
- over :: Window a -> Partition -> Window a
- data Partition
- partitionBy :: Field_ n a -> Partition
- orderPartitionBy :: a -> Order a -> Partition
- cumulative :: Aggregator a b -> a -> Window b
- rowNumber :: Window (Field SqlInt8)
- rank :: Window (Field SqlInt8)
- denseRank :: Window (Field SqlInt8)
- percentRank :: Window (Field SqlFloat8)
- cumeDist :: Window (Field SqlFloat8)
- ntile :: Field SqlInt4 -> Window (Field SqlInt4)
- lag :: Field_ n a -> Field SqlInt4 -> Field_ n a -> Window (Field_ n a)
- lead :: Field_ n a -> Field SqlInt4 -> Field_ n a -> Window (Field_ n a)
- firstValue :: Field_ n a -> Window (Field_ n a)
- lastValue :: Field_ n a -> Window (Field_ n a)
- nthValue :: Field_ n a -> Field SqlInt4 -> Window (FieldNullable a)
Window queries
window :: Select (Window a) -> Select a Source #
window
runs a query composed of expressions containing
window functions.
window
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. To put this into a Haskell context, aggregate
is to foldl
as window
is to scanl
.
Window
is an applicative functor that represents expressions that
contain
window functions.
window
can be used to evaluate these expressions over a particular query.
over :: Window a -> Partition -> Window a infixl 1 Source #
over
adds a Partition
to a Window
expression.
cumulative
sum
salary `over
`partitionBy
department <>orderPartitionBy
salary (desc
id)
In PostgreSQL, window functions must specify the "window" or
"partition" over which they operate. The syntax for this looks like:
SUM(salary) OVER (PARTITION BY department)
. The Opaleye type Partition
represents everything that comes after OVER
.
Partition
is a Monoid
, so Partition
s created with partitionBy
and
orderPartitionBy
can be combined using <>
.
partitionBy :: Field_ n a -> Partition Source #
Restricts a window function to operate only the group of rows that share the same value(s) for the given expression(s).
orderPartitionBy :: a -> Order a -> Partition Source #
Controls the order in which rows are processed by window functions. This does not need to match the ordering of the overall query.
Specific window functions
cumulative :: Aggregator a b -> a -> Window b Source #
cumulative
allows the use of aggregation functions in Window
expressions. In particular,
(when combined with cumulative
sum
orderPartitionBy
) gives a running total,
also known as a "cumulative sum", hence the name cumulative
.