postgresql - 如何在 Postgresql 中查找特定日期之前的第一个和最后一个日期？

Question

我是一个 SQL 初学者。我很难找到这个问题的答案

For each customer_id who made an order on January 1, 2006, what was their historical (prior to January 1, 2006) first and last order dates?

我尝试使用子查询来解决它。但我不知道如何找到 1 月 1 日之前的第一个和最后一个订单日期。

表A的列:

customer_id            
order_id              
order_date   
revenue  
product_id

表B的列:

product_id  
category_id

SELECT customer_id, order_date FROM A
(
     SELECT customer_id FROM A
     WHERE order_date = ‘2006-01-01’
)
WHERE ...

Answer 1

实际上有两个子查询。第一个是“每个在 2006 年 1 月 1 日下订单的 customer_id”，第二个是“他们的历史(2006 年 1 月 1 日之前)第一个和最后一个订单日期”

所以，首先:

select customer_id from A where order_date = '2006-01-01';

第二个:

select customer_id, min(order_date) as first_date, max(order_date) as last_date
from A
where order_date < '2006-01-01' group by customer_id;

最后，您只需要从第二个子查询中获取第一个子查询中存在的那些客户:

select customer_id, min(order_date) as first_date, max(order_date) as last_date
from A as t1
where 
    order_date < '2006-01-01' and
    customer_id in (
        select customer_id from A where order_date = '2006-01-01')
group by customer_id;

或者，可以更有效:

select customer_id, min(order_date) as first_date, max(order_date) as last_date
from A as t1
where 
    order_date < '2006-01-01' and
    exists (
        select 1 from A as t2
        where t1.customer_id = t2.customer_id and t2.order_date = '2006-01-01')
group by customer_id;