加速 Django 数据库函数，用于缺失值的地理插值

Question

我有一个庞大的商业地产地址数据库(大约500万行)，其中20万行缺少建筑面积。这些房产按行业分类，我知道每个房产的租金。

我插入缺失楼层面积的方法是过滤物业指定半径内建筑面积未知的类似分类的房产，然后根据附近房产的成本/m2 的中位数计算建筑面积。

最初，我使用熊猫来解决这个问题，但随着数据集变大(甚至使用group_by)，这已经成为问题。它通常会超出可用内存并停止。当它工作时，大约需要 3 小时才能完成。

我正在测试是否可以在数据库中执行相同的任务。我为径向填充编写的函数如下：

def _radial_fill(self):
# Initial query selecting all latest locations, and excluding null rental valuations
q = Location.objects.order_by("locode","-update_cycle") 
.distinct("locode")
# Chained Q objects to use in filter
f = Q(rental_valuation__isnull=False) & 
Q(use_category__grouped_by__isnull=False) & 
Q(pc__isnull=False)
# All property categories at subgroup level
for c in LocationCategory.objects.filter(use_category="SGP").all():
# Start looking for appropriate interpolation locations
fc = f & Q(use_category__grouped_by=c)
for l in q.filter(fc & Q(floor_area__isnull=True)).all():
r_degree = 0
while True:
# Default Distance is metres, so multiply accordingly
r = (constants.BOUNDS**r_degree)*1000 # metres
ql = q.annotate(distance=Distance("pc__point", l.pc.point)) 
.filter(fc & Q(floor_area__isnull=False) & Q(distance__lte=r)) 
.values("rental_valuation", "floor_area")
if len(ql) < constants.LOWER_RANGE:
if r > constants.UPPER_RADIUS*1000:
# Further than the longest possible distance
break
r_degree += 1
else:
m = median([x["rental_valuation"]/x["floor_area"]
for x in ql if x["floor_area"] > 0.0])
l.floor_area = l.rental_valuation / m
l.save()
break

我的问题是这个函数需要 6 天才能运行。一定有更快的方法，对吧？我确定我做错了什么...

模型如下：

class LocationCategory(models.Model):
# Category types
GRP = "GRP"
SGP = "SGP"
UST = "UST"
CATEGORIES = (
(GRP, "Group"),
(SGP, "Sub-group"),
(UST, "Use type"),
)
slug = models.CharField(max_length=24, primary_key=True, unique=True)
usecode = models.CharField(max_length=14, db_index=True)
use_category = models.CharField(max_length=3, choices=CATEGORIES,
db_index=True, default=UST)
grouped_by = models.ForeignKey("self", null=True, blank=True,
on_delete=models.SET_NULL,
related_name="category_by_group")
class Location(models.Model):
# Hereditament identity and location
slug = models.CharField(max_length=24, db_index=True)
locode = models.CharField(max_length=14, db_index=True)
pc = models.ForeignKey(Postcode, null=True, blank=True,
on_delete=models.SET_NULL,
related_name="locations_by_pc")
use_category = models.ForeignKey(LocationCategory, null=True, blank=True,
on_delete=models.SET_NULL,
related_name="locations_by_category")
# History fields
update_cycle = models.CharField(max_length=14, db_index=True)
# Location-specific econometric data
floor_area = models.FloatField(blank=True, null=True)
rental_valuation = models.FloatField(blank=True, null=True)
class Postcode(models.Model):
pc = models.CharField(max_length=7, primary_key=True, unique=True) # Postcode excl space
pcs = models.CharField(max_length=8, unique=True)                  # Postcode incl space
# http://spatialreference.org/ref/epsg/osgb-1936-british-national-grid/
point = models.PointField(srid=4326)

使用 Django 2.0 和 Postgresql 10

更新

通过以下代码更改，我在运行时提高了 35%：

# Initial query selecting all latest locations, and excluding null rental valuations
q = Location.objects.order_by("slug","-update_cycle") 
.distinct("slug")
# Chained Q objects to use in filter
f = Q(rental_valuation__isnull=False) & 
Q(pc__isnull=False) & 
Q(use_category__grouped_by_id=category_id)
# All property categories at subgroup level
# Start looking for appropriate interpolation locations
for l in q.filter(f & Q(floor_area__isnull=True)).all().iterator():
r = q.filter(f & Q(floor_area__isnull=False) & ~Q(floor_area=0.0))
rl = Location.objects.filter(id__in = r).annotate(distance=D("pc__point", l.pc.point)) 
.order_by("distance")[:constants.LOWER_RANGE] 
.annotate(floor_ratio = F("rental_valuation")/
F("floor_area")) 
.values("floor_ratio")
if len(rl) == constants.LOWER_RANGE:
m = median([h["floor_ratio"] for h in rl])
l.floor_area = l.rental_valuation / m
l.save()

id__in=r效率低下，但这似乎是在添加和排序新批注时维护distinct查询集的唯一方法。鉴于在r查询中可以返回大约 100,000 行，因此在那里应用的任何注释以及随后按距离排序都可能需要很长时间。

然而。。。我在尝试实现子查询功能时遇到了许多问题。AttributeError: 'ResolvedOuterRef' object has no attribute '_output_field_or_none'我认为这与注释有关，但我找不到太多内容。

相关的重构代码是：

rl = Location.objects.filter(id__in = r).annotate(distance=D("pc__point", OuterRef('pc__point'))) 
.order_by("distance")[:constants.LOWER_RANGE] 
.annotate(floor_ratio = F("rental_valuation")/
F("floor_area")) 
.distinct("floor_ratio")

和：

l.update(floor_area= F("rental_valuation") / CustomAVG(Subquery(locs),0))

我可以看到这种方法应该非常有效，但正确处理似乎远远超出了我的技能水平。

Answer 1

你可以使用(大部分)优化的 Django 内置查询方法来简化你的方法。更具体地说，我们将使用：

• Subquery和OuterRef方法(对于版本>= 1.11)。
• 来自 Django 聚合的annotation和AVG。
• dwithin查找。
• F()表达式(F()的详细用例可以在我的 QA 样式示例中找到：如何在 django 中的模型字段之间执行算术运算

---

我们将创建一个自定义的 Aggregate 类来应用我们的AVG函数(方法的灵感来自这个优秀的答案：Django 1.11 注释子查询聚合)

class CustomAVG(Subquery):
template = "(SELECT AVG(area_value) FROM (%(subquery)s))"
output_field = models.FloatField()

我们将使用它来计算以下平均值：

for location in Location.objects.filter(rental_valuation__isnull=True):
location.update(
rental_valuation=CustomAVG(
Subquery(
Location.objects.filter(
pc__point__dwithin=(OuterRef('pc__point'), D(m=1000)),
rental_valuation__isnull=False
).annotate(area_value=F('rental_valuation')/F('floor_area'))
.distinct('area_value')
)
)
)

以上细分：

• 我们收集所有Location对象而不rental_valuation并"传递"列表。
• 子查询：我们选择从当前位置点开始的radius=1000m圈内的Location对象(根据需要进行更改)，并在它们上annotate成本/m2 计算(使用F()获取每个对象的列rental_valuation和floor_area的值)，作为名为area_value的列。为了获得更准确的结果，我们仅选择此列的不同值。
• 我们将CustomAVG应用于Subquery，并更新当前位置rental_valuation。