时间戳

首页 > 解决方案 > 树表的 Django 复杂分区和排序

python - 树表的 Django 复杂分区和排序

问题描述

我使用 Django 和 PostgreSQL 作为我的后端堆栈的应用程序，其主要功能是一个复杂的多级表，显示不同产品的带注释的时间序列数据。

所以基本上我定义了 2 个模型，一个是Product另一个是Timestamp，而 Product Model 是分层的，并使用MPTT库来实现树结构。每个产品可以有多个孩子，最多有4 个深度级别，并有多个时间戳，描述它们在特定日期的表现数据。因此，我基本上是在为查询集中的每个产品注释所有时间序列数据，并且可以完美运行。我面临的问题是我需要通过注释值动态订购产品，而不会破坏它们在层次结构中的位置和/或亲子关系。当我使用一些基本的 order_by("tree_id", "level" ...)之类的方法时，父子关系会被 'overwritten'。同样重要的是，每个 Product-root都有自己的 tree_id 并且是单独的 tree。

模型.py

class Product(MPTTModel):
    owner = models.ForeignKey(Profile, on_delete=models.CASCADE, null=True)
    budget = models.FloatField(null=True)
    creation_date = models.CharField(max_length=35, null=True)
    product_type = models.CharField(max_length=35, null=True)
    last_updated = models.DateTimeField(null=True)
    name = models.CharField(max_length=35, null=True)
    parent = parent = TreeForeignKey('self', on_delete=models.CASCADE,
                            null=True, blank=True)

class Timestamp(models.Model):
    product = models.ForeignKey(Product, on_delete=models.CASCADE, null=True)
    viewed = models.IntegerField(default=0)
    bought = models.IntegerField(default=0)
    shown = models.IntegerField(default=0)
    date = models.DateField(null=True)

下面可以看到一个非常基本的表格外观模型。在此处输入图像描述

我当前的查询集排序功能无法正常工作，可以在这里看到

  filters = self.request.GET
  d_start, d_end = filters["date_start"], filters["date_end"]
  

  qs=Product._tree_manager.get_queryset_descendants(Product.objects.filter(
     Exists(Timestamp.objects.filter(
         date__gte=d_start,
         date__lte=d_end,
         product=OuterRef("pk"))
         ), level=0), include_self=True).annotate(
            viewed=(Sum("timestamp__viewed")),
            bought=(Sum("timestamp__bought")),
            shown=(Sum("timestamp__shown"))).order_by("tree_id", "level", "bought")

其结果如下所示： 在此处输入图像描述

我怀疑该解决方案将需要一些Raw SQL，也许是Partitioning与 ordering 相结合。

我期待着您的回答，并在此先感谢您。

标签： pythonsqldjangodatabasepostgresql

解决方案

postgreSQL 的递归 CTE 正是我所需要的。

Django 的 ORM 非常可靠和健壮，但它也有其局限性。复杂的、依赖于体系结构的功能（如递归查询）无法使用 ORM 实现——至少在我写这个答案的时候是这样。

因此，这是一个需要我编写原始 SQL 的场景。我不能在此处包含 SQL 代码，因为它包含合理的后端信息和逻辑。

但是，如果您必须在 Django 中处理分层模型/查询，我可以告诉您一些事情。

  • 如果性能是重中之重，请勿使用Mptt或TreeBeard等库。它们都非常慢，并且在我的场景中不适合生产使用，因为加载时间较长，用户体验会受到极大的影响。

  • 不要因为 Django 提供 ORM 而犹豫使用Raw-SQL 查询。迟早你会遇到 ORM 无法解决的问题，因为它的抽象级别很高。

  • 不要浪费你宝贵的时间试图重新发明轮子。

  • 如果您出于某种原因必须使用关系数据库来解决此问题，请使用PostgreSQL，因为它提供了解决此问题的最佳性能、功能和扩展（我个人认为 PostgreSQL 是迄今为止最好的 SQL-DB atm .)

  • 使用ltree扩展进行查询。它非常直观且易于理解，您可以在完整的postgres-docs中阅读它。

  • 如果您必须在不破坏层次结构的情况下订购兄弟姐妹，请使用Array与ROW OVER组合。

当我从Mptt迁移到CTEs时，整体性能在我的场景中快了大约 10 倍。

有用的链接：

推荐阅读