在Django ORM中：从给定属性值最大的每组中选择记录

Question

假设我有三个模型，代表同一家公司的几个零售点销售的商品价格，如下所示：

class Store(models.Model):
    name = models.CharField(max_length=256)
    address = models.TextField()

class Product(models.Model):
    name = models.CharField(max_length=256)
    description = models.TextField()

class Price(models.Model):
    store = models.ForeignKey(Store)
    product = models.ForeignKey(Product)
    effective_date = models.DateField()
    value = models.FloatField()

当一个价格被设定时，它是根据特定的商店和产品来设定的。也就是说，同一件商品在不同的商店可以有不同的价格。每种价格都有一个生效日期。对于给定的store和给定的product，当前有效价格是具有最新effective_date的价格。你知道吗

写查询返回所有商店中所有商品的当前有效价格的最佳方法是什么？你知道吗

如果我使用Pandas，我会得到一个带有列['store', 'product', 'effective_date', 'price']的数据帧，然后运行

dataframe\
    .sort_values(columns=['store', 'product', 'effective_date'], ascending=[True, True, False])\
    .groupby('store', 'product')['price'].first()

但必须有某种方法可以直接在数据库级别上实现这一点。思想？你知道吗

Answer 1

如果您的DBMS是PostgreSQL，您可以使用distinct和order\u by组合，方法如下：

Price.objects.order_by('store','product','-effective_date').distinct('store','product')

它将为您提供所有产品/商店组合的最新价格。你知道吗

关于独特的有一些技巧，看看这里的文档：https://docs.djangoproject.com/en/1.9/ref/models/querysets/#django.db.models.query.QuerySet.distinct

Answer 2

如果您使用的是PostgreSQL，那么可以使用^{}和^{}获得所有商店中所有产品的当前有效价格，如下所示：

prices = Price.objects.order_by('store', 'product', '-effective_date')
                      .distinct('store', 'product')

现在，这非常类似于Pandas。你知道吗

请注意，在distinct中使用字段名只能在PostgreSQL中使用。一旦您根据store、product和effective date的降序对价格进行排序，distinct('store', 'product')将只保留每个商店产品对的第一个条目，这将是您的当前条目和最新价格。你知道吗

---

不是PostgreSQL数据库：

如果不使用PostgreSQL，可以通过两个查询来完成：

首先，我们得到所有store-product组的最新生效日期：

latest_effective_dates = Price.objects.values('store_id', 'product_id')
                             .annotate(led=Max('effective_date')).values('led')

一旦我们有了这些日期，我们可以得到这个日期的价格：

prices = Price.objects.filter(effective_date__in=latest_effective_dates)

免责声明：这假设for noeffective_date对于任何store-product组都是相同的。你知道吗

Answer 3

如果没有Postgres的附加功能（您应该真正使用它），有一个更复杂的解决方案（基于ryanpitts' idea），它需要两个db命中：

latest_set = Price.objects
    .values('store_id', 'product_id')  # important to have values before annotate ...
    .annotate(max_date=Max('effective_date')).order_by()
    # ... to annotate for the grouping that results from values

# Build a query that reverse-engineers the Price records that contributed to 
# 'latest_set'. (Relying on the fact that there are not 2 Prices
# for the same product-store with an identical date)

q_statement = Q(product_id=-1)  # sth. that results in empty qs
for latest_dict in latest_set:          
    q_statement |= 
        (Q(product_id=latest_dict['product_id']) & 
         Q(store_id=latest_dict['store_id']) & 
         Q(effective_date=latest_dict['max_date']))

Price.objects.filter(q_statement)