如何将 SQL 中具有成对关系的行合并为单个行

Question

我有一个名为trading_orders的大表，其中包含有关用户在交易市场上进行的交易的信息。每个订单都是表中的单独行条目，因此开仓和平仓订单是分开的。为每个条目捕获单独的订单ID，对于平仓交易，名为linked_order_id的列包含相应的开仓order_id。如果订单是开仓订单，则此列包含负 1。

我希望每对未平仓和平仓订单都显示在同一行上，每列都以交易类型(开仓/平仓(为前缀

从初始表中，我可以得到下表：

+------------+-----------------+-------------+-----------+-----------------+
| ORDER_TYPE | ORDER_DIRECTION | TRADE_PRICE | ORDER_ID  | LINKED_ORDER_ID |
+------------+-----------------+-------------+-----------+-----------------+
| closing    | buy             |      1.1234 | O1232     | O2789           |
| opening    | sell            |      1.1239 | O2789     | -1              |
| closing    | sell            |       345.9 | O3841     | O1270           |
| closing    | sell            |       12.55 | O1284     | O8923           |
| opening    | buy             |       345.8 | O1270     | -1              |
| opening    | buy             |       12.12 | O8923     | -1              |
+------------+-----------------+-------------+-----------+-----------------+
using this query
SELECT
ORDER_TYPE,
ORDER_DIRECTION,
TRADE_PRICE,
ORDER_ID,
LINKED_ORDER_ID
FROM
trading_orders

从这一点开始，我想使用ORDER_ID和LINKED_ORDER_ID之间的关系将订单配对到一个表中，例如：

+-------------------------+-------------------------+---------------------+---------------------+------------------+------------------+
| OPENING_ORDER_DIRECTION | CLOSING_ORDER_DIRECTION | OPENING_TRADE_PRICE | CLOSING_TRADE_PRICE | OPENING_ORDER_ID | CLOSING_ORDER_ID |
+-------------------------+-------------------------+---------------------+---------------------+------------------+------------------+
| buy                     | sell                    |               12.12 |               12.55 | O8923            | O1284            |
| buy                     | sell                    |               345.8 |               345.9 | O1270            | O3841            |
| sell                    | buy                     |              1.1239 |              1.1234 | O2789            | O1232            |
+-------------------------+-------------------------+---------------------+---------------------+------------------+------------------+

我的第一个想法是将初始查询包装在 WITH 语句中，在 id 上进行某种形式的自连接，但无法确定获取我想要的表的逻辑。 例：

WITH temp_table AS(
SELECT
ORDER_TYPE,
ORDER_DIRECTION,
TRADE_PRICE,
ORDER_ID,
LINKED_ORDER_ID
FROM
trading_orders)
SELECT
...

哪里。。。包含自联接的逻辑。

谁能帮我确定这一点？

Answer 1

您可以自行加入：这个想法是从开仓订单开始(由linked_order_id中的-1标识(，然后带上相应的平仓订单，并带有join：

select
o.order_direction   opening_order_direction,
c.order_direction   closing_order_direction,
o.trade_price       opening_trade_price,
c.trade_price       closing_trade_price,
o.order_id          opening_order_id,
c.order_id          closing_order_id
from trading_orders o
left join trading_orders c 
on c.linked_order_id = o.order_id
where o.linked_order_id = -1

另一种解决方案是条件聚合：

select
max(case when linked_order_id =  -1 then order_direction end) opening_order_direction,
max(case when linked_order_id <> -1 then order_direction end) closing_order_direction,
max(case when linked_order_id =  -1 then trade_price     end) opening_trade_price,
max(case when linked_order_id <> -1 then trade_price     end) closing_trade_price,
max(case when linked_order_id =  -1 then order_id        end) opening_order_id,
max(case when linked_order_id <> -1 then order_id        end) closing_order_id
from trading_orders
group by case when linked_order_id = -1 then order_id else linked_order_id end

group by子句动态决定应使用哪一列;然后select子句从相关行中选取值以将其放入相应的列中。