sql - 如何链接查询中的两条记录

Question

我想共同计算利润，其中两种交易类型(placed(1) 和 won(2) 或 cancel(3) )都在指定时间段内，或者只有下单交易。

这是表架构:

CREATE TABLE transactions (
    transaction_id integer,
    reference_id integer,
    customer_id integer,
    amount integer,
    transaction_date date,
    transaction_type integer

);

具有以下数据:

INSERT INTO transactions 
 VALUES
(1,1, 100, 8,'2019-01-04',1),
(2,1, 100, 12,'2019-01-05',2),
(3,2, 100, 20,'2019-01-05',1),
(4,2, 100, 20,'2019-01-06',3),
(5,3, 101, 11,'2019-01-05',1),
(6,3, 101, 32,'2019-01-05',2),
(7,4, 102, 7,'2019-01-04',1),
(8,4, 102, 14,'2019-01-06',2),
(9,5, 102, 8,'2019-01-02',1),
(10,5, 102, 8,'2019-01-04',3),
(11,6, 102, 20,'2019-01-06',1),
(12,7, 103, 25,'2019-01-06',1),
(13,8, 103, 10,'2019-01-06',1),
(14,9, 103, 5,'2019-01-01',1),
(15,10, 103, 40,'2019-01-06',1);

以及尝试的查询:

select customer_id, sum(won-placed+cancel) as profit
from
(select customer_id, 
 sum(case when transaction_type = 1 then amount else 0 END) AS placed,
 sum(case when transaction_type = 2 then amount else 0 END) AS won,
 sum(case when transaction_type = 3 then amount else 0 END) AS cancel
 from transactions
 where transaction_date > '2019-01-04'

group by 1) x

group by 1 order by 1

在这种情况下，例如对于客户 100，利润应该等于 0，因为应该只从 reference_id = 2 开始计算，因为 transaction_id = 1 是在给定时间之前放置的范围。

客户 200，利润应该是 -20，因为在给定时间范围后只有一个 transaction_type = 1。

我不知道如何通过其引用 ID 链接每笔交易，非常感谢任何帮助，谢谢!

Answer 1

使用相关子查询

DEMO

 select customer_id, sum(won-placed+cancel) as profit
from
(select customer_id, 
 sum(case when transaction_type = 1 then amount else 0 END) AS placed,
 sum(case when transaction_type = 2 then amount else 0 END) AS won,
 sum(case when transaction_type = 3 then amount else 0 END) AS cancel
 from transactions a
 where transaction_date > '2019-01-04' and 
 exists  (select 1 from transactions b where a.customer_id=b.customer_id
and  b.transaction_date > '2019-01-04')
and  not exists 
     (select 1 from transactions c where 
     a.customer_id=c.customer_id and transaction_date < '2019-01-05' 
     and a.reference_id=c.reference_id)
group by 1) x
group by 1 order by 1

输出:

customer_id profit
   100           0
   101          -21
   102          -6
   103          -75