postgresql - 如何找到整数的 Django ArrayField 的平均值

Question

我有以下模型:-

class companyData(models.Model):
    companyId = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
   ratings = ArrayField(models.IntegerField(), blank=True,null=True)

在数据库中我得到 arrayField 为:-

[3,4.5,2.5]

如何编写 Django ORM 查询来获取这样的平均评分:-

a = companyData.objects.filter(companyId='022c4ee5-18a6-4461-8945-dd407be3fab9').annotate(Avg('ratings'))

上面的查询给出错误如下:-

Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/home/piyush/.environments/awsd/local/lib/python2.7/site-packages/django/db/models/query.py", line 234, in __repr__
    data = list(self[:REPR_OUTPUT_SIZE + 1])
  File "/home/piyush/.environments/awsd/local/lib/python2.7/site-packages/django/db/models/query.py", line 258, in __iter__
    self._fetch_all()
  File "/home/piyush/.environments/awsd/local/lib/python2.7/site-packages/django/db/models/query.py", line 1074, in _fetch_all
    self._result_cache = list(self.iterator())
  File "/home/piyush/.environments/awsd/local/lib/python2.7/site-packages/django/db/models/query.py", line 52, in __iter__
    results = compiler.execute_sql()
  File "/home/piyush/.environments/awsd/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 848, in execute_sql
    cursor.execute(sql, params)
  File "/home/piyush/.environments/awsd/local/lib/python2.7/site-packages/django/db/backends/utils.py", line 79, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/home/piyush/.environments/awsd/local/lib/python2.7/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/home/piyush/.environments/awsd/local/lib/python2.7/site-packages/django/db/utils.py", line 95, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/home/piyush/.environments/awsd/local/lib/python2.7/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
ProgrammingError: function avg(integer[]) does not exist
LINE 1: ...mpanyapp_companyData"."ratings", AVG("compa...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Answer 1

如回答here ，您可以创建具有所需功能的自定义函数:

CREATE OR REPLACE FUNCTION avg(double precision[])
RETURNS double precision AS $$
SELECT avg(v) FROM unnest($1) g(v)
$$ LANGUAGE sql;

和用法:

t=# select avg('{1,2}'::float[]);
 avg
-----
 1.5
(1 row)

请注意，根据您的数据示例 [3,4.5,2.5]，我使用了 float[] 而不是 integer[]，而你的 ORM 出于某种原因将 ratings 视为 integer[]...

任何这对你来说都是一个可能的解决方法，而不是我猜的解决方案。