SQL从表中选择特定组

Question

我有一个名为 trades 的表，如下所示:

id      trade_date      trade_price     trade_status    seller_name
1       2015-01-02      150             open            Alex
2       2015-03-04      500             close           John
3       2015-04-02      850             close           Otabek
4       2015-05-02      150             close           Alex
5       2015-06-02      100             open            Otabek
6       2015-07-02      200             open            John

我想总结 trade_price 上次(按 trade_date)trade_status 为“打开”时按 seller_name 分组'.即:

sum_trade_price     seller_name     
700                 John
950                 Otabek

seller_name 为 Alex 的行被跳过，因为最后一个 trade_status 为 'close'。虽然我可以借助nested select

获得理想的输出结果

SELECT SUM(t1.trade_price), t1.seller_name
WHERE t1.seller_name NOT IN 
                (SELECT t2.seller_name FROM trades t2 
                    WHERE t2.seller_name = t1.seller_name AND t2.trade_status = 'close' 
                    ORDER BY t2.trade_date DESC LIMIT 1)
from trades t1
group by t1.seller_name

但是执行上述查询需要超过 1 分钟(我有大约 10 万行)。还有别的办法处理吗？我正在使用 PostgreSQL。

Answer 1

我会用窗口函数来解决这个问题:

SELECT SUM(t.trade_price), t.seller_name
FROM (SELECT t.*,
             FIRST_VALUE(trade_status) OVER (PARTITION BY seller_name ORDER BY trade_date desc) as last_trade_status
      FROM trades t
     ) t
WHERE last_trade_status <> 'close;
GROUP BY t.seller_name;