如何使用Django ORM为CurrencyHistory数据集构建透视表？

通用全数据库兼容方案 #

该方案仅访问数据库1次，所有聚合逻辑在Python侧完成，兼容所有Django支持的数据库，数据量在百万级以下时性能足够。

1. 首先一次性查询所有所需数据，使用select_related避免关联查询N+1问题，同时用TruncDate按天截断时间字段：

from django.db.models import F
from django.db.models.functions import TruncDate

# 假设Currency模型中存储币种编码（如EUR、CAD）的字段名为code，可根据实际字段名修改
history_qs = CurrencyHistory.objects.select_related("code").annotate(
    # 按天截断日期，解决DateTime带时分秒无法按天分组的问题
    record_date=TruncDate("change_rate_date"),
    currency_code=F("code__code")
).values("record_date", "currency_code", "to_usd_rate").order_by("record_date")

# 如果同一天同一币种存在多条记录，可添加聚合逻辑取对应汇率，示例取当天最新汇率：
# from django.db.models import Max
# history_qs = CurrencyHistory.objects.select_related("code").annotate(
#     record_date=TruncDate("change_rate_date"),
#     currency_code=F("code__code")
# ).values("record_date", "currency_code").annotate(
#     to_usd_rate=Max("to_usd_rate")
# ).order_by("record_date")

2. Python侧聚合生成目标结构：

result = {}
for item in history_qs:
    date_str = item["record_date"].strftime("%Y-%m-%d")
    if date_str not in result:
        result[date_str] = {"Date": date_str}
    result[date_str][item["currency_code"]] = item["to_usd_rate"]

# 转换为列表格式可直接用于前端表格渲染
final_data = list(result.values())

生成的final_data结构如下，完全符合需求：

[
    {"Date": "2021-01-01", "EUR": Decimal("0.123456"), "CAD": Decimal("0.987654")},
    {"Date": "2021-01-02", "EUR": Decimal("0.123459"), "CAD": Decimal("0.987651")}
]

PostgreSQL专属高性能方案（大数据量适用） #

如果使用PostgreSQL数据库，可直接调用数据库的crosstab函数实现行转列，聚合逻辑在数据库侧完成，适合百万级以上大表查询。
需先确保django.contrib.postgres已添加到项目的INSTALLED_APPS配置中。

from django.db import connection

# 先获取所有币种编码
currencies = list(Currency.objects.values_list("code", flat=True))
currency_cols = ", ".join([f"{curr} DECIMAL" for curr in currencies])

# 构造行转列查询SQL
query = f"""
SELECT * FROM crosstab(
    'SELECT DATE(change_rate_date) AS record_date, code_id, to_usd_rate FROM currency_history ORDER BY 1,2',
    'SELECT code FROM currency ORDER BY code'
) AS ct (record_date DATE, {currency_cols});
"""

# 执行查询并转换为目标结构
final_data = []
with connection.cursor() as cursor:
    cursor.execute(query)
    for row in cursor.fetchall():
        item = {"Date": row[0].strftime("%Y-%m-%d")}
        for idx, curr in enumerate(currencies):
            item[curr] = row[idx + 1]
        final_data.append(item)

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