django中model聚合使用

from django.db.models.functions import Cast, Coalesce, Concat, ConcatPair, Greatest

# Cast，类型转换
q1 = TradeDetail.objects.values("profit", "name").annotate( profit=F("money") * F(Cast('direction', FloatField())))

# Coalesce，从前向后，查询第一个不为空的值
q2 = TradeDetail.objects.annotate(c=Coalesce('name', 'money'))

# Concat拼接
TradeDetail.objects.update(name=Concat('name', 'money'))

# ConcatPair,拼接(仅两个参数)
TradeDetail.objects.annotate(c=ConcatPair('name', 'money'))

# Greatest, 获取比较大的值; least获取比较小的值
TradeDetail.objects.annotate(c=Greatest('id', 'money',output_field=FloatField()))

# Length获取长度
TradeDetail.objects.annotate(c=Length('name'))

# Lower,Upper，变大小写
TradeDetail.objects.annotate(c=Lower('name'))
TradeDetail.objects.annotate(c=Upper('name'))

# Now。获取当前时间
TradeDetail.objects.annotate(c=Now())

# Extract将数据库时间转换成需要的格式
>>> from datetime import datetime
>>> from django.utils import timezone
>>> from django.db.models.functions import (
ExtractDay, ExtractHour, ExtractMinute, ExtractMonth,
ExtractQuarter, ExtractSecond, ExtractWeek, ExtractWeekDay,
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'),
hour=ExtractHour('start_datetime'),
minute=ExtractMinute('start_datetime'),
second=ExtractSecond('start_datetime'),
).values(
'year', 'isoyear', 'month', 'week', 'day',
'weekday', 'hour', 'minute', 'second',
).get(end_datetime__year=ExtractYear('start_datetime'))
{'year': 2015, 'isoyear': 2015, 'quarter': 2, 'month': 6, 'week': 25,
'day': 15, 'weekday': 2, 'hour': 23, 'minute': 30, 'second': 1}
如果USE_TZ是True那么日期时间存储在UTC数据库。如果Django中的其他时区处于活动状态，则在提取值之前将datetime转换为该时区。下面的示例将转换为墨尔本时区（UTC +10：00），该时区将更改返回的星期几，星期几和小时数：
>>> import pytz
>>> melb = pytz.timezone('Australia/Melbourne')
>>> Experiment.objects.annotate(
day=ExtractDay('start_datetime', tzinfo=melb),
weekday=ExtractWeekDay('start_datetime', tzinfo=melb),
hour=ExtractHour('start_datetime', tzinfo=melb),
).values('day', 'weekday', 'hour').get(
end_datetime__year=ExtractYear('start_datetime'),
)
{'day': 16, 'weekday': 3, 'hour': 9}

-------------------------------------------

个性签名：代码过万，键盘敲烂！！！

如果觉得这篇文章对你有小小的帮助的话，记得“推荐”哦，博主在此感谢！