时间戳

首页 > 解决方案 > 计算回收期

sql - 计算回收期

问题描述

我想计算客户达到“回报期”所需的时间。也就是说，当他们有效地在我们的商店花费了足够的钱来抵消该特定客户的广告成本时。

Customers
| id | created_at | acquisition_cost |
| 3 | 2019-03-04 20:24:49 | 39.90 |

Orders
| id | customer_id | created_at | profit |
| 39 | 3 | 2019-04-03 05:39:39 | 19.91 |
| 86 | 3 | 2019-06-03 07:29:18 | 17.39 |

我想知道的是，Postgresql 中是否有一种高效的方法可以找到使客户超过 39.90created_at的订单（由 订购） 。created_at ASCacquisition_cost

标签： sqlpostgresqle-commerce

解决方案

您可以使用累积总和：

select c.*,
       sum(o.profit) over (partition by o.customer_id order by o.created_at) as running-profit
from orders o join
     customers c
     on o.customer_id = c.id;

如果你想要第一次约会，那么：

select distinct on (id) oc.*
from (select c.*, o.created_at as order_created_at,
             sum(o.profit) over (partition by o.customer_id order by o.created_at) as running_profit
      from orders o join
           customers c
           on o.customer_id = c.id
     ) oc
where running_profit >= c.acquisition_cost
order by id, order_created_at

推荐阅读