代码之家  ›  专栏  ›  技术社区  ›  Florent

如何在Django中聚合多个字段?

  •  1
  • Florent  · 技术社区  · 1 年前

    我有一个丹戈模型 Trade 那家商店为几个市场交易信息。对象每5分钟加一次时间戳,我需要通过 market datetime

    考虑到性能的最佳解决方案是什么?

    正如您在下面看到的,我可以提取所需时间戳的列表,迭代和聚合数据,但恐怕这不是最有效的解决方案。

    class Trade(TimestampedModel):
        market = models.ForeignKey(Market, on_delete=models.CASCADE, null=True)
        datetime = models.DateTimeField(null=True)
        amount = models.FloatField(null=True)
        price = models.FloatField(null=True)
        trades = models.FloatField(null=True)
    

    这是我的代码:

    from django.db.models import Sum, Avg
    
    # Time order object 
    qs = Trade.objects.all().order_by("-datetime")
    
    # Extract unique timestamps
    dts = qs.values_list("datetime", flat=True).distinct()
    
    for dt in dts:
    
        cum_a = qs.filter(datetime=dt).aggregate(num_a=Sum('amount'))['num_a']
        cum_t = qs.filter(datetime=dt).aggregate(num_t=Sum('trades'))['num_t']
        avg_p = qs.filter(datetime=dt).aggregate(avg_p=Avg('price'))['avg_p']
        ....
        # Store aggregated data
    
    1 回复  |  直到 1 年前
        1
  •  1
  •   Baktybek Baiserkeev    1 年前

    试试这个:)

    from django.db.models import Sum, Avg
    
    qs = Trade.objects.values('datetime').order_by('datetime').annotate(
        cum_a=Sum('amount'), 
        cum_t=Sum('trades'), 
        avg_p=Avg('price')
    )
    

    你这样迭代:

    for row in qs:
        print(row["datetime"], row["cum_a"], row["cum_t"], row["avg_p"])
    

    在SQL方面,您的分组依据 datetime 并为每个组运行聚合。

    SELECT "datetime", SUM("amount") AS "cum_a", SUM("trades") AS "cum_t", AVG("price") AS "avg_p"
    FROM "Trade"
    GROUP BY "datetime"
    ORDER BY "datetime"
    

    如果您也想按市场分组:

    qs = Trade.objects.values('datetime', 'market').order_by('datetime', 'market') \
                  .annotate(
                      cum_a=Sum('amount'), 
                      cum_t=Sum('trades'), 
                      avg_p=Avg('price')
                  )
    
    for row in qs:
        print(row["datetime"], row["market"], row["cum_a"], row["cum_t"], row["avg_p"])