My other changeset developed at this week's Django Sprint is much more
extensive and is best explained by the new section that I am adding to
the DB API docs.
Feedback is welcome.
Rock
Aggregate Functions
===================
Aggregate functions perform calculations on columns. Typically
they return a single value. They are in two groups: high_level
and low_level.
High Level Functions
--------------------
The high_level functions are sum(), min(), max(), avg(), stddev()
and median(). Each takes a fieldname as an argument. The type of
the field is checked for correctness as only certain datatypes are
allowed for each of the high level functions.
sum(fieldname)
---------------
Returns the sum of the named field. The field must be an
IntegerField or a FloatField. The returned value corresponds
with the type of the column.
min(fieldname), max(fieldname)
--------------------------------
Returns the minimum or maximum value of the named field. The field
must be an IntegerField, FloatField or DateField. The returned value
corresponds with the type of the field. (This is a string
representation if the field is a DateField.)
avg(fieldname)
---------------
Returns the average of the named field. The field must be an
IntegerField or a FloatField. The returned value is a Float.
stddev(fieldname)
------------------
Returns the standard deviation of the named field. The field must be an
IntegerField or a FloatField. The returned value is a Float.
(Not supported on sqlite3. You get an OperationError exception.)
median(fieldname)
------------------
Returns the median value of the named field. The field
must be an IntegerField, FloatField or DateField. The returned
value corresponds with the type of the field. (This is a string
representation if the column is a DateField.) Unlike the other
functions in this group, this function does not use the DB
supplied capabilities. It fetches all of the values of the field
ordered by that field and returns the middle value. (If there
are an even number of values, the second of the two middle
values is returned.)
Low Level Functions
-------------------
There are two low level functions: get_aggregate() and
get_aggregates(). They do minimal checking and allow for
powerful queries that potentially return multiple values
and/or combine multiple column arithmetically.
The low_level functions take columnnames instead of fieldnames.
You must do your own conversion from fieldname to columnname
if you are taking advantage of the fieldname mapping. (By
default fieldnames and columnnames match each other and so
most users will not have to worry about this distinction.)
get_aggregate(type,columnname)
------------------------------
This function supplies direct support for all database-supplied
aggregate functions. The type parameter is the name of an aggregate
function such as 'SUM', 'VARIANCE' or so forth limited only by
what set of functions your particular database supports. The return
value uses whatever type your database connonically returns. (Most
databases return the same type as the named column, although this
is not the case for some functions such as "avg" or "stddev" which
always returns a Float. Also note that sqlite3 always returns a Float
for all aggregate function.)
Note that the columnname is not explicitly checked for type and
so it is possible to combine columns arithmetically (with care!)
as follows:
Inventory.objects.get_aggregate('AVG','quantity*price')
This returns the average value of the 'quantity' column multiplied
by the 'price' column.
Meals.objects.get_aggregate('MAX','price+tax+tip')
This returns the highest priced meal which is calculated by the
database by adding the 'price', the 'tax' and the 'tip' columns.
(As a repeat warning: Don't forget to get the columnname from your
fieldname if you are using fieldname mapping.)
get_aggregates(types,columnname)
--------------------------------
This function allows a single SQL operation to perform multiple
aggregate functions. The types field is an iterable list of
aggregate function names. The columnname is handled in the same
manner as with the get_aggregate() function. For example:
Inventory.objects.get_aggregates(['AVG','MIN','MAX'],'quantity')
The results are returned in an array.
Usage
-----
Typical use targets all of the rows in the targeted table.
For example:
Articles.objects.sum('wordcount')
However it is possible to combine the aggregate functions with
judicious filtering. For example:
Poll.objects.filter(question__contains='football').min('pub_date')
Exceptions
----------
The most common exceptions encountered when using aggregate functions
are:
FieldDoesNotExist - the columnname is not found.
TypeError - the named column uses an unsupported type.
OperationError - the functype is not supported by the database.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django developers" group.
To post to this group, send email to django-developers@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-developers
-~----------~----~----~----~------~----~------~--~---
