postgresql - 复杂匹配并加入 PostgreSQL

Question

我有以下 3 个表:打开交易、关闭交易和另一个带有价格(或报价)的交易。

开闭互为镜像。如果一个是买入，另一个是卖出。它们由相同的 txn_id 匹配。

INSERT INTO opening_txns (txn_id,txn_timestamp,cust_txn_type,exch_txn_type,currency,amount) VALUES 
('0001','2019-01-16 09:00:00.000','SELL','BUY','Euro',1000)
,('0002','2019-01-25 09:00:00.000','BUY','SELL','Euro',1000)
,('0003','2019-01-30 09:00:00.000','BUY','SELL','Euro',1000)
,('0004','2019-02-06 09:00:00.000','SELL','BUY','Euro',1000)
,('0005','2019-02-12 09:00:00.000','SELL','BUY','Euro',1000)
,('0006','2019-02-25 09:00:00.000','BUY','SELL','Euro',1000)
,('0007','2019-03-21 09:00:00.000','BUY','SELL','Euro',1000)
;

INSERT INTO closing_txns (txn_id,txn_timestamp,cust_txn_type,exch_txn_type,currency,amount) VALUES 
('0001','2019-03-29 12:00:00.000','BUY','SELL','Euro',1000)
,('0002','2019-03-29 12:00:00.000','SELL','BUY','Euro',1000)
,('0003','2019-03-29 12:00:00.000','SELL','BUY','Euro',1000)
,('0004','2019-03-29 12:00:00.000','BUY','SELL','Euro',1000)
,('0005','2019-03-29 12:00:00.000','BUY','SELL','Euro',1000)
,('0006','2019-03-29 12:00:00.000','SELL','BUY','Euro',1000)
,('0007','2019-03-29 12:00:00.000','SELL','BUY','Euro',1000)
;

INSERT INTO bc_quotes (quote_timestamp,currency,unit,quote_type,"quote") VALUES  ('2019-02-25 09:00:00.000','Euro',1,'SELL',1.1375) ,('2019-02-25 09:00:00.000','Euro',1,'BUY',1.1355) ,('2019-03-21 09:00:00.000','Euro',1,'SELL',1.1416) ,('2019-03-21 09:00:00.000','Euro',1,'BUY',1.1392) ,('2019-03-29 12:00:00.000','Euro',1,'BUY',1.1225) ,('2019-03-29 12:00:00.000','Euro',1,'SELL',1.1246) ;

我正在寻找以下结果:

• 交易编号

• 数量

• sell_price(找出开盘交易或平盘交易中哪一个是 SELL cust_txn。将该交易的货币、时间戳和 exch_txn_type 与 bc_quotes 表中的货币、时间戳和 quote_type 匹配并选择报价)

• 买入价(找出开盘价或收盘价中的哪一个是买入 csut_txn。将货币、时间戳和 exch_txn_type 与 bc_quotes 表中的货币、时间戳和 quote_type 匹配并选择报价)

Answer 1

我的回答是假设 opening_txns 和 closing_txns 的列属于同一类型。

请尝试以下方法并告诉我它是否适合您:

WITH txns AS (
  SELECT
    txn_id,
    amount,
    currency,
    timestamp,
    exch_txn_type
  FROM opening_txns
  UNION
  SELECT
    txn_id,
    amount,
    currency,
    timestamp,
    exch_txn_type
  FROM closing_txns
)
SELECT
  txn_id,
  amount,
  CASE WHEN cust_txn_type = 'SELL' THEN quote ELSE NULL END AS sell_price,
  CASE WHEN cust_txn_type = 'BUY' THEN quote ELSE NULL END AS buy_price
FROM txns T
  LEFT JOIN bc_quotes Q 
    ON (T.currency = Q.currency AND T.timestamp = Q.timestamp AND T.exch_txn_type = Q.quote_type);

解释:

• txns 是一个 common table expression帮助澄清查询。
• 由于 opening_txns 和 closing_txns 共享相同的列，您可以使用 UNION 有效地将两个结果集合并为一个txns 结果集。
• 然后你可以使用LEFT JOIN来匹配行txns 使用中提供的条件将其各自的报价ON 子句。
• 最后，您可以使用条件 CASE 语句在SELECT中区分'SELL'和'BUY交易；如果交易是 'BUY'(resp. 'SELL')，则buy_price 列将是 quote 而 sell_price 将是NULL。

最终的结果集有以下列:txn_id、amount和sell_price、buy_price。

希望对您有所帮助。