时间戳

首页 > 解决方案 > 提取月份和年份，然后使用 Django ORM 计数并通过分组获取平均值

python - 提取月份和年份，然后使用 Django ORM 计数并通过分组获取平均值

问题描述

我想提取年份和月份。然后我想按年、月和地区分组，然后计算行数并计算每个组的平均价格。实际上下面的 SQL 语句做了我想做的事情。那么，我怎样才能用 Django ORM 做到这一点？

SELECT district, month, year, COUNT(ilan_no) , TO_CHAR(AVG(price), '9999999999') as avg_price
FROM (
    SELECT 
    ilan_no,
    district,
    price,
    EXTRACT (MONTH FROM add_date) as month,
    EXTRACT (YEAR FROM add_date) as year     
    FROM ilan) as foo
GROUP BY district, month, year
ORDER BY year, month, district

模型.py：

class Ilan(models.Model):
    ilan_no = models.IntegerField(unique=True, blank=True, null=True)
    url = models.CharField(unique=True, max_length=255, blank=True, null=True)
    add_date= models.DateField()
    origin = models.CharField(max_length=100, blank=True, null=True)
    city = models.CharField(max_length=20, blank=True, null=True)
    district = models.CharField(max_length=30, blank=True, null=True)
    price = models.IntegerField(blank=True, null=True)

序列化程序.py：

class IlanSerializer(serializers.ModelSerializer):
    class Meta:
        model = Ilan
        fields = ['ilan_no', 'add_date', 'district', 'price']

我在下面尝试过查询集，但 value() 方法不适用于 Rest-Framework 序列化程序。

视图.py：

class IlcePriceAndSizeDistributionListView(ListAPIView):
    queryset = Ilan.objects.annotate(year=ExtractYear('add_date')).annotate(month=ExtractMonth('add_date')).values('district', 'year', 'month', 'ilan_no', 'add_date', 'price').annotate(
        ortalama_m2=Avg('m2_net')).annotate(ortalama=Avg('price')).annotate(count=Count('ilan_no')).order_by('year', 'month')
    serializer_class = IlanSerializer

标签： pythondjangodjango-rest-frameworkdjango-orm

解决方案

试试这种方式，让我知道它以后是否有效。

from django.db.models import F, Q, Avg, Subquery, OuterRef, Count, IntegerField
from django.db.models.functions import ExtractMonth, ExtractYear
q = Ilan.objects.annotate(
        month=ExtractMonth('add_date'),
        year=ExtractYear('add_date')
    ).values('district','month','year')

qsub = q.filter(
            Q(district=OuterRef('district')),
            Q(month=OuterRef('month')),
            Q(year=OuterRef('year')),
        )

qset = q.annotate(count = Subquery(
            qsub.annotate(num = Count('year')).values('num'), 
            output_field = IntegerField)
        ).annotate(avg_price = Subquery(
            qsub.annotate(avg = Avg('price').values('avg'),
            output_field = IntegerField)
        )
    ).order_by('year', 'month', 'district')

生成的sql：

SELECT "myapp_ilan"."district",
       django_date_extract('month', "myapp_ilan"."add_date") AS "month",
       django_date_extract('year', "myapp_ilan"."add_date") AS "year",

  (SELECT COUNT(django_date_extract('year', U0."add_date")) AS "num"
   FROM "myapp_ilan" U0
   WHERE (U0."district" = ("myapp_ilan"."district")
          AND django_date_extract('month', U0."add_date") = (django_date_extract('month', "myapp_ilan"."add_date"))
          AND django_date_extract('year', U0."add_date") = (django_date_extract('year', "myapp_ilan"."add_date")))
   GROUP BY U0."district",
            django_date_extract('month', U0."add_date"),
            django_date_extract('year', U0."add_date")) AS "count",

  (SELECT AVG(U0."price") AS "avg"
   FROM "myapp_ilan" U0
   WHERE (U0."district" = ("myapp_ilan"."district")
          AND django_date_extract('month', U0."add_date") = (django_date_extract('month', "myapp_ilan"."add_date"))
          AND django_date_extract('year', U0."add_date") = (django_date_extract('year', "myapp_ilan"."add_date")))
   GROUP BY U0."district",
            django_date_extract('month', U0."add_date"),
            django_date_extract('year', U0."add_date")) AS "avg_price"
FROM "myapp_ilan"
ORDER BY "year" ASC,
         "month" ASC,
         "myapp_ilan"."district" ASC

推荐阅读