时间戳

首页 > 解决方案 > 从当前和以前的记录中选择数据

mysql - 从当前和以前的记录中选择数据

问题描述

我希望我的查询返回“流失”帐户的列表，这意味着account_ids下表中存在最新记录为plan_id = 4. 这表示曾经支付的帐户不再支付或“搅动”。

有了这个，我需要包含该帐户的先前值plan_id并计算该帐户先前的月收入损失price。

这是我加入我正在使用的两个表时架构的示例。（您会注意到plan_id = 4具有NULL元数据的帐户，因为它们没有收入。）：

| account_id | plan_id | start_date           | plan_interval | price  | id     |
|------------|---------|----------------------|---------------|--------|--------|
| 2          | 1       | 2018-01-03T14:52:13Z | month         | 39     | 1      |
| 1          | 3       | 2018-02-07T11:10:17Z | year          | 999    | 3      |
| 1          | 2       | 2018-02-07T11:11:17Z | month         | 99     | 2      |
| 2          | 4       | 2018-03-25T07:09:00Z | (null)        | (null) | (null) |

我希望输出是这样的，按monthand分组previous_plan_id：

| month | previous_plan_id | num_churned | monthly_revenue_churned (mrr) |
|-------|------------------|-------------|-------------------------------|
| 3     | 3                | 1           | 83.25                         |

到目前为止，我已经编译了这个查询。plan_id当一个account_id移动到时，我仍然需要拉上前一个plan_id = 4，以及price那个流失帐户的前一个（计算为月收入或“mrr”）。

SELECT
MONTH(s.start) AS month,
s.plan_id,
COUNT(*) AS num_churned,
SUM(CASE WHEN p.plan_interval = 'month'
     THEN p.price * 0.01 
     ELSE (p.price * 0.01)/12 END) AS mrr
FROM subscriptions s
  LEFT JOIN plans p
  ON p.id = s.plan_id
WHERE s.start = (SELECT MAX(s2.start)
                  FROM subscriptions s2
                  WHERE s.plan_id = 4) 
      AND s.school_id > 1 IN (SELECT COUNT(DISTINCT s.school_id)
                          FROM subscriptions)
GROUP BY month, s.plan_id;

我认为也许一个LAG子句会起作用，但我的问题是：我可以在这个查询中添加什么来实现所需的输出？

这是一个例子：sqlfiddle

标签： mysqlsql

解决方案

我不确定你的计算到底应该是什么——你小提琴中的数据与问题中的数据不匹配。但是你可以这样做：

select s.*, p.price / 12
from (select s.*,
             lag(plan_id) over (partition by account_id order by start_date) as prev_plan_id,
             lag(start_date) over (partition by account_id order by start_date) as prev_start_date
      from subscriptions s 
     ) s join
     plans p
     on s.prev_plan_id = p.id
where plan_id = 4 and prev_plan_id <> 4;

这是一个 db<>fiddle，它支持更新版本的 MySQL。

推荐阅读