| 1 |
""" |
| 2 |
Classes to represent the definitions of aggregate functions. |
| 3 |
""" |
| 4 |
|
| 5 |
from django.core.exceptions import FieldError, FullResultSet |
| 6 |
from django.db.models.expressions import Case, ColPairs, Func, Star, Value, When |
| 7 |
from django.db.models.fields import IntegerField |
| 8 |
from django.db.models.functions import Coalesce |
| 9 |
from django.db.models.functions.mixins import ( |
| 10 |
FixDurationInputMixin, |
| 11 |
NumericOutputFieldMixin, |
| 12 |
) |
| 13 |
|
| 14 |
__all__ = [ |
| 15 |
"Aggregate", |
| 16 |
"Avg", |
| 17 |
"Count", |
| 18 |
"Max", |
| 19 |
"Min", |
| 20 |
"StdDev", |
| 21 |
"Sum", |
| 22 |
"Variance", |
| 23 |
] |
| 24 |
|
| 25 |
|
| 26 |
class Aggregate(Func): |
| 27 |
template = "%(function)s(%(distinct)s%(expressions)s)" |
| 28 |
contains_aggregate = True |
| 29 |
name = None |
| 30 |
filter_template = "%s FILTER (WHERE %%(filter)s)" |
| 31 |
window_compatible = True |
| 32 |
allow_distinct = False |
| 33 |
empty_result_set_value = None |
| 34 |
|
| 35 |
def __init__( |
| 36 |
self, *expressions, distinct=False, filter=None, default=None, **extra |
| 37 |
): |
| 38 |
if distinct and not self.allow_distinct: |
| 39 |
raise TypeError("%s does not allow distinct." % self.__class__.__name__) |
| 40 |
if default is not None and self.empty_result_set_value is not None: |
| 41 |
raise TypeError(f"{self.__class__.__name__} does not allow default.") |
| 42 |
self.distinct = distinct |
| 43 |
self.filter = filter |
| 44 |
self.default = default |
| 45 |
super().__init__(*expressions, **extra) |
| 46 |
|
| 47 |
def get_source_fields(self): |
| 48 |
# Don't return the filter expression since it's not a source field. |
| 49 |
return [e._output_field_or_none for e in super().get_source_expressions()] |
| 50 |
|
| 51 |
def get_source_expressions(self): |
| 52 |
source_expressions = super().get_source_expressions() |
| 53 |
return source_expressions + [self.filter] |
| 54 |
|
| 55 |
def set_source_expressions(self, exprs): |
| 56 |
*exprs, self.filter = exprs |
| 57 |
return super().set_source_expressions(exprs) |
| 58 |
|
| 59 |
def resolve_expression( |
| 60 |
self, query=None, allow_joins=True, reuse=None, summarize=False, for_save=False |
| 61 |
): |
| 62 |
# Aggregates are not allowed in UPDATE queries, so ignore for_save |
| 63 |
c = super().resolve_expression(query, allow_joins, reuse, summarize) |
| 64 |
if summarize: |
| 65 |
# Summarized aggregates cannot refer to summarized aggregates. |
| 66 |
for ref in c.get_refs(): |
| 67 |
if query.annotations[ref].is_summary: |
| 68 |
raise FieldError( |
| 69 |
f"Cannot compute {c.name}('{ref}'): '{ref}' is an aggregate" |
| 70 |
) |
| 71 |
elif not self.is_summary: |
| 72 |
# Call Aggregate.get_source_expressions() to avoid |
| 73 |
# returning self.filter and including that in this loop. |
| 74 |
expressions = super(Aggregate, c).get_source_expressions() |
| 75 |
for index, expr in enumerate(expressions): |
| 76 |
if expr.contains_aggregate: |
| 77 |
before_resolved = self.get_source_expressions()[index] |
| 78 |
name = ( |
| 79 |
before_resolved.name |
| 80 |
if hasattr(before_resolved, "name") |
| 81 |
else repr(before_resolved) |
| 82 |
) |
| 83 |
raise FieldError( |
| 84 |
"Cannot compute %s('%s'): '%s' is an aggregate" |
| 85 |
% (c.name, name, name) |
| 86 |
) |
| 87 |
if (default := c.default) is None: |
| 88 |
return c |
| 89 |
if hasattr(default, "resolve_expression"): |
| 90 |
default = default.resolve_expression(query, allow_joins, reuse, summarize) |
| 91 |
if default._output_field_or_none is None: |
| 92 |
default.output_field = c._output_field_or_none |
| 93 |
else: |
| 94 |
default = Value(default, c._output_field_or_none) |
| 95 |
c.default = None # Reset the default argument before wrapping. |
| 96 |
coalesce = Coalesce(c, default, output_field=c._output_field_or_none) |
| 97 |
coalesce.is_summary = c.is_summary |
| 98 |
return coalesce |
| 99 |
|
| 100 |
@property |
| 101 |
def default_alias(self): |
| 102 |
expressions = [ |
| 103 |
expr for expr in self.get_source_expressions() if expr is not None |
| 104 |
] |
| 105 |
if len(expressions) == 1 and hasattr(expressions[0], "name"): |
| 106 |
return "%s__%s" % (expressions[0].name, self.name.lower()) |
| 107 |
raise TypeError("Complex expressions require an alias") |
| 108 |
|
| 109 |
def get_group_by_cols(self): |
| 110 |
return [] |
| 111 |
|
| 112 |
def as_sql(self, compiler, connection, **extra_context): |
| 113 |
extra_context["distinct"] = "DISTINCT " if self.distinct else "" |
| 114 |
if self.filter: |
| 115 |
if connection.features.supports_aggregate_filter_clause: |
| 116 |
try: |
| 117 |
filter_sql, filter_params = self.filter.as_sql(compiler, connection) |
| 118 |
except FullResultSet: |
| 119 |
pass |
| 120 |
else: |
| 121 |
template = self.filter_template % extra_context.get( |
| 122 |
"template", self.template |
| 123 |
) |
| 124 |
sql, params = super().as_sql( |
| 125 |
compiler, |
| 126 |
connection, |
| 127 |
template=template, |
| 128 |
filter=filter_sql, |
| 129 |
**extra_context, |
| 130 |
) |
| 131 |
return sql, (*params, *filter_params) |
| 132 |
else: |
| 133 |
copy = self.copy() |
| 134 |
copy.filter = None |
| 135 |
source_expressions = copy.get_source_expressions() |
| 136 |
condition = When(self.filter, then=source_expressions[0]) |
| 137 |
copy.set_source_expressions([Case(condition)] + source_expressions[1:]) |
| 138 |
return super(Aggregate, copy).as_sql( |
| 139 |
compiler, connection, **extra_context |
| 140 |
) |
| 141 |
return super().as_sql(compiler, connection, **extra_context) |
| 142 |
|
| 143 |
def _get_repr_options(self): |
| 144 |
options = super()._get_repr_options() |
| 145 |
if self.distinct: |
| 146 |
options["distinct"] = self.distinct |
| 147 |
if self.filter: |
| 148 |
options["filter"] = self.filter |
| 149 |
return options |
| 150 |
|
| 151 |
|
| 152 |
class Avg(FixDurationInputMixin, NumericOutputFieldMixin, Aggregate): |
| 153 |
function = "AVG" |
| 154 |
name = "Avg" |
| 155 |
allow_distinct = True |
| 156 |
arity = 1 |
| 157 |
|
| 158 |
|
| 159 |
class Count(Aggregate): |
| 160 |
function = "COUNT" |
| 161 |
name = "Count" |
| 162 |
output_field = IntegerField() |
| 163 |
allow_distinct = True |
| 164 |
empty_result_set_value = 0 |
| 165 |
arity = 1 |
| 166 |
allows_composite_expressions = True |
| 167 |
|
| 168 |
def __init__(self, expression, filter=None, **extra): |
| 169 |
if expression == "*": |
| 170 |
expression = Star() |
| 171 |
if isinstance(expression, Star) and filter is not None: |
| 172 |
raise ValueError("Star cannot be used with filter. Please specify a field.") |
| 173 |
super().__init__(expression, filter=filter, **extra) |
| 174 |
|
| 175 |
def resolve_expression(self, *args, **kwargs): |
| 176 |
result = super().resolve_expression(*args, **kwargs) |
| 177 |
expr = result.source_expressions[0] |
| 178 |
|
| 179 |
# In case of composite primary keys, count the first column. |
| 180 |
if isinstance(expr, ColPairs): |
| 181 |
if self.distinct: |
| 182 |
raise ValueError( |
| 183 |
"COUNT(DISTINCT) doesn't support composite primary keys" |
| 184 |
) |
| 185 |
|
| 186 |
cols = expr.get_cols() |
| 187 |
return Count(cols[0], filter=result.filter) |
| 188 |
|
| 189 |
return result |
| 190 |
|
| 191 |
|
| 192 |
class Max(Aggregate): |
| 193 |
function = "MAX" |
| 194 |
name = "Max" |
| 195 |
arity = 1 |
| 196 |
|
| 197 |
|
| 198 |
class Min(Aggregate): |
| 199 |
function = "MIN" |
| 200 |
name = "Min" |
| 201 |
arity = 1 |
| 202 |
|
| 203 |
|
| 204 |
class StdDev(NumericOutputFieldMixin, Aggregate): |
| 205 |
name = "StdDev" |
| 206 |
arity = 1 |
| 207 |
|
| 208 |
def __init__(self, expression, sample=False, **extra): |
| 209 |
self.function = "STDDEV_SAMP" if sample else "STDDEV_POP" |
| 210 |
super().__init__(expression, **extra) |
| 211 |
|
| 212 |
def _get_repr_options(self): |
| 213 |
return {**super()._get_repr_options(), "sample": self.function == "STDDEV_SAMP"} |
| 214 |
|
| 215 |
|
| 216 |
class Sum(FixDurationInputMixin, Aggregate): |
| 217 |
function = "SUM" |
| 218 |
name = "Sum" |
| 219 |
allow_distinct = True |
| 220 |
arity = 1 |
| 221 |
|
| 222 |
|
| 223 |
class Variance(NumericOutputFieldMixin, Aggregate): |
| 224 |
name = "Variance" |
| 225 |
arity = 1 |
| 226 |
|
| 227 |
def __init__(self, expression, sample=False, **extra): |
| 228 |
self.function = "VAR_SAMP" if sample else "VAR_POP" |
| 229 |
super().__init__(expression, **extra) |
| 230 |
|
| 231 |
def _get_repr_options(self): |
| 232 |
return {**super()._get_repr_options(), "sample": self.function == "VAR_SAMP"} |