如何用单条SQL查询获取每个ticker的最近40条Trades记录中buy交易数？

需求：从Trades表中获取每个ticker的最近40条记录，统计其中trade为buy的交易数量。当前用Python循环查询可实现，但查询次数过多，希望用单条SQL完成。

现有单ticker查询SQL #

SELECT ticker, count(ticker)
FROM (SELECT * from Trades WHERE ticker = '<this_will_be_replaced>' ORDER BY timestamp DESC LIMIT 40)
WHERE trade = 'buy'
GROUP BY ticker

尝试的错误方案及问题 #

你尝试的内连接SQL无法达到预期，因为该SQL是先将Trades和Tickers表连接后，对整个结果集取前40条记录，而非针对每个ticker分别取最近40条：

SELECT ticker, count(ticker)
FROM (SELECT * from Trades tr INNER JOIN Tickers tk ON tk.ticker = tr.ticker ORDER BY tr.timestamp DESC LIMIT 40)
WHERE trade = 'buy'
GROUP BY ticker

正确的单条SQL方案 #

使用窗口函数ROW_NUMBER()按ticker分组、时间倒序给每条记录编号，筛选出每个ticker的前40条记录后，再统计买入交易数量：

SELECT 
    t.ticker,
    COUNT(CASE WHEN t.trade = 'buy' THEN 1 END) AS buy_count
FROM (
    SELECT 
        ticker,
        trade,
        ROW_NUMBER() OVER (PARTITION BY ticker ORDER BY timestamp DESC) AS rn
    FROM Trades
) t
WHERE t.rn <= 40
GROUP BY t.ticker;

如果需要确保所有Tickers表中的ticker都被返回（即使没有交易记录，买入次数显示为0），可以左连接Tickers表：

SELECT 
    tk.ticker,
    COUNT(CASE WHEN tr.trade = 'buy' THEN 1 END) AS buy_count
FROM Tickers tk
LEFT JOIN (
    SELECT 
        ticker,
        trade,
        ROW_NUMBER() OVER (PARTITION BY ticker ORDER BY timestamp DESC) AS rn
    FROM Trades
) tr ON tk.ticker = tr.ticker AND tr.rn <= 40
GROUP BY tk.ticker;

逻辑说明 #

1. 内层子查询通过PARTITION BY ticker按交易对分组，ORDER BY timestamp DESC按时间倒序排序，用ROW_NUMBER()给每组内的记录生成序号rn，序号1对应最新的交易。
2. 外层筛选出rn <=40的记录，即每个ticker的最近40条交易。
3. 最后通过COUNT(CASE WHEN ...)统计每组中trade='buy'的记录数量，按ticker分组返回结果。

示例数据验证 #

针对提供的Trades示例数据：

执行第一个SQL会返回：

执行左连接版本的SQL会返回：

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