diff options
Diffstat (limited to 'docs/ref')
| -rw-r--r-- | docs/ref/contrib/postgres/aggregates.txt | 36 | ||||
| -rw-r--r-- | docs/ref/models/conditional-expressions.txt | 43 | ||||
| -rw-r--r-- | docs/ref/models/expressions.txt | 10 | ||||
| -rw-r--r-- | docs/ref/models/querysets.txt | 25 |
4 files changed, 74 insertions, 40 deletions
diff --git a/docs/ref/contrib/postgres/aggregates.txt b/docs/ref/contrib/postgres/aggregates.txt index 1c738c35ae..a51249b6c4 100644 --- a/docs/ref/contrib/postgres/aggregates.txt +++ b/docs/ref/contrib/postgres/aggregates.txt @@ -22,7 +22,7 @@ General-purpose aggregation functions ``ArrayAgg`` ------------ -.. class:: ArrayAgg(expression, distinct=False, **extra) +.. class:: ArrayAgg(expression, distinct=False, filter=None, **extra) Returns a list of values, including nulls, concatenated into an array. @@ -36,7 +36,7 @@ General-purpose aggregation functions ``BitAnd`` ---------- -.. class:: BitAnd(expression, **extra) +.. class:: BitAnd(expression, filter=None, **extra) Returns an ``int`` of the bitwise ``AND`` of all non-null input values, or ``None`` if all values are null. @@ -44,7 +44,7 @@ General-purpose aggregation functions ``BitOr`` --------- -.. class:: BitOr(expression, **extra) +.. class:: BitOr(expression, filter=None, **extra) Returns an ``int`` of the bitwise ``OR`` of all non-null input values, or ``None`` if all values are null. @@ -52,7 +52,7 @@ General-purpose aggregation functions ``BoolAnd`` ----------- -.. class:: BoolAnd(expression, **extra) +.. class:: BoolAnd(expression, filter=None, **extra) Returns ``True``, if all input values are true, ``None`` if all values are null or if there are no values, otherwise ``False`` . @@ -60,7 +60,7 @@ General-purpose aggregation functions ``BoolOr`` ---------- -.. class:: BoolOr(expression, **extra) +.. class:: BoolOr(expression, filter=None, **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``. @@ -68,7 +68,7 @@ General-purpose aggregation functions ``JSONBAgg`` ------------ -.. class:: JSONBAgg(expressions, **extra) +.. class:: JSONBAgg(expressions, filter=None, **extra) .. versionadded:: 1.11 @@ -77,7 +77,7 @@ General-purpose aggregation functions ``StringAgg`` ------------- -.. class:: StringAgg(expression, delimiter, distinct=False) +.. class:: StringAgg(expression, delimiter, distinct=False, filter=None) Returns the input values concatenated into a string, separated by the ``delimiter`` string. @@ -105,7 +105,7 @@ field or an expression returning a numeric data. Both are required. ``Corr`` -------- -.. class:: Corr(y, x) +.. class:: Corr(y, x, filter=None) Returns the correlation coefficient as a ``float``, or ``None`` if there aren't any matching rows. @@ -113,7 +113,7 @@ field or an expression returning a numeric data. Both are required. ``CovarPop`` ------------ -.. class:: CovarPop(y, x, sample=False) +.. class:: CovarPop(y, x, sample=False, filter=None) Returns the population covariance as a ``float``, or ``None`` if there aren't any matching rows. @@ -129,7 +129,7 @@ field or an expression returning a numeric data. Both are required. ``RegrAvgX`` ------------ -.. class:: RegrAvgX(y, x) +.. class:: RegrAvgX(y, x, filter=None) Returns the average of the independent variable (``sum(x)/N``) as a ``float``, or ``None`` if there aren't any matching rows. @@ -137,7 +137,7 @@ field or an expression returning a numeric data. Both are required. ``RegrAvgY`` ------------ -.. class:: RegrAvgY(y, x) +.. class:: RegrAvgY(y, x, filter=None) Returns the average of the dependent variable (``sum(y)/N``) as a ``float``, or ``None`` if there aren't any matching rows. @@ -145,7 +145,7 @@ field or an expression returning a numeric data. Both are required. ``RegrCount`` ------------- -.. class:: RegrCount(y, x) +.. class:: RegrCount(y, x, filter=None) Returns an ``int`` of the number of input rows in which both expressions are not null. @@ -153,7 +153,7 @@ field or an expression returning a numeric data. Both are required. ``RegrIntercept`` ----------------- -.. class:: RegrIntercept(y, x) +.. class:: RegrIntercept(y, x, filter=None) 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 @@ -162,7 +162,7 @@ field or an expression returning a numeric data. Both are required. ``RegrR2`` ---------- -.. class:: RegrR2(y, x) +.. class:: RegrR2(y, x, filter=None) Returns the square of the correlation coefficient as a ``float``, or ``None`` if there aren't any matching rows. @@ -170,7 +170,7 @@ field or an expression returning a numeric data. Both are required. ``RegrSlope`` ------------- -.. class:: RegrSlope(y, x) +.. class:: RegrSlope(y, x, filter=None) 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 @@ -179,7 +179,7 @@ field or an expression returning a numeric data. Both are required. ``RegrSXX`` ----------- -.. class:: RegrSXX(y, x) +.. class:: RegrSXX(y, x, filter=None) 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. @@ -187,7 +187,7 @@ field or an expression returning a numeric data. Both are required. ``RegrSXY`` ----------- -.. class:: RegrSXY(y, x) +.. class:: RegrSXY(y, x, filter=None) 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 @@ -196,7 +196,7 @@ field or an expression returning a numeric data. Both are required. ``RegrSYY`` ----------- -.. class:: RegrSYY(y, x) +.. class:: RegrSYY(y, x, filter=None) 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. diff --git a/docs/ref/models/conditional-expressions.txt b/docs/ref/models/conditional-expressions.txt index af134d4561..35f0c808f1 100644 --- a/docs/ref/models/conditional-expressions.txt +++ b/docs/ref/models/conditional-expressions.txt @@ -184,12 +184,14 @@ their registration dates. We can do this using a conditional expression and the >>> Client.objects.values_list('name', 'account_type') <QuerySet [('Jane Doe', 'G'), ('James Smith', 'R'), ('Jack Black', 'P')]> +.. _conditional-aggregation: + Conditional aggregation ----------------------- What if we want to find out how many clients there are for each -``account_type``? We can nest conditional expression within -:ref:`aggregate functions <aggregation-functions>` to achieve this:: +``account_type``? We can use the ``filter`` argument of :ref:`aggregate +functions <aggregation-functions>` to achieve this:: >>> # Create some more Clients first so we can have something to count >>> Client.objects.create( @@ -207,17 +209,30 @@ What if we want to find out how many clients there are for each >>> # Get counts for each value of account_type >>> from django.db.models import IntegerField, Sum >>> Client.objects.aggregate( - ... regular=Sum( - ... Case(When(account_type=Client.REGULAR, then=1), - ... output_field=IntegerField()) - ... ), - ... gold=Sum( - ... Case(When(account_type=Client.GOLD, then=1), - ... output_field=IntegerField()) - ... ), - ... platinum=Sum( - ... Case(When(account_type=Client.PLATINUM, then=1), - ... output_field=IntegerField()) - ... ) + ... regular=Count('pk', filter=Q(account_type=Client.REGULAR)), + ... gold=Count('pk', filter=Q(account_type=Client.GOLD)), + ... platinum=Count('pk', filter=Q(account_type=Client.PLATINUM)), ... ) {'regular': 2, 'gold': 1, 'platinum': 3} + +This aggregate produces a query with the SQL 2003 ``FILTER WHERE`` syntax +on databases that support it: + +.. code-block:: sql + + SELECT count('id') FILTER (WHERE account_type=1) as regular, + count('id') FILTER (WHERE account_type=2) as gold, + count('id') FILTER (WHERE account_type=3) as platinum + FROM clients; + +On other databases, this is emulated using a ``CASE`` statement: + +.. code-block:: sql + + SELECT count(CASE WHEN account_type=1 THEN id ELSE null) as regular, + count(CASE WHEN account_type=2 THEN id ELSE null) as gold, + count(CASE WHEN account_type=3 THEN id ELSE null) as platinum + FROM clients; + +The two SQL statements are functionally equivalent but the more explicit +``FILTER`` may perform better. diff --git a/docs/ref/models/expressions.txt b/docs/ref/models/expressions.txt index 361b170412..0974a9dd51 100644 --- a/docs/ref/models/expressions.txt +++ b/docs/ref/models/expressions.txt @@ -339,7 +339,7 @@ some complex computations:: The ``Aggregate`` API is as follows: -.. class:: Aggregate(expression, output_field=None, **extra) +.. class:: Aggregate(expression, output_field=None, filter=None, **extra) .. attribute:: template @@ -370,9 +370,17 @@ should define the desired ``output_field``. For example, adding an ``IntegerField()`` and a ``FloatField()`` together should probably have ``output_field=FloatField()`` defined. +The ``filter`` argument takes a :class:`Q object <django.db.models.Q>` that's +used to filter the rows that are aggregated. See :ref:`conditional-aggregation` +and :ref:`filtering-on-annotations` for example usage. + The ``**extra`` kwargs are ``key=value`` pairs that can be interpolated into the ``template`` attribute. +.. versionchanged:: 2.0 + + The ``filter`` argument was added. + Creating your own Aggregate Functions ------------------------------------- diff --git a/docs/ref/models/querysets.txt b/docs/ref/models/querysets.txt index 74f83ab8c5..9c329d48ee 100644 --- a/docs/ref/models/querysets.txt +++ b/docs/ref/models/querysets.txt @@ -3085,6 +3085,17 @@ of the return value ``output_field`` if all fields are of the same type. Otherwise, you must provide the ``output_field`` yourself. +``filter`` +~~~~~~~~~~ + +.. versionadded:: 2.0 + +An optional :class:`Q object <django.db.models.Q>` that's used to filter the +rows that are aggregated. + +See :ref:`conditional-aggregation` and :ref:`filtering-on-annotations` for +example usage. + ``**extra`` ~~~~~~~~~~~ @@ -3094,7 +3105,7 @@ by the aggregate. ``Avg`` ~~~~~~~ -.. class:: Avg(expression, output_field=FloatField(), **extra) +.. class:: Avg(expression, output_field=FloatField(), filter=None, **extra) Returns the mean value of the given expression, which must be numeric unless you specify a different ``output_field``. @@ -3106,7 +3117,7 @@ by the aggregate. ``Count`` ~~~~~~~~~ -.. class:: Count(expression, distinct=False, **extra) +.. class:: Count(expression, distinct=False, filter=None, **extra) Returns the number of objects that are related through the provided expression. @@ -3125,7 +3136,7 @@ by the aggregate. ``Max`` ~~~~~~~ -.. class:: Max(expression, output_field=None, **extra) +.. class:: Max(expression, output_field=None, filter=None, **extra) Returns the maximum value of the given expression. @@ -3135,7 +3146,7 @@ by the aggregate. ``Min`` ~~~~~~~ -.. class:: Min(expression, output_field=None, **extra) +.. class:: Min(expression, output_field=None, filter=None, **extra) Returns the minimum value of the given expression. @@ -3145,7 +3156,7 @@ by the aggregate. ``StdDev`` ~~~~~~~~~~ -.. class:: StdDev(expression, sample=False, **extra) +.. class:: StdDev(expression, sample=False, filter=None, **extra) Returns the standard deviation of the data in the provided expression. @@ -3169,7 +3180,7 @@ by the aggregate. ``Sum`` ~~~~~~~ -.. class:: Sum(expression, output_field=None, **extra) +.. class:: Sum(expression, output_field=None, filter=None, **extra) Computes the sum of all values of the given expression. @@ -3179,7 +3190,7 @@ by the aggregate. ``Variance`` ~~~~~~~~~~~~ -.. class:: Variance(expression, sample=False, **extra) +.. class:: Variance(expression, sample=False, filter=None, **extra) Returns the variance of the data in the provided expression. |