时间戳

首页 > 解决方案 > 如何在查询中链接两条记录

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

问题描述

我想共同计算利润，其中两种交易类型（放置（1）和赢得（2）或取消（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时间范围之前放置的位置开始计算。

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

我不知道如何通过参考 ID 链接每笔交易，非常感谢任何帮助，谢谢！

标签： sqlpostgresql

解决方案

使用相关子查询

演示

 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

推荐阅读