如何将复杂SQL转换为Django模型查询？股票分析应用开发求助

你好！结合你给出的Stock和StockHistory模型（我先把模型补全方便后续示例）：

class Stock(models.Model):
    symbol = models.CharField(db_index=True, max_length=5, null=False, editable=False, unique=True)

class StockHistory(models.Model):
    stock = models.ForeignKey(Stock, related_name='history', editable=False, on_delete=models.CASCADE)
    trading_date = models.DateField(db_index=True, null=False, editable=False)
    close = models.DecimalField(max_digits=12, decimal_places=2, db_index=True, null=False)

我整理了几种常见的复杂SQL场景，以及对应的Django ORM实现方式，你可以参考适配自己的需求：

1. 关联查询：获取某只股票的所有历史交易记录 #

对应的SQL #

SELECT sh.*
FROM stock_history sh
JOIN stock s ON sh.stock_id = s.id
WHERE s.symbol = 'AAPL';

Django ORM实现 #

# 方式1：通过Stock对象关联查询
stock = Stock.objects.get(symbol='AAPL')
history_records = stock.history.all()

# 方式2：直接从StockHistory过滤
history_records = StockHistory.objects.filter(stock__symbol='AAPL')

2. 聚合查询：计算某只股票的平均收盘价、最高收盘价 #

对应的SQL #

SELECT AVG(sh.close) as avg_close, MAX(sh.close) as max_close
FROM stock_history sh
JOIN stock s ON sh.stock_id = s.id
WHERE s.symbol = 'AAPL';

Django ORM实现 #

from django.db.models import Avg, Max

stats = StockHistory.objects.filter(stock__symbol='AAPL').aggregate(
    avg_close=Avg('close'),
    max_close=Max('close')
)
# stats会是一个字典：{'avg_close': Decimal('xxx'), 'max_close': Decimal('xxx')}

3. 过滤+聚合：获取最近30天收盘价高于自身平均收盘价的股票记录 #

对应的SQL #

SELECT sh.*
FROM stock_history sh
JOIN (
    SELECT stock_id, AVG(close) as avg_close
    FROM stock_history
    WHERE trading_date >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY stock_id
) sub ON sh.stock_id = sub.stock_id
WHERE sh.trading_date >= CURRENT_DATE - INTERVAL '30 days'
AND sh.close > sub.avg_close;

Django ORM实现 #

from django.db.models import Avg, F
from datetime import datetime, timedelta

thirty_days_ago = datetime.today() - timedelta(days=30)

# 子查询计算每只股票近30天的平均收盘价
subquery = StockHistory.objects.filter(
    trading_date__gte=thirty_days_ago,
    stock_id=F('stock__id')
).values('stock_id').annotate(avg_close=Avg('close')).values('avg_close')

# 主查询过滤出收盘价高于自身平均的记录
filtered_records = StockHistory.objects.filter(
    trading_date__gte=thirty_days_ago,
    close__gt=subquery
)

4. 分组查询：按交易日期分组，计算每日所有股票的平均收盘价 #

对应的SQL #

SELECT sh.trading_date, AVG(sh.close) as daily_avg_close
FROM stock_history sh
GROUP BY sh.trading_date
ORDER BY sh.trading_date DESC;

Django ORM实现 #

from django.db.models import Avg

daily_avg = StockHistory.objects.values('trading_date').annotate(
    daily_avg_close=Avg('close')
).order_by('-trading_date')

5. 窗口函数：计算每只股票的5日移动平均线（复杂场景） #

对应的SQL #

SELECT 
    s.symbol,
    sh.trading_date,
    sh.close,
    AVG(sh.close) OVER (
        PARTITION BY sh.stock_id 
        ORDER BY sh.trading_date 
        ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
    ) as ma5
FROM stock_history sh
JOIN stock s ON sh.stock_id = s.id
ORDER BY sh.stock_id, sh.trading_date;

Django ORM实现 #

from django.db.models import Window, Avg
from django.db.models.functions import RowRange

ma5_records = StockHistory.objects.select_related('stock').annotate(
    ma5=Window(
        expression=Avg('close'),
        partition_by=['stock_id'],
        order_by=F('trading_date').asc(),
        frame=RowRange(start=-4, end=0)
    )
).values('stock__symbol', 'trading_date', 'close', 'ma5').order_by('stock_id', 'trading_date')

如果有特定的复杂SQL需要转换，你可以把具体的SQL语句贴出来，我再帮你转成对应的ORM写法~

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