连接和聚合，其中右表中的日期 <= 左表中的日期

Question

假设我有一个表，它有两列——customer_id和date:

customer_id          date
1    2020-01-29
1    2020-03-14
2    2020-04-05
2    2020-02-18

我有另一张表，显示了客户在上购买的日期和金额

customer_id          date    amount
1    2019-12-03        10
1    2020-01-30        20
1    2020-03-10        30
1    2020-03-18        40
2    2020-02-29        50 
2    2020-03-10        60
2    2020-04-01        70

我现在想把第一个表和第二个表连接起来，创建一个新列，显示第一个表每一行迄今为止的采购总额：

customer_id          date    amount_td
1    2020-01-29           10
1    2020-03-14           60
2    2020-04-05          180
2    2020-03-20          110    

我该怎么做？我最初的逻辑想法是：

SELECT
table1.customer_id,
table1.date,
table2_agg as amount_td
FROM
table1
LEFT JOIN (
SELECT
customer_id,
SUM(amount)
FROM
table2
HAVING
table2.date <= table1.date
) table2_agg
ON
table1.customer_id = table2_agg.customer_id

当然，这在句法和逻辑上都不完全存在。

Answer 1

由于需要在table2中基于table1中的date进行聚合，因此作为相关子查询编写可能比JOIN:更容易

SELECT
table1.customer_id,
table1.date,
COALESCE((SELECT SUM(amount) AS amount 
FROM table2
WHERE table2.date <= table1.date AND
table2.customer_id = table1.customer_id), 0) AS amount
FROM
table1

或者，您可以将其写为JOIN，但聚合需要发生在查询的顶层：

SELECT
table1.customer_id,
table1.date,
COALESCE(SUM(table2.amount), 0) AS amount
FROM
table1
LEFT JOIN 
table2 ON table2.customer_id = table1.customer_id
AND table2.date <= table1.date
GROUP BY table1.customer_id, table1.date

无论哪种情况，输出都是：

customer_id     date        amount
1               2020-01-29  10
1               2020-03-14  60
2               2020-04-05  180
2               2020-03-20  110

dbfiddle 演示

Answer 2

如果您的(未公开的(DBMS支持横向联接，则可以执行以下操作：

select t1.*, t.amount
from table1 t1
left join lateral (
select t2.customer_id, sum(t2.amount) as amount
from table2 t2
where t2.customer_id = t1.customer_id
and t2.date <= t1.date
group by t2.customer_id
) as t on true

在线演示