django中model聚合使用

原创

wx62aaee4be59a4 2022-06-17 06:42:22 ©著作权

文章标签 django 数据库类型转换 文章分类 Python 后端开发

©著作权归作者所有：来自51CTO博客作者wx62aaee4be59a4的原创作品，请联系作者获取转载授权，否则将追究法律责任

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}