mysql - SQL 中的保留分析

Question

我正在尝试分析首次购买后 30 天内购买的用户。我不断得到

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':: date) - integer '30' AND i.customer_id = o.customer_id group by o.custome' at line 7

这是我运行的代码。

select 
o.customer_id, 
o.purchase_time
from orders as o
join orders as i on 
i.purchase_time <= o.purchase_time AND
i.purchase_time >= (o.purchase_time :: date) - integer '30' AND
i.customer_id = o.customer_id
group by o.customer_id, o.purchase_time;

Answer 1

我怀疑这就是你想要的，MySQL 语法:

select o.*
from orders o
where exists (select 1
              from orders o2
              where o2.customer_id = o.customer_id and
                    o2.purchase_time > o.purchase_time and
                    o2.purchase_time <= purchase_time + interval 30 day
            );