Opaleye.Window

Window queries

window :: Select (Window a) -> Select a

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.

data Window a

Window is an applicative functor that represents expressions that contain window functions. window can be used to evaluate these expressions over a particular query.

Instances

Functor Window

Defined in Opaleye.Internal.Window

Methods

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

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

Applicative Window

Defined in Opaleye.Internal.Window

Methods

pure :: a -> Window a

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

liftA2 :: (a -> b -> c) -> Window a -> Window b -> Window c

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

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

over :: Window a -> Partition -> Window a infixl 1

over adds a Partition to a Window expression.

cumulative sum salary `over` partitionBy department <> orderPartitionBy salary (desc id)

data Partition

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 Partitions created with partitionBy and orderPartitionBy can be combined using <>.

Instances

Semigroup Partition

Defined in Opaleye.Internal.Window

Methods

(<>) :: Partition -> Partition -> Partition

sconcat :: NonEmpty Partition -> Partition

stimes :: Integral b => b -> Partition -> Partition

Monoid Partition

Defined in Opaleye.Internal.Window

Methods

mempty :: Partition

mappend :: Partition -> Partition -> Partition

mconcat :: [Partition] -> Partition

partitionBy :: Field_ n a -> Partition

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

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

cumulative allows the use of aggregation functions in Window expressions. In particular, cumulative sum (when combined with orderPartitionBy) gives a running total, also known as a "cumulative sum", hence the name cumulative.

rowNumber :: Window (Field SqlInt8)

row_number()

rank :: Window (Field SqlInt8)

rank()

denseRank :: Window (Field SqlInt8)

dense_rank()

percentRank :: Window (Field SqlFloat8)

percent_rank()

cumeDist :: Window (Field SqlFloat8)

cume_dist()

ntile :: Field SqlInt4 -> Window (Field SqlInt4)

ntile(num_buckets)

lag :: Field_ n a -> Field SqlInt4 -> Field_ n a -> Window (Field_ n a)

lag(value, offset, default)

lead :: Field_ n a -> Field SqlInt4 -> Field_ n a -> Window (Field_ n a)

lead(value, offset, default)

firstValue :: Field_ n a -> Window (Field_ n a)

first_value(value)

lastValue :: Field_ n a -> Window (Field_ n a)

last_value(value)

nthValue :: Field_ n a -> Field SqlInt4 -> Window (FieldNullable a)

nth_value(value, n)