From e4cf8c8420634d6f2dc8ce873246256ce635972d Mon Sep 17 00:00:00 2001 From: Andriy Sokolovskiy Date: Sun, 8 Feb 2015 17:21:48 +0200 Subject: Fixed #24301 -- Added PostgreSQL-specific aggregate functions --- docs/ref/contrib/postgres/aggregates.txt | 212 +++++++++++++++++++++++++++++++ docs/ref/contrib/postgres/index.txt | 1 + 2 files changed, 213 insertions(+) create mode 100644 docs/ref/contrib/postgres/aggregates.txt (limited to 'docs/ref/contrib/postgres') diff --git a/docs/ref/contrib/postgres/aggregates.txt b/docs/ref/contrib/postgres/aggregates.txt new file mode 100644 index 0000000000..af4a0661b3 --- /dev/null +++ b/docs/ref/contrib/postgres/aggregates.txt @@ -0,0 +1,212 @@ +========================================= +PostgreSQL specific aggregation functions +========================================= + +.. module:: django.contrib.postgres.aggregates + :synopsis: PostgreSQL specific aggregation functions + +.. versionadded:: 1.9 + +These functions are described in more detail in the `PostgreSQL docs +`_. + +.. note:: + + All functions come without default aliases, so you must explicitly provide + one. For example:: + + >>> SomeModel.objects.aggregate(arr=ArrayAgg('somefield')) + {'arr': [0, 1, 2]} + +General-purpose aggregation functions +------------------------------------- + +ArrayAgg +~~~~~~~~ + +.. class:: ArrayAgg(expression, **extra) + + Returns a list of values, including nulls, concatenated into an array. + +BitAnd +~~~~~~ + +.. class:: BitAnd(expression, **extra) + + Returns an ``int`` of the bitwise ``AND`` of all non-null input values, or + ``None`` if all values are null. + +BitOr +~~~~~ + +.. class:: BitOr(expression, **extra) + + Returns an ``int`` of the bitwise ``OR`` of all non-null input values, or + ``None`` if all values are null. + +BoolAnd +~~~~~~~~ + +.. class:: BoolAnd(expression, **extra) + + Returns ``True``, if all input values are true, ``None`` if all values are + null or if there are no values, otherwise ``False`` . + +BoolOr +~~~~~~ + +.. class:: BoolOr(expression, **extra) + + Returns ``True`` if at least one input value is true, ``None`` if all + values are null or if there are no values, otherwise ``False``. + +StringAgg +~~~~~~~~~ + +.. class:: StringAgg(expression, delimiter) + + Returns the input values concatenated into a string, separated by + the ``delimiter`` string. + + .. attribute:: delimiter + + Required argument. Needs to be a string. + +Aggregate functions for statistics +---------------------------------- + +``y`` and ``x`` +~~~~~~~~~~~~~~~ + +The arguments ``y`` and ``x`` for all these functions can be the name of a +field or an expression returning a numeric data. Both are required. + +Corr +~~~~ + +.. class:: Corr(y, x) + + Returns the correlation coefficient as a ``float``, or ``None`` if there + aren't any matching rows. + +CovarPop +~~~~~~~~ + +.. class:: CovarPop(y, x, sample=False) + + Returns the population covariance as a ``float``, or ``None`` if there + aren't any matching rows. + + Has one optional argument: + + .. attribute:: sample + + By default ``CovarPop`` returns the general population covariance. + However, if ``sample=True``, the return value will be the sample + population covariance. + +RegrAvgX +~~~~~~~~ + +.. class:: RegrAvgX(y, x) + + Returns the average of the independent variable (``sum(x)/N``) as a + ``float``, or ``None`` if there aren't any matching rows. + +RegrAvgY +~~~~~~~~ + +.. class:: RegrAvgY(y, x) + + Returns the average of the independent variable (``sum(y)/N``) as a + ``float``, or ``None`` if there aren't any matching rows. + +RegrCount +~~~~~~~~~ + +.. class:: RegrCount(y, x) + + Returns an ``int`` of the number of input rows in which both expressions + are not null. + +RegrIntercept +~~~~~~~~~~~~~ + +.. class:: RegrIntercept(y, x) + + Returns the y-intercept of the least-squares-fit linear equation determined + by the ``(x, y)`` pairs as a ``float``, or ``None`` if there aren't any + matching rows. + +RegrR2 +~~~~~~ + +.. class:: RegrR2(y, x) + + Returns the square of the correlation coefficient as a ``float``, or + ``None`` if there aren't any matching rows. + +RegrSlope +~~~~~~~~~ + +.. class:: RegrSlope(y, x) + + Returns the slope of the least-squares-fit linear equation determined + by the ``(x, y)`` pairs as a ``float``, or ``None`` if there aren't any + matching rows. + +RegrSXX +~~~~~~~ + +.. class:: RegrSXX(y, x) + + Returns ``sum(x^2) - sum(x)^2/N`` ("sum of squares" of the independent + variable) as a ``float``, or ``None`` if there aren't any matching rows. + +RegrSXY +~~~~~~~ + +.. class:: RegrSXY(y, x) + + Returns ``sum(x*y) - sum(x) * sum(y)/N`` ("sum of products" of independent + times dependent variable) as a ``float``, or ``None`` if there aren't any + matching rows. + +RegrSYY +~~~~~~~ + +.. class:: RegrSYY(y, x) + + Returns ``sum(y^2) - sum(y)^2/N`` ("sum of squares" of the dependent + variable) as a ``float``, or ``None`` if there aren't any matching rows. + +Usage examples +-------------- + +We will use this example table:: + + | FIELD1 | FIELD2 | FIELD3 | + |--------|--------|--------| + | foo | 1 | 13 | + | bar | 2 | (null) | + | test | 3 | 13 | + + +Here's some examples of some of the general-purpose aggregation functions:: + + >>> TestModel.objects.aggregate(result=StringAgg('field1', delimiter=';')) + {'result': 'foo;bar;test'} + >>> TestModel.objects.aggregate(result=ArrayAgg('field2')) + {'result': [1, 2, 3]} + >>> TestModel.objects.aggregate(result=ArrayAgg('field1')) + {'result': ['foo', 'bar', 'test']} + +The next example shows the usage of statistical aggregate functions. The +underlying math will be not described (you can read about this, for example, at +`wikipedia `_):: + + >>> TestModel.objects.aggregate(count=RegrCount(y='field3', x='field2')) + {'count': 2} + >>> TestModel.objects.aggregate(avgx=RegrAvgX(y='field3', x='field2'), + ... avgy=RegrAvgY(y='field3', x='field2')) + {'avgx': 2, 'avgy': 13} diff --git a/docs/ref/contrib/postgres/index.txt b/docs/ref/contrib/postgres/index.txt index c97a61d123..7ff66d0770 100644 --- a/docs/ref/contrib/postgres/index.txt +++ b/docs/ref/contrib/postgres/index.txt @@ -31,6 +31,7 @@ Psycopg2 2.5 or higher is required. .. toctree:: :maxdepth: 2 + aggregates fields forms lookups -- cgit v1.3