时间戳

首页 > 解决方案 > 可变滞后长度的困难

mysql - 可变滞后长度的困难

问题描述

为这个问题的措辞道歉，但我不知道如何描述这个问题。

我有俱乐部会员的数据，如下


| id  | start               | end                 | cancelled           |
| --- | ------------------- | ------------------- | ------------------- |
| 1   | 2020-01-01 00:00:00 | 2020-12-31 00:00:00 | 2021-01-10 00:00:00 |
| 1   | 2021-02-01 00:00:00 | 2022-01-31 00:00:00 |                     |
| 2   | 2021-01-01 00:00:00 | 2020-12-31 00:00:00 |                     |
| 3   | 2020-01-01 00:00:00 | 2020-06-30 00:00:00 | 2020-07-01 00:00:00 |
| 3   | 2020-07-10 00:00:00 | 2021-01-09 00:00:00 | 2021-01-31 00:00:00 |
| 3   | 2021-02-02 00:00:00 | 2021-08-01 00:00:00 |                     |
| 4   | 2018-04-28 00:00:00 | 2019-04-28 00:00:00 | 2020-01-03 00:00:00 |
| 4   | 2019-03-07 00:00:00 | 2021-08-01 00:00:00 |                     |
| 4   | 2020-02-22 00:00:00 | 2021-02-22 00:00:00 |                     |

兴趣集中在会员资格被取消和新会员资格被取消之间的时间。我有以下代码，在 Gordon Linoff 对我上一个问题的回答中提供

select t.*,
       datediff(start, prev_cancelled) as num_days_since_cancel
from (select dt.*,
             lag(cancelled) over (partition by id order by start) as prev_cancelled
      from dt
     ) t
where prev_cancelled is not null;

产生以下内容：


| id  | start               | end                 | cancelled           | prev_cancelled      | num_days_since_cancel |
| --- | ------------------- | ------------------- | ------------------- | ------------------- | --------------------- |
| 1   | 2021-02-01 00:00:00 | 2022-01-31 00:00:00 |                     | 2021-01-10 00:00:00 | 22                    |
| 3   | 2020-07-10 00:00:00 | 2021-01-09 00:00:00 | 2021-01-31 00:00:00 | 2020-07-01 00:00:00 | 9                     |
| 3   | 2021-02-02 00:00:00 | 2021-08-01 00:00:00 |                     | 2021-01-31 00:00:00 | 2                     |
| 4   | 2019-03-07 00:00:00 | 2021-08-01 00:00:00 |                     | 2020-01-03 00:00:00 | -302                  |

这是除 id 4 之外的所有对象的期望输出，它应该返回 50 天，而不是 -302。我明白为什么会这样做，因为滞后 1，所以在 id 4 的情况下，我们需要滞后 2，因为在第一个成员被取消之前，第二个成员还没有结束，但是我找不到方法去做这个。此外，尽管这是一种极端情况，但没有理由在第一个被取消之前不应该有 2 个或更多后续未过期的成员资格。

也许更好的方法是以某种方式过滤掉在第一个成员资格之后开始并在第一个成员资格被取消之后到期的任何未过期的成员资格。或者也许有更好的方法？玩具数据：

CREATE TABLE IF NOT EXISTS `dt` (
  `id` int(6) unsigned NOT NULL,
  `start` TIMESTAMP,
  `end` TIMESTAMP,
  `cancelled` TIMESTAMP NULL
) DEFAULT CHARSET=utf8;
INSERT INTO `dt` (`id`, `start`, `end`, `cancelled`) VALUES
  ('1', '2020-01-01', '2020-12-31', '2021-01-10'),
  ('1', '2021-02-01', '2022-01-31', NULL ),
  ('2', '2021-01-01', '2020-12-31', NULL ),
  ('3', '2020-01-01', '2020-06-30', '2020-07-01'),
  ('3', '2020-07-10', '2021-01-09', '2021-01-31'),
  ('3', '2021-02-02', '2021-08-01', NULL ),
  ('4', '2018-04-28', '2019-04-28', '2020-01-03' ),
  ('4', '2019-03-07', '2021-08-01', NULL ),
  ('4', '2020-02-22', '2021-02-22', NULL )

标签： mysqlsql

解决方案

max()您可以改为对取消使用累积：

select t.*,
       datediff(start, prev_cancelled) as num_days_since_cancel
from (select dt.*,
             max(cancelled) over (partition by id order by start) as prev_cancelled
      from dt
     ) t
where prev_cancelled is not null;

这将回到过去的NULL价值观。它确实假设cancelled日期是按start它们不是的时间排序的NULL。这似乎是一个合理的假设。

推荐阅读