python - 如何将 Teradata QUALIFY 关键字添加到 SQLAlchemy 核心语句的末尾？

Question

我正在尝试将一些原始 Teradata SQL 重写为 SQLAlchemy Core，并且遇到了QUALIFY关键字的障碍。例如，给定以下代码：

SELECT itemid, sumprice, RANK() OVER (ORDER BY sumprice DESC)
FROM (SELECT a1.itemid, SUM(a1.sale) AS sumprice
      FROM sales AS a1
      GROUP BY a1.itemid) as t1
QUALIFY RANK() OVER (ORDER BY sumprice DESC) <= 100;

我可以将大部分内容重写如下：

from sqlalchemy import create_engine, MetaData, Table
from sqlalchemy.sql import select, func

engine = create_engine('teradatasql://user:password@server/logmech=LDAP&encrpytdata=true')
metadata = MetaData(bind=engine)

a1 = Table('sales', metadata, autoload=True, autoload_with=engine).alias('a1')

t1 = select([a1.c.itemid, func.sum(a1.c.sale).label('sumprice')]).group_by(a1.c.itemid).subquery()

sql = select([t1.c.itemid, t1.c.sumprice, func.rank().over(order_by=t1.c.sumprice.desc())])
sql = sql.select_from(t1)

如果我要打印此代码以stdout喜欢这样，例如：

print(sql.compile(engine, compile_kwargs={'literal_binds': True}))

除了最后一行之外，它会给我上面的确切查询。我怎么把那条QUALIFY线弄进去？

标签： pythonsqlalchemyteradata