外部应用于SQLAlchemy

Question

我想用SQLAlchemy编写一个类似下面的sqlserver外部apply查询。在

外汇表可能没有价格表中日期的对应行，因此我需要使用toouterapply来获取每个日期的外汇表中的最后一行。在

SELECT p.EffectiveDate, p.Close_ * FX.Rate as USD_PRICE
FROM PRICE p
OUTER APPLY (
    SELECT TOP 1 *
    FROM FX
    WHERE 
        FromCurrency = p.Currency
        AND ToCurrency = 'USD'
        AND ExRateDate <= p.EffectiveDate
    ORDER BY ExRateDate DESC
) fx

表格背景简介：

• PRICE表是一个timeseries，包含EffectiveDate、Currency和Close_u列。有效日期是主键。在
• FX表有FromCurrCode、ToCurrCode、ExRateDate和Rate列。主键是（FromCurrCode，ToCurrCode，ExRateDate）
• 我的用例是连接PRICE和FX表以获得美元价格。但是，对于价格表中给定的生效日期，FX表中可能没有该日期的行。因此，表不能直接与汇率外汇= 价格生效日期. 为了解决这个问题，我需要使用outerapply来查找FX表中最后一行外汇汇率是最接近价格生效日期在

似乎SQLAlchemy不支持外部应用表达式。我看了看Custom SQL Constructs and Compilation Extension。但我不确定如何创建外部应用程序的自定义构造。你有它的例子吗？在

我想一个解决方法是用outer join替换outer apply。如果您可以提供一个查询，可以在不使用outerapply的情况下生成相同的结果，那也可以解决我的问题。在

谢谢

Answer 1

使用Correlated Subquery这是使用OUTER JOIN的解决方案：

sq = (
    session.query(FX.id.label("last_id"))
    .filter(FX.FromCurrency == Price.Currency)
    .filter(FX.ToCurrency == 'USD')
    .filter(FX.ExRateDate <= Price.EffectiveDate)
    .order_by(FX.ExRateDate.desc())
    .order_by(FX.id.desc())  # handle duplicates just in case
    .limit(1)
    .correlate(Price)
    .as_scalar()
)

q = session.query(
    Price.EffectiveDate,
    (Price.Close_ * FX.Rate).label("USD_PRICE"),
).outerjoin(FX, FX.id == sq)