README
@graphile/pg-aggregates
Adds a powerful suite of aggregate functionality to a PostGraphile schema: calculating aggregates, grouped aggregates, applying conditions to grouped aggregates, ordering by relational aggregates, filtering by the results of aggregates on related connections, etc.
IMPORTANT: aggregates are added to connections, they do not work with "simple collections".
Crowd-funded open-source software
To help us develop this software sustainably under the MIT license, we ask all individuals and businesses that use it to help support its ongoing maintenance and development via sponsorship.
Click here to find out more about sponsors and sponsorship.
And please give some love to our featured sponsors 🤩:
Chad Furman * |
Storyscript * |
Surge.io * |
Postlight * |
* Sponsors the entire Graphile suite
Status
This module is currently "experimental" status; we may change any part of it in a semver minor release.
Usage
Requires PostGraphile v4.12.0-alpha.0 or higher.
Install with:
yarn add postgraphile @graphile/pg-aggregates
CLI usage via --append-plugins
:
postgraphile --append-plugins @graphile/pg-aggregates -c postgres://localhost/my_db ...
Library usage via applyPlugins
:
import PgAggregatesPlugin from "@graphile/pg-aggregates";
// or: const PgAggregatesPlugin = require("@graphile/pg-aggregates").default;
const middleware = postgraphile(DATABASE_URL, SCHEMAS, {
appendPlugins: [PgAggregatesPlugin],
});
If you want you could install our example schema and then issue a GraphQL query such as:
query GameAggregates {
allMatchStats {
aggregates {
max {
points
goals
saves
}
min {
points
}
}
}
allPlayers(orderBy: [MATCH_STATS_BY_PLAYER_ID_SUM_GOALS_ASC]) {
nodes {
name
matchStatsByPlayerId {
totalCount
aggregates {
sum {
points
goals
saves
}
average {
points
goals
saves
teamPosition
}
}
}
}
}
}
or:
query GroupedAggregatesByDerivative {
allMatchStats {
byDay: groupedAggregates(groupBy: [CREATED_AT_TRUNCATED_TO_DAY]) {
keys # The timestamp truncated to the beginning of the day
average {
points
}
}
byHour: groupedAggregates(groupBy: [CREATED_AT_TRUNCATED_TO_HOUR]) {
keys # The timestamp truncated to the beginning of the hour
average {
points
}
}
}
}
To filter by aggregates on related tables, you will also need
postgraphile-plugin-connection-filter,
and you will need to enable graphileBuildOptions.connectionFilterRelations
as documented here.
app.use(
postgraphile(DATABASE_URL, SCHEMA_NAME, {
graphileBuildOptions: {
connectionFilterRelations: true,
},
})
);
Interaction with connection parameters
Aggregates respect the conditions/filters of the connection but are unaffected
by the pagination of the connection (they ignore the
first
/last
/after
/before
/orderBy
parameters). You may retrieve
(optionally paginated) node data from a connection at the same time as
retrieving aggregates from it. Aggregates are supported on connections at any
level of the GraphQL query.
Aggregates
Connection-wide aggregates are available via the aggregates
field directly on
a GraphQL connection; for example:
query LoadsOfAggregates {
allFilms {
aggregates {
average {
durationInMinutes
}
}
}
}
We support the following aggregates out of the box:
sum
(applies to number-like fields) - the result of adding all the values togetherdistinctCount
(applies to all fields) - the count of the number of distinct valuesmin
(applies to number-like fields) - the smallest valuemax
(applies to number-like fields) - the greatest valueaverage
(applies to number-like fields) - the average (arithmetic mean) valuestddevSample
(applies to number-like fields) - the sample standard deviation of the valuesstddevPopulation
(applies to number-like fields) - the population standard deviation of the valuesvarianceSample
(applies to number-like fields) - the sample variance of the valuesvariancePopulation
(applies to number-like fields) - the population variance of the values
See Defining your own aggregates below for details on how to add your own aggregates.
Different aggregates apply to different data types; in general we attempt to add aggregate entries for each column and computed column function that appears to be compatible with the aggregate.
Ordering by aggregates
This plugin automatically adds some additional orderBy
criteria to your graph
allowing you to order by aggregates over relations; e.g. you could find the top
5 players ordered by their average points scored in each match, and grab some
more aggregate information about them too:
query FocussedOrderedAggregate {
allPlayers(
first: 5
orderBy: [MATCH_STATS_BY_PLAYER_ID_AVERAGE_POINTS_DESC]
) {
nodes {
name
matchStatsByPlayerId {
totalCount
aggregates {
sum {
goals
}
average {
points
}
}
}
}
}
}
Filtering by aggregates
(You will need postgraphile-plugin-connection-filter
for this; see above.)
query PlayersWith9OrMoreSavesInMatchesTheyScoredIn {
allPlayers(
filter: {
matchStatsByPlayerId: {
aggregates: {
sum: { saves: { greaterThan: "9" }, rating: { lessThan: 143 } }
filter: { goals: { greaterThan: 0 } }
}
}
}
) {
nodes {
name
matchStatsByPlayerId(filter: { goals: { greaterThan: 0 } }) {
aggregates {
sum {
saves
rating
goals
}
}
}
}
}
}
Grouped aggregates
We also support grouping your data via the value of one of your columns, no-additional-arguments computed columns, or a derivative thereof; and calculating aggregates over each of the matching groups. Out of the box we support two derivatives:
truncated-to-hour
(applies to timestamp-like values) - truncates to the beginning of the (UTC) hourtruncated-to-day
(applies to timestamp-like values) - truncates to the beginning of the (UTC) day
See Defining your own grouping derivatives below for details on how to add your own grouping derivatives.
The aggregates supported over groups are the same as over the connection as a
whole (see Aggregates above), but in addition you may also
determine the keys
that were used for the aggregate. There will be one key for
each of the groupBy
values; for example in this query:
query AverageDurationByYearOfRelease {
allFilms {
groupedAggregates(groupBy: [YEAR_OF_RELEASE]) {
keys
average {
durationInMinutes
}
}
}
}
each entry in the groupedAggregates
result will have a keys
entry that will
be a list containing one value which will be the year of release (as a string).
The values in the keys
list are always stringified, this is a known limitation
due to interactions with GraphQL.
Having
If these grouped aggregates are returning too much data, you can filter the
groups down by applying a having
clause against them; for example you could
see the average number of goals on days where the average points score was over
200:
query AverageGoalsOnDaysWithAveragePointsOver200 {
allMatchStats {
byDay: groupedAggregates(
groupBy: [CREATED_AT_TRUNCATED_TO_DAY]
having: { average: { points: { greaterThan: 200 } } }
) {
keys
average {
goals
}
}
}
}
Defining your own aggregates
You can add your own aggregates by using a plugin to add your own aggregate specs. Aggregate specs aren't too complicated, for example here is a spec that could define the "min" aggregate:
const isNumberLike = (pgType) => pgType.category === "N";
const minSpec = {
id: "min",
humanLabel: "minimum",
HumanLabel: "Minimum",
isSuitableType: isNumberLike,
sqlAggregateWrap: (sqlFrag) => sql.fragment`min(${sqlFrag})`,
};
See src/AggregateSpecsPlugin.ts for more details/examples.
Defining your own grouping derivatives
You may add your own derivatives by adding a group by spec; see src/AggregateSpecsPlugin.ts for examples and more information. Derivative specs are also fairly straightforward:
const TIMESTAMP_OID = "1114";
const TIMESTAMPTZ_OID = "1184";
const truncatedToHourSpec = {
id: "truncated-to-hour",
isSuitableType: (pgType) =>
pgType.id === TIMESTAMP_OID || pgType.id === TIMESTAMPTZ_OID,
sqlWrap: (sqlFrag) => sql.fragment`date_trunc('hour', ${sqlFrag})`,
};
Thanks
This plugin was started as a proof of concept in 2019 thanks to sponsorship from OneGraph, and was made into fully featured released module thanks to sponsorship from Surge in 2021. It is maintained thanks to the support of Graphile's sponsors - thank you sponsors!