英文:
How to aggregate over multiple fields in Django?
问题
我有一个名为Trade的Django模型，用于存储多个市场的交易信息。这些对象每5分钟时间戳一次，我需要按市场和日期时间进行聚合。
在性能方面，最佳解决方案是什么？
如您所见，我可以提取所需时间戳的列表，进行迭代和数据聚合，但我担心这不是最有效的解决方案。
这是我的代码：
from django.db.models import Sum, Avg# 按时间排序对象qs = Trade.objects.all().order_by("-datetime")# 提取唯一时间戳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']# 存储聚合数据
希望这对您有所帮助。
英文:
I have a Dango model Trade that store trades information for several markets. The objects are timestamped at every 5 minutes and I need to aggregate them by market and by datetime.
What is the best solution to do that with performance in mind ?
As you can see below, I could extract a list of the desired timestamps, iterate and aggregate data but I'm afraid it's not the most efficient solution.
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)
This is my code:
from django.db.models import Sum, Avg# Time order objectqs = Trade.objects.all().order_by("-datetime")# Extract unique timestampsdts = 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
从 django.db.models 中导入 Sum, Avgqs = 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"])
英文:
Try this:)
from django.db.models import Sum, Avgqs = Trade.objects.values('datetime').order_by('datetime').annotate(cum_a=Sum('amount'),cum_t=Sum('trades'),avg_p=Avg('price'))
You iterate like that:
for row in qs:print(row["datetime"], row["cum_a"], row["cum_t"], row["avg_p"])
On SQL side you are grouping by datetime and running aggregation for each group.
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"
if you want to group by market too:
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"])
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论