如何在Django QuerySet中实现交易记录与价格数据的关联匹配（替代循环逻辑）

看起来你现在是用Python循环逐个给交易匹配价格，不仅会产生N次数据库查询（效率很低），代码也显得繁琐。咱们可以用Django的查询表达式、子查询和Coalesce来实现你要的逻辑，直接在QuerySet层面把价格字段关联到交易记录上，完全替代手动循环的操作。

核心思路 #

你的需求逻辑是：

1. 对每笔交易，根据timestamp的日期部分，优先从Price表中匹配对应fiat的价格
2. 如果Price表没有匹配结果，再从PriceTextBackup表中匹配格式为%d-%m-%Y的日期对应的价格
3. 可选：对金额为正的交易，计算price * amount的sats_price值

我们可以用Django的Subquery+OuterRef实现跨表匹配，用Coalesce实现“取第一个非空值”的逻辑，用Case/When实现金额为正的条件计算。

具体实现步骤 #

1. 导入所需的查询工具

在你的view文件顶部导入这些Django ORM工具：

from django.db.models import Subquery, OuterRef, FloatField, CharField, Value, F, Func, Coalesce, Case, When, Sum

2. 优化QuerySet，关联价格字段

在你的account视图中，替换原来的循环匹配逻辑，直接用annotate给交易集添加价格字段：

def account(request):
    logger = logging.getLogger(__name__)
    id = request.GET.get('id', -1)
    key = Keys.objects.filter(id=id).first()
    settings = Settings.objects.first()
    cur_id = settings.cur.id if settings else None

    # 基础交易查询集
    transactions = Transactions.objects.filter(public_key=id).order_by('-position')

    if transactions and cur_id:
        # 1. 构建Price表的子查询：匹配日期部分和fiat_id
        price_subquery = Subquery(
            Price.objects.filter(
                fiat_id=cur_id,
                date__date=OuterRef('timestamp__date')  # 只匹配日期部分
            ).values('price')[:1],  # 取第一个匹配的价格，和first()逻辑一致
            output_field=FloatField()
        )

        # 2. 构建PriceTextBackup的子查询：需要将交易日期格式化为%d-%m-%Y
        # 注意：数据库不同，日期格式化函数不同，以下是MySQL版本
        formatted_date = Func(
            F('timestamp'),
            Value('%d-%m-%Y'),
            function='DATE_FORMAT',
            output_field=CharField()
        )
        # 如果是PostgreSQL，替换成下面的formatted_date：
        # formatted_date = Func(F('timestamp'), Value('DD-MM-YYYY'), function='TO_CHAR', output_field=CharField())
        # 如果是SQLite，替换成：
        # formatted_date = Func(F('timestamp'), Value('%d-%m-%Y'), function='strftime', output_field=CharField())

        price_backup_subquery = Subquery(
            PriceTextBackup.objects.filter(
                fiat_id=cur_id,
                date=formatted_date
            ).values('price')[:1],
            output_field=FloatField()
        )

        # 3. 用Coalesce取第一个非空价格，同时计算sats_price（仅当amount>0时）
        transactions = transactions.annotate(
            # 优先取Price的价格，没有则取备份表的，都没有则默认0.0
            matched_price=Coalesce(price_subquery, price_backup_subquery, Value(0.0)),
            # 仅当交易金额为正时，计算price*amount
            sats_price=Case(
                When(amount__gt=0, then=F('matched_price') * F('amount')),
                default=Value(0.0),
                output_field=FloatField()
            )
        )

    # 计算平均价格（替代原来的循环求和）
    avg_price = 0.0
    transaction_counter = 0
    if transactions and cur_id:
        price_agg = transactions.filter(amount__gt=0).aggregate(
            total_sats=Sum('amount'),
            total_price=Sum('sats_price')
        )
        total_sats = price_agg.get('total_sats', 0)
        total_price = price_agg.get('total_price', 0.0)
        if total_sats > 0:
            avg_price = total_price / total_sats
        transaction_counter = total_sats

    # 其余原有逻辑（余额计算、价格API调用等）保持不变
    sats_balance = Transactions.objects.order_by('block').filter(public_key=id).last().saldo if transactions else 0

    btcprice = 0
    fiat_price = 0
    if settings:
        try:
            btcprice = util.get_price(settings.cur.short)
            fiat_price = sats_balance * util.get_sats_price(btcprice)
        except:
            pass

    dif = btcprice - avg_price if btcprice !=0 and avg_price !=0 else None

    # 上下文传递带价格字段的交易集
    context = {
        'keys': Keys.objects.filter(visible=True),
        'key': key,
        'transactionsReversed': transactions,
        'sats_balance': sats_balance,
        'settings': settings,
        'activeSite': 'account',
        'avgprice': avg_price,
        'counter': transaction_counter,
        'dif': dif,
        'fiat': fiat_price
    }

    # 原有图表逻辑可以保留，或者也可以用ORM优化，这里暂时不变
    # ...（你的图表生成代码）

    return render(request, 'account.html', context)

3. 模板中使用关联的价格字段

现在每笔交易对象都有matched_price和sats_price字段了，你可以直接在模板中使用，比如在鼠标悬浮弹窗里显示价格：

{% if transaction.amount >= 0 %}
<td onmouseover="showPopup({{ transaction.matched_price|floatformat:2 }})" onmouseout="hidePopup()" class="text-success">
    {{ transaction.fiat_CHF|floatformat:2|intcomma }}
</td>
{% else %}
<td class="text-danger">{{ transaction.fiat_CHF|floatformat:2|intcomma }}</td>
{% endif %}

如果你的showPopup需要的是sats_price值，直接替换成{{ transaction.sats_price|floatformat:2 }}即可。

关键注意事项 #

1. 数据库兼容性：日期格式化函数要根据你用的数据库调整：
   • MySQL：DATE_FORMAT
   • PostgreSQL：TO_CHAR（参数格式为DD-MM-YYYY）
   • SQLite：strftime（参数格式为%d-%m-%Y）
2. 索引优化：为了加快价格查询速度，给Price和PriceTextBackup表添加联合索引：

   # 在Price模型中添加索引
   class Price(models.Model):
       date = models.DateTimeField()
       price = models.FloatField()
       fiat = models.ForeignKey(Currencies, on_delete=models.DO_NOTHING)
   
       class Meta:
           indexes = [
               models.Index(fields=['fiat', 'date']),
           ]
   
   # 在PriceTextBackup模型中添加索引
   class PriceTextBackup(models.Model):
       date = models.CharField(max_length=20)
       price = models.FloatField()
       fiat = models.ForeignKey(Currencies, on_delete=models.DO_NOTHING)
   
       class Meta:
           indexes = [
               models.Index(fields=['fiat', 'date']),
           ]
   

3. 空值处理：Coalesce的第三个参数Value(0.0)确保即使没有匹配到价格，matched_price也不会是None，避免模板或计算时出错。

内容来源于stack exchange