数据库函数Link to this heading

下面记录的类为用户提供了一种方法，可以在 Django 中使用底层数据库提供的函数作为注解、聚合或过滤器。函数也是表达式，所以它们可以和其他表达式一起使用和组合，比如聚合函数。

我们将在每个函数的例子中使用以下模型：

Code

class Author(models.Model):
    name = models.CharField(max_length=50)
    age = models.PositiveIntegerField(null=True, blank=True)
    alias = models.CharField(max_length=50, null=True, blank=True)
    goes_by = models.CharField(max_length=50, null=True, blank=True)

我们通常不建议允许 null=True 为 CharField，因为这允许字段有两个 Coalesce，但它对下面的 Coalesce 例子很重要。

比较和转换函数Link to this heading

`Cast`Link to this heading

class Cast(expression, output_field)Link to this definition

强制 expression 的结果类型为 output_field 的类型。

使用实例：

Code

>>> from django.db.models import FloatField
>>> from django.db.models.functions import Cast
>>> Author.objects.create(age=25, name='Margaret Smith')
>>> author = Author.objects.annotate(
...    age_as_float=Cast('age', output_field=FloatField()),
... ).get()
>>> print(author.age_as_float)
25.0

`Coalesce`Link to this heading

class Coalesce(*expressions, **extra)Link to this definition

接受至少两个字段名或表达式的列表，并返回第一个非空值（注意，空字符串不被视为空值）。每个参数必须是同样的类型，因此混合文本和数字将导致数据库错误。

使用实例：

Code

>>> # Get a screen name from least to most public
>>> from django.db.models import Sum, Value as V
>>> from django.db.models.functions import Coalesce
>>> Author.objects.create(name='Margaret Smith', goes_by='Maggie')
>>> author = Author.objects.annotate(
...    screen_name=Coalesce('alias', 'goes_by', 'name')).get()
>>> print(author.screen_name)
Maggie

>>> # Prevent an aggregate Sum() from returning None
>>> aggregated = Author.objects.aggregate(
...    combined_age=Coalesce(Sum('age'), V(0)),
...    combined_age_default=Sum('age'))
>>> print(aggregated['combined_age'])
0
>>> print(aggregated['combined_age_default'])
None

Warning

在 MySQL 上传递给 Coalesce 的 Python 值可能会被转换为不正确的类型，除非明确地转换为正确的数据库类型：

Code

>>> from django.db.models import DateTimeField
>>> from django.db.models.functions import Cast, Coalesce
>>> from django.utils import timezone
>>> now = timezone.now()
>>> Coalesce('updated', Cast(now, DateTimeField()))

`Collate`Link to this heading

class Collate(expression, collation)Link to this definition

Takes an expression and a collation name to query against.

For example, to filter case-insensitively in SQLite:

Code

>>> Author.objects.filter(name=Collate(Value('john'), 'nocase'))
<QuerySet [<Author: John>, <Author: john>]>

It can also be used when ordering, for example with PostgreSQL:

Code

>>> Author.objects.order_by(Collate('name', 'et-x-icu'))
<QuerySet [<Author: Ursula>, <Author: Veronika>, <Author: Ülle>]>

`Greatest`Link to this heading

class Greatest(*expressions, **extra)Link to this definition

接受至少两个字段名或表达式的列表，并返回最大的值。每个参数必须是同样的类型，所以混合文本和数字会导致数据库错误。

使用实例：

Code

class Blog(models.Model):
    body = models.TextField()
    modified = models.DateTimeField(auto_now=True)

class Comment(models.Model):
    body = models.TextField()
    modified = models.DateTimeField(auto_now=True)
    blog = models.ForeignKey(Blog, on_delete=models.CASCADE)

>>> from django.db.models.functions import Greatest
>>> blog = Blog.objects.create(body='Greatest is the best.')
>>> comment = Comment.objects.create(body='No, Least is better.', blog=blog)
>>> comments = Comment.objects.annotate(last_updated=Greatest('modified', 'blog__modified'))
>>> annotated_comment = comments.get()

annotated_comment.last_updated 将是 blog.modified 和 comment.modified 中最近的。

`JSONObject`Link to this heading

class JSONObject(**fields)Link to this definition

Takes a list of key-value pairs and returns a JSON object containing those pairs.

使用实例：

Code

>>> from django.db.models import F
>>> from django.db.models.functions import JSONObject, Lower
>>> Author.objects.create(name='Margaret Smith', alias='msmith', age=25)
>>> author = Author.objects.annotate(json_object=JSONObject(
...     name=Lower('name'),
...     alias='alias',
...     age=F('age') * 2,
... )).get()
>>> author.json_object
{'name': 'margaret smith', 'alias': 'msmith', 'age': 50}

`Least`Link to this heading

class Least(*expressions, **extra)Link to this definition

接受至少两个字段名或表达式的列表，并返回最小值。每个参数必须是同样的类型，因此混合文本和数字将导致数据库错误。

`NullIf`Link to this heading

class NullIf(expression1, expression2)Link to this definition

接受两个表达式，如果相等则返回 None，否则返回 expression1。

日期函数Link to this heading

我们将在每个函数的例子中使用以下模型：

Code

class Experiment(models.Model):
    start_datetime = models.DateTimeField()
    start_date = models.DateField(null=True, blank=True)
    start_time = models.TimeField(null=True, blank=True)
    end_datetime = models.DateTimeField(null=True, blank=True)
    end_date = models.DateField(null=True, blank=True)
    end_time = models.TimeField(null=True, blank=True)

`Extract`Link to this heading

class Extract(expression, lookup_name=None, tzinfo=None, **extra)Link to this definition

提取日期的一个组成部分作为一个数字。

取一个代表 DateField、DateTimeField、TimeField 或 DurationField 和 lookup_name 的 expression 和一个 lookup_name 引用的日期部分作为 IntegerField 返回。Django 通常使用数据库的 extract 函数，所以你可以使用任何数据库支持的 lookup_name。tzinfo 子类，通常由 pytz 提供，可以通过提取特定时区的值。

给定日期时间 2015-06-15 23:30:01.000321+00:00，内置的 lookup_name 返回。

"year": 2015
"iso_year": 2015
"quarter": 2
"month": 6
"day": 15
"week": 25
"week_day": 2
"iso_week_day": 1
"hour": 23
"minute": 30
"second": 1

如果在 Django 中使用了不同的时区，比如 Australia/Melbourne ，那么在提取值之前，日期时间会被转换为该时区。在上面的例子中，墨尔本的时区偏移是 +10:00。当这个时区被激活时，返回的值将与上述相同，除了：

"day": 16
"week_day": 3
"iso_week_day": 2
"hour": 9

week_day 值

week_day lookup_type 的计算方式与大多数数据库和 Python 的标准函数不同。这个函数将返回星期日的 1，星期一的 2，到星期六的 7。

在 Python 中的等价计算是：

Code

>>> from datetime import datetime
>>> dt = datetime(2015, 6, 15)
>>> (dt.isoweekday() % 7) + 1
2

上面的每个 lookup_name 都有一个相应的 Extract 子类（下面列出的），通常应该用这个子类来代替比较啰嗦的等价物，例如，使用 ExtractYear(...) 而不是 Extract(...，lookup_name='year')。

使用实例：

Code

>>> from datetime import datetime
>>> from django.db.models.functions import Extract
>>> start = datetime(2015, 6, 15)
>>> end = datetime(2015, 7, 2)
>>> Experiment.objects.create(
...    start_datetime=start, start_date=start.date(),
...    end_datetime=end, end_date=end.date())
>>> # Add the experiment start year as a field in the QuerySet.
>>> experiment = Experiment.objects.annotate(
...    start_year=Extract('start_datetime', 'year')).get()
>>> experiment.start_year
2015
>>> # How many experiments completed in the same year in which they started?
>>> Experiment.objects.filter(
...    start_datetime__year=Extract('end_datetime', 'year')).count()
1

`DateField` 提取Link to this heading

class ExtractYear(expression, tzinfo=None, **extra)Link to this definition

lookup_name = 'year'

class ExtractIsoYear(expression, tzinfo=None, **extra)Link to this definition

返回 ISO-8601 的周号年份。

lookup_name = 'iso_year'

class ExtractMonth(expression, tzinfo=None, **extra)Link to this definition

lookup_name = 'month'

class ExtractDay(expression, tzinfo=None, **extra)Link to this definition

lookup_name = 'day'

class ExtractWeekDay(expression, tzinfo=None, **extra)Link to this definition

lookup_name = 'week_day'

class ExtractIsoWeekDay(expression, tzinfo=None, **extra)Link to this definition

返回 ISO-8601 的星期日，第 1 天是星期一，第 7 天是星期天。

lookup_name = 'iso_week_day'

class ExtractWeek(expression, tzinfo=None, **extra)Link to this definition

lookup_name = 'week'

class ExtractQuarter(expression, tzinfo=None, **extra)Link to this definition

lookup_name = 'quarter'

这些类在逻辑上等同于 Extract('date_field', lookup_name)。每个类也是一个 Transform 在 DateField 和 DateTimeField 上注册为 __(lookup_name)`，例如 __year。

由于 DateField 没有时间部分，只有处理日期部分的 Extract 子类才能与 DateField 使用：

Code

>>> from datetime import datetime
>>> from django.utils import timezone
>>> from django.db.models.functions import (
...     ExtractDay, ExtractMonth, ExtractQuarter, ExtractWeek,
...     ExtractIsoWeekDay, ExtractWeekDay, ExtractIsoYear, ExtractYear,
... )
>>> start_2015 = datetime(2015, 6, 15, 23, 30, 1, tzinfo=timezone.utc)
>>> end_2015 = datetime(2015, 6, 16, 13, 11, 27, tzinfo=timezone.utc)
>>> Experiment.objects.create(
...    start_datetime=start_2015, start_date=start_2015.date(),
...    end_datetime=end_2015, end_date=end_2015.date())
>>> Experiment.objects.annotate(
...     year=ExtractYear('start_date'),
...     isoyear=ExtractIsoYear('start_date'),
...     quarter=ExtractQuarter('start_date'),
...     month=ExtractMonth('start_date'),
...     week=ExtractWeek('start_date'),
...     day=ExtractDay('start_date'),
...     weekday=ExtractWeekDay('start_date'),
...     isoweekday=ExtractIsoWeekDay('start_date'),
... ).values(
...     'year', 'isoyear', 'quarter', 'month', 'week', 'day', 'weekday',
...     'isoweekday',
... ).get(end_date__year=ExtractYear('start_date'))
{'year': 2015, 'isoyear': 2015, 'quarter': 2, 'month': 6, 'week': 25,
 'day': 15, 'weekday': 2, 'isoweekday': 1}

`DateTimeField` 提取Link to this heading

除以下内容外，上述 DateField 的所有提取物也可用于``DateTimeField``。

class ExtractHour(expression, tzinfo=None, **extra)Link to this definition

lookup_name = 'hour'

class ExtractMinute(expression, tzinfo=None, **extra)Link to this definition

lookup_name = 'minute'

class ExtractSecond(expression, tzinfo=None, **extra)Link to this definition

lookup_name = 'second'

这些类在逻辑上等同于 Extract('datetime_field', lookup_name)。每个类也是一个 Transform 在 DateTimeField 上注册为 __(lookup_name)，例如 __minute。

DateTimeField 例子：

Code

>>> from datetime import datetime
>>> from django.utils import timezone
>>> from django.db.models.functions import (
...     ExtractDay, ExtractHour, ExtractMinute, ExtractMonth,
...     ExtractQuarter, ExtractSecond, ExtractWeek, ExtractIsoWeekDay,
...     ExtractWeekDay, ExtractIsoYear, ExtractYear,
... )
>>> start_2015 = datetime(2015, 6, 15, 23, 30, 1, tzinfo=timezone.utc)
>>> end_2015 = datetime(2015, 6, 16, 13, 11, 27, tzinfo=timezone.utc)
>>> Experiment.objects.create(
...    start_datetime=start_2015, start_date=start_2015.date(),
...    end_datetime=end_2015, end_date=end_2015.date())
>>> Experiment.objects.annotate(
...     year=ExtractYear('start_datetime'),
...     isoyear=ExtractIsoYear('start_datetime'),
...     quarter=ExtractQuarter('start_datetime'),
...     month=ExtractMonth('start_datetime'),
...     week=ExtractWeek('start_datetime'),
...     day=ExtractDay('start_datetime'),
...     weekday=ExtractWeekDay('start_datetime'),
...     isoweekday=ExtractIsoWeekDay('start_datetime'),
...     hour=ExtractHour('start_datetime'),
...     minute=ExtractMinute('start_datetime'),
...     second=ExtractSecond('start_datetime'),
... ).values(
...     'year', 'isoyear', 'month', 'week', 'day',
...     'weekday', 'isoweekday', 'hour', 'minute', 'second',
... ).get(end_datetime__year=ExtractYear('start_datetime'))
{'year': 2015, 'isoyear': 2015, 'quarter': 2, 'month': 6, 'week': 25,
 'day': 15, 'weekday': 2, 'isoweekday': 1, 'hour': 23, 'minute': 30,
 'second': 1}

当 USE_TZ 为 True 时，数据库中的日期时间是以 UTC 存储的。如果在 Django 中使用了不同的时区，那么在提取值之前，日期时间会被转换为该时区。下面的例子将转换为墨尔本时区（UTC +10:00），从而改变了返回的日期、星期和小时值：

Code

>>> import pytz
>>> melb = pytz.timezone('Australia/Melbourne')  # UTC+10:00
>>> with timezone.override(melb):
...    Experiment.objects.annotate(
...        day=ExtractDay('start_datetime'),
...        weekday=ExtractWeekDay('start_datetime'),
...        isoweekday=ExtractIsoWeekDay('start_datetime'),
...        hour=ExtractHour('start_datetime'),
...    ).values('day', 'weekday', 'isoweekday', 'hour').get(
...        end_datetime__year=ExtractYear('start_datetime'),
...    )
{'day': 16, 'weekday': 3, 'isoweekday': 2, 'hour': 9}

明确地将时区传递给 Extract 函数的行为与此相同，并优先于活动时区：

Code

>>> import pytz
>>> melb = pytz.timezone('Australia/Melbourne')
>>> Experiment.objects.annotate(
...     day=ExtractDay('start_datetime', tzinfo=melb),
...     weekday=ExtractWeekDay('start_datetime', tzinfo=melb),
...     isoweekday=ExtractIsoWeekDay('start_datetime', tzinfo=melb),
...     hour=ExtractHour('start_datetime', tzinfo=melb),
... ).values('day', 'weekday', 'isoweekday', 'hour').get(
...     end_datetime__year=ExtractYear('start_datetime'),
... )
{'day': 16, 'weekday': 3, 'isoweekday': 2, 'hour': 9}

`Now`Link to this heading

class NowLink to this definition

返回数据库服务器执行查询时的当前日期和时间，通常使用 SQL CURRENT_TIMESTAMP。

使用实例：

Code

>>> from django.db.models.functions import Now
>>> Article.objects.filter(published__lte=Now())
<QuerySet [<Article: How to Django>]>

`Trunc`Link to this heading

class Trunc(expression, kind, output_field=None, tzinfo=None, is_dst=None, **extra)Link to this definition

将一个日期截断到一个重要的部分。

当你只关心某事是否发生在某年、某小时或某天，而不关心确切的秒数时，那么 Trunc （及其子类）可以用来过滤或汇总你的数据。例如，你可以使用 Trunc 来计算每天的销售数量。

Trunc 接受一个 expression，表示 DateField、TimeField 或 DateTimeField，一个 kind 表示日期或时间部分，一个 output_field 是 DateTimeField()`、TimeField() 或 DateField()。它根据 output_field 返回一个日期时间、日期或时间，其中 kind 以下的字段设置为最小值。如果省略了 output_field，它将默认为 expression 的 output_field。tzinfo 子类，通常由 pytz 提供，可以通过传递来截断特定时区的值。

is_dst 参数表示 pytz 是否应该解释夏令时中不存在的和模糊的日期。默认情况下（当 is_dst=None），pytz 会对这种日期时间产生异常。

给定日期时间 2015-06-15 14:30:50.000321+00:00，内置 kind 返回：

"year": 2015-01-01 00:00:00+00:00
"quarter": 2015-04-01 00:00:00+00:00
"month": 2015-06-01 00:00:00+00:00
"week": 2015-06-15 00:00:00+00:00
"day": 2015-06-15 00:00:00+00:00
"hour": 2015-06-15 14:00:00+00:00
"minute": 2015-06-15 14:30:00+00:00
"second": 2015-06-15 14:30:50+00:00

如果在 Django 中使用了不同的时区，比如 Australia/Melbourne，那么日期时间会在被截断之前转换为新的时区。在上面的例子中，墨尔本的时区偏移是 +10:00。当这个时区被激活时，返回的值将是：

"year": 2015-01-01 00:00:00+11:00
"quarter": 2015-04-01 00:00:00+10:00
"month": 2015-06-01 00:00:00+10:00
"week": 2015-06-16 00:00:00+10:00
"day": 2015-06-16 00:00:00+10:00
"hour": 2015-06-16 00:00:00+10:00
"minute": 2015-06-16 00:30:00+10:00
"second": 2015-06-16 00:30:50+10:00

年的偏移量为 +11:00，因为结果过渡到夏令时。

以上每个 kind 都有一个对应的 Trunc 子类（下面列出的），通常应该用这个子类来代替比较啰嗦的等价物，例如使用 TruncYear(...) 而不是 Trunc(...，kind='year')。

子类都被定义为变换，但它们没有注册任何字段，因为查找名称已经被 Extract 子类保留。

使用实例：

Code

>>> from datetime import datetime
>>> from django.db.models import Count, DateTimeField
>>> from django.db.models.functions import Trunc
>>> Experiment.objects.create(start_datetime=datetime(2015, 6, 15, 14, 30, 50, 321))
>>> Experiment.objects.create(start_datetime=datetime(2015, 6, 15, 14, 40, 2, 123))
>>> Experiment.objects.create(start_datetime=datetime(2015, 12, 25, 10, 5, 27, 999))
>>> experiments_per_day = Experiment.objects.annotate(
...    start_day=Trunc('start_datetime', 'day', output_field=DateTimeField())
... ).values('start_day').annotate(experiments=Count('id'))
>>> for exp in experiments_per_day:
...     print(exp['start_day'], exp['experiments'])
...
2015-06-15 00:00:00 2
2015-12-25 00:00:00 1
>>> experiments = Experiment.objects.annotate(
...    start_day=Trunc('start_datetime', 'day', output_field=DateTimeField())
... ).filter(start_day=datetime(2015, 6, 15))
>>> for exp in experiments:
...     print(exp.start_datetime)
...
2015-06-15 14:30:50.000321
2015-06-15 14:40:02.000123

`DateField` 截断Link to this heading

class TruncYear(expression, output_field=None, tzinfo=None, is_dst=None, **extra)Link to this definition

kind = 'year'

class TruncMonth(expression, output_field=None, tzinfo=None, is_dst=None, **extra)Link to this definition

kind = 'month'

class TruncWeek(expression, output_field=None, tzinfo=None, is_dst=None, **extra)Link to this definition

截断到每周一的午夜。

kind = 'week'

class TruncQuarter(expression, output_field=None, tzinfo=None, is_dst=None, **extra)Link to this definition

kind = 'quarter'

这些在逻辑上等同于 Trunc('date_field', kind)。它们截断日期的所有部分，直至 kind，允许以较低的精度对日期进行分组或过滤。expression 可以有一个 output_field 的 DateField 或 DateTimeField。

由于 DateField 没有时间部分，只有处理日期部分的 Trunc 子类才能与 DateField 使用：

Code

>>> from datetime import datetime
>>> from django.db.models import Count
>>> from django.db.models.functions import TruncMonth, TruncYear
>>> from django.utils import timezone
>>> start1 = datetime(2014, 6, 15, 14, 30, 50, 321, tzinfo=timezone.utc)
>>> start2 = datetime(2015, 6, 15, 14, 40, 2, 123, tzinfo=timezone.utc)
>>> start3 = datetime(2015, 12, 31, 17, 5, 27, 999, tzinfo=timezone.utc)
>>> Experiment.objects.create(start_datetime=start1, start_date=start1.date())
>>> Experiment.objects.create(start_datetime=start2, start_date=start2.date())
>>> Experiment.objects.create(start_datetime=start3, start_date=start3.date())
>>> experiments_per_year = Experiment.objects.annotate(
...    year=TruncYear('start_date')).values('year').annotate(
...    experiments=Count('id'))
>>> for exp in experiments_per_year:
...     print(exp['year'], exp['experiments'])
...
2014-01-01 1
2015-01-01 2

>>> import pytz
>>> melb = pytz.timezone('Australia/Melbourne')
>>> experiments_per_month = Experiment.objects.annotate(
...    month=TruncMonth('start_datetime', tzinfo=melb)).values('month').annotate(
...    experiments=Count('id'))
>>> for exp in experiments_per_month:
...     print(exp['month'], exp['experiments'])
...
2015-06-01 00:00:00+10:00 1
2016-01-01 00:00:00+11:00 1
2014-06-01 00:00:00+10:00 1

`DateTimeField` 截断Link to this heading

class TruncDate(expression, tzinfo=None, **extra)Link to this definition

lookup_name = 'date'

output_field = DateField()

TruncDate 将 expression 投射到一个日期，而不是使用内置的 SQL truncate 函数。在 DateTimeField 上，它也被注册为 __date 的转换。

class TruncTime(expression, tzinfo=None, **extra)Link to this definition

lookup_name = 'time'

output_field = TimeField()

TruncTime 将 expression 投射到一个时间，而不是使用内置的 SQL truncate 函数。在 DateTimeField 上，它也被注册为 __time 的转换。

class TruncDay(expression, output_field=None, tzinfo=None, is_dst=None, **extra)Link to this definition

kind = 'day'

class TruncHour(expression, output_field=None, tzinfo=None, is_dst=None, **extra)Link to this definition

kind = 'hour'

class TruncMinute(expression, output_field=None, tzinfo=None, is_dst=None, **extra)Link to this definition

kind = 'minute'

class TruncSecond(expression, output_field=None, tzinfo=None, is_dst=None, **extra)Link to this definition

kind = 'second'

这些在逻辑上等同于 Trunc('datetime_field', kind)。它们截断日期的所有部分，直至 kind，并允许以较低的精度对日期时间进行分组或过滤。expression 必须有一个 output_field 的 DateTimeField。

使用实例：

Code

>>> from datetime import date, datetime
>>> from django.db.models import Count
>>> from django.db.models.functions import (
...     TruncDate, TruncDay, TruncHour, TruncMinute, TruncSecond,
... )
>>> from django.utils import timezone
>>> import pytz
>>> start1 = datetime(2014, 6, 15, 14, 30, 50, 321, tzinfo=timezone.utc)
>>> Experiment.objects.create(start_datetime=start1, start_date=start1.date())
>>> melb = pytz.timezone('Australia/Melbourne')
>>> Experiment.objects.annotate(
...     date=TruncDate('start_datetime'),
...     day=TruncDay('start_datetime', tzinfo=melb),
...     hour=TruncHour('start_datetime', tzinfo=melb),
...     minute=TruncMinute('start_datetime'),
...     second=TruncSecond('start_datetime'),
... ).values('date', 'day', 'hour', 'minute', 'second').get()
{'date': datetime.date(2014, 6, 15),
 'day': datetime.datetime(2014, 6, 16, 0, 0, tzinfo=<DstTzInfo 'Australia/Melbourne' AEST+10:00:00 STD>),
 'hour': datetime.datetime(2014, 6, 16, 0, 0, tzinfo=<DstTzInfo 'Australia/Melbourne' AEST+10:00:00 STD>),
 'minute': 'minute': datetime.datetime(2014, 6, 15, 14, 30, tzinfo=<UTC>),
 'second': datetime.datetime(2014, 6, 15, 14, 30, 50, tzinfo=<UTC>)
}

`TimeField` 截断Link to this heading

class TruncHour(expression, output_field=None, tzinfo=None, is_dst=None, **extra)

kind = 'hour'

class TruncMinute(expression, output_field=None, tzinfo=None, is_dst=None, **extra)

kind = 'minute'

class TruncSecond(expression, output_field=None, tzinfo=None, is_dst=None, **extra)

kind = 'second'

这些在逻辑上等同于 Trunc('time_field', kind)。它们截断时间的所有部分，直至 kind，这就允许以较低的精度对时间进行分组或过滤。expression 可以有一个 output_field 的 TimeField 或 DateTimeField。

由于 TimeField 没有日期部分，只有处理时间部分的 Trunc 子类可以与 TimeField 使用：

Code

>>> from datetime import datetime
>>> from django.db.models import Count, TimeField
>>> from django.db.models.functions import TruncHour
>>> from django.utils import timezone
>>> start1 = datetime(2014, 6, 15, 14, 30, 50, 321, tzinfo=timezone.utc)
>>> start2 = datetime(2014, 6, 15, 14, 40, 2, 123, tzinfo=timezone.utc)
>>> start3 = datetime(2015, 12, 31, 17, 5, 27, 999, tzinfo=timezone.utc)
>>> Experiment.objects.create(start_datetime=start1, start_time=start1.time())
>>> Experiment.objects.create(start_datetime=start2, start_time=start2.time())
>>> Experiment.objects.create(start_datetime=start3, start_time=start3.time())
>>> experiments_per_hour = Experiment.objects.annotate(
...    hour=TruncHour('start_datetime', output_field=TimeField()),
... ).values('hour').annotate(experiments=Count('id'))
>>> for exp in experiments_per_hour:
...     print(exp['hour'], exp['experiments'])
...
14:00:00 2
17:00:00 1

>>> import pytz
>>> melb = pytz.timezone('Australia/Melbourne')
>>> experiments_per_hour = Experiment.objects.annotate(
...    hour=TruncHour('start_datetime', tzinfo=melb),
... ).values('hour').annotate(experiments=Count('id'))
>>> for exp in experiments_per_hour:
...     print(exp['hour'], exp['experiments'])
...
2014-06-16 00:00:00+10:00 2
2016-01-01 04:00:00+11:00 1

数学函数Link to this heading

我们将在数学函数实例中使用以下模型：

Code

class Vector(models.Model):
    x = models.FloatField()
    y = models.FloatField()

`Abs`Link to this heading

class Abs(expression, **extra)Link to this definition

返回一个数值字段或表达式的绝对值。

使用实例：

Code

>>> from django.db.models.functions import Abs
>>> Vector.objects.create(x=-0.5, y=1.1)
>>> vector = Vector.objects.annotate(x_abs=Abs('x'), y_abs=Abs('y')).get()
>>> vector.x_abs, vector.y_abs
(0.5, 1.1)

也可以注册为变换。例如：

Code

>>> from django.db.models import FloatField
>>> from django.db.models.functions import Abs
>>> FloatField.register_lookup(Abs)
>>> # Get vectors inside the unit cube
>>> vectors = Vector.objects.filter(x__abs__lt=1, y__abs__lt=1)

`ACos`Link to this heading

class ACos(expression, **extra)Link to this definition

返回一个数值字段或表达式的余弦值。表达式的值必须在 -1 到 1 的范围内。

使用实例：

Code

>>> from django.db.models.functions import ACos
>>> Vector.objects.create(x=0.5, y=-0.9)
>>> vector = Vector.objects.annotate(x_acos=ACos('x'), y_acos=ACos('y')).get()
>>> vector.x_acos, vector.y_acos
(1.0471975511965979, 2.6905658417935308)

也可以注册为变换。例如：

Code

>>> from django.db.models import FloatField
>>> from django.db.models.functions import ACos
>>> FloatField.register_lookup(ACos)
>>> # Get vectors whose arccosine is less than 1
>>> vectors = Vector.objects.filter(x__acos__lt=1, y__acos__lt=1)

`ASin`Link to this heading

class ASin(expression, **extra)Link to this definition

返回一个数值字段或表达式的正弦值。表达式的值必须在 -1 到 1 的范围内。

使用实例：

Code

>>> from django.db.models.functions import ASin
>>> Vector.objects.create(x=0, y=1)
>>> vector = Vector.objects.annotate(x_asin=ASin('x'), y_asin=ASin('y')).get()
>>> vector.x_asin, vector.y_asin
(0.0, 1.5707963267948966)

也可以注册为变换。例如：

Code

>>> from django.db.models import FloatField
>>> from django.db.models.functions import ASin
>>> FloatField.register_lookup(ASin)
>>> # Get vectors whose arcsine is less than 1
>>> vectors = Vector.objects.filter(x__asin__lt=1, y__asin__lt=1)

`ATan`Link to this heading

class ATan(expression, **extra)Link to this definition

返回一个数值字段或表达式的正切值。

使用实例：

Code

>>> from django.db.models.functions import ATan
>>> Vector.objects.create(x=3.12, y=6.987)
>>> vector = Vector.objects.annotate(x_atan=ATan('x'), y_atan=ATan('y')).get()
>>> vector.x_atan, vector.y_atan
(1.2606282660069106, 1.428638798133829)

也可以注册为变换。例如：

Code

>>> from django.db.models import FloatField
>>> from django.db.models.functions import ATan
>>> FloatField.register_lookup(ATan)
>>> # Get vectors whose arctangent is less than 2
>>> vectors = Vector.objects.filter(x__atan__lt=2, y__atan__lt=2)

`ATan2`Link to this heading

class ATan2(expression1, expression2, **extra)Link to this definition

返回 expression1 / expression2 的正切值。

使用实例：

Code

>>> from django.db.models.functions import ATan2
>>> Vector.objects.create(x=2.5, y=1.9)
>>> vector = Vector.objects.annotate(atan2=ATan2('x', 'y')).get()
>>> vector.atan2
0.9209258773829491

`Ceil`Link to this heading

class Ceil(expression, **extra)Link to this definition

返回大于或等于一个数值字段或表达式的最小整数。

使用实例：

Code

>>> from django.db.models.functions import Ceil
>>> Vector.objects.create(x=3.12, y=7.0)
>>> vector = Vector.objects.annotate(x_ceil=Ceil('x'), y_ceil=Ceil('y')).get()
>>> vector.x_ceil, vector.y_ceil
(4.0, 7.0)

也可以注册为变换。例如：

Code

>>> from django.db.models import FloatField
>>> from django.db.models.functions import Ceil
>>> FloatField.register_lookup(Ceil)
>>> # Get vectors whose ceil is less than 10
>>> vectors = Vector.objects.filter(x__ceil__lt=10, y__ceil__lt=10)

`Cos`Link to this heading

class Cos(expression, **extra)Link to this definition

返回一个数值字段或表达式的余弦值。

使用实例：

Code

>>> from django.db.models.functions import Cos
>>> Vector.objects.create(x=-8.0, y=3.1415926)
>>> vector = Vector.objects.annotate(x_cos=Cos('x'), y_cos=Cos('y')).get()
>>> vector.x_cos, vector.y_cos
(-0.14550003380861354, -0.9999999999999986)

也可以注册为变换。例如：

Code

>>> from django.db.models import FloatField
>>> from django.db.models.functions import Cos
>>> FloatField.register_lookup(Cos)
>>> # Get vectors whose cosine is less than 0.5
>>> vectors = Vector.objects.filter(x__cos__lt=0.5, y__cos__lt=0.5)

`Cot`Link to this heading

class Cot(expression, **extra)Link to this definition

返回数值字段或表达式的正切值。

使用实例：

Code

>>> from django.db.models.functions import Cot
>>> Vector.objects.create(x=12.0, y=1.0)
>>> vector = Vector.objects.annotate(x_cot=Cot('x'), y_cot=Cot('y')).get()
>>> vector.x_cot, vector.y_cot
(-1.5726734063976826, 0.642092615934331)

也可以注册为变换。例如：

Code

>>> from django.db.models import FloatField
>>> from django.db.models.functions import Cot
>>> FloatField.register_lookup(Cot)
>>> # Get vectors whose cotangent is less than 1
>>> vectors = Vector.objects.filter(x__cot__lt=1, y__cot__lt=1)

`Degrees`Link to this heading

class Degrees(expression, **extra)Link to this definition

将数值字段或表达式从弧度转换为度。

使用实例：

Code

>>> from django.db.models.functions import Degrees
>>> Vector.objects.create(x=-1.57, y=3.14)
>>> vector = Vector.objects.annotate(x_d=Degrees('x'), y_d=Degrees('y')).get()
>>> vector.x_d, vector.y_d
(-89.95437383553924, 179.9087476710785)

也可以注册为变换。例如：

Code

>>> from django.db.models import FloatField
>>> from django.db.models.functions import Degrees
>>> FloatField.register_lookup(Degrees)
>>> # Get vectors whose degrees are less than 360
>>> vectors = Vector.objects.filter(x__degrees__lt=360, y__degrees__lt=360)

`Exp`Link to this heading

class Exp(expression, **extra)Link to this definition

返回 e （自然对数基数）的值，将其升为一个数值字段或表达式的幂。

使用实例：

Code

>>> from django.db.models.functions import Exp
>>> Vector.objects.create(x=5.4, y=-2.0)
>>> vector = Vector.objects.annotate(x_exp=Exp('x'), y_exp=Exp('y')).get()
>>> vector.x_exp, vector.y_exp
(221.40641620418717, 0.1353352832366127)

也可以注册为变换。例如：

Code

>>> from django.db.models import FloatField
>>> from django.db.models.functions import Exp
>>> FloatField.register_lookup(Exp)
>>> # Get vectors whose exp() is greater than 10
>>> vectors = Vector.objects.filter(x__exp__gt=10, y__exp__gt=10)

`Floor`Link to this heading

class Floor(expression, **extra)Link to this definition

返回不大于数值字段或表达式的最大整数值。

使用实例：

Code

>>> from django.db.models.functions import Floor
>>> Vector.objects.create(x=5.4, y=-2.3)
>>> vector = Vector.objects.annotate(x_floor=Floor('x'), y_floor=Floor('y')).get()
>>> vector.x_floor, vector.y_floor
(5.0, -3.0)

也可以注册为变换。例如：

Code

>>> from django.db.models import FloatField
>>> from django.db.models.functions import Floor
>>> FloatField.register_lookup(Floor)
>>> # Get vectors whose floor() is greater than 10
>>> vectors = Vector.objects.filter(x__floor__gt=10, y__floor__gt=10)

`Ln`Link to this heading

class Ln(expression, **extra)Link to this definition

返回一个数值字段或表达式的自然对数。

使用实例：

Code

>>> from django.db.models.functions import Ln
>>> Vector.objects.create(x=5.4, y=233.0)
>>> vector = Vector.objects.annotate(x_ln=Ln('x'), y_ln=Ln('y')).get()
>>> vector.x_ln, vector.y_ln
(1.6863989535702288, 5.4510384535657)

也可以注册为变换。例如：

Code

>>> from django.db.models import FloatField
>>> from django.db.models.functions import Ln
>>> FloatField.register_lookup(Ln)
>>> # Get vectors whose value greater than e
>>> vectors = Vector.objects.filter(x__ln__gt=1, y__ln__gt=1)

`Log`Link to this heading

class Log(expression1, expression2, **extra)Link to this definition

接受两个数字字段或表达式，并返回第一个字段的对数到第二个字段的基数。

使用实例：

Code

>>> from django.db.models.functions import Log
>>> Vector.objects.create(x=2.0, y=4.0)
>>> vector = Vector.objects.annotate(log=Log('x', 'y')).get()
>>> vector.log
2.0

`Mod`Link to this heading

class Mod(expression1, expression2, **extra)Link to this definition

接受两个数值字段或表达式，并返回第一个字段除以第二个字段的余数（模数运算）。

使用实例：

Code

>>> from django.db.models.functions import Mod
>>> Vector.objects.create(x=5.4, y=2.3)
>>> vector = Vector.objects.annotate(mod=Mod('x', 'y')).get()
>>> vector.mod
0.8

`Pi`Link to this heading

class Pi(**extra)Link to this definition

返回数学常数 π 的值。

`Power`Link to this heading

class Power(expression1, expression2, **extra)Link to this definition

接受两个数值字段或表达式，并将第一个字段的值提高到第二个字段的幂。

使用实例：

Code

>>> from django.db.models.functions import Power
>>> Vector.objects.create(x=2, y=-2)
>>> vector = Vector.objects.annotate(power=Power('x', 'y')).get()
>>> vector.power
0.25

`Radians`Link to this heading

class Radians(expression, **extra)Link to this definition

将数值字段或表达式从度数转换为弧度。

使用实例：

Code

>>> from django.db.models.functions import Radians
>>> Vector.objects.create(x=-90, y=180)
>>> vector = Vector.objects.annotate(x_r=Radians('x'), y_r=Radians('y')).get()
>>> vector.x_r, vector.y_r
(-1.5707963267948966, 3.141592653589793)

也可以注册为变换。例如：

Code

>>> from django.db.models import FloatField
>>> from django.db.models.functions import Radians
>>> FloatField.register_lookup(Radians)
>>> # Get vectors whose radians are less than 1
>>> vectors = Vector.objects.filter(x__radians__lt=1, y__radians__lt=1)

`Random`Link to this heading

class Random(**extra)Link to this definition

Returns a random value in the range 0.0 ≤ x < 1.0.

`Round`Link to this heading

class Round(expression, **extra)Link to this definition

将一个数值字段或表达式四舍五入到最接近的整数。半数值是向上还是向下取整取决于数据库。

使用实例：

Code

>>> from django.db.models.functions import Round
>>> Vector.objects.create(x=5.4, y=-2.3)
>>> vector = Vector.objects.annotate(x_r=Round('x'), y_r=Round('y')).get()
>>> vector.x_r, vector.y_r
(5.0, -2.0)

也可以注册为变换。例如：

Code

>>> from django.db.models import FloatField
>>> from django.db.models.functions import Round
>>> FloatField.register_lookup(Round)
>>> # Get vectors whose round() is less than 20
>>> vectors = Vector.objects.filter(x__round__lt=20, y__round__lt=20)

`Sign`Link to this heading

class Sign(expression, **extra)Link to this definition

返回一个数字字段或表达式的符号（-1，0，1）。

使用实例：

Code

>>> from django.db.models.functions import Sign
>>> Vector.objects.create(x=5.4, y=-2.3)
>>> vector = Vector.objects.annotate(x_sign=Sign('x'), y_sign=Sign('y')).get()
>>> vector.x_sign, vector.y_sign
(1, -1)

也可以注册为变换。例如：

Code

>>> from django.db.models import FloatField
>>> from django.db.models.functions import Sign
>>> FloatField.register_lookup(Sign)
>>> # Get vectors whose signs of components are less than 0.
>>> vectors = Vector.objects.filter(x__sign__lt=0, y__sign__lt=0)

`Sin`Link to this heading

class Sin(expression, **extra)Link to this definition

返回一个数值字段或表达式的正弦值。

使用实例：

Code

>>> from django.db.models.functions import Sin
>>> Vector.objects.create(x=5.4, y=-2.3)
>>> vector = Vector.objects.annotate(x_sin=Sin('x'), y_sin=Sin('y')).get()
>>> vector.x_sin, vector.y_sin
(-0.7727644875559871, -0.7457052121767203)

也可以注册为变换。例如：

Code

>>> from django.db.models import FloatField
>>> from django.db.models.functions import Sin
>>> FloatField.register_lookup(Sin)
>>> # Get vectors whose sin() is less than 0
>>> vectors = Vector.objects.filter(x__sin__lt=0, y__sin__lt=0)

`Sqrt`Link to this heading

class Sqrt(expression, **extra)Link to this definition

返回一个非负数值字段或表达式的平方根。

使用实例：

Code

>>> from django.db.models.functions import Sqrt
>>> Vector.objects.create(x=4.0, y=12.0)
>>> vector = Vector.objects.annotate(x_sqrt=Sqrt('x'), y_sqrt=Sqrt('y')).get()
>>> vector.x_sqrt, vector.y_sqrt
(2.0, 3.46410)

也可以注册为变换。例如：

Code

>>> from django.db.models import FloatField
>>> from django.db.models.functions import Sqrt
>>> FloatField.register_lookup(Sqrt)
>>> # Get vectors whose sqrt() is less than 5
>>> vectors = Vector.objects.filter(x__sqrt__lt=5, y__sqrt__lt=5)

`Tan`Link to this heading

class Tan(expression, **extra)Link to this definition

返回一个数值字段或表达式的正切值。

使用实例：

Code

>>> from django.db.models.functions import Tan
>>> Vector.objects.create(x=0, y=12)
>>> vector = Vector.objects.annotate(x_tan=Tan('x'), y_tan=Tan('y')).get()
>>> vector.x_tan, vector.y_tan
(0.0, -0.6358599286615808)

也可以注册为变换。例如：

Code

>>> from django.db.models import FloatField
>>> from django.db.models.functions import Tan
>>> FloatField.register_lookup(Tan)
>>> # Get vectors whose tangent is less than 0
>>> vectors = Vector.objects.filter(x__tan__lt=0, y__tan__lt=0)

文本函数Link to this heading

`Chr`Link to this heading

class Chr(expression, **extra)Link to this definition

接受一个数值字段或表达式，并将表达式的文本表示形式作为单个字符返回。它的工作原理与 Python 的 chr() 函数相同。

和 Length 一样，它也可以在 IntegerField 上作为变换注册。默认的查询名是 chr。

使用实例：

Code

>>> from django.db.models.functions import Chr
>>> Author.objects.create(name='Margaret Smith')
>>> author = Author.objects.filter(name__startswith=Chr(ord('M'))).get()
>>> print(author.name)
Margaret Smith

`Concat`Link to this heading

class Concat(*expressions, **extra)Link to this definition

接受至少两个文本字段或表达式的列表，并返回连接后的文本。每个参数必须是文本或字符类型。如果你想把一个 TextField() 和一个 CharField() 连接起来，那么一定要告诉 Django，output_field 应该是一个 TextField()。当连接一个 Value 时，也需要指定一个 output_field，如下面的例子。

这个函数永远不会有一个空的结果。在后端，如果一个空参数导致整个表达式为空，Django 会确保每个空的部分先转换成空字符串。

使用实例：

Code

>>> # Get the display name as "name (goes_by)"
>>> from django.db.models import CharField, Value as V
>>> from django.db.models.functions import Concat
>>> Author.objects.create(name='Margaret Smith', goes_by='Maggie')
>>> author = Author.objects.annotate(
...     screen_name=Concat(
...         'name', V(' ('), 'goes_by', V(')'),
...         output_field=CharField()
...     )
... ).get()
>>> print(author.screen_name)
Margaret Smith (Maggie)

`Left`Link to this heading

class Left(expression, length, **extra)Link to this definition

返回给定文本字段或表达式的第一个 length 字符。

使用实例：

Code

>>> from django.db.models.functions import Left
>>> Author.objects.create(name='Margaret Smith')
>>> author = Author.objects.annotate(first_initial=Left('name', 1)).get()
>>> print(author.first_initial)
M

`Length`Link to this heading

class Length(expression, **extra)Link to this definition

接受单个文本字段或表达式，并返回值的字符数。如果表达式为空，则长度也为空。

使用实例：

Code

>>> # Get the length of the name and goes_by fields
>>> from django.db.models.functions import Length
>>> Author.objects.create(name='Margaret Smith')
>>> author = Author.objects.annotate(
...    name_length=Length('name'),
...    goes_by_length=Length('goes_by')).get()
>>> print(author.name_length, author.goes_by_length)
(14, None)

也可以注册为变换。例如：

Code

>>> from django.db.models import CharField
>>> from django.db.models.functions import Length
>>> CharField.register_lookup(Length)
>>> # Get authors whose name is longer than 7 characters
>>> authors = Author.objects.filter(name__length__gt=7)

`Lower`Link to this heading

class Lower(expression, **extra)Link to this definition

接受单个文本字段或表达式，并返回小写表示。

它也可以像 Length 中描述的那样，作为一个变换注册。

使用实例：

Code

>>> from django.db.models.functions import Lower
>>> Author.objects.create(name='Margaret Smith')
>>> author = Author.objects.annotate(name_lower=Lower('name')).get()
>>> print(author.name_lower)
margaret smith

`LPad`Link to this heading

class LPad(expression, length, fill_text=Value(' '), **extra)Link to this definition

返回给定的文本字段或表达式的值，在左侧用 fill_text 填充，使结果是 length 字符长。默认的 fill_text 是一个空格。

使用实例：

Code

>>> from django.db.models import Value
>>> from django.db.models.functions import LPad
>>> Author.objects.create(name='John', alias='j')
>>> Author.objects.update(name=LPad('name', 8, Value('abc')))
1
>>> print(Author.objects.get(alias='j').name)
abcaJohn

`LTrim`Link to this heading

class LTrim(expression, **extra)Link to this definition

类似于 Trim，但只删除前导空格。

`MD5`Link to this heading

class MD5(expression, **extra)Link to this definition

接受单个文本字段或表达式，并返回字符串的 MD5 哈希值。

它也可以像 Length 中描述的那样，作为一个变换注册。

使用实例：

Code

>>> from django.db.models.functions import MD5
>>> Author.objects.create(name='Margaret Smith')
>>> author = Author.objects.annotate(name_md5=MD5('name')).get()
>>> print(author.name_md5)
749fb689816b2db85f5b169c2055b247

`Ord`Link to this heading

class Ord(expression, **extra)Link to this definition

接受一个文本字段或表达式，并返回该表达式第一个字符的 Unicode 码点值。它的工作原理类似于 Python 的 ord() 函数，但如果表达式超过一个字符，则不会引发异常。

也可以像 Length 中描述的那样，把它注册为一个变换。默认的查找名称是 ord。

使用实例：

Code

>>> from django.db.models.functions import Ord
>>> Author.objects.create(name='Margaret Smith')
>>> author = Author.objects.annotate(name_code_point=Ord('name')).get()
>>> print(author.name_code_point)
77

`Repeat`Link to this heading

class Repeat(expression, number, **extra)Link to this definition

返回给定文本字段或表达式重复 number 次数的值。

使用实例：

Code

>>> from django.db.models.functions import Repeat
>>> Author.objects.create(name='John', alias='j')
>>> Author.objects.update(name=Repeat('name', 3))
1
>>> print(Author.objects.get(alias='j').name)
JohnJohnJohn

`Replace`Link to this heading

class Replace(expression, text, replacement=Value(''), **extra)Link to this definition

在 expression 中用 replacement 替换所有出现的 text。默认替换文本是空字符串。函数的参数是区分大小写的。

使用实例：

Code

>>> from django.db.models import Value
>>> from django.db.models.functions import Replace
>>> Author.objects.create(name='Margaret Johnson')
>>> Author.objects.create(name='Margaret Smith')
>>> Author.objects.update(name=Replace('name', Value('Margaret'), Value('Margareth')))
2
>>> Author.objects.values('name')
<QuerySet [{'name': 'Margareth Johnson'}, {'name': 'Margareth Smith'}]>

`Reverse`Link to this heading

class Reverse(expression, **extra)Link to this definition

接受单个文本字段或表达式，并将该表达式的字符按相反顺序返回。

也可以像 Length 中描述的那样，把它注册为一个变换。默认的查询名称是 reverse。

使用实例：

Code

>>> from django.db.models.functions import Reverse
>>> Author.objects.create(name='Margaret Smith')
>>> author = Author.objects.annotate(backward=Reverse('name')).get()
>>> print(author.backward)
htimS teragraM

`Right`Link to this heading

class Right(expression, length, **extra)Link to this definition

返回给定文本字段或表达式的最后 length 字符。

使用实例：

Code

>>> from django.db.models.functions import Right
>>> Author.objects.create(name='Margaret Smith')
>>> author = Author.objects.annotate(last_letter=Right('name', 1)).get()
>>> print(author.last_letter)
h

`RPad`Link to this heading

class RPad(expression, length, fill_text=Value(' '), **extra)Link to this definition

类似于 LPad，但垫在右边。

`RTrim`Link to this heading

class RTrim(expression, **extra)Link to this definition

类似于 Trim，但只删除尾部的空格。

`SHA1`、SHA224`、SHA256`、SHA384` 和 `SHA512`。Link to this heading

class SHA1(expression, **extra)Link to this definition

class SHA224(expression, **extra)Link to this definition

class SHA256(expression, **extra)Link to this definition

class SHA384(expression, **extra)Link to this definition

class SHA512(expression, **extra)Link to this definition

接受单个文本字段或表达式，并返回字符串的特定哈希值。

它们也可以像 Length 中描述的那样注册为变换。

使用实例：

Code

>>> from django.db.models.functions import SHA1
>>> Author.objects.create(name='Margaret Smith')
>>> author = Author.objects.annotate(name_sha1=SHA1('name')).get()
>>> print(author.name_sha1)
b87efd8a6c991c390be5a68e8a7945a7851c7e5c

`StrIndex`Link to this heading

class StrIndex(string, substring, **extra)Link to this definition

返回一个正整数，对应于 string 中第一次出现的 substring 的 1 个索引位置，如果没有找到 substring，则返回 0。

使用实例：

Code

>>> from django.db.models import Value as V
>>> from django.db.models.functions import StrIndex
>>> Author.objects.create(name='Margaret Smith')
>>> Author.objects.create(name='Smith, Margaret')
>>> Author.objects.create(name='Margaret Jackson')
>>> Author.objects.filter(name='Margaret Jackson').annotate(
...     smith_index=StrIndex('name', V('Smith'))
... ).get().smith_index
0
>>> authors = Author.objects.annotate(
...    smith_index=StrIndex('name', V('Smith'))
... ).filter(smith_index__gt=0)
<QuerySet [<Author: Margaret Smith>, <Author: Smith, Margaret>]>

`Substr`Link to this heading

class Substr(expression, pos, length=None, **extra)Link to this definition

从字段或表达式的位置 pos 开始返回一个长度为 length 的子串。如果 length 是 None，那么将返回字符串的其余部分。

使用实例：

Code

>>> # Set the alias to the first 5 characters of the name as lowercase
>>> from django.db.models.functions import Lower, Substr
>>> Author.objects.create(name='Margaret Smith')
>>> Author.objects.update(alias=Lower(Substr('name', 1, 5)))
1
>>> print(Author.objects.get(name='Margaret Smith').alias)
marga

`Trim`Link to this heading

class Trim(expression, **extra)Link to this definition

返回给定的文本字段或表达式的值，并去除前导和尾部的空格。

使用实例：

Code

>>> from django.db.models.functions import Trim
>>> Author.objects.create(name='  John  ', alias='j')
>>> Author.objects.update(name=Trim('name'))
1
>>> print(Author.objects.get(alias='j').name)
John

`Upper`Link to this heading

class Upper(expression, **extra)Link to this definition

接受单个文本字段或表达式，并返回大写表示。

它也可以像 Length 中描述的那样，作为一个变换注册。

使用实例：

Code

>>> from django.db.models.functions import Upper
>>> Author.objects.create(name='Margaret Smith')
>>> author = Author.objects.annotate(name_upper=Upper('name')).get()
>>> print(author.name_upper)
MARGARET SMITH

窗口函数Link to this heading

在 Window 表达式中，有很多函数可以用来计算元素的等级或某些行的 Ntile。

`CumeDist`Link to this heading

class CumeDist(*expressions, **extra)Link to this definition

计算一个窗口或分区内的数值的累积分布。累计分布被定义为当前行之前的行数或同行行数除以框架中的总行数。

`DenseRank`Link to this heading

class DenseRank(*expressions, **extra)Link to this definition

相当于 Rank，但没有间隙。

`FirstValue`Link to this heading

class FirstValue(expression, **extra)Link to this definition

返回窗口帧第一行的值，如果没有这个值，则返回 None。

`Lag`Link to this heading

class Lag(expression, offset=1, default=None, **extra)Link to this definition

计算 offset 的偏移值，如果没有行存在，返回 default。

default 必须与 expression 具有相同的类型，但是，这只由数据库验证，而不是在 Python 中验证。

`LastValue`Link to this heading

class LastValue(expression, **extra)Link to this definition

类似于 FirstValue，它计算给定框架子句中的最后一个值。

`Lead`Link to this heading

class Lead(expression, offset=1, default=None, **extra)Link to this definition

计算给定 frame 中的前导值。offset 和 default 都是根据当前行的情况来计算的。

default 必须与 expression 具有相同的类型，但是，这只由数据库验证，而不是在 Python 中验证。

`NthValue`Link to this heading

class NthValue(expression, nth=1, **extra)Link to this definition

计算相对于窗口内偏移量 nth （必须是正值）的行。如果没有行，返回 None。

一些数据库可能会以不同的方式处理不存在的 nth-value，例如，对于基于字符的表达式，Oracle 会返回一个空字符串，而不是 None。在这些情况下，Django 不做任何转换。

`Ntile`Link to this heading

class Ntile(num_buckets=1, **extra)Link to this definition

为帧子句中的每一行计算一个分区，在 1 和 num_buckets 之间尽可能均匀地分配数字。如果行没有被平均分配到若干个桶中，则一个或多个桶将被更频繁地表示。

`PercentRank`Link to this heading

class PercentRank(*expressions, **extra)Link to this definition

计算帧子句中行的百分位数。这个计算相当于执行：

Code

(rank - 1) / (total rows - 1)

下表解释了行的百分位数的计算：

行 #	值	排名	计算	百分比排名
1	15	1	(1-1)/(7-1)	0.0000
2	20	2	(2-1)/(7-1)	0.1666
3	20	2	(2-1)/(7-1)	0.1666
4	20	2	(2-1)/(7-1)	0.1666
5	30	5	(5-1)/(7-1)	0.6666
6	30	5	(5-1)/(7-1)	0.6666
7	40	7	(7-1)/(7-1)	1.0000

`Rank`Link to this heading

class Rank(*expressions, **extra)Link to this definition

与 RowNumber 类似，该函数对窗口中的行进行排序。计算出的排名包含有空隙。使用 DenseRank 来计算没有空隙的排名。

`RowNumber`Link to this heading

class RowNumber(*expressions, **extra)Link to this definition

如果没有对窗口帧进行分区，则根据帧子句的顺序或整个查询的顺序计算行数。

数据库函数Link to this heading#

比较和转换函数Link to this heading#

CastLink to this heading#

CoalesceLink to this heading#

CollateLink to this heading#

GreatestLink to this heading#

JSONObjectLink to this heading#

LeastLink to this heading#

NullIfLink to this heading#

日期函数Link to this heading#

ExtractLink to this heading#

DateField 提取Link to this heading#

DateTimeField 提取Link to this heading#

NowLink to this heading#

TruncLink to this heading#

DateField 截断Link to this heading#

DateTimeField 截断Link to this heading#

TimeField 截断Link to this heading#

数学函数Link to this heading#

AbsLink to this heading#

ACosLink to this heading#

ASinLink to this heading#

ATanLink to this heading#

ATan2Link to this heading#

CeilLink to this heading#

CosLink to this heading#

CotLink to this heading#

DegreesLink to this heading#

ExpLink to this heading#

FloorLink to this heading#

LnLink to this heading#

LogLink to this heading#

ModLink to this heading#

PiLink to this heading#

PowerLink to this heading#

RadiansLink to this heading#

RandomLink to this heading#

RoundLink to this heading#

SignLink to this heading#

SinLink to this heading#

SqrtLink to this heading#

TanLink to this heading#

文本函数Link to this heading#

ChrLink to this heading#

ConcatLink to this heading#

LeftLink to this heading#

LengthLink to this heading#

LowerLink to this heading#

LPadLink to this heading#

LTrimLink to this heading#

MD5Link to this heading#

OrdLink to this heading#

RepeatLink to this heading#

ReplaceLink to this heading#

ReverseLink to this heading#

RightLink to this heading#

RPadLink to this heading#

RTrimLink to this heading#

SHA1、SHA224`、SHA256`、SHA384` 和 SHA512。Link to this heading#

StrIndexLink to this heading#

SubstrLink to this heading#

TrimLink to this heading#

UpperLink to this heading#

窗口函数Link to this heading#

CumeDistLink to this heading#

DenseRankLink to this heading#

FirstValueLink to this heading#

LagLink to this heading#

LastValueLink to this heading#

LeadLink to this heading#

NthValueLink to this heading#

NtileLink to this heading#

PercentRankLink to this heading#

RankLink to this heading#

RowNumberLink to this heading#

数据库函数Link to this heading

比较和转换函数Link to this heading

`Cast`Link to this heading

`Coalesce`Link to this heading

`Collate`Link to this heading

`Greatest`Link to this heading

`JSONObject`Link to this heading

`Least`Link to this heading

`NullIf`Link to this heading

日期函数Link to this heading

`Extract`Link to this heading

`DateField` 提取Link to this heading

`DateTimeField` 提取Link to this heading

`Now`Link to this heading

`Trunc`Link to this heading

`DateField` 截断Link to this heading

`DateTimeField` 截断Link to this heading

`TimeField` 截断Link to this heading

数学函数Link to this heading

`Abs`Link to this heading

`ACos`Link to this heading

`ASin`Link to this heading

`ATan`Link to this heading

`ATan2`Link to this heading

`Ceil`Link to this heading

`Cos`Link to this heading

`Cot`Link to this heading

`Degrees`Link to this heading

`Exp`Link to this heading

`Floor`Link to this heading

`Ln`Link to this heading

`Log`Link to this heading

`Mod`Link to this heading

`Pi`Link to this heading

`Power`Link to this heading

`Radians`Link to this heading

`Random`Link to this heading

`Round`Link to this heading

`Sign`Link to this heading

`Sin`Link to this heading

`Sqrt`Link to this heading

`Tan`Link to this heading

文本函数Link to this heading

`Chr`Link to this heading

`Concat`Link to this heading

`Left`Link to this heading

`Length`Link to this heading

`Lower`Link to this heading

`LPad`Link to this heading

`LTrim`Link to this heading

`MD5`Link to this heading

`Ord`Link to this heading

`Repeat`Link to this heading

`Replace`Link to this heading

`Reverse`Link to this heading

`Right`Link to this heading

`RPad`Link to this heading

`RTrim`Link to this heading

`SHA1`、SHA224`、SHA256`、SHA384` 和 `SHA512`。Link to this heading

`StrIndex`Link to this heading

`Substr`Link to this heading

`Trim`Link to this heading

`Upper`Link to this heading

窗口函数Link to this heading

`CumeDist`Link to this heading

`DenseRank`Link to this heading

`FirstValue`Link to this heading

`Lag`Link to this heading

`LastValue`Link to this heading

`Lead`Link to this heading

`NthValue`Link to this heading

`Ntile`Link to this heading

`PercentRank`Link to this heading

`Rank`Link to this heading

`RowNumber`Link to this heading