查找具有定义结束的连续相同值的行组(sql红移)

mo49yndu  于 2021-08-09  发布在  Java
关注(0)|答案(1)|浏览(278)

我有一个表,其中包含用户在任何给定日期的订阅状态。数据是这样的

+------------+------------+--------------+
| account_id |    date    | current_plan |
+------------+------------+--------------+
| 1          | 2019-08-01 | free         |
| 1          | 2019-08-02 | free         |
| 1          | 2019-08-03 | yearly       |
| 1          | 2019-08-04 | yearly       |
| 1          | 2019-08-05 | yearly       |
| ...        |            |              |
| 1          | 2020-08-02 | yearly       |
| 1          | 2020-08-03 | free         |
| 2          | 2019-08-01 | monthly      |
| 2          | 2019-08-02 | monthly      |
| ...        |            |              |
| 2          | 2019-08-31 | monthly      |
| 2          | 2019-09-01 | free         |
| ...        |            |              |
| 2          | 2019-11-26 | free         |
| 2          | 2019-11-27 | monthly      |
| ...        |            |              |
| 2          | 2019-12-27 | monthly      |
| 2          | 2019-12-28 | free         |
| 3          | 2020-05-31 | monthly      |
| 3          | 2020-06-01 | monthly      |
| 4          | 2019-08-01 | yearly       |
| ...        |            |              |
| 4          | 2020-06-01 | yearly       |
+------------+------------+--------------+

我想有一个表格,提供订阅的开始和结束日期。它看起来像这样。请注意,重要的是
account_ids 3 以及 4 未包含在此表中,因为截至今天(2020年6月1日),它们仍在订阅中。我只想要一份从订阅中搬出来的人的摘要。

+------------+------------+------------+-------------------+
| account_id | start_date |  end_date  | subscription_type |
+------------+------------+------------+-------------------+
|          1 | 2019-08-03 | 2020-08-02 | yearly            |
|          2 | 2019-08-01 | 2019-08-31 | monthly           |
|          2 | 2019-11-27 | 2019-12-27 | monthly           |
+------------+------------+------------+-------------------+

目前,我有以下这是非常接近,但仍然给我的用户没有退出订阅

select account_id, current_plan, min(date), max(date)
from (select d.*,
             row_number() over (partition by account_id order by date) as seqnum,
             row_number() over (partition by account_id, current_plan order by date) as seqnum_2
      from data d
     ) d
where current_plan not in ('free', 'trial')
group by account_id, current_plan, (seqnum - seqnum_2);
mwecs4sa

mwecs4sa1#

如果您想对目前已退出的用户进行非常简单的筛选,只需添加: having max(date)<current_date 但这也将包括以前的余波,如用户\u id=2的第一个余波
但是如果你想向前看(比如userid=1)并且只过滤掉最后的余波,你就需要有一个更好的“缺口和孤岛”查询 lag 功能,如果你检查更多的“差距和岛屿”的解决方案,你会发现它。。。一般来说, lag(currrent_plan) over (partition by id order by date) 给你前一天的计划,这样你就可以确定辐射日期,然后在同一个窗口排序,得到每个id的最后一个

相关问题