计算django中组的平均值

I would like to know in Django how to get the average number of grouped elements in my table per element (without using .extra(...) if possible).

我想知道在Django中如何获得每个元素表中分组元素的平均数量(不使用.extra(…)如果可能的话)。

Concretely I have a table (DwarvesEatingCakes) consisting of Dwarves and Cakes. Each pair (dwarf, cake) is unique. I want to get the average number of cakes eaten by each dwarf.

具体来说，我有一张桌子(小矮人吃的蛋糕)，由矮人和蛋糕组成。每一对(矮子，蛋糕)都是独一无二的。我想要得到每个矮人吃的蛋糕的平均数量。

The following code does not seem to work:

下面的代码似乎不起作用:

avg_cakeeaten_dict = DwarvesEatingCakes.values('dwarf').annotate(num_cake=Count('cake')).aggregate(avg_cake_eaten=Avg('num_cake'))

Thanks for the help!

谢谢你的帮助!

2 个解决方案

#1

The answer I ended up using is the following (based on Rob's answer):

我最后使用的答案是(基于罗布的答案):

num_entries = DwarvesEatingCakes.objects.count() # get the number of entries
num_dwarves = DwarvesEatingCakes.objects.aggregate(num_dwarf=Count('dwarf', distinct=True))
return num_entries / float(num_dwarves['num_dwarf'])

Of course you also make your checks against divide by 0. There might be a standard way of doing this though since it is a relatively common place query.

当然，你也要对除以0进行校验。尽管如此，可能有一种标准的方法，因为它是一个相对常见的place查询。

#2

I'm not sure I am understanding you correctly, but I think you are wanting to compute this single number (using PostgreSQL syntax):

我不确定我是否理解正确，但我认为您需要计算这个单独的数字(使用PostgreSQL语法):

SELECT CAST(COUNT(DISTINCT cake_id) AS REAL) / COUNT(DISTINCT dwarf_id) FROM dwarves_eating_cakes

It's possible there is some way to do that in a single QuerySet if you join the table with itself (commence handwaving), but that strikes me as both inefficient and awkward. Personally, I'm inclined to issue the query directly:

如果您自己加入了这个表(开始与它握手)，那么在一个单独的QuerySet中可能有某种方法可以做到这一点，但我觉得这既低效又尴尬。我个人倾向于直接提问:

def cake_average() :
  from django.db import connection
  c = connection.cursor()
  c.execute("SELECT CAST(COUNT(DISTINCT cake_id) AS REAL) / COUNT(DISTINCT dwarf_id) FROM dwarves_eating_cakes")
  return c.fetchall()[0][0]

One advantage of this approach is that you can expand this function to compute additional statistics in the very same query.

这种方法的一个优点是可以扩展这个函数，以便在相同的查询中计算其他统计信息。

#1