时间戳

首页 > 解决方案 > 获取到期后续订套餐的用户

sql - 获取到期后续订套餐的用户

问题描述

我有一张表格，其中提供了有关哪些用户购买了包裹、开始时间、过期等信息。

桌子：

+------+--------------+------------+------------+---------+
| user | Package_Type |   Start    |    End     | Status  |
+------+--------------+------------+------------+---------+
|  111 | A            | 2019-06-01 | 2019-06-18 | Valid   |
|  123 | A            | 2019-06-01 | 2019-06-18 | Valid   |
|  124 | A            | 2019-06-02 | 2019-06-06 | Expired | //Type A expired
|  124 | C            | 2019-06-02 | 2019-06-18 | Valid   |
|  125 | B            | 2019-06-03 | 2019-06-09 | Expired |
|  126 | B            | 2019-06-04 | 2019-06-20 | Valid   |
|  127 | B            | 2019-06-04 | 2019-06-07 | Expired |  // Type B expired
|  124 | B            | 2019-06-06 | 2019-06-18 | Valid   |  //Renewal to type B after expiry of type A
|  127 | B            | 2019-06-08 | 2019-06-18 | Valid   |  // Renwal to Type B
+------+--------------+------------+------------+---------+

在一个时间范围内（让 timeframe be WHERE start BETWEEN '2019-06-01' AND '2019-06-10'）我想知道有多少用户的包过期已经更新了它。

预期输出如下

+------------+---------+---------+
|    Date    | Expired | Renewed |
+------------+---------+---------+
| 2019-06-01 |       0 |       0 |
| 2019-06-02 |       0 |       0 |
| 2019-06-03 |       0 |       0 |
| 2019-06-04 |       0 |       0 |
| 2019-06-05 |       0 |       0 |
| 2019-06-06 |       1 |       1 | // Expiry and renewal of user 124
| 2019-06-07 |       1 |       0 | // Expiry of user 127
| 2019-06-08 |       0 |       1 |//  Renewal of user 127
| 2019-06-09 |       1 |       0 |// Expiry of user 125
| 2019-06-10 |       0 |       0 |
+------------+---------+---------+

用户不必更新同类型的包。

  1. 对于用户 124，类型 A 已过期，但他更新为类型 B。（另请注意，用户 124 对于类型 C 仍处于活动状态）。
  2. 因此，可能存在某种类型的包过期的情况，但同一用户可以在其他包上处于活动状态，并且他可以续订一些不同的包本身。
  3. 我只想知道用户续订时套餐到期后。（即使他在任何其他套餐上都处于活动状态）

更新 1 我还希望将上述数据表示为

+--------------+---------+---------+
| Package_Type | Expired | Renewed |
+--------------+---------+---------+
| A            |       1 |       0 |
| B            |       2 |       2 |
| C            |       0 |       0 |
+--------------+---------+---------+

我想要一个查询，如果我改变 WHERE start BETWEEN '2019-06-01' AND '2019-06-10')*

上表将根据start过滤器更新

SQL Server 链接

标签： sqlsql-server

解决方案

您可以使用累积max()来获取已续订的用户：

select t1.*,
       (case when max(case when status = 'expired' then [start] end) over (partition by user order by [start]) < [start] and
                  status = 'Valid'
             then 1 else 0
        end) as renewed
from table1 t1;

然后，您可以使用此信息来汇总数据。然而，这个总结是相当棘手的。续订在开始日期计算，但到期日期在到期日期。为了处理这个问题，它使用apply.

select dte, sum(is_expire), sum(is_renew)
from (select t1.*,
             (case when max(case when status = 'expired' then [start] end) over (partition by user order by [start]) < start and
                        status = 'Valid'
                   then 1 else 0
              end) as renewed
      from table1 t1
     ) t1 cross apply
     (values (start, 0, renewed),
             ([end], case when status = 'expired' then 1 else 0 end, 0)
     ) v(dte, is_expire, is_renew)
group by dte
order by dte;

这是一个 SQLFiddle。

推荐阅读