查找一段时间内的固定总和的mysql查询

Question

我有一个包含以下列的mysql表

order_id customer_id total_amount date_added

现在，我想询问一位特定客户，了解该客户在其订单历史记录的六个月内是否下了超过或等于12000美元的订单。这段时间不是最近的。

对此有什么解决方案吗？

问候你，迪鹏

Answer 1

select sum(total_amount) >= 12000 over_12000
from mytable
where customer_id = nnn
and date_added between 'YYYY-MM-DD' and date_add('YYYY-MM-DD', interval 6 month)

将nnn替换为特定的客户ID，并将YYYY-MM-DD替换为您感兴趣的6个月期间的开始。

更新：

这将使用客户最后一次订单之前的6个月时间：

select sum(total_amount) >= 12000 over_1200
from mytable join (select max(date_added) last_date
                   from mytable
                   where customer_id = nnn) last
where customer_id = nnn
and date_added between last_date and date_sub(last_date, interval 6 month)

更新2：

select m1.date_added end_date, sum(m2.total_amount) >= 12000 
from mytable m1 join mytable m2 on m2.date_added between date_sub(m1.date_added, interval 6 month) and m1.date_added and m1.customer_id = m2.customer_id
where m1.customer_id = nnn
group by end_date
order by end_date