Opaleye.Aggregate

Perform aggregation on Selects. To aggregate a Select you should construct an Aggregator encoding how you want the aggregation to proceed, then call aggregate on it. The Aggregator should be constructed from the basic Aggregators below by using the combining operations from Data.Profunctor.Product.

Aggregation

aggregate :: Default Unpackspec a a => Aggregator a b -> Select a -> Select b

Given a Select producing rows of type a and an Aggregator accepting rows of type a, apply the aggregator to the select.

If you simply want to count the number of rows in a query you might find the countRows function more convenient.

If you want to use aggregate with SelectArrs then you should compose it with laterally:

laterally . aggregate :: Aggregator a b -> SelectArr a b -> SelectArr a b

Please note that when aggregating an empty query with no GROUP BY clause, Opaleye's behaviour differs from Postgres's behaviour. Postgres returns a single row whereas Opaleye returns zero rows. Opaleye's behaviour is consistent with the meaning of aggregating over groups of rows and Postgres's behaviour is inconsistent. When a query has zero rows it has zero groups, and thus zero rows in the result of an aggregation.

aggregateOrdered :: Default Unpackspec a a => Order a -> Aggregator a b -> Select a -> Select b

distinctAggregator :: Aggregator a b -> Aggregator a b

Aggregate only distinct values

filterWhere :: (a -> Field SqlBool) -> Aggregator a b -> Aggregator a (MaybeFields b)

Aggregate only rows matching the given predicate

data Aggregator a b

An Aggregator takes a collection of rows of type a, groups them, and transforms each group into a single row of type b. This corresponds to aggregators using GROUP BY in SQL.

You should combine basic Aggregators into Aggregators on compound types by using the operations in Data.Profunctor.Product.

An Aggregator corresponds closely to a Fold from the foldl package. Whereas an Aggregator a b takes each group of type a to a single row of type b, a Fold a b takes a list of a and returns a single value of type b.

Instances

ProductProfunctor Aggregator

Defined in Opaleye.Internal.Aggregate

Methods

purePP :: b -> Aggregator a b

(****) :: Aggregator a (b -> c) -> Aggregator a b -> Aggregator a c

empty :: Aggregator () ()

(***!) :: Aggregator a b -> Aggregator a' b' -> Aggregator (a, a') (b, b')

SumProfunctor Aggregator

Defined in Opaleye.Internal.Aggregate

Methods

(+++!) :: Aggregator a b -> Aggregator a' b' -> Aggregator (Either a a') (Either b b')

Profunctor Aggregator

Defined in Opaleye.Internal.Aggregate

Methods

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

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

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

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

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

Applicative (Aggregator a)

Defined in Opaleye.Internal.Aggregate

Methods

pure :: a0 -> Aggregator a a0

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

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

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

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

Functor (Aggregator a)

Defined in Opaleye.Internal.Aggregate

Methods

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

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

Basic Aggregators

groupBy :: Aggregator (Field_ n a) (Field_ n a)

Group the aggregation by equality on the input to groupBy.

sum :: Aggregator (Field a) (Field a)

Sum all rows in a group.

WARNING! The type of this operation is wrong and will crash at runtime when the argument is SqlInt4 or SqlInt8. For those use sumInt4 or sumInt8 instead.

sumInt4 :: Aggregator (Field SqlInt4) (Field SqlInt8)

sumInt8 :: Aggregator (Field SqlInt8) (Field SqlNumeric)

count :: Aggregator (Field a) (Field SqlInt8)

Count the number of non-null rows in a group.

countStar :: Aggregator a (Field SqlInt8)

Count the number of rows in a group. This Aggregator is named countStar after SQL's COUNT(*) aggregation function.

avg :: Aggregator (Field SqlFloat8) (Field SqlFloat8)

Average of a group

max :: SqlOrd a => Aggregator (Field a) (Field a)

Maximum of a group

min :: SqlOrd a => Aggregator (Field a) (Field a)

Maximum of a group

boolOr :: Aggregator (Field SqlBool) (Field SqlBool)

boolAnd :: Aggregator (Field SqlBool) (Field SqlBool)

arrayAgg :: Aggregator (Field a) (Field (SqlArray a))

arrayAgg_ :: Aggregator (Field_ n a) (Field (SqlArray_ n a))

jsonAgg :: Aggregator (Field a) (Field SqlJson)

Aggregates values as a JSON array. An example usage:

import qualified Opaleye as O

O.aggregate O.jsonAgg $ do
    (firstCol, secondCol) <- O.selectTable table6
    return
      . O.jsonBuildObject
      $ O.jsonBuildObjectField "summary" firstCol
        <> O.jsonBuildObjectField "details" secondCol

The above query, when executed, will return JSON of the following form from postgres:

"[{\"summary\" : \"xy\", \"details\" : \"a\"}, {\"summary\" : \"z\", \"details\" : \"a\"}, {\"summary\" : \"more text\", \"details\" : \"a\"}]"

stringAgg :: Field SqlText -> Aggregator (Field SqlText) (Field SqlText)

Counting rows

countRows :: Select a -> Select (Field SqlInt8)

Count the number of rows in a query. This is different from aggregate count because it always returns exactly one row, even when the input query is empty.

Explicit

aggregateExplicit :: Unpackspec a a' -> Aggregator a' b -> Select a -> Select b

Order the values within each aggregation in Aggregator using the given ordering. This is only relevant for aggregations that depend on the order they get their elements, like arrayAgg and stringAgg.

Note that this orders all aggregations with the same ordering. If you need different orderings for different aggregations, use orderAggregate.