如何筛选至少有5笔交易且最新5笔交易状态均为paid的发票？

需要筛选出满足以下两个条件的ChargeOverInvoice记录：

• 该发票关联的ChargeOverTransaction总数至少为5笔
• 该发票的最新5笔交易的状态全部为'paid'

提供的Django模型定义 #

class ChargeOverInvoice(models.Model):
    id = models.CharField(db_column='Id', max_length=18, primary_key=True)
    created_date = models.DateTimeField(db_column='CreatedDate', auto_now_add=True)


class ChargeOverTransaction(models.Model):
    name = models.CharField(db_column='Name', max_length=80, verbose_name='Transaction #',
                            default=models.DEFAULTED_ON_CREATE, blank=True, null=True)
    chargeover_invoice_id = models.CharField(db_column='ChargeOver_Invoice_ID__c', max_length=255)
    customer = models.ForeignKey(Account, models.DO_NOTHING, db_column='Customer__c')
    invoice = models.ForeignKey(ChargeOverInvoice, models.DO_NOTHING, db_column='ChargeOver_Invoice__c', blank=True,
                                null=True)
    status = models.CharField(db_column='Status__c', max_length=255, verbose_name='Status')
    created_date = models.DateTimeField(db_column='CreatedDate', verbose_name='Created Date')

解决方案 #

可以通过窗口函数+子查询的组合实现需求，具体代码及说明如下：

from django.db.models import Count, Q, F
from django.db.models.functions import RowNumber
from django.db.models.window import Window

# 子查询：标记每个发票的最新5笔交易，仅保留状态为paid的记录
latest_paid_transactions = ChargeOverTransaction.objects.annotate(
    # 按发票分组，交易创建时间倒序分配行号，行号≤5即为最新5笔
    row_num=RowNumber().over(
        partition_by=F('invoice'),
        order_by=F('created_date').desc()
    )
).filter(row_num__lte=5, status='paid').values('invoice')

# 主查询：筛选符合条件的发票
valid_invoices = ChargeOverInvoice.objects.annotate(
    # 统计该发票最新5笔中状态为paid的交易数
    paid_latest_count=Count('chargeovertransaction', filter=Q(chargeovertransaction__in=latest_paid_transactions)),
    # 统计该发票的总交易数
    total_transactions=Count('chargeovertransaction')
).filter(
    total_transactions__gte=5,  # 交易总数至少5笔
    paid_latest_count=5  # 最新5笔全为paid
)

代码说明 #

1. 窗口函数RowNumber()：按发票分组，交易创建时间倒序排序，给每笔交易分配行号，行号≤5的就是该发票的最新5笔交易。
2. 子查询筛选：只保留最新5笔中状态为'paid'的交易记录，关联到对应发票。
3. 主查询验证条件：
   • paid_latest_count等于5，说明该发票的最新5笔交易全部是'paid'状态
   • total_transactions__gte=5确保发票的交易总数满足至少5笔的要求

内容的提问来源于stack exchange，提问作者Abu Sayem