Opaleye.Aggregate

Perform aggregations on query results.

Documentation

aggregate :: Aggregator a b -> Query a -> Query b

Given a Query producing rows of type a and an Aggregator accepting rows of type a, apply the aggregator to the results of the query.

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

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

By design there is no aggregation function of type Aggregator b b' -> QueryArr a b -> QueryArr a b'. Such a function would allow violation of SQL's scoping rules and lead to invalid queries.

aggregateOrdered :: Order a -> Aggregator a b -> Query a -> Query 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.

groupBy :: Aggregator (Column a) (Column a)

Group the aggregation by equality on the input to groupBy.

sum :: Aggregator (Column a) (Column a)

Sum all rows in a group.

count :: Aggregator (Column a) (Column PGInt8)

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

countStar :: Aggregator a (Column PGInt8)

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

avg :: Aggregator (Column PGFloat8) (Column PGFloat8)

Average of a group

max :: PGOrd a => Aggregator (Column a) (Column a)

Maximum of a group

min :: PGOrd a => Aggregator (Column a) (Column a)

Maximum of a group

boolOr :: Aggregator (Column PGBool) (Column PGBool)

boolAnd :: Aggregator (Column PGBool) (Column PGBool)

arrayAgg :: Aggregator (Column a) (Column (PGArray a))

stringAgg :: Column PGText -> Aggregator (Column PGText) (Column PGText)

countRows :: Query a -> Query (Column PGInt8)

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.

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.

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 row of type b.

Instances

SumProfunctor Aggregator

ProductProfunctor Aggregator

Profunctor Aggregator

Functor (Aggregator a)

Applicative (Aggregator a)