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

hgtggwj0 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(321)

我有一个名为trading\ u orders的大表，其中包含有关用户在交易市场上进行的交易的信息。每个订单都是表中一个单独的行条目，因此开始和结束订单是分开的。为每个分录捕获单个订单id，对于结束交易，一个名为linked\u order\u id的列包含相应的期初订单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            |
+-------------------------+-------------------------+---------------------+---------------------+------------------+------------------+

我的第一个想法是将初始查询 Package 在with语句中，在ids上执行某种形式的自连接，但无法确定获取所需表的逻辑。例子：

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

哪里。。。包含自联接的逻辑。
有人能帮我确定吗？

sql Join pivot select oracle

来源：https://stackoverflow.com/questions/62583992/how-to-merge-rows-with-a-pair-wise-relationship-in-sql-into-a-singular-row

1条答案

按热度按时间

zqdjd7g91#

您可以自行加入：这个想法是从打开订单（由 -1 在 linked_order_id )，然后用 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 子句从相关行中选取值，将其放入相应的列中。

赞(0）回复(0）举报 2021-07-24

我来回答

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

1条答案

相关问题

热门标签

最新问答